How to Build Your Own Mortgage Amortization Schedule in Excel (Without Going Crazy)

It’s 11:30 PM. The house is quiet, except for the hum of the refrigerator and the blinking cursor on a blank Excel spreadsheet. You’ve got a mortgage offer sitting on your kitchen counter, or perhaps you're just trying to figure out how on earth a £250,000 loan turns into nearly half a million pounds over twenty-five years.

You typed "mortgage amortization schedule excel" into a search engine because you want to see the truth. Not a vague summary from a bank brochure, but the actual month-by-month math. You want to see every single payment broken down into principal and interest, row by row, until that balance hits absolute zero.

You don't need a degree in finance to build this. You just need a few basic formulas, a little patience, and a spreadsheet that won't yell at you when you miss a decimal point. Let’s build it together.

Why Lenders Hide the Schedule (And Why You Need to See It)

When most people sign a mortgage, they look at the monthly payment and stop there. Lenders are happy to let you do that. Because if you glance at the early years of a standard repayment schedule, a sobering reality stares back at you: the vast majority of your hard-earned money isn't paying off your home at all. It’s paying rent on the money you borrowed.

In month one, interest is calculated on your enormous starting balance. That means nearly every penny of your first payment goes straight into the lender's pocket, while your actual loan balance barely budges.

Seeing this in black and white can feel a bit deflating at first. But there is a massive upside to looking behind the curtain. Once you understand the mechanics of how your balance shrinks, you stop being a passive passenger and start seeing the levers you can pull—like how a tiny extra payment today rewrites the financial future of the next decade.

Before we start building cells and formulas, let's look at how this plays out in real life with a specific example.

Meet Sarah and Her 25-Year Mortgage

To make our spreadsheet practical, let’s follow Sarah. She’s buying a home and taking out a £200,000 mortgage over 25 years (300 monthly payments). Her bank has offered her a fixed interest rate of 5% per year.

If you want to check these numbers or test your own scenarios instantly without wrestling with formulas, you can plug them right into a tool like the Mortgage Calculator — /calculators/mortgage-calculator.

For Sarah, that calculator spits out a fixed monthly payment of £1,169.97.

Now, if you ask Sarah what happens to that £1,169.97 every month, she knows it pays off the house. But an amortization schedule reveals the secret life of that payment.

• In Month 1: Out of that £1,169.97, a staggering £833.33 goes to interest. Only £336.64 actually pays down the principal balance.
• In Month 150 (Halfway through): The tide finally turns. Interest drops to £465.12, while principal climbs to £704.85.
• In Month 300 (The final payment): Interest is down to £4.85, and the final £1,165.12 wipes out the last dust particle of the debt.

Seeing this progression changes how you view your loan. It turns a giant, faceless debt into a predictable journey. And the best part? You can build this exact tracking mechanism yourself in Excel in about ten minutes.

Step 1: Setting Up Your Excel Dashboard

Open a blank Excel workbook. We are going to divide your sheet into two logical zones: the Loan Summary Inputs at the top, and the Amortization Schedule Table below it.

In the top left corner, let's set up the variables using cells A1 to B5:

• A1: Loan Amount | B1: 200000
• A2: Annual Interest Rate | B2: 0.05 (Format this as a percentage)
• A3: Loan Term (Years) | B3: 25
• A4: Payments Per Year | B4: 12
• A5: Monthly Payment | B5: We'll use a formula here

To make Excel calculate Sarah's monthly payment automatically in cell B5, click on that cell and paste this standard financial formula:

=PMT(B2/B4, B3*B4, -B1)

Let's break down why that formula works:

• B2/B4 takes the annual interest rate (5%) and divides it by 12 to get the periodic monthly rate.
• B3*B4 multiplies the 25-year term by 12 months to get 300 total payment periods.
• -B1 is the starting loan amount. We make it negative because Excel expects cash outflows (like loan payments) to be negative, and we want our final payment result to display as a clean, positive number.

Hit Enter, and cell B5 should proudly display £1,169.97. If it doesn't, check your decimal places and make sure cell B2 is formatted as a percentage.

Step 2: Building the Column Headers

Now we need to build the table where the magic happens. Skip a couple of rows down to row 8. We need six columns to track the life of the loan.

Type these headers into cells A8 through F8:

• A8: Payment Number
• B8: Beginning Balance
• C8: Payment
• D8: Principal
• E8: Interest
• F8: Ending Balance

To keep things looking sharp, bold these headers and give them a light grey fill color. A clean spreadsheet is much easier to debug when a formula goes rogue.

Step 3: Writing the Formulas for Row 1 (The First Month)

This is where most people get tripped up. The first row of your table is slightly different from the rows below it because it pulls its starting balance directly from your input cell at the top, rather than the previous row's ending balance.

Let's populate row 9 (Months 1):

• Cell A9 (Payment Number): Type 0 in row 8? No, let's start right at the top. Type 1 in cell A9.
• Cell B9 (Beginning Balance): We want this to pull straight from our input cell. Type =B1.
• Cell C9 (Payment): We want this to lock onto our calculated monthly payment so it never changes. Type =$B$5. (The dollar signs create an "absolute reference," meaning Excel won't shift this cell down when we drag the formula later).
• Cell D9 (Principal): How much of this payment goes to the house? We use Excel's PPMT formula. Type: =PPMT($B$2/$B$4, A9, $B$3*$B$4, -$B$1)
• Cell E9 (Interest): How much goes to the bank? We use the IPMT formula. Type: =IPMT($B$2/$B$4, A9, $B$3*$B$4, -$B$1)
• Cell F9 (Ending Balance): What is left over after the payment? Take your beginning balance and subtract the principal paid. Type: =B9-D9

Hit Enter. For Sarah, cell D9 should show £336.64, cell E9 should show £833.33, and cell F9 should show £199,663.36.

If your numbers match, take a deep breath. You just wrote the hardest part of the entire spreadsheet.

Step 4: Extending the Schedule for the Rest of the Loan

Now we need to repeat this math for all 300 months of Sarah's mortgage.

Let's set up row 10 (Month 2) so that it connects seamlessly to the row above it:

• Cell A10: =A9+1 (This increases the payment number by 1)
• Cell B10: =F9 (The new beginning balance is yesterday's ending balance)
• Cell C10: =$B$5 (The payment amount stays fixed)
• Cell D10: =PPMT($B$2/$B$4, A10, $B$3*$B$4, -$B$1)
• Cell E10: =IPMT($B$2/$B$4, A10, $B$3*$B$4, -$B$1)
• Cell F10: =B10-D10

Now, highlight cells A10 through F10. Hover your mouse over the bottom-right corner of the selection until your cursor turns into a solid black plus sign (+).

Click and drag that fill handle down until you hit row 308 (which represents payment 300).

Scroll all the way down to the bottom of your table. If you built it correctly, cell F308 in the ending balance column should display £0.00 (or a tiny rounding error like -£0.01). Seeing that exact zero pop up at the bottom of a 300-row spreadsheet is one of the most satisfying feelings in personal finance.

Common Mistakes That Break Excel Amortization Schedules

Even experienced spreadsheet users make a few classic blunders when building these out. If your ending balance refuses to hit zero, check for these common traps:

1. Forgetting Absolute References ($)

If you copy your monthly payment cell down the column and your formulas start throwing #VALUE! or #REF! errors, you probably forgot to lock your input cells with dollar signs (like -$B$1 instead of -B1). Without those dollar signs, Excel tries to look down the blank columns of your table for input values.

2. Mismatching Terms and Frequencies

If you have a monthly mortgage (12 payments a year) but your interest rate input is divided by something else, the math falls apart instantly. Always ensure your interest rate is divided by 12 and your total periods are multiplied by 12 if you are tracking monthly schedules.

3. Ignoring Small Rounding Errors on Final Payments

Sometimes, due to the way banks round fractions of pennies, your 300th payment might leave a residual balance of £0.02 or -£0.01. This is normal. Lenders adjust the final payment by a penny or two to square the books. If it bothers you visually, you can wrap your final ending balance formula in an IF statement, but a tiny rounding discrepancy is just a quirk of financial math.

What Changes the Answer? (Edge Cases and Adjustments)

Life rarely follows a clean 25-year fixed path without a few bumps. What happens when your financial reality diverges from a textbook spreadsheet?

Variable Interest Rates

If you have a tracker or adjustable-rate mortgage, a static amortization schedule breaks down the moment your interest rate changes. To handle a variable rate in Excel, you can't just use a single global rate in cell B2. Instead, you have to turn your interest rate column (E) into an adjustable input where you manually update the rate whenever your central bank or lender adjusts their standard variable rate.

Making Extra Payments

This is where building your own spreadsheet truly pays off. If Sarah decides to pay an extra £100 a month toward her principal, a static bank schedule won't show her how much time and interest that saves.

If you want to test the compounding power of extra payments without building a complex macro-enabled ledger, you can check your scenarios instantly using a specialized tool like the Mortgage Overpayment Calculator — /calculators/mortgage-overpayment-calculator.

Generally speaking, throwing even a modest extra sum at your principal in the first five years of a mortgage hacks years off the tail end of your loan because it starves the interest calculation of its fuel.

The Real Value of the Spreadsheet

When you finish building your amortization schedule, you realize something empowering: a mortgage isn't an impenetrable black box controlled entirely by a bank. It is simply a mathematical function governed by three things: how much you borrow, how long you take to pay it back, and the interest rate attached to it.

When you can see every row in Excel, you stop fearing the debt. You can test what happens if interest rates rise upon renewal. You can see how much faster you build equity if you round your payments up to the nearest hundred.

You started this process tonight staring at a blank screen and a daunting set of numbers. Now, you have a living, breathing model of your financial future sitting right on your desktop.

---

Disclaimer: This guide is for educational and informational purposes only and does not constitute financial or legal advice. Mortgage rules, tax laws, and lending criteria vary by region (UK, US, and India). Always consult a qualified mortgage broker or financial advisor before making major borrowing or repayment decisions.

---

Frequently Asked Questions

Can I use the Excel AMORLINC or AMORDEGRC formulas instead of manual rows?

Excel does have built-in depreciation and amortization formulas, but be careful—AMORLINC is designed specifically for French accounting and tax depreciation methods, not standard consumer repayment mortgages. Using the PMT, PPMT, and IPMT combination we walked through above is the gold standard for standard residential repayment schedules because it matches how banks actually calculate consumer loans.

What if my mortgage uses daily interest compounding instead of monthly?

Most standard consumer mortgages calculate interest daily based on the outstanding balance, even if you only make payments once a month. However, the practical difference between daily compounding and monthly amortization tables over the course of a 25- or 30-year term is usually negligible (often just a few pounds or dollars over the life of the loan). A monthly schedule is accurate enough for 99% of personal financial planning and forecasting.

How do I handle a balloon payment or interest-only period in my spreadsheet?

If you have an interest-only period at the beginning of your loan (common in some buy-to-let structures or commercial arrangements), your principal payment columns will be set to zero for those initial months, and your payment will only cover the interest. To model this, you can switch between standard repayment formulas and a dedicated calculator structure. For specialized loans, you can map out the math using an Interest-Only Mortgage Calculator — /calculators/interest-only-mortgage-calculator to see how deferred principal impacts your future balance.

---

Want to run these numbers on the go? Download the free Finlaa app to take our mortgage and loan calculators with you anywhere.