Build a Loan Amortization Schedule in One Formula (No Helper Columns)

Okay, real talk: almost every loan amortization schedule in the wild is held together with hope. You type PMT once, work out interest and principal for row one, then drag the whole thing down 360 rows and call it a mortgage.

It looks authoritative. It even reconciles to the penny — right up until someone inserts a row to add a note, changes the term from 30 years to 15, or refinances halfway through. Then the ladder quietly comes apart, and the only symptom is that the last row reads -$0.02 instead of $0.00. Nobody investigates a two-cent difference.

Grab a coffee. We’ll build one that cannot come apart: the classic version that opens anywhere, then the Excel 365 one-liner that spills the entire schedule from a single cell and never needs dragging again.

Who this quietly bites

You, if you own any model with a repayment profile in it. Mortgages are the obvious case, but the same table sits inside lease schedules, bond amortisation, vendor financing, intercompany loans and every “can we afford this?” board paper.

The pattern is always the same. Somebody builds a schedule that is correct on the day it is built. Then it gets inherited. A row is inserted to flag a payment holiday, or the table is sorted by date because it looked untidy, or four rows are deleted because the term changed. Each of those actions silently repoints a relative reference, and the schedule keeps producing a full set of plausible numbers.

Here is what makes it worse than an ordinary bug: the error compounds down the table. One repointed reference on row 12 is wrong on row 12 and on all 348 rows after it, in a direction that gets larger, and the only place it surfaces is the final balance.

Why the copy-down version breaks

Every fixed-rate payment is the same total amount, but its makeup shifts every period. Early on, most of it is interest on a big balance; as the balance falls, more goes to principal. An amortization schedule is just that split, period by period, until the balance hits exactly zero on the final payment.

The usual build writes the running balance as “previous row minus this row’s principal” — =G4-E5, copied down. That relative reference is exactly the problem. Insert a row, sort the table, or delete a period and those G4/E5 links point somewhere else.

Here is the top of a $240,000 loan at 5.0% over 30 years:

Beautiful — until row 2 gets nudged and rows 3 to 360 start reading the wrong balance.

The fix, two ways

The scenario, in full. $240,000 borrowed at 5.0% nominal, repaid monthly over 30 years, first payment 1 February 2026, payments in arrears. Optionally, an extra $200 of principal every month.

Option 1 — Classic (backward-compatible functions, step by step)

PMT, IPMT and PPMT have shipped since Excel 97, so this version opens cleanly for any colleague, lender or auditor.

Step 1 — Put the inputs somewhere real and name them.

On an Inputs sheet: Loan_Amt (240000), Loan_Rate (5%), Loan_Years (30), Start_Date (1 Feb 2026), Extra_Pmt (200). Formulas → Define Name, one each. Named ranges are the difference between a formula you can audit and a formula you have to reverse-engineer.

Step 2 — The payment, once.

B3  =PMT(Loan_Rate/12, Loan_Years*12, -Loan_Amt)

$1,288.37 a month. The principal goes in negative so the payment comes back positive; sign conventions are the single most common reason a loan schedule comes out upside down.

Step 3 — Build the 360-row schedule.

Headers in row 8, data from row 9: Period, Payment date, Payment, Interest, Principal, Closing balance.

A9  =1
B9  =EDATE(Start_Date,A9-1)
C9  =$B$3
D9  =IPMT(Loan_Rate/12, A9, Loan_Years*12, -Loan_Amt)
E9  =PPMT(Loan_Rate/12, A9, Loan_Years*12, -Loan_Amt)
F9  =Loan_Amt-SUM($E$9:E9)

Row 10 differs in exactly one cell — A10 =A9+1 — so the period counts up. Everything else copies straight down to row 368. Two of those rows are doing the real work:

D9 and E9 take the period number from column A. That is the trick most homemade schedules miss: IPMT and PPMT accept the period as an argument, so each row computes its own interest and principal directly from the period, with no dependence whatsoever on the row above.

F9 is Loan_Amt minus an anchored running total — $E$9 pinned at the top, E9 open at the bottom. Insert a row inside that range and the range grows to include it. The relative =F8-E9 version repoints instead, and that is the entire bug.

Step 4 — Prove it with something that never touched the schedule.

B4  =SUM(D9:D368)
B18 =-CUMIPMT(Loan_Rate/12, Loan_Years*12, Loan_Amt, 1, Loan_Years*12, 0)

Total interest $223,813.88, both ways. CUMIPMT computes it directly from the loan terms and never looks at your table, so if these two disagree the schedule is wrong and not the rounding. That is a genuinely independent check, and it costs one cell.

Option 2 — Excel 365 spill (dynamic-array equivalent)

Same maths, same answers to the cent — but the schedule stops being 2,160 individual formulas and becomes three.

Step 1 — The period and date columns.

A9  =SEQUENCE(Loan_Years*12)
B9  =EDATE(Start_Date, SEQUENCE(Loan_Years*12)-1)

Step 2 — The whole money block, from one cell.

=LET(
   n,    Loan_Years*12,
   r,    Loan_Rate/12,
   per,  SEQUENCE(n),
   pay,  PMT(r, n, -Loan_Amt),
   intr, IPMT(r, per, n, -Loan_Amt),
   prin, PPMT(r, per, n, -Loan_Amt),
   bal,  Loan_Amt - SCAN(0, prin, LAMBDA(a,b, a+b)),
   HSTACK(per*0+pay, intr, prin, bal)
)

Type that in C9 and four columns spill across to F and down to row 368. Reading it top to bottom:

• intr and prin hand IPMT/PPMT the whole period array at once. There is nothing to copy down because there is nothing being copied.
• bal uses SCAN to accumulate principal the correct way — walking the array carrying one running value, with no cross-row cell references at all. The balance lands on a clean zero on the last row every time.
• HSTACK bolts the four columns together into one array.

Change the term from 30 years to 15 on the Inputs sheet and the schedule redraws itself at 180 rows. Insert a row anywhere? You cannot break it, because there are no individual row formulas to break.

The bit IPMT cannot do: overpayments

Here is where dynamic arrays genuinely pull ahead rather than just tidying up. IPMT and PPMT assume a level payment for the entire term. Add $200 a month of extra principal and that assumption is gone — those functions simply cannot describe the loan any more.

SCAN can, because it does not care that the payment changed:

=LET(
   n,    Loan_Years*12,
   r,    Loan_Rate/12,
   pay,  PMT(r, n, -Loan_Amt) + Extra_Pmt,
   per,  SEQUENCE(n),
   clo,  SCAN(Loan_Amt, per, LAMBDA(a,i, MAX(0, a*(1+r)-pay))),
   opn,  VSTACK(Loan_Amt, DROP(clo,-1)),
   intr, opn*r,
   act,  opn+intr-clo,
   HSTACK(per, opn, act, intr, act-intr, clo)
)

The MAX(0, ...) inside the SCAN is what stops the balance going negative on the final stub payment, and deriving the payment as opening + interest − closing means the last row automatically shows the smaller amount actually needed rather than a full instalment.

On our mortgage, $200 a month extra pays the loan off in 269 months instead of 360 — 7 years and 7 months early — and saves $64,925.12 in interest. That is $53,800 of extra principal buying $64,925 of interest, which is the sort of number that ends an argument.

Where else this pattern pays for itself

The engine — period number in, interest and principal out, anchored running balance — is the same shape for a lot more than mortgages:

• Auto and personal loans — same maths, shorter terms, and the same fragile copy-down.
• Business term loans and SBA financing — board decks love a clean principal-versus-interest split, and this one survives being edited in the meeting.
• Bond premium and discount amortisation — the accountant’s version of the same curve.
• Lease schedules under ASC 842 and IFRS 16 — right-of-use asset and lease liability unwind on identical mechanics, with one twist: rent is usually paid in advance, so interest accrues on (opening − payment) and every PV needs its annuity-due flag.
• Payment-holiday and restructuring modelling — where the SCAN version is not an optimisation, it is the only version that works.

Download the workbook

Everything above, live, with both builds side by side and a Check sheet that proves they agree.

Six sheets: Inputs (all yellow, all yours) carries the loan terms and the optional overpayment. Classic Schedule runs all 360 rows on PMT/IPMT/PPMT with the anchored balance column. 365 Schedule produces the identical 360 rows from three cells. Extra Payments (365) runs the SCAN engine, and reports the new payoff month, the years and months saved and the interest saved right at the top. Check ties the two builds to the cent, proves the sum of every principal payment equals exactly what was borrowed, cross-checks total interest against CUMIPMT — which never touches the schedule — and prices the three wrong answers this calculation usually produces.

Set the extra payment to 0 and the overpayment sheet reproduces the contractual schedule exactly. That is the check that the engine is honest before you trust anything it tells you about an overpayment.

Download the amortization schedule (.xlsx)

The 365 sheets need Microsoft 365 or Excel 2021+. The classic sheet opens in anything back to 1997.

Takeaways

• A payment splits into interest (rate × prior balance) and principal (payment − interest). The schedule is just that split until the balance hits zero.
• IPMT and PPMT take the period number directly, so every row stands on its own. There is no “previous row minus this row” chain to shatter when the table is edited.
• Anchor the running balance — =Loan_Amt-SUM($E$9:E9), not =F8-E9. The first describes the loan; the second describes the spreadsheet’s geometry, and geometry is what people change.
• The failure has no error state. Only the final closing balance shows it, and only if you are looking. Put a hard zero-check beside it.
• Cross-check total interest against CUMIPMT, which computes it from the loan terms and never reads your table. One cell, genuinely independent.
• On 365, LET + SEQUENCE + SCAN spill the entire schedule from one cell — and SCAN is the only one of these that can handle an overpayment, because IPMT and PPMT assume a level payment for the whole term.
• Your move: open your current schedule and look at the last row. If the closing balance is not exactly 0.00, the balance column is chained rather than anchored — and it has been wrong for longer than the two cents suggest.