# Building an Amortisation Schedule With Extra Payments in Excel

PMT, IPMT, and PPMT all break the moment you pay extra, because they assume a fixed schedule. Here is the row-by-row build that handles any extra payment pattern, with the two guard formulas that stop the last row going negative.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-amortization-schedule-extra-payments
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 12 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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.

Tool: [Try the amortisation calculator](https://dothecalculation.com/calculators/amortization-calculator) — Generate a full schedule with extra payments before deciding whether you need the spreadsheet at all.

## The loan

**Inputs, in named cells at the top of the sheet**
| Cell | Name | Value |
| --- | --- | --- |
| B1 | Principal | $320,000 |
| B2 | AnnualRate | 6.75% |
| B3 | Years | 30 |
| B4 | MonthlyRate | =B2/12 |
| B5 | Periods | =B3*12 |
| B6 | Payment | =PMT(B4, B5, -B1) |
| B7 | ExtraMonthly | $250 |

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.

> **Name your input cells** — Use Formulas, Define Name, or select the range and use Create from Selection. A schedule full of $B$4 is unreadable and unauditable; one full of MonthlyRate explains itself. It also stops the classic error of typing the annual rate where the monthly one belongs.

## The seven columns

**Row 10 is period 1; every later row copies down from it**
| Column | Header | Formula in row 10 |
| --- | --- | --- |
| A | Period | 1 |
| B | Opening balance | =Principal |
| C | Scheduled payment | =IF(B10<=0, 0, MIN(Payment, B10*(1+MonthlyRate))) |
| D | Extra payment | =IF(B10<=0, 0, MIN(ExtraMonthly, B10-(C10-E10))) |
| E | Interest | =B10*MonthlyRate |
| F | Principal paid | =C10+D10-E10 |
| G | Closing balance | =B10-F10 |

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 two guards, and why they are there** — The MIN in column C stops the final payment exceeding what is left plus one month of interest. The MIN in column D stops an extra payment overshooting the balance. Without both, the last few rows show a negative balance and a negative interest charge, and the totals are wrong by a few hundred dollars.

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 same $320,000 loan, with and without extra payments**
|  | Scheduled only | Plus $250 a month |
| --- | --- | --- |
| Monthly payment | $2,075.48 | $2,325.48 |
| Months to payoff | 360 | 265 |
| Time saved | — | 7 years 11 months |
| Total paid | $747,173 | $616,625 |
| Total interest | $427,173 | $296,625 |
| Interest saved | — | $130,549 |

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.

**Checking the payoff month without the schedule**

```
=NPER(MonthlyRate, -(Payment + ExtraMonthly), Principal)
```
- =NPER(0.0675/12, -2325.48, 320000) returns 265.16
- Use this to sanity check the row count where your schedule reaches zero.
- It only works for a constant extra payment; irregular extras need the schedule.

## The summary block

**Put these above the schedule so they are visible without scrolling**
| Figure | Formula |
| --- | --- |
| Total interest | =SUM(E10:E369) |
| Total paid | =SUM(C10:D369) |
| Payoff period | =COUNTIF(C10:C369, ">0") |
| Payoff date | =EDATE(StartDate, PayoffPeriod) |
| Interest without extras | =Payment*Periods-Principal |
| Interest saved | =InterestWithoutExtras-TotalInterest |

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.

Tool: [Try the biweekly mortgage payoff calculator](https://dothecalculation.com/calculators/biweekly-mortgage-payoff-calculator) — Compare a true biweekly schedule against monthly payments plus an equivalent extra.

## 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.

**Why do PPMT and IPMT stop matching my schedule after the first extra payment?**

Because they compute from the original amortisation assumption rather than from your actual balance. They take the period number and the original term and calculate what the split would have been on the scheduled path. Once the balance diverges from that path, they are describing a loan you no longer have.

**How do I stop the last row of my schedule going negative?**

Wrap the payment in a MIN against the balance plus one month of interest: =MIN(Payment, B10*(1+MonthlyRate)). Do the same for the extra payment column against the remaining balance. Both guards are needed, since either alone can still overshoot.

**Is a biweekly mortgage better than paying extra monthly?**

Marginally, and only if the lender applies each payment on receipt. The bulk of the benefit is the thirteenth payment each year, which you can replicate by adding one twelfth of a payment to each month with no fee and no commitment.

**Should the extra payment reduce my monthly payment or my term?**

Term, in almost every case. Reducing the term is where the interest saving comes from. Recasting to a lower payment keeps the same end date and saves far less, though it is worth considering if cash flow is tight and the lender offers it without a full refinance.

**How do I model an irregular extra payment schedule?**

Put period numbers and amounts in a two-column lookup table, then use =IFERROR(VLOOKUP(A10, ExtraTable, 2, FALSE), 0) in the extra column, keeping the MIN guard around it. This handles bonuses, tax refunds, and any pattern that does not repeat.

**What is CUMIPMT for, if it cannot handle extras?**

Totalling interest across a range of periods on a schedule with no extras, which is useful for a quick answer such as interest paid in year one. =CUMIPMT(rate, nper, pv, 1, 12, 0). It shares the same limitation as IPMT, so it belongs in the baseline comparison rather than the live schedule.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-amortization-schedule-extra-payments). Quote freely with attribution and a link to this page._