Excel 365: 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 the 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, and the entire ladder quietly comes apart at the seams.

Let’s build one that doesn’t. Coffee up: we’ll do the classic, travels-anywhere version first, then the Excel 365 one-liner that spills the entire schedule from a single cell and never needs dragging again.

What an amortization schedule actually is

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

Three quantities do all the work: the level payment (constant), the interest portion (this period’s rate times last period’s balance), and the principal portion (payment minus interest). Excel has purpose-built functions for all three — the trick is wiring them together without a scaffolding of fragile copy-down references.

Why the copy-down version breaks

The usual build hard-codes 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 at the wrong cells. The schedule doesn’t error; it just silently stops summing to zero, and you don’t notice until the final balance reads -$0.02 or $1,413.77 instead of a clean $0.00.

That’s a $240,000 loan at 5% over 30 years. Beautiful — until row 2 gets nudged and rows 3-360 start referencing the wrong balance.

The build, two ways

Option 1 — PMT / IPMT / PPMT: works in every Excel

These three functions have shipped since Excel 97, so this version opens cleanly for any colleague, lender or auditor. Set up your inputs — Rate (annual), Years, Principal — then a period number in column A starting at 1:

Payment (one cell):  =PMT(Rate/12, Years*12, -Principal)
Interest  (row):     =IPMT(Rate/12, A2, Years*12, -Principal)
Principal (row):     =PPMT(Rate/12, A2, Years*12, -Principal)

The magic most homemade schedules miss: IPMT and PPMT take the period number as an argument, so each row computes its own interest and principal directly from the period — no dependence on the row above. Your balance column can then simply be =Principal - SUM($principal$2:principal_this_row), an anchored running total that survives edits far better than a relative “previous minus this.”

Build it in 4 steps:

1. Lay out inputs. Rate, Years, Principal in labelled cells; name them (Formulas → Define Name).

2. Number the periods. Column A: 1 to Years*12.

3. Drop in the three formulas above, using A2 as the period.

4. Add a balance column: =Principal + CUMIPMT_or_running_principal. The cleanest anchored form is =Principal - SUMPRODUCT((periods<=A2)*principal_range), which never cares what row it lives on.

Option 2 — the Excel 365 spill: the whole schedule from one cell

On 365, you don’t build 360 rows — you describe them once and let Excel spill. SEQUENCE generates the period numbers, IPMT/PPMT accept that whole array at once, and HSTACK assembles the columns:

=LET(
   n,    Years*12,
   per,  SEQUENCE(n),
   pay,  PMT(Rate/12, n, -Principal),
   intr, IPMT(Rate/12, per, n, -Principal),
   prin, PPMT(Rate/12, per, n, -Principal),
   bal,  Principal - SCAN(0, prin, LAMBDA(a,p, a+p)),
   HSTACK(per, INDEX(pay,1)*(per>0), intr, prin, bal)
)

Type it once in the top-left cell and the entire schedule spills down and to the right — the # badge marking a single dynamic array. SCAN builds the running principal total the “correct” way (accumulating down the array with no cross-row cell references at all), so the balance lands on a clean zero on the last row every time. Change the term from 30 years to 15 and the schedule instantly redraws itself to 180 rows. Insert a row anywhere? You can’t break it — there are no individual row formulas to break.

Where this pays off beyond mortgages

• Auto and personal loans — same math, shorter terms.
• Business term loans and SBA financing — board decks love a clean principal-vs-interest split.
• Bond coupon and premium/discount amortization — the accountant’s version of the same curve.
• Lease schedules (ASC 842 / IFRS 16) — right-of-use asset and liability unwind on identical mechanics.
• “Should I make extra payments?” modelling — add an extra-principal column and watch the payoff date jump years earlier.

Download the workbook

The 30-year mortgage above lives in a free workbook with both builds side by side: the backward-compatible PMT/IPMT/PPMT schedule so it opens anywhere, and the 365 LET + SEQUENCE spill on a second sheet so you can see the one-cell version in action. Blue cells are inputs — change the rate, term or principal and the whole schedule (and the total-interest summary up top) recalculates live.

Download the amortization schedule (.xlsx)

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 each row stands on its own — no “previous row minus this row” chain to shatter when you edit the table.
• Anchor the running balance with SUMPRODUCT/SUM over an absolute range, not a relative G4-E5, and inserts stop breaking it.
• On Excel 365, LET + SEQUENCE + SCAN spill the entire schedule from one cell — change the term and it redraws itself, with no row formulas left to corrupt.
• The same mechanics power car loans, bonds, and IFRS 16 lease schedules — build it once, reuse it everywhere.