Building an Amortisation Schedule With Extra Payments in Excel

Why the Built-In Functions Stop Working

Excel has three functions for loan schedules and none of them handles extra payments. PMT, IPMT, and PPMT all assume the payment is identical every period and the term is fixed in advance. The instant you add $250 to one month, every subsequent figure they return is wrong.

The fix is to build the schedule as rows rather than as formulas. Each row computes interest from the actual balance, so any extra payment in any month simply flows through. This article builds that schedule and shows what the extra payments are worth.

The loan

The scheduled payment comes out at $2,075.48. Note the minus sign on the principal: PMT returns a negative number for a positive present value, and negating the input is cleaner than wrapping the whole thing in ABS.

The seven columns

Row 11 onward is identical except that the opening balance points at the previous closing balance: =G10. Copy rows 11 down to period 360 and the schedule finishes itself, going to zero wherever the loan actually ends.

The IF wrappers matter too. Once the loan is paid, every remaining row must show zero rather than continuing to compute interest on a negative balance. Without them, a 360-row schedule that pays off in month 265 keeps accruing and the summary totals are nonsense.

What $250 a month is worth

The $250 costs $66,250 across the 265 months it is actually paid and saves $130,549 of interest. That is a return of about 6.75%, guaranteed and untaxed, which is exactly the loan rate. Prepaying a loan always returns precisely the loan rate, which makes the comparison against any alternative use of the money straightforward.

The summary block

COUNTIF on the payment column is the reliable way to find the payoff period, because the guard formulas make every row after payoff show zero. Counting non-blank rows or looking for the first zero balance both break on edge cases.

Three variations worth building

An annual lump sum instead of a monthly extra

Replace the extra payment formula with one that fires only in a chosen month: =IF(MOD(A10, 12)=0, AnnualLump, 0), wrapped in the same MIN guard. A $3,000 annual lump pays off this loan about three months later than $250 monthly, because the same total arrives later within each year.

Biweekly payments

Paying half the monthly amount every two weeks produces 26 half-payments a year, which is 13 monthly payments rather than 12. Model it as an extra $173 a month, which is one twelfth of a payment. On this loan that pays off in about 287 months, saving roughly six years and $101,000 of interest.

Check whether the lender actually applies biweekly payments biweekly. Many hold each half payment and apply them monthly, which delivers the extra thirteenth payment but none of the interest benefit from paying two weeks early. Some charge a setup fee for the privilege of doing what you can do yourself with a standing transfer.

A one-off payment in a specific month

Add a small lookup table of period numbers and amounts, then use =IFERROR(VLOOKUP(A10, ExtraTable, 2, FALSE), 0) in the extra payment column. This is the version that actually matches real life, where extras happen when a bonus arrives rather than on a schedule.

The four errors that produce wrong schedules

• Annual rate in a monthly formula. =PMT(6.75%, 360, -320000) returns a payment of $21,601 and looks obviously wrong; =PMT(6.75%/12, 30, -320000) returns $11,622 and looks plausible. Always divide the rate and multiply the term together.
• Interest computed on the closing balance rather than the opening one. Interest accrues on what you owed at the start of the month, so column E must reference column B, not column G.
• Copying the row 10 formula down without changing the opening balance reference. Row 11 must read =G10, not =Principal.
• Forgetting that extras reduce the term, not the payment. On most loans the scheduled payment stays the same and the loan ends early. If your lender re-amortises instead, the saving is far smaller and the schedule needs rebuilding after each recast.

What the schedule does not tell you

• Whether prepaying is the best use of the money. The guaranteed return equals the loan rate, so compare it against your other options after tax. Clearing 22% card debt beats prepaying a 6.75% mortgage every time.
• Anything about escrow. Taxes and insurance are usually collected with the payment and are not part of the loan. A "monthly payment" of $2,700 may be $2,075 of principal and interest plus $625 of escrow, and only the first part amortises.
• Whether the loan has a prepayment penalty. Uncommon on mortgages now and still present on some auto and commercial loans. Check before building a plan around extras.
• Rate changes. This model assumes a fixed rate. An adjustable loan needs a rate column and a recalculated payment at each reset.
• The tax treatment of mortgage interest, which for those who itemise reduces the effective rate and therefore the value of prepaying.
• Liquidity. Money paid into a mortgage is hard to get back out. An emergency fund generally comes before extra principal, even at a poor interest rate.