Building a basic interest amortization schedule is easy in Excel. The design provides a schedule that can change for different interest rates, payment amounts and terms, and can be set up to include an amount for additional payments.
Best of all, designing this useful schedule provides real world practice in using Functions in Excel, knowledge that can be used in other applications.
An amortization schedule computes the monthly payment for a loan, and shows the payoff by month, along with the principle and interest paid each month.
Calculating the Payment Amount
The user can customize the look of the spreadsheet by changing cells, but make sure the formulas used reflect the new cell references, or it will not calculate correctly. For any of these commands, if you are familiar with using the mouse in Excel, you can reference the cells by pointing and clicking, and get the same result as direct entry.
The first step is to create the titles and enter starting amounts for the appropriate amounts and columns.
- In cell C3 enter “Loan Amount.”
- In cell D3, enter the total loan amount desired.
- In cell C4 enter “Interest Rate.”
- In cell D4, enter the interest rate, in whole dollars. For instance, enter an annual interest rate of 8% as 8.
- In cell C5 enter “Term (Months).”
- In cell D5, enter the term in months. A five-year loan would be entered as 60.
- To calculate the payment, in cell C7, enter “Payment =”
- In cell D7, enter =PMT(D4/1200,D5,-D3). You can also use the function command, referencing the cells. Note that in dividing cell D4 by 1200, the user is converting the whole number interest rate to a monthly percentage.
Creating the Amortization Schedule
Create 6 columns, and label them as follows:
- Cell B10 – "Period Number", C10 - "Beginning Balance", D10 - "Additional Payment", E10 - "Interest Payment", F10 - "Principle Payment", G10 - "Ending Balance".
- In cell B12, enter the number 1, and below that number at least as many months as the number in cell C5.
- In cell C12, enter =D3. This places the beginning loan amount in the amortization schedule. In cell E12, enter =C12*$D$4/1200 to calculate the monthly interest In Excel, dollar signs keep the formula fixed on a certain cell, even if the formula is copied elsewhere.
- In cell F12, enter =$D$7-E12. The rest of the payment goes to principle.
- In cell G12, enter =C12-F12-D12. This is the ending balance.
- In the next row, column D13 must be =G12. The first month’s ending balance is the second months beginning balance.
- Copy the formulas in cells E12, F12 and G12 to E13, F13 and G13 respectively.
- Finally, copy the formulas in row 13 all the way down to the last month.
Once the spreadsheet is designed, a user can change the loan amount, interest rate or term at any time to see the impact on payment. An added feature is that the user can see the effect of additional payments by adding an amount into column D for any month.
Summary
Designing a practical spreadsheet is a great way to learn how spreadsheets work. The completed amortization schedule will give the user the ability to calculate interest payments and to measure progress toward payoff.
Join the Conversation