How to Create an Amortization Schedule in Excel With Extra Payment Scenarios

An amortisation schedule is one of the clearest examples of Excel being useful for real planning. Once you can see how interest, principal, and balance behave over time, the loan stops feeling abstract — and once you can test extra-payment scenarios, the schedule stops being a display and starts being a decision tool.

This tutorial builds the schedule from scratch with PMT, IPMT, and PPMT, then layers in two extra-payment scenarios that competitor tutorials almost always treat as a single column: a one-off lump sum early in the loan, and a fixed monthly overpayment across the whole term. The side-by-side comparison tells you which strategy saves more interest for your specific numbers, which is the question borrowers actually want answered.

It works in Excel 365, 2021, 2019, and Excel for the web without any add-ins. For broader Excel context, see the Excel formulas hub; for the companion planning sheet, see the monthly budget spreadsheet tutorial.

Prerequisites

• Excel 365, 2021, 2019, or Excel for the web. No add-ins required.
• Comfort with cell references and formulas (no macros).
• The five loan inputs you can change later: principal, annual interest rate, term in years, payments per year, and an extra-payment amount.

Step 1 — Set up the inputs block

Put every input in its own named cell at the top of the sheet so the schedule below recalculates the moment you change a number. Hidden assumptions are where amortisation sheets go wrong, and they are the reason a model built for one loan cannot be reused for a second.

• B3 Principal — loan amount (for example, 250000).
• B4 Annual rate — as a percentage with the % symbol (for example, 6.5%). Excel stores it as 0.065, but displaying the % keeps it readable.
• B5 Term in years — 30 for a standard mortgage, 5 for a car loan.
• B6 Payments per year — 12 for monthly, 26 for fortnightly, 52 for weekly.
• B7 Total periods — formula =B5*B6.
• B8 Periodic rate — formula =B4/B6.
• B9 Scheduled payment — formula =PMT(B8, B7, -B3). Note the minus sign on principal — PMT expects the principal as a negative present-value cash flow, and this keeps the payment displayed as a positive number.
• B10 Extra monthly payment — 0 for the baseline run. Raise this in Step 4.
• B11 Lump-sum extra — 0 for the baseline run. Raise this in Step 4.
• B12 Lump-sum period — period number when the lump sum lands (for example, 12 for end of year 1).

Beginner pitfall: do not multiply the annual rate by the term in one combined cell. You will lose the ability to flip between monthly and fortnightly later, and the formula trail becomes impossible to audit.

Step 2 — Build the schedule columns

Below the inputs, create an 8-column table starting at row 15 with headers in row 15 and the first period in row 16:

• A Period — 1, 2, 3 … up to the total periods in B7.
• B Beginning balance — row 16 formula =$B$3, then row 17 onwards =H16 (previous ending balance).
• C Scheduled payment — =IF(B16>0, MIN($B$9, B16+D16), 0). The MIN guard stops the final row from overpaying when the balance is almost zero.
• D Interest — =B16*$B$8. Interest charges each period are the beginning balance times the periodic rate, independent of the principal payment.
• E Principal — =C16-D16.
• F Extra monthly — =IF(B16>0, MIN($B$10, B16-E16), 0).
• G Lump sum — =IF(A16=$B$12, MIN($B$11, B16-E16-F16), 0).
• H Ending balance — =MAX(0, B16-E16-F16-G16). The MAX wraps the floor at zero so floating-point drift does not leave a £0.003 balance hanging on the final row.

Convert the range A15:H15 plus the first data row into a Table with Ctrl+T. Every structured reference and every new row formula then auto-fills when you extend the Period column. The Table also makes the schedule easier to chart later.

Beginner pitfall: do not calculate interest from the ending balance — it lags by one period and the final row will never fully close out. Interest is always on the beginning balance.

Step 3 — Use IPMT and PPMT for a cross-check column

Excel has two dedicated functions that compute period-level interest and principal directly from the loan inputs, with no extra column required: IPMT for interest and PPMT for principal. They ignore extra payments, so they are not a replacement for the running-balance schedule above, but they are the fastest way to sanity-check the first few rows of your table.

In a spare column, add =IPMT($B$8, A16, $B$7, -$B$3) and =PPMT($B$8, A16, $B$7, -$B$3). The values for period 1 should match columns D and E exactly. If they do not, you have a sign error or rate-frequency mismatch in the inputs block — fix that before moving on.

Real-world scenario: an analyst at a regional credit union used this IPMT cross-check to catch a long-standing bug in a colleague's loan approval sheet. The colleague had entered the annual rate directly into PMT without dividing by 12, making every quoted monthly payment about 13× too high. The IPMT spot-check surfaced the mismatch in thirty seconds.

Step 4 — Test the two extra-payment scenarios

This is where the schedule earns its keep. Most competitor tutorials show a single "extra payments" column and stop. Borrowers ask a different question: given a fixed amount of spare cash, is it better to throw it at the loan as a one-off lump sum, or spread it across the year as a monthly overpayment? The answer depends on the rate and how early the lump sum lands, and your schedule can now show both.

Scenario A — Monthly overpayment

Set B10 (extra monthly) to your chosen amount, for example 200. Leave B11 (lump sum) at 0. Recalculate the totals: how many periods until the balance hits zero, and what is the sum of column D (interest) up to that row? Use =SUMIFS(D16:D375, H16:H375, ">0") combined with a small buffer, or simply total column D across the used rows.

Scenario B — Lump-sum overpayment

Set B10 back to 0. Set B11 to 2400 (the same £2,400 per year that 12 × £200 gives you in Scenario A) and B12 to 12 (end of year 1). Recalculate the totals the same way.

On a £250,000 30-year loan at 6.5%, Scenario A (£200/month from day one) pays the loan off around month 265 — roughly 7 years 11 months early — and saves roughly £97,600 in interest. Scenario B (one £2,400 lump at month 12) pays it off around month 351 — roughly 9 months early — and saves roughly £13,000. Same total extra paid in the first year — very different lifetime result, because the recurring payment chips away at the balance every single month for 22+ years while the lump is a one-time reduction.

The headline rule: the earlier extra principal lands and the longer it keeps compounding against you, the more interest it saves. Recurring wins for long mortgages; a very early lump sum can win for short loans (car, personal) where the time horizon is too short for monthly contributions to compound meaningfully.

Step 5 — Add a summary panel and scenario comparison

Next to the inputs block, add a small 5-row summary that reads straight from the schedule:

• Total interest paid — =SUM(D16:D375).
• Total payments made — =SUM(C16:C375)+SUM(F16:F375)+SUM(G16:G375).
• Months to payoff — =MATCH(0, H16:H375, 0). Returns the first period where the ending balance hits zero.
• Years to payoff — =<months cell>/B6.
• Interest saved vs baseline — hard-code the baseline total interest as a reference value after your first run with B10=0 and B11=0, then subtract the current scenario total from it.

Now set B10 and B11, watch the summary update, and you have a working what-if analyser. For multi-scenario comparison, Excel's Scenario Manager (Data tab → What-If Analysis → Scenario Manager) can save each setting under a name and toggle between them without retyping.

Step 6 — Chart the declining balance

Select columns A and H (period and ending balance). Insert a line chart. The curve is almost straight at the start — because interest dominates early payments — and steepens towards the end as principal finally takes over. Adding a second series for the no-extra baseline and overlaying both curves is the single most persuasive visual in any loan conversation: the gap between the two lines is the interest you save.

Worked example — £250,000 mortgage over 30 years at 6.5%

Numbers to plug in to verify your sheet:

• Principal 250000, annual rate 6.5%, term 30, payments per year 12.
• Scheduled monthly payment (B9) = £1,580.17.
• Baseline total interest over 360 months ≈ £318,861.
• Scenario A (£200 extra per month): payoff ≈ month 265, total interest ≈ £221,243, saving ≈ £97,618.
• Scenario B (£2,400 lump at month 12): payoff ≈ month 351, total interest ≈ £305,903, saving ≈ £12,958.

If your sheet gives numbers within a few pounds of these, it is correctly built. Larger discrepancies usually mean a rate-frequency mistake in B8 or a sign error in the PMT call.

The full formula reference

Variations: customise for your loan type

The skeleton stays identical. What changes is the inputs, the term, and which scenario column you actually use.

For a quarterly business loan, set B6=4 and the periodic-rate / total-periods cells handle themselves. For interest-only periods at the start, you need a mode column per row — see the financial modelling tutorial for that pattern.

When a simple amortisation sheet stops being the right tool

• Variable-rate or tracker loans — the periodic rate changes during the term. Add a column for period-specific rate and drive B8 from there, or switch to a full scenario model.
• Interest-only periods followed by amortisation — needs a mode column per row, not a single PMT. Consider a proper financial model.
• Multiple loans with cross-subsidy — spreadsheet complexity climbs fast. Move to a multi-tab model with one summary sheet.
• Tax and insurance bundled in — amortisation is about principal and interest. Escrow items belong in a separate cash-flow sheet alongside the household budget.

Common mistakes

• Putting the annual rate directly into PMT instead of dividing by payments per year — gives a payment roughly 12× too high.
• Forgetting the minus sign on principal in PMT/IPMT/PPMT — returns a negative payment that breaks downstream totals.
• Calculating interest from the ending balance instead of the beginning balance — the schedule never fully closes and the final row shows a residual.
• Hard-coding the scheduled payment as a number instead of =PMT(...) — the sheet stops responding to rate or term changes.
• Comparing scenarios by changing inputs in place with no record of the baseline — once the baseline is overwritten the "interest saved" figure is meaningless.

Troubleshooting

• #NUM! from PMT — almost always a sign-convention mismatch or a rate/term combination that implies the loan never pays off. Check that the periodic rate in B8 is the annual rate divided by payments per year, and that principal is entered as a negative in the PMT call.
• Final row shows a tiny residual balance (for example £0.003) — floating-point drift from monthly rounding. The MAX(0, ...) wrapper in column H hides it; the MIN guard in column C stops the scheduled payment overshooting when the balance is about to close.
• Extra monthly column stays 0 even though B10 is set — the Table range did not auto-extend to cover the new rows. Click inside the Table, go to Table Design → Resize Table, and re-select the full range through the last period.
• Lump sum never applies — the period number in B12 is outside the range of filled rows, or does not match any value in column A. Check that B12 is between 1 and B7 and that column A is filled numerically (not as text).
• Scenario Manager says "reference is not valid" — Scenario Manager only accepts single-cell references on the active sheet. Move the changing cells (B10, B11, B12) onto the same tab as the schedule before saving scenarios, or use a named range.

FAQ

Do I need a template or can I build this from scratch?

From scratch is faster once you understand PMT, IPMT, and PPMT, and the sheet is auditable because every formula is yours. Templates are useful for one-off calculations, but for teaching yourself the mechanics, the twenty-minute build above is the better investment.

Is it better to pay a lump sum or make monthly extra payments?

For long mortgages (20+ years), monthly overpayments almost always save more total interest than the same money dropped as a single lump sum, because they reduce the balance every single month across the whole term. For short loans (car, personal) or when you expect to refinance soon, an early lump sum can win. Your schedule now answers this question for your specific numbers.

Why does the balance column not quite hit zero on the last row?

Monthly compounding introduces tiny rounding errors that compound across hundreds of rows. The =MAX(0, ...) wrapper in the ending-balance formula floors the final value at zero, and the =MIN($B$9, ...) guard in the scheduled payment prevents the last payment from overshooting. Together they give a clean close-out.

Can I use this for fortnightly or weekly payments?

Yes. Set B6 (payments per year) to 26 for fortnightly or 52 for weekly. PMT, IPMT, PPMT, and the periodic-rate formula all recalculate automatically. Fortnightly payments alone — with no extra amount — typically shorten a 30-year mortgage by roughly 4–5 years compared to monthly, because 26 fortnightly payments equal 13 monthly payments per year.

What is the difference between IPMT, PPMT, and building the schedule row-by-row?

IPMT and PPMT return the interest and principal portions for a specific period directly from the loan inputs, with no extra column needed. But they assume the regular repayment schedule with no extra payments — so they are fast for the baseline case and useful as a cross-check, but cannot model lump-sum or recurring overpayments. For scenario testing, the running-balance schedule is the tool you need.

Sources and further reading

• Microsoft — PMT function reference
• Microsoft — IPMT function reference
• Microsoft — PPMT function reference

Related tutorials on this site

• Complete Excel Formulas Guide — the Excel cluster hub, useful context for PMT/IPMT/PPMT alongside every other function family.
• How to Build a Monthly Budget Spreadsheet in Excel From Scratch — the companion planning sheet that pairs with the amortisation view.
• How to Build a Financial Model in Excel From Scratch — the next step up when a single schedule is no longer enough (variable-rate loans, interest-only periods, multi-loan models).
• Build a Forecasting Model in Excel With AI Assistance — for projecting affordability when the input to the loan is an uncertain income stream.
• Excel Tables: Structured References, Growth, and Cleaner Models — why Ctrl+T on the schedule saves hours later.
• 15 Advanced Excel Formulas Every Power User Should Know — deepens formula fluency beyond the financial family.