Every loan you’ll ever meet — mortgage, car finance, personal loan — is the same machine wearing different paperwork. Interest accrues on the balance; your payment covers the interest and chips at the rest. That’s it. The paperwork, though, shows you a monthly payment and an APR and hopes you’ll stop reading — while the machine’s most important behaviours stay out of sight.

An amortisation table is the machine with the cover off: one row per month, showing exactly where every pound of every payment goes. It takes ten minutes to build and it permanently changes how you read loan paperwork.

Usual honesty: this is for understanding credit, not choosing it — real agreements have fees and clauses the model can’t see.

Three functions do the arithmetic

Excel has the loan maths built in. For a £15,000 car loan at 7% over 5 years — inputs in named cells loan, rate, months:

=PMT(rate/12, months, -loan)        → £297.02 per month

(The minus sign is loan-convention: money you received is negative from the lender’s view. Feed PMT a positive loan and the answer just arrives negative — same number.)

Its siblings split any single month: IPMT gives the interest share of payment n, PPMT the capital share. You could stop here — but the table is where the understanding lives.

The table: one row per month

Columns: month, opening balance, interest, capital, closing balance. Row one starts at loan; every row after says:

interest  =B2 * rate/12
capital   =payment - C2
closing   =B2 - D2

Copy down months rows — fill-handle work — and the closing balance should land on zero at the final row (within a rounding whisker). If it doesn’t, an input is wrong: the model just audited the paperwork for you.

Now read what the paperwork didn’t show:

interest capital the crossover — before here, most of each payment is rent on the debt months → every bar is the same £297 — the split is what changes
The payment never changes; its meaning does. Early on you're mostly paying interest — which is why early overpayments punch so far above their weight.

Early payments are mostly interest. Month one of that car loan: £87.50 interest, £209.52 capital. The split improves every month — but on long loans the early years are heavily rent, which is why a 25-year mortgage’s first years barely dent the balance.

The true cost is the sum of a column. =SUM the interest column: £2,821 on that £15,000 loan. Not the APR, not a monthly drip — a single number you can weigh against alternatives, like the TCO stack it belongs inside if it’s a car.

Term is the expensive dial. Rebuild at 7 years and the payment falls to £226 — but total interest jumps to £4,003. “Lower monthly payment” usually means more months of interest, and the salesperson’s favourite dial is now one you can price.

What overpaying really does

Add an extra column and subtract it in the closing balance. Watch two things happen: the zero row arrives early, and — because every subsequent month’s interest is computed on a smaller balance — the interest column shrinks all the way down. A £50/month overpayment on that 5-year loan saves ~£360 of interest and ends it five months early; on a long mortgage the same experiment produces numbers people don’t believe until their own table shows them.

Whether overpaying beats investing the same £50 is its own model — this table is the machine both arguments stand on.

Build it once with real paperwork beside you. After that, every loan offer you ever read is just a row-one you can extend — and nobody gets to hope you’ll stop reading again.