Loan amortisation: IPMT, PPMT and the balance

◈ 8 cards

Interest first, then principal, then what is left — build the schedule, and pull any period’s interest, principal and closing balance straight from =IPMT, =PPMT and =FV.

A level payment that is never split the same way twice

An amortising loan is repaid by a level payment — the PMT of Lesson 7.2 — and each payment is split into interest on the opening balance and principal, which is whatever is left. Because the balance falls, the interest falls, and the principal share rises: early payments are mostly interest, late ones mostly principal. The four-line recipe for each period:

interest = opening balance × $r$
principal = payment − interest
closing balance = opening balance − principal
next period's opening balance = this period's closing balance

Worked example — Ossington Brewing's $20,000

Ossington Brewing borrows $20,000 over 5 years at 6 %, repaid in equal year-end instalments.

The payment. =-PMT(0.06,5,20000) = 4,747.93.

Year 1. Interest 20,000 × 0.06 = 1,200.00; principal 4,747.93 − 1,200.00 = 3,547.93; balance 20,000 − 3,547.93 = 16,452.07.

Year 2. Interest 16,452.07 × 0.06 = 987.12; principal 3,760.80; balance 12,691.27.

And so on to year 5, where the interest is 268.75, the principal 4,479.18 equals the opening balance, and the closing balance is 0.00 — the check that the schedule is right. Total interest over the five years: 5 × 4,747.93 − 20,000 = 3,739.65.

Straight to any period

Excel will give any line without building the table. =IPMT(rate, per, nper, pv) returns the interest in payment number per; =PPMT the principal. Both return negatives for a positive pv, so negate:

=-IPMT(0.06,2,5,20000) = 987.12; =-PPMT(0.06,2,5,20000) = 3,760.80. They add to the payment.
=-IPMT(0.06,1,5,20000) = 1,200.00; =-PPMT(0.06,1,5,20000) = 3,547.93.

The balance after $k$ payments is the future value of the loan less the future value of the payments made — which is exactly what FV computes when both are entered: =-FV(0.06,2,-4747.93,20000) = 12,691.27. Read it as: the 20,000 has grown for two years, the two payments have grown too, and the difference is what is still owed. This is the fastest way to a mid-loan balance on an answer-only paper, and it does not need the schedule at all.

Past the midpoint

Because the early payments are mostly interest, the loan is not half repaid halfway through. Cumulative principal after year 2 is 7,308.73 — 36.5 % of the loan after 40 % of the payments; it first passes 10,000 in year 3 (11,295.18). On a 25-year mortgage the effect is far larger: the half-paid point is well past year 15. The code block below finds the crossing year from the schedule.

Same payment every year; the interest column falls and the principal column rises. The closing balance of 0.00 in year 5 is the check that the schedule is right.