Calculate loan amortization using the template
Let’s now see how to use this template to track a monthly loan payment.
Once you open the template, click enable editing at the top of the Excel sheet.
You can now edit the spreadsheet.
The template might seem daunting at first, but it’s really simple actually.
Let’s break down all the information given in this template
Loan Amount: Refers to the amount you have borrowed
Annual Interest Rate: The current annual interest rate
Loan Period in Years: The expected period of loan repayment in years
Start Date of Loan: The day the first loan payment will be made
Note that you only need to change these values under Loan Details. The calculator will automatically update the Loan Summary.
Monthly Payment: The loan amount you will be paying off every month
Number of Payments: Payment frequency to pay off the loan
Total Interest: Total interest to be paid on the loan
Total Cost of Loan: Total amount of loan to be paid after adding interest
You also have a table at the bottom. It offers insights into the payment amount, number, remaining balance, interest, and principal.
Now that you know all the values listed in the template let’s see how to use them.
Say we have borrowed a loan of $20,000, and the current annual interest rate is 7.60%. We anticipate paying off this loan in around six years, and the payment date of the first payment is 1/12/2022.
Let’s add all this information to the amortization table now.
As visible, the template has automatically updated the loan term and final payment. It shows a payment frequency of 12 months. This means you will be making payments on a monthly basis.
The values in the table have also been changed as follows:
You can use this to check when you need to make the next payment and its present value with interest.
How convenient is that? 😃
The only downside of this template is that editing the payment period is messy. By default, it shows monthly payments and not weekly or yearly.
Most of the time, this is not a big deal. But if you do need a weekly loan amortization schedule, you can create your own in minutes.
You can use the advanced PMT calculator for this purpose. And make sure your payments are set up just as you want. Learn more about it here.