Create a Flexible Amortization Schedule in Excel 2026

You're probably staring at a lender estimate, an online mortgage calculator, or a Zillow payment box and thinking the same thing most buyers think. The monthly number is helpful, but it doesn't tell you what's really happening to the loan.

That's where an amortization schedule in Excel becomes useful. It turns a vague mortgage payment into a full map of your loan. You can see how the balance falls, how much of each payment goes to interest, and what changes when you add extra principal. For first-time buyers, that's often the difference between feeling intimidated by a mortgage and understanding it.

Table of Contents

• Why Build Your Own Amortization Schedule
  • What Excel shows that calculators often hide
• Set Up Your Loan Inputs and Key Formulas
  • Start with a clean input box
  • Use PMT, IPMT, and PPMT with fixed references
• Build the Amortization Payment Table
  • Create the columns that matter
  • Write the first two rows correctly
• Add Summary Totals and Visualize Your Loan
  • Build a simple summary box
  • Turn the table into a chart you can read in seconds
• Model Extra Payments to See Your Savings
  • Add one input that changes everything
  • Handle the payoff row cleanly
• Advanced Tips and Your Free Template
  • Make the sheet easier to reuse
  • Use it for real decisions, not just curiosity

Why Build Your Own Amortization Schedule

Online calculators are fine for a fast estimate. They're weak when you need to answer a real buying question.

A mortgage isn't just one payment. It's a long stream of payments where the mix of interest and principal changes over time. Early payments usually send more money to interest, and principal falls more slowly at the start. If you only look at the monthly total, you miss the part that affects payoff strategy, refinancing decisions, and how fast you build equity.

That's why a spreadsheet beats a black-box calculator. You can inspect every line.

What Excel shows that calculators often hide

A solid amortization schedule in Excel helps you answer questions like these:

• Where is this payment going? You can see the split between principal and interest for each period.
• How does the balance decline? Each row shows the loan shrinking over time instead of staying abstract.
• What happens if I pay extra? You can model recurring extra principal and watch the ending date move sooner.
• How should I compare scenarios? A spreadsheet makes it easier to test different terms, rates, and payment frequencies side by side.

Practical rule: If you can't trace a mortgage payment from beginning balance to ending balance, you don't yet understand the loan well enough to compare options confidently.

There's also a control benefit. With Excel, you decide what the model includes. You can keep it simple at first, then layer in extras like payoff dates, summary totals, and prepayment logic when you're ready. That flexibility matters because first-time buyers rarely ask only one question. They ask several, usually in quick succession.

One day it's, “Can I afford this payment?” The next day it's, “What if I put a little extra toward principal each month?” A reusable spreadsheet handles both without making you start over.

Set Up Your Loan Inputs and Key Formulas

Open a blank worksheet and give yourself a clean input area before you write a single formula. That one step prevents a lot of common errors later, especially once you start copying formulas down hundreds of rows and testing extra payment scenarios.

Start with a clean input box

Put the inputs at the top of the sheet in a compact block. Use one column for labels and one for values so every formula can point back to a single source of truth.

For a mortgage model, start with these four fields:

• Loan Amount
• Annual Interest Rate
• Loan Term in Years
• Payments Per Year

A simple setup might look like this:

Cell	Label	Value
B2	Loan Amount	user input
B3	Annual Interest Rate	user input
B4	Loan Term in Years	user input
B5	Payments Per Year	user input

For most U.S. mortgages, Payments Per Year will be 12. Keeping it editable still matters. If you later want to test biweekly payments, or reuse the workbook for another loan, you can change one cell instead of rewriting the schedule.

If you want to confirm the payment math before building the full table, this guide on how to calculate mortgage payment pairs well with the setup below.

Use PMT, IPMT, and PPMT with fixed references

These three Excel functions do the core loan math:

• PMT returns the required payment for each period
• IPMT returns the interest portion for a given payment number
• PPMT returns the principal portion for that same payment number

Together, they let you build a schedule that is both readable and reusable. They also provide the structure you need later when you add extra principal and compare payoff options.

Start by locking your input cells with absolute references. Use $B$2, not B2, for values that should stay fixed as you copy formulas down the worksheet. If you skip that now, the schedule may look fine for the first few rows and then begin to drift off course.

Use these supporting formulas first:

1. Periodic rate
   Divide the annual rate by the number of payments per year.
   =$B$3/$B$5

2. Total number of payments
   Multiply the loan term by payments per year.
   =$B$4*$B$5

3. Payment amount
   Use PMT with the periodic rate, total periods, and loan amount.
   =PMT($B$3/$B$5,$B$4*$B$5,-$B$2)

The negative sign before $B$2 is there for a practical reason. Excel treats the loan as cash received, so using a negative present value returns the payment as a positive number. That keeps the schedule easier to scan and makes later summary formulas less messy.

Pick one sign convention and keep it consistent. If the payment is positive in your model, the interest and principal amounts should read as positive too.

For a standard 30-year mortgage with monthly payments, the schedule usually runs for 360 periods because the formula is 30 × 12. That long fill-down is a good stress test. If your references are locked correctly and your core formulas are clean, the sheet can handle a full mortgage term and still be flexible enough to test extra payments without a rebuild.

Build the Amortization Payment Table

Once the inputs are working, the schedule itself is straightforward. The trick is getting the row logic right at the beginning. If the first two rows are correct, the rest usually follows cleanly.

Create the columns that matter

Use these headers for the main table:

• Payment Number
• Beginning Balance
• Payment
• Interest
• Principal
• Ending Balance

This layout works because it mirrors how the loan behaves. Every payment starts with a balance, applies interest for that period, allocates the remainder to principal, and leaves a new balance behind. That last value becomes the next row's starting point.

A clean first data row might be structured like this:

Column	What it represents	First-row formula pattern
Payment Number	current period	1
Beginning Balance	starting loan balance	=$B$2
Payment	fixed required payment	=PMT(...) or a reference to your PMT cell
Interest	interest for the period	=IPMT(...)
Principal	principal for the period	=PPMT(...)
Ending Balance	remaining loan after payment	=Beginning Balance-Principal

That last formula matters more than many beginners realize. The remaining balance is typically calculated row by row as the previous balance minus the principal portion. That's what makes the schedule transparent instead of acting like a simple payment widget.

Write the first two rows correctly

The first row sets the formulas. The second row sets the pattern.

For row one of the schedule:

• Payment Number is 1
• Beginning Balance links to the original loan amount
• Payment links to your PMT result
• Interest uses IPMT for period 1
• Principal uses PPMT for period 1
• Ending Balance equals beginning balance minus principal

For row two:

• Payment Number becomes the prior payment number plus one
• Beginning Balance equals the previous row's ending balance
• Payment stays fixed
• Interest uses IPMT for period 2
• Principal uses PPMT for period 2
• Ending Balance again equals beginning balance minus principal

The second row is where most broken schedules reveal themselves. If row two doesn't pull the previous ending balance forward, every later row becomes decorative rather than useful.

After that, fill down through the term of the loan. If your term or payment frequency changes, the schedule should still work as long as the formulas point back to the input box.

A few formatting decisions make review much easier:

• Freeze headers so the table labels stay visible while scrolling.
• Format money as currency to keep balances readable.
• Use whole numbers for payment count and avoid accidental decimals in period numbering.
• Turn the range into an Excel Table if you want filters and cleaner visual formatting.

This is also where you start to see the actual shape of the loan. Early lines show heavier interest allocation, while later lines show principal taking over. Seeing that progression row by row is the whole value of building the schedule yourself.

Add Summary Totals and Visualize Your Loan

A long payment table is accurate, but it's not easy to interpret at a glance. Add a summary box above the schedule so the important takeaways are visible immediately.

Build a simple summary box

Create a compact area that pulls in a few decision-making fields:

• Periodic Payment
• Total Interest Paid
• Total Principal Paid
• Final Scheduled Payment Number
• Payoff Date

For totals, simple SUM formulas usually do the job. Sum the interest column to show total borrowing cost across the schedule. Sum the principal column as a check that your model fully pays off the original balance.

The payoff date is especially useful because it converts a math exercise into a planning tool. If you add a start date input, you can derive each payment date in the schedule and display the last one in the summary. That's the kind of detail borrowers use when comparing options. If you want a fast benchmark against an online tool, try this mortgage amortization calculator.

Turn the table into a chart you can read in seconds

A chart does what rows of numbers can't. It makes the timing of interest and principal obvious.

Use a stacked area chart or stacked column chart with payment periods along the horizontal axis and the Interest and Principal columns as the two series. In the earlier part of the loan, the interest band will be visually larger. As the balance falls, that shape changes and principal takes a larger share.

That pattern matters because many buyers underestimate how slowly the balance can move at the beginning of a mortgage. The schedule shows it in detail. The chart makes it intuitive.

A good mortgage sheet doesn't just calculate. It explains.

If the chart feels cluttered, keep it simple. Don't plot the ending balance on the same chart. Let one visual answer one question well: how each payment is divided over time.

Model Extra Payments to See Your Savings

A basic worksheet becomes useful for real decisions. A key gap in many tutorials is extra principal and early payoff modeling, even though it's one of the most common practical questions borrowers ask, as highlighted in this early payoff modeling discussion.

Add one input that changes everything

Add a new input cell near the top of the sheet:

• Extra Payment

Then add a new column to the schedule, usually beside Principal, called Extra Principal.

The logic is simple:

• The required payment stays the same.
• Interest is still calculated from the beginning balance.
• Scheduled principal still comes from the normal payment formula.
• Extra payment goes directly against principal.
• Ending balance becomes beginning balance minus scheduled principal minus extra principal.

That means your ending balance formula changes from:

Beginning Balance - Principal

to:

Beginning Balance - Principal - Extra Principal

If you want a separate way to test what recurring prepayments do, this extra payment calculator is a handy comparison point.

Handle the payoff row cleanly

Extra payments create one awkward issue. At some point, the remaining balance becomes smaller than your usual principal-plus-extra amount. If you don't account for that, Excel can push the ending balance below zero.

Use an IF formula to cap the final principal reduction at the remaining balance. There are a few ways to do this, but the goal is always the same: the last row should land on zero, not on a negative number.

A practical setup usually includes:

1. An adjusted extra principal formula that limits the extra payment if the remaining balance is small.
2. An ending balance formula that returns zero once the loan is paid off.
3. Conditional formatting that highlights the payoff row so you can instantly spot the final payment.

Here's a useful checkpoint after you add prepayment logic.

If the ending balance goes negative, your model isn't showing savings. It's just over-applying cash beyond payoff.

After you've made those changes, compare your original summary totals to the updated ones. The difference in total interest paid shows the savings from extra principal. The new final row shows the earlier payoff point. That side-by-side comparison is what most buyers want.

For a visual walkthrough, this video can help fill in the spreadsheet mechanics:

This is also where Excel becomes better than a static calculator. You can test small recurring extra payments, occasional lump sums, or temporary prepayment periods without rebuilding anything. Once the formulas are set, the model becomes reusable.

Advanced Tips and Your Free Template

A good mortgage sheet should be flexible enough to survive real-life edits. Buyers change term assumptions, compare payment timing, and revisit the same workbook more than once. If the file breaks every time an input changes, it's not a tool. It's a one-time exercise.

Make the sheet easier to reuse

One practical upgrade is to keep Payments Per Year as a true input instead of hard-coding monthly assumptions. That lets you adapt the workbook for biweekly modeling without changing the overall structure.

For a more modern build, newer Excel approaches use dynamic-array functions like SEQUENCE and SCAN so the schedule resizes automatically when the term changes. That approach is covered in this guide to a dynamic amortization schedule with SCAN. The big advantage is less drag-and-drop work and fewer manual fill errors when you change inputs.

Use it for real decisions, not just curiosity

Once the model works, make it easy to share and review:

• Name the input cells if you want formulas that are easier to read later.
• Protect formula cells so only input boxes can be edited.
• Print to PDF before discussing scenarios with a partner, broker, or advisor.
• Save separate versions for base case, refinance case, and extra-payment case instead of overwriting one file repeatedly.

The best spreadsheet is the one you'll reopen. Keep the inputs obvious, the formulas consistent, and the summary visible at the top. That's usually enough to turn a mortgage from something abstract into something manageable.

---

If you want plain-English mortgage tools without sales pressure, Home Ready Calculator is a practical place to start. It's built for first-time buyers who want honest monthly cost estimates, amortization views, and scenario testing before talking to a lender.