Financial
Loan amortization schedule: reconcile payment, interest, principal, and ending balance
Combine PMT, IPMT, PPMT, and FV to build a self-balancing loan amortization schedule that amortizes cleanly to zero.
Four linked columns that must reconcile every month
Every row of an amortizing loan schedule tracks four numbers: Total Payment = -PMT(r, N, PV), Interest = -IPMT(r, per, N, PV), Principal Retired = -PPMT(r, per, N, PV), and Ending Balance = Opening Balance - Principal Retired.
Using positive display signs (negating PMT/IPMT/PPMT) makes schedule columns intuitive to read while ensuring Ending Balance hits $0.00 on the final installment.
Assume a constant rate, no fees or taxes, and the stated compounding schedule. The loan example uses month-end payments. Effective annual rates require conversion rather than direct division by the number of periods. Real payment schedules can include rounding adjustments.
| LoanInput (A) | Value (B) | Month 1 Output |
|---|---|---|
| Loan Amount (PV) | 12000 | Payment: $1,032.80 |
| Annual Rate | 0.06 | Interest: $60.00 | Principal: $972.80 |
| Term Months | 12 | Month 1 Ending Bal: $11,027.20 |
Calculate Month 1 ending balance and verify final payoff
On $12,000 at 6% nominal annual interest over 12 months (0.5%/month), Month 1 payment is $1,032.80 ($60.00 interest + $972.80 principal), reducing the balance to $11,027.20.
=ROUND(B1 + PPMT(B2/12, 1, B3, B1), 2)Check remaining balance after any period with FV
FV(B2/12, 12, PMT(B2/12, B3, B1), B1) computes the remaining balance at Month 12 directly—giving zero after rounding to cents in this idealized schedule.
=ROUND(-FV(B2/12, B3, PMT(B2/12, B3, B1), B1), 2)Try the example
Use the original values or change them to test your understanding.