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

You’ve probably opened a blank spreadsheet, stared at the blinking cursor in cell A1, and wondered why something so fundamental feels like building a rocket ship.

Maybe you’re sitting at your desk past midnight, nursing a cold cup of tea, trying to figure out what actually happens to your monthly mortgage payment over the next thirty years. You typed "mortgage amortization table excel" into a search engine because you want control. You want to see the numbers laid out row by row, month by month, so you can stop guessing how much of your hard-earned cash is going toward bricks and mortar versus pure bank profit.

Take a breath. You don't need a degree in finance or a masterclass in macros to make this work.

Building your own amortization schedule in Excel isn't just about satisfying your inner accountant; it’s about demystifying the biggest financial commitment of your life. When you can see the exact tipping point where more of your payment starts hitting the principal than the interest, the future feels a lot less foggy.

Let's walk through how to build a foolproof amortization table from scratch, what those mysterious formulas actually do, and how you can spot the hidden levers that could save you thousands.

---

Why Relying on a Generic Template Falls Short

If you’ve downloaded free templates from the internet before, you know the frustration. They're either locked down so tight you can't customize them, or they're bursting with confusing Visual Basic code that breaks the moment you try to add an extra payment.

A DIY spreadsheet gives you something better: transparency. When you build the formulas yourself, you understand every single moving part.

Before we drop our first formula into a cell, let’s look at the anatomy of what we are building. An amortization table is simply a chronological log of every payment you will ever make. For a standard repayment mortgage, every single row needs to track five specific columns:

1. Payment Number: The month counter (1 through 360 for a 30-year loan).
2. Beginning Balance: What you owe on day one of that specific month.
3. Total Payment: The fixed amount leaving your bank account.
4. Interest Paid: The slice of your payment that goes to the lender for the privilege of borrowing.
5. Principal Paid: The slice that actually shrinks your debt.
6. Ending Balance: What you carry over into the next month.

If any one of these columns is out of whack, your final balance won't hit zero at the end of the term, and your spreadsheet will throw a silent fit. Let's make sure that doesn't happen.

---

Setting Up Your Inputs (The Control Panel)

Before we touch the grid where the rows live, we need a dedicated space at the top of your sheet for your loan details. Think of this as your control panel. If you ever want to test a different interest rate or a shorter term, you only change these cells—the rest of the table will update automatically.

Open a fresh Excel sheet and set up these labels in Column A, with your values in Column B:

• Cell A1: Loan Amount | Cell B1: 300000 (representing your £300,000 or $300,000 mortgage)
• Cell A2: Annual Interest Rate | Cell B2: 0.05 (5%)
• Cell A3: Loan Term (Years) | Cell B3: 30
• Cell A4: Payments Per Year | Cell B4: 12

Now, let's calculate the two hidden numbers Excel needs to build the schedule: your total number of payments and your periodic interest rate.

In Cell A5, type Total Payments, and in Cell B5, enter the formula: =B3*B4 (This gives you 360 total monthly payments).

In Cell A6, type Monthly Interest Rate, and in Cell B6, enter the formula: =B2/B4 (This divides your annual rate by 12 months to get your periodic rate).

Finally, we need to calculate your fixed monthly payment using Excel’s built-in PMT function. In Cell A7, type Monthly Payment, and in Cell B7, enter this exact formula: =PMT(B6, B5, -B1)

Notice the minus sign before B1? That’s an Excel quirk. Because the loan amount is money you received (a cash inflow), Excel treats it as a negative number. Putting a minus sign in front of it tells the function to spit out a positive monthly payment. For our example numbers, you should see a monthly payment of roughly $1,610.46 (or £1,610.46 depending on your local currency).

---

Building the Table Headers and Row 1

Now we move down the sheet to create our actual table. Leave a blank row or two, and starting in row 10, set up your column headers across columns A through F:

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

Let’s tackle Row 11, which represents your very first month.

• Cell A11: Type 1.
• Cell B11: Link this straight to your control panel. Type =B1 (your starting loan amount).
• Cell C11: Type =$B$7 (your fixed monthly payment). Notice the dollar signs? These make it an absolute reference so that when you drag the formula down later, it doesn't wander off.
• Cell D11: This is where we calculate how much interest you owe for month one. Type =B11*$B$6 (your beginning balance multiplied by your monthly interest rate).
• Cell E11: This is your principal reduction. Type =C11-D11 (your total payment minus the interest).
• Cell F11: This calculates your new ending balance. Type =B11-E11 (your beginning balance minus the principal paid).

Look at that row. For our example loan of $300,000 at 5%, your first month's payment of $1,610.46 breaks down into $1,250.00 of interest and $360.46 of principal. Your ending balance is $299,639.54.

That first month can be a bit sobering. More than 77% of your first payment went straight to the bank as interest. But don't worry—the math shifts in your favor every single month.

---

Expanding to Row 2 and Dragging Down

To make the rest of the spreadsheet work, Row 12 needs to know what happened in Row 11.

• Cell A12: Type =A11+1
• Cell B12: Type =F11 (Your beginning balance for month 2 is the ending balance from month 1).
• Cell C12: Type =$B$7
• Cell D12: Type =B12*$B$6
• Cell E12: Type =C12-D12
• Cell F12: Type =B12-E12

Now, select cells A12 through F12. Click the small green square in the bottom-right corner of your selection (the fill handle) and drag it all the way down until your payment counter hits 360 (row 370).

Scroll down to the bottom. Cell F370 should read 0.00 (or a tiny decimal flicker of fractions of a penny). If it hits zero, congratulations—you have successfully engineered a fully functioning mortgage amortization table.

---

When Excel Becomes a Chore (And When to Use a Calculator Instead)

Building an Excel sheet gives you immense satisfaction, but it also reveals the rigid reality of long-term debt. When you scroll down through those 360 rows, you start noticing things that pure numbers on a loan statement don't show you.

For instance, look at row 180—exactly halfway through your 30-year term. In a standard repayment setup, you might assume you’ve paid off half the balance. Scroll over to Column F. Because interest is front-loaded, you actually still owe roughly 65% of your original loan amount at the 15-year mark.

This is the exact moment when people start asking questions about extra payments, refinancing, or shortening their terms. And this is also where wrestling with an Excel sheet to test "what-if" scenarios gets tedious. If you want to instantly model how an extra lump sum changes your timeline without manually rewriting formulas, you can test different scenarios using our dedicated Amortization Calculator to see how small tweaks shift your payoff date.

---

Common Gotchas That Break Amortization Sheets

Even meticulous people run into spreadsheet errors. If your final row doesn't equal zero, or if Excel starts shouting error codes like #VALUE! or #NUM!, check for these common traps:

1. Hardcoding Instead of Referencing

If you type numbers directly into your formula cells instead of linking them to your control panel or previous rows, your table will break the moment you update your interest rate. Always reference cells; never hardcode constants into your math rows.

2. Forgetting Absolute References ($)

If you drag your monthly payment formula down and all your interest calculations return zeros or weird errors, you probably forgot the dollar signs in =$B$7 or =$B$6. Without those anchors, Excel shifts the cell reference down every single row, looking for data in blank space.

3. Ignoring Interest-Only or Variable Rate Complications

Standard amortization formulas assume a fixed interest rate and a fixed payment for the entire life of the loan. If you have an adjustable-rate mortgage (ARM), a static Excel sheet won't predict your future rates. Similarly, if you are looking at investment properties or periods where no principal is being paid down, you'll need a completely different structure, such as the formulas used in an Interest-Only Mortgage Calculator.

---

The Hidden Power of Overpaying

Here is the real magic of building your own amortization table: once you have the basic grid working, you can add an extra column for Extra Principal Payments.

Imagine Sarah, who bought that same $300,000 home at 5% over 30 years. Staring at her Excel sheet, she realizes she pays nearly $1,250 in interest during month one. She decides she hates the idea of paying double the home's purchase price over three decades.

So, she adds a "Extra Payment" column (Column G) and starts typing an extra $200 into every single row.

What happens?

• That $200 goes straight to reducing the beginning balance of the next month.
• Because the beginning balance is lower, the interest calculation for the following month drops.
• More of her standard payment shifts from interest to principal.

Instead of taking 30 full years (360 months) to clear the debt, Sarah’s spreadsheet reveals that adding just $200 a month slashes nearly 6 years off her mortgage term and saves her over $53,000 in lifetime interest.

You don't have to guess at these numbers or wonder if it's worth it. If you're curious about how skipping a few years of payments could alter your personal cash flow, you can map out different overpayment strategies using our Mortgage Overpayment Calculator to see your new projected end date instantly.

---

What to Do Next

Building a mortgage amortization table in Excel takes about ten minutes, but the clarity it gives you lasts for decades. You no longer have to wonder where your money is going or feel intimidated by the scale of long-term debt. You can see the mechanism clearly, row by row, from the first heavy interest payment to the final zero balance.

If you're evaluating a brand-new property purchase, running the numbers on a rental property investment, or just trying to find the breathing room in your monthly budget, having the right tools makes all the difference.

Disclaimer: The figures and formulas used in this guide are for illustrative and educational purposes to help you understand how amortization mechanics work. Actual loan terms, interest calculations, and fees vary by lender and jurisdiction.

For a fast way to run these numbers on the go without building spreadsheets from scratch, try the free Finlaa app to check your scenarios anytime.