An amortization schedule shows each payment of a loan split into interest and principal, and the balance left after it. One row per period:
- Opening balance: the loan amount in period 1, then last period's closing balance.
- Payment: the same every period, from PMT.
- Interest = opening balance x the rate per period.
- Principal = payment - interest.
- Closing balance = opening balance - principal.
Early payments are mostly interest; late ones are mostly principal. If the schedule is right, the closing balance in the last period is exactly 0, which makes a handy check.
To pull one period out without the whole table, IPMT(rate, per, nper, pv) returns the interest in period per and PPMT the principal. CUMIPMT adds up the interest between two periods.
Extra payments go straight to principal, so the loan ends early. Cap the principal at what is still owed with MIN, or the last row pays too much.
Year Opening Payment Interest Principal Closing
1 10,000.00 3,880.34 800.00 3,080.34 6,919.66
2 6,919.66 3,880.34 553.57 3,326.76 3,592.90
3 3,592.90 3,880.34 287.43 3,592.90 0.00
A 3-year loan of 10,000 at 8%: the interest falls each year and the balance ends at exactly 0.