Excel templates / Loan amortization schedule
Loan amortization schedule template
The monthly payment for a loan, and a row for every payment showing interest, principal and the balance left, for up to 30 years.
Download the loan amortization schedule template (.xlsx). Free, no sign-up. Works in Excel 2010 and later, Excel for Mac, Excel on the web, Google Sheets and LibreOffice.
How to use it
- Enter the loan amount, the annual interest rate, the term in years and the date of the first payment in the yellow cells,
B3toB6. - The monthly payment appears in
B8, and the schedule below fills in one row per payment: up to 360 payments, a 30-year mortgage. Rows past the last payment stay blank. - The balance in the last row comes to $0.00: the loan is paid off.
How it works
B8=-PMT(B4/12,B5*12,B3): The monthly payment. PMT takes the monthly rate (the yearly rate ÷ 12), the number of monthly payments and the amount borrowed. It returns money going out as a negative number, so the minus sign makes it positive.A15:A373=IF(A14="","",IF(A14<$B$9,A14+1,"")): Counts the payments 1, 2, 3… and stops after the last one, so a shorter loan leaves the rest of the schedule blank. Every other column checks this number first.B14:B373=IF(A14="","",EDATE($B$6,A14-1)): Each payment’s date: the first payment date plus one month per payment. EDATE adds whole months.D15:D373=IF(A15="","",F14*$B$4/12): This month’s interest: the balance left after last month’s payment × the monthly rate. The $ signs keepB4fixed as the formula is copied down.E15:E373=IF(A15="","",C15-D15): The part of the payment that pays down the loan: the payment minus the interest. It grows every month.F15:F373=IF(A15="","",MAX(0,F14-E15)): The balance still owed: last month’s balance minus this month’s principal. MAX(0, …) stops a leftover fraction of a cent from showing as -$0.00 at the end.B10=SUM(D14:D373): Total interest over the life of the loan. SUM skips the blank rows.
Learn the skills behind it
- Loan payments and savings growth
- Build a loan amortization schedule
- Absolute references with $
- Date maths: gaps, renewals and month ends
- Nested IF and IFS
Functions used: PMT, EDATE, IF, MAX, SUM.