We can create a loan amortization schedule using the PMT function which calculates the equal monthly payments on a loan taken for a specific period at a fixed interest rate per year. We now start creating a loan amortization schedule for a loan amount of 200000, loan period of 60 months or 5 years and an interest rate of 12.50%. First we calculate one equal monthly payment using the PMT function in cell B8. The rate of interest per year, the loan period and the loan amount is entered in cells B3, B4 and B5 respectively.
Next we write the header Pmt No. in cell B11. In cells B12 to B14 we enter the values 1, 2 and 3. Then we do an autofill till we have pmt no. 60.
In cell C11 we enter the header Payment Date. In cell C12, C13 and C14 we enter the date of our first, second and third payments 1/1/2010, 2/1/2010 and 3/1/2010. Our payments are made in the beginning of every month. This will reduce our interest payments slightly as compared to payments made at the end of the month or period. Next we select all the cells from C12 to C71 i. e. upto payment no. 60. Then we click on Fill in the Editing Group of the Home tab. We select ‘Series…’ from the drop-down menu. In the Series window that pos up we select ‘Date’ and ‘Month’ and click on OK. Now all the 60 payment dates are filled and the last payment date is 12/1/2014.
In cell D11 we enter the header Beginning Balance. In cell D12 we enter our loan amount by referencing the cell B5 as ‘=B5’ and the amount 200000 is automatically filled.
In cell E11 we enter the header Payment and in cell E12 we get the value of the first payment using absolute reference to cell ‘=$B$8’. Now our first amount is filled automatically.
In cell F11 we have entered the header Total Payment. Below it in cell F12 we enter ‘=$B$8’. This first total installment consists of part principal amount and the interest.on the loan.
We enter the headers Principal and Interest in cells G11 and H11 respectively. The interest is calculated using the formula ‘=D12*$B$3/12’. The value in D2 is 200000, the loan amount and ‘$B$3/12’ represents the interest rate for a month since we pay every month. This is equal to 12.5%/12. We get the value of 2083..33 in cell H12.
From the total payment for the month we deduct the interest due to the bank for that month and get the principal that we have paid back. The interest payment is what we pay the bank for the loan. Therefore we now deduct the principal paid amount from the total loan of 200000 and get the balance principal amount that we still have to pay to the bank. This value of 197583.75 is automatically calculated in cell I12 under the header Ending Balance in cell I11. This ending balance will become the ‘opening amount’ for the next payment and will be brought to cell D13 by referencing it as ‘=I12’. In cell J12 we calculate the ‘cumulative interest’. The cell J11 has the header ‘Cumulative Interest’.
We perform the same calculations again using the ending balance as opening balance for the next payment. Now we get a new ending balance which becomes the opening balance for the next month. We also calculate the cumulative interest.
We select the cells D14 to J14 and do an auto-fill to get the complete data for all the 60 months. You’ll notice that the ending balance in the 60th month is 0.00.
This is how we create a complete loan amortization schedule exactly as the banks do it!
View the Excel training video:
How do you create an amortization table using the Pmt function?