Free Excel template
Free Excel loan amortization schedule template
For anyone with a car loan, personal loan or student loan who wants to see where each payment goes. Enter the amount, annual rate, term and first payment date; the template calculates the monthly payment with PMT and builds a 24-month schedule showing the interest, principal and remaining balance for every payment.
Preview and try it
This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Loan amortization schedule | ||||||
| 2 | Loan amount | 18000 | |||||
| 3 | Annual interest rate | 0.069 | |||||
| 4 | Term (months) | 24 | |||||
| 5 | First payment date | Dec 1, 2026 | |||||
| 6 | Monthly payment | 805.09 | |||||
| 7 | Total interest | 1322.18 | |||||
| 8 | Total of payments | 19322.18 | |||||
| 9 | No. | Payment date | Opening balance | Payment | Interest | Principal | Closing balance |
| 10 | 1 | Dec 1, 2026 | 18000 | 805.09 | 103.5 | 701.59 | 17298.41 |
| 11 | 2 | Jan 1, 2027 | 17298.41 | 805.09 | 99.47 | 705.62 | 16592.79 |
| 12 | 3 | Feb 1, 2027 | 16592.79 | 805.09 | 95.41 | 709.68 | 15883.11 |
| 13 | 4 | Mar 1, 2027 | 15883.11 | 805.09 | 91.33 | 713.76 | 15169.35 |
| 14 | 5 | Apr 1, 2027 | 15169.35 | 805.09 | 87.22 | 717.87 | 14451.48 |
| 15 | 6 | May 1, 2027 | 14451.48 | 805.09 | 83.1 | 721.99 | 13729.49 |
| 16 | 7 | Jun 1, 2027 | 13729.49 | 805.09 | 78.94 | 726.15 | 13003.34 |
| 17 | 8 | Jul 1, 2027 | 13003.34 | 805.09 | 74.77 | 730.32 | 12273.02 |
| 18 | 9 | Aug 1, 2027 | 12273.02 | 805.09 | 70.57 | 734.52 | 11538.5 |
| 19 | 10 | Sep 1, 2027 | 11538.5 | 805.09 | 66.35 | 738.74 | 10799.76 |
| 20 | 11 | Oct 1, 2027 | 10799.76 | 805.09 | 62.1 | 742.99 | 10056.77 |
How the formulas work
Monthly payment with PMT
PMT needs the rate per period, so the annual rate is divided by 12, and the number of payments, which is the term in months. The loan amount is entered as negative so the payment comes out positive (money you receive is positive, money you pay back is negative). ROUND keeps it to cents, like a real payment.
Interest for each month
Each month you pay interest on the balance you still owe: opening balance times the monthly rate. Early payments are mostly interest; as the balance falls, more of each payment goes to principal. Principal in F10 is simply payment minus interest.
Running balance
The closing balance is the opening balance minus the principal you paid. The next row picks it up as its opening balance (C11 is =G10), which chains the whole schedule together.
A final payment that lands exactly on zero
Because the payment is rounded to cents, a few cents can be left over at the end. On the last payment the formula pays whatever is still owed plus that month's interest, so the closing balance is exactly 0. Lenders do the same: the last payment is often slightly different.
Make it yours
- Longer loan? Select the last schedule row (row 33) and drag the fill handle down until you have one row per payment, then update the Total row ranges. A 5-year loan needs 60 rows.
- Check the result: Total of payments minus the loan amount should equal Total interest.
- To model extra payments, add an Extra column, subtract it in the principal formula (
=D10-E10+extra) and add an IF that stops payments once the balance reaches 0. - Your lender may calculate interest daily rather than monthly, so its figures can differ by a few cents. The monthly method here is the standard one for fixed-rate installment loans.
FAQ
What is the formula for a loan payment in Excel?
=PMT(annual_rate/12, number_of_months, -loan_amount). For $18,000 at 6.9% over 24 months that is =PMT(6.9%/12,24,-18000).
Why is the PMT result negative in Excel?
Excel uses a cash flow sign convention: money you receive is positive and money you pay is negative. Enter the loan amount as negative (or put a minus in front of PMT) to see a positive payment.
Can I use this for a mortgage?
Yes, the math is the same. Set the term to 360 months for a 30-year mortgage and extend the schedule to 360 rows. Taxes and insurance paid through escrow are not part of the PMT result, so add them separately.
Functions used
PMTLoan paymentROUNDRounds to digitsEDATESame day, months laterIFPicks a result from a testSUMAdds numbers
Related guides
- Absolute vs relative cell references in Excel
- ##### in Excel: why it happens and how to fix it
- How to find and fix a circular reference in Excel
More free templates
- Expense report templateFor employees who need to claim back business costs, and for the managers and bookkeepers who check those claims.Free .xlsx
- Purchase order templateFor small businesses, office managers and buyers who send purchase orders to suppliers and want to track what actually arrives.Free .xlsx
- Accounts receivable aging report templateFor owners, bookkeepers and credit controllers who invoice customers on terms and need to see who owes what and for how long.Free .xlsx
- Weekly employee shift schedule templateFor managers of shops, cafes, restaurants and small teams who build the rota in a spreadsheet.Free .xlsx
Updated