Finance template
Loan Amortisation Schedule
PMT-driven schedule — change the amount, rate or term and all payments redo themselves.
- Live formulas
- 102
- Columns
- 5
- File
- 9 KB
$5
Checkout opening soonOr get it with all 66 paid templates in the complete pack — $45.
Works in Excel 2013 and later, Microsoft 365, Google Sheets and LibreOffice Calc. Set up to print on one page wide.
About this Loan Amortisation Schedule template
An amortisation schedule shows how each loan payment is split between interest and paying down what you owe. This Excel template works out the monthly payment with the PMT function, then lays out every payment with its interest, principal and remaining balance.
Change the loan amount, annual interest rate or term and the whole schedule rebuilds, along with the total interest and the total you will repay.
Good for
- →Checking a quoted loan payment before you sign
- →Comparing how a shorter term changes the total interest
- →Seeing how slowly the balance falls in the early years
What it works out for you
Result
- →Monthly payment
- →Total interest
- →Total repaid
What you fill in
The table has 5 columns. Calculated columns fill themselves in; the rest are yours to type over.
- →Payment #
- →Payment
- →Interest
- →Principal
- →Balance
Settings in the “Loan details” box
- →Loan amount
- →Annual interest rate
- →Term (months)
How to use it
Change the three shaded inputs below the table. Every payment row, the interest split and the totals recalculate from them.
The file opens with sample rows so you can see every formula working before you change anything. Type your own figures over them. When you need more rows, insert them inside the existing block rather than underneath it, so the totals keep covering every row.
Shaded, boxed cells below the table are settings the formulas read from. Change those first — every row that depends on them updates at once.
Functions it uses
Every one of these exists in Excel, Google Sheets and LibreOffice, so the file behaves the same wherever you open it.
IFIFERRORMAXMINPMTSUMQuestions about the Loan Amortisation Schedule
How is the monthly payment calculated?
With Excel's PMT function, using the annual rate divided by 12, the term in months and the loan amount — the standard fixed-payment calculation.
Why is so much of each early payment interest?
Interest is charged on the outstanding balance, which is highest at the start. As the balance falls, less of each payment goes on interest and more pays down the loan.
Does it handle overpayments?
No — it assumes the same payment every month. For extra monthly payments, the Mortgage Overpayment Calculator shows the interest saved and the months knocked off.
