Mortgage Amortization Calculator Excel: How to Build Your Own Schedule

It is usually around 11:42 p.m. when the spreadsheet curiosity strikes. You are staring at your online banking portal, looking at a mortgage balance that barely seems to budge despite a year of steady payments. You know a chunk of every monthly direct debit goes to interest, and a smaller chunk goes to the actual loan, but the exact split is locked behind your lender’s clunky mobile app. You want to see the future. You want to know what happens if you throw an extra £100 at the principal next month, or what your balance looks like on your daughter's tenth birthday.

So you open a blank spreadsheet, type "mortgage amortization calculator excel" into a search engine, and hope you don't accidentally download a macro-heavy template that asks for your macro permissions and looks like it was designed in 1998.

Building your own mortgage spreadsheet doesn't require a degree in data science or an afternoon of fighting with broken VBA code. In fact, building it from scratch is the single best way to demystify how your home loan actually works. Once you see the formulas click into place, the mystery vanishes. Let's build one together, step by step, so you can take the wheel.

Why Excel Beats Lender Apps Every Time

Most lender portals give you a summary. They tell you your monthly payment, your current balance, and your remaining term. What they rarely show you in plain, editable black-and-white is the full trajectory of your debt.

When you use a personal Excel sheet, you gain total visibility. You can model scenarios your bank’s app won’t let you touch:

• What happens if interest rates tick up when your fixed-rate period ends?
• How many years do you shave off your term if you round your monthly payment up to the nearest hundred?
• Exactly how much total interest will you hand over to the bank over twenty-five or thirty years?

If you prefer a quick digital check before diving into cell formulas, you can always test your figures on a standard Mortgage Calculator to get your baseline monthly payment. But to truly understand the mechanics of principal versus interest, nothing beats laying out the rows yourself.

Setting Up Your Inputs

Before we build the timeline, we need to define the variables. Open a fresh Excel workbook, click on cell A1, and let's set up a clean input block in the top left corner.

Keep your inputs isolated in columns A and B so your table stays organized. Type these labels and values:

• Cell A1: Property Value | Cell B1: £300,000
• Cell A2: Deposit / Down Payment | Cell B2: £60,000
• Cell A3: Loan Amount (Principal) | Cell B3: =B1-B2
• Cell A4: Annual Interest Rate | Cell B4: 4.5%
• Cell A5: Loan Term (Years) | Cell B5: 25
• Cell A6: Payments Per Year | Cell B6: 12

Notice how cell B3 subtracts your deposit from the property value automatically. This ensures that if you decide to change your deposit amount later, your entire calculation updates without you having to retype your core loan balance.

Next, we need to calculate your fixed monthly payment using Excel’s built-in PMT function. In Cell B7, type this exact formula:

=PMT(B4/B6, B5*B6, -B3)

Hit enter, and you should see a monthly payment of £1,334.81.

Why the negative sign before B3? Excel’s financial formulas treat money going away from you (the loan you receive) as a negative cash flow and money you pay back as a positive one. Adding the minus sign keeps your final payment output positive so it reads intuitively.

Building the Amortization Schedule Headers

Now that Excel knows your monthly obligation, it’s time to map out every single payment from month one to month three hundred.

Move down to row 10. This is where your table headers will live. Set up these columns across row 10:

• Cell A10: Payment No.
• Cell B10: Beginning Balance
• Cell C10: Payment
• Cell D10: Principal
• Cell E10: Interest
• Cell F10: Ending Balance

Format row 10 with a bold font and a clean background fill so it stands out from your data.

Writing the Magic Formulas for Month 1

This is where people usually get intimidated, but we are only going to write formulas for a single row (Row 11). Once Row 11 works, Excel will do the heavy lifting for the rest of the term.

Let’s walk through the math for Month 1 in row 11:

• Cell A11 (Payment No.): Type 1.
• Cell B11 (Beginning Balance): This is simply your total starting loan amount. Type =B3.
• Cell C11 (Payment): This is your fixed monthly payment. To make sure Excel references our input cell correctly and doesn't shift when we drag the formula down, use an absolute reference: =$B$7.
• Cell D11 (Principal): How much of this month's payment goes toward paying down the actual debt? That is calculated using the PPMT function. Type:
  =PPMT($B$4/$B$6, A11, $B$5*$B$6, -$B$3)
  Translation: Look at the annual rate divided by 12, look at payment period A11 (Month 1), look at the total number of periods, and calculate the principal portion of the payment.
• Cell E11 (Interest): How much goes straight to the lender as the cost of borrowing? Use the IPMT function. Type:
  =IPMT($B$4/$B$6, A11, $B$5*$B$6, -$B$3)
• Cell F11 (Ending Balance): Your starting balance minus the principal you just paid off. Type:
  =B11-D11

When you press enter, you should see that out of your £1,334.81 payment in month one, £1,122.31 went straight to interest, and only £212.50 chipped away at your principal. Your ending balance for month one should read £239,787.50.

Seeing that split for the first time can be a sobering moment. It explains why your balance felt frozen for the first two years of homeownership. But don't panic—this is the natural architecture of a standard mortgage.

Scaling It Across the Life of the Loan

Now comes the satisfying part. We are going to turn that single row into a full schedule.

1. Click on cell A12 and type =A11+1. This ensures your payment numbers increment automatically (2, 3, 4...).
2. Click on cell B12 (Beginning Balance for month 2) and type =F11. Your starting balance this month is last month's ending balance.
3. Highlight cells C11 through F11 (Payment, Principal, Interest, Ending Balance).
4. Grab the bottom-right corner of that highlighted block (the fill handle) and drag it down to row 310 (to cover all 300 months of a 25-year mortgage).
5. Do the same for cells A12 and B12, dragging them down to row 310 as well.

Scroll down to row 310. Your ending balance in cell F310 should hit £0.00 (or a penny or two off due to rounding). You have just successfully built a fully functioning amortization schedule.

If you want to test the long-term impact of chipping away at your balance early, you can explore how extra contributions alter this timeline by pairing your spreadsheet with an Amortization Calculator to double-check your manual totals.

Common Spreadsheet Traps and How to Avoid Them

Even seasoned Excel users run into a few classic snags when building financial schedules. If your numbers start throwing #NUM! errors or your final balance refuses to hit zero, check for these common culprits:

1. Forgetting Absolute References

If you drag your payment formula down and your numbers turn into gibberish, you likely forgot the dollar signs ($) in =$B$7. Without the dollar signs, Excel assumes you want to move down to cell B8, B9, and B10 as you drag the row down, which breaks the formula.

2. Mismatching Terms and Frequencies

If your loan term is 25 years, make sure your total periods equal 25 * 12 (300 months). If you mix up annual figures with monthly figures inside your PPMT or IPMT arguments, Excel will return an error because the timeline lengths won't align.

3. Ignoring Rate Resets

Remember that our basic spreadsheet assumes a fixed interest rate for the entire 25 years. If you are on a 2-year or 5-year fixed rate deal (common in the UK) or an adjustable-rate mortgage (common in the US), your rate will change when that introductory period expires. A standard spreadsheet won't predict future interest rate hikes; it simply models what happens if your current rate stays locked forever.

What Changes the Answer?

Once your spreadsheet is humming along, you can start playing with the levers. This is where a custom Excel model transforms from a static homework assignment into a practical financial tool.

• Changing the interest rate: If you change cell B4 from 4.5% to 5.5%, watch what happens to your total interest paid at the bottom of column E. A single percentage point shift can add tens of thousands of pounds or dollars over the lifetime of the loan.
• Adding small overpayments: What if you add an extra £100 to your principal every month? You can modify your principal formula or add an "Overpayment" column that subtracts from your ending balance each month. When you do this, watch how quickly row 310 creeps up—you might find that your 25-year mortgage drops to 21 years just by cutting out one takeout meal a week.

If you are specifically modeling how lump sums or monthly overpayments shorten your timeline, running those adjustments through a Mortgage Overpayment Calculator can give you a quick reality check before you rewrite your spreadsheet formulas.

Taking Control of Your Debt

Building a mortgage amortization calculator in Excel takes about ten minutes, but the clarity it provides lasts for the entire life of your loan.

You no longer have to guess how much of your hard-earned money is vanishing into interest charges, and you don't have to rely on a opaque banking app to tell you when you'll finally cross the halfway mark on your equity. You built the engine, you control the variables, and you can see the exact path from where you are today to the day you own your home outright.

Disclaimer: This guide is for educational purposes and provides general information, not personalized financial advice. Always verify your loan terms and calculation outputs against your official lender statements before making major financial decisions.

---

For financial calculations on the go, check out the free Finlaa app to run your numbers anytime, anywhere.

Frequently Asked Questions

Can I use Google Sheets instead of Microsoft Excel for this?

Yes, absolutely. Google Sheets uses the exact same financial functions (PMT, PPMT, IPMT) with the exact same syntax. You can build this exact model in your browser for free without needing a paid Microsoft 365 subscription.

Why does my final row end with a few pennies left over?

Rounding variances are completely normal in long-term amortization schedules due to how fractional pence or cents are handled across hundreds of monthly compounding periods. In your final month, you can simply adjust your final payment slightly so that your ending balance hits zero cleanly.

How do I handle interest-only periods in my spreadsheet?

If you have an interest-only mortgage period (where you aren't paying down any principal for the first few years), your principal payment column will read zero, and your monthly payment will consist entirely of interest calculated as (Beginning Balance * Annual Rate) / 12. You can also model this structure directly using an Interest-Only Mortgage Calculator to see the distinct shift when the repayment phase finally kicks in.