Amortization Schedule Excel: Build the Three-Line Loop

The payment

What PMT gives you, and what it does not

Nearly every guide to an amortization schedule Excel offers begins and ends with one function. Microsoft’s syntax is PMT(rate, nper, pv, [fv], [type]), where rate is “the interest rate for the loan”, nper is how many payments the loan runs for, and pv is the present value, which Microsoft also names as the principal.

Two optional arguments follow. Future value defaults to zero if omitted, which is what you want for a loan intended to be repaid in full. Type is 0 or 1 and “indicates when payments are due”, meaning end or beginning of each period.

What PMT gives you is one number: the level payment. What it does not give you is the schedule, and the schedule is where all the understanding is, because a single payment figure cannot show you how much of each instalment is interest, when that changes, or what an extra payment would do.

The units

The units mistake Microsoft warns about

Before anything else, the error that produces a confidently wrong answer. Microsoft states it plainly on the same page: “Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper.”

The habit that removes it is to put the annual rate, the years and the payments per year in three separate labelled cells, then derive the period rate and the period count from them. Nobody ever types a monthly rate; the sheet computes it. It also means switching a comparison from monthly to fortnightly is one cell rather than a rebuild.

One more troubleshooting note. If your payment appears as a negative number, that is a cash-flow direction rather than an error; wrapping the result in ABS, or entering the principal as a negative, gives you the figure you were expecting.

The loop

The three-line loop that is the whole schedule

Here is the part the templates hide inside themselves. Once you have a payment and an opening balance, every row of an amortization schedule is the same three steps, repeated.

1. 1

   Interest is the balance times the period rate

   Not the payment times anything. Interest is rent on the money still outstanding, so it is computed from the balance at the start of the period and from nothing else.

2. 2

   Principal is whatever the payment has left over

   Payment minus interest. The payment is fixed and the interest changes, so the principal portion is a remainder rather than a plan.

3. 3

   The closing balance is the opening balance minus that principal

   And it becomes the next row’s opening balance. That single link is what makes the table a schedule rather than a list.

Five columns hold it: period, opening balance, interest, principal, closing balance. Everything else any published template adds, cumulative interest, remaining term, a chart, is derived from those five and none of it is necessary to make the thing work.

Because each row reads the row above, this is a chain, and chains break when a row is inserted. Keep the schedule as a generated block that nobody edits by hand, and if you build the surrounding workbook around a table, Microsoft’s structured references, which “reference table names in a formula” rather than cell rectangles, will keep the summary formulas pointing at the right data as the term changes.

The shape

Why the early years are almost all interest

Anyone who has looked at a mortgage statement in year two has had the same reaction: almost nothing has come off the balance. It feels like a trick and it is a consequence of step one above.

Interest is charged on the balance, and the balance is at its highest on the first day, so the first payment is the one with the least principal in it. Everything else follows.

As the balance falls, the interest charged falls with it, and since the payment is fixed, the principal portion grows. The curve is slow at first and accelerates, which is why the second half of a long loan repays far faster than the first even though nothing about the payment changed.

Building the schedule yourself is what makes this obvious rather than merely believed, and it is the reason a template you did not build teaches you nothing. The five columns are the explanation.

Extra

Extra payments, and where they do the most

This is the question the schedule exists to answer and a payment calculator cannot. Add a sixth column for extra payment, subtract it from the closing balance alongside the scheduled principal, and every row below responds.

What you see is that an extra amount paid early is worth dramatically more than the same amount paid late, and the reason is now visible rather than mysterious. Money taken off the balance stops being charged interest for every remaining period, so an early payment cancels interest on many periods while a late one cancels it on few. The saving is not the payment; it is all the interest that payment prevented.

Two honest caveats. Whether extra payments shorten the term or reduce the instalment depends on the agreement, and so does whether they are permitted at all without a charge. Those are questions for your lender’s paperwork, and this post is not financial advice. The arithmetic above tells you what the money does; it cannot tell you what your contract allows.

The limit

What the schedule leaves out

A clean amortization schedule models one thing: a fixed rate, a fixed payment and a fixed term. Real agreements add items it does not know about, and it is better to hold them separately than to bolt them on.

Rate changes are the big one: a variable rate makes every row after the change a different loan, which is handled by starting a new block from the balance on that date rather than by trying to make one formula cover both. Arrangement fees, insurance bundled into a payment and any charge on early repayment are all real cash and none of them is interest, so they belong in a separate list rather than inside the interest column.

Once you have the payment figure, it belongs in the budget as a fixed monthly commitment, and our Excel budget template guide covers where that sits. If the loan is against a property being let, the interest line feeds a rental statement rather than a household budget, which our Excel profit and loss template guide covers. The Excel templates hub holds the free files, and our bill tracker guide covers keeping the payment itself from being missed.

Derive the period rate rather than typing it, write the three-line loop yourself, and add the extra-payment column even if you never use it. That last one changes how the whole document reads. Tell us in the comments whether the early-years shape surprised you the first time you built it out, because it surprises almost everyone.

FAQ

Common questions, answered briefly

People also ask

Other questions, briefly answered