Loans & amortization — Excel guide
Loan math: the constant payment, its shifting interest/principal split, and the whole-life totals — everything an amortization schedule knows, one function call at a time.
PMT
The fixed periodic payment that retires a loan.
- The units rule again: monthly loan = rate/12 and years×12. Mixing them is the #1 finance-formula bug.
- A balloon goes in fv: PMT(rate/12, 84, 300000, -200000).
- type 1 (pay at period start) is the lease convention — slightly smaller payments.
- Total interest for the life: -PMT(...) × nper - pv.
IPMT
The INTEREST portion of one period's payment (PPMT = principal).
- Early payments are mostly interest; the split flips over the loan's life — which is why extra early principal punches above its weight.
- Remaining balance after n payments, no schedule needed: FV(rate/12, n, pmt, pv).
- Schedule columns: period, PMT, IPMT, PPMT, balance. If the balance doesn't walk to zero, the units are off.
CUMIPMT
Total interest across ANY window of periods, one call.
- CUMPRINC is the principal twin.
- The bi-weekly trick — half the payment every two weeks — sneaks in a 13th payment per year, all principal; years fall off the loan.
- Refinance break-even: closing costs ÷ monthly savings = months to stay put.