ModeldrillAmortization table
A loan amortization table has one row per period with opening balance, payment, interest, principal and closing balance. Payment = PMT(rate, nper, loan), interest = opening balance x rate, principal = payment - interest, closing = opening - principal. The closing balance reaches zero at the last payment.
Syntax
Interest = Opening x rate; Principal = Payment - Interest; Closing = Opening - Principal| Part | What it is |
|---|---|
rate | Rate per period (annual / 12 for monthly, / 4 for quarterly) |
nper | Number of periods |
Payment | Level payment from PMT, the same in every row |
Examples
Loan 100,000, 5%, 10 annual payments: payment 12,950.46
Year 1: interest 5,000.00, principal 7,950.46Result: Closing 92,049.54
Year 2: interest = 92,049.54 * 5%
principal = 12,950.46 - 4,602.48Result: Interest 4,602.48, principal 8,347.98, closing 83,701.56
Quarterly: loan 10,000, 6% annual, 8 quarters
=PMT(6%/4, 8, 10000)Result: -1,335.84; quarter 1 interest 150.00, principal 1,185.84, closing 8,814.16
Common mistakes
- A closing balance of a few cents instead of zero: caused by rounding payments; keep full precision or add a final-period adjustment.
- Hard-coding the payment in each row instead of referencing one PMT cell with $.
- Mixing an annual rate with monthly rows; the interest column must use the per-period rate.
Practice Amortization table
These drills check your answer on live data, free, no account needed.
- Quarterly amortization tableBuild the full amortization table for a loan repaid in equal quarterly payments over the quarters in B3. Fill all five columns for quarters 1 to 8: opening balance, payment (PMT), interest on the opening balance, principal (payment less interest) and closing balance. Write row 6, then fill down.
- Monthly payment for three loansWork out the level monthly payment for each loan with PMT. The rates are annual and the terms are in years, so convert both to months. Write E5 and fill down to E7. Show the payment as a positive number.
- Split payments with IPMT and PPMTFor the months listed in column A, split the monthly payment into interest (IPMT, column B) and principal (PPMT, column C) without building a full table. Column D adds them back up. Show all three as positive numbers.
Questions
Why does the interest portion fall each period?
Interest is charged on the remaining balance, which drops as principal is repaid. With a level payment, the principal portion rises by exactly the amount interest falls.
How do I handle extra principal payments?
Add an extra payment column: principal = scheduled principal + extra, closing = opening - principal, and let the table end early with MIN(closing, 0) guards.
Related
- PMT, IPMT and PPMTPMT gives the level loan payment; IPMT and PPMT split it into interest and principal for any period. Syntax, sign conventions and examples with exact results.
- Debt scheduleA debt schedule rolls each loan forward: closing = opening + draws - repayments, with interest on the opening balance. Layout, example, amortization, sweeps.
- Interest on the opening balanceInterest = rate x opening balance avoids a circular reference; rate x average balance is more accurate but circular. Both formulas, an example, when to use.
- DSCRDSCR = cash flow available for debt service / (interest + principal). How lenders compute it, exact examples, typical covenant levels and sizing debt from it.