How to Build a Loan Amortization Schedule in Excel
A loan schedule shows, month by month, how each payment splits between interest and principal and how the balance falls to zero. Excel can build one, but it's a fiddly grid of formulas you have to wire up just right. Here's the quick way.
Last updated: September 2026
| Period | Payment | Principal | Interest | Balance |
|---|---|---|---|---|
| 1 | 193.33 | 93.33 | 100.00 | 9,906.67 |
| 2 | 193.33 | 94.26 | 99.07 | 9,812.41 |
Method 1 — formulas
- Level payment:
=PMT(rate/12, months, -principal). - Each row: interest
=balance*rate/12, principal=payment-interest, new balance=balance-principal. - Correct, but you build the grid by hand every time and it breaks if you add extra payments.
Method 2 — the 1-click way (Tellsheet)
Amortization Schedule
Enter the principal, annual rate, term, and payment frequency, and Amortization Schedule writes the full table to a new sheet — payment, principal, interest, and balance for every instalment, with the total interest in the summary. Add an extra payment per period and it recalculates a shorter payoff automatically. Zero-interest loans divide evenly; the final payment adjusts to land the balance exactly on zero. Pairs with Date Table for dating each instalment.
Frequently asked questions
How do I create an amortization schedule?
PMT plus a per-row interest/principal split, or Amortization Schedule in one click.
Can I model extra payments?
Yes — an extra amount each period pays the loan off early.
Does it show total interest?
Yes — in the summary, plus per-period interest.
Monthly only?
No — weekly, fortnightly, quarterly, or annual too.
Related Excel guides
Build a loan schedule in one click
Amortization Schedule writes payment, principal, interest, and balance for every period.
Get Tellsheet free See pricing