How to Build an Amortization Spreadsheet (That Doesn't Break)

It is usually around 11:43 PM when you decide you need a custom amortization spreadsheet. You’ve been staring at a loan offer or a mortgage breakdown, and the lender’s payment portal is giving you the grand total—say, a monthly bite of £1,200 or $1,500—without telling you the quiet story of how that money gets carved up. You want to see the mechanics. You want to know how much of your hard-earned cash is vanishing into interest this month versus actually chipping away at the principal.

So you open Excel or Google Sheets, type "Amortization Schedule" into a blank cell, and brace yourself.

Within twenty minutes, you’re usually knee-deep in a spreadsheet hellscape. #VALUE! errors are mocking you from column D. Your ending balance is coming out negative. And you've somehow built a formula that calculates a monthly interest payment higher than your actual monthly income. You close the laptop, sigh, and wonder why something as simple as borrowing money requires a degree in financial engineering just to track.

Take a breath. You don't need to be a VBA macro wizard or a certified accountant to map out a loan. In fact, the cleanest, most resilient amortization spreadsheets use just a handful of standard functions you can set up in five minutes flat.

Let’s build one together, step by step. We’ll look at why templates often break, how the underlying math actually works, and how to set up columns that won't fall apart when you change your loan amount.

---

Why the Standard Templates Always Break Down

Before we drop a single formula into a cell, it helps to understand why downloading a generic template from the internet often leaves you more frustrated than you started.

Most pre-made templates are over-engineered. They’re stuffed with conditional formatting, hidden macro buttons, and rigid assumptions about whether you pay weekly, monthly, or bi-annually. The moment you want to tweak something simple—like adding an extra £100 a month to your principal, or adjusting your start date from January to mid-August—the delicate web of references snaps.

A good amortization spreadsheet shouldn't be a black box. It should be a transparent window into your debt. It needs to answer three simple questions for every single month of your loan's life:

1. How much interest am I being charged right now?
2. How much of my payment is actually shrinking the debt?
3. What is my exact balance the morning after I pay?

To answer those questions, we aren't going to download a fragile template. We are going to build a lean, bulletproof tracker from scratch.

---

Setting Up Your Control Panel (The Inputs)

Every great spreadsheet starts with an isolated workspace for your variables. Never hard-code numbers like interest rates or loan terms directly into your calculation formulas. If your bank calls you next year to offer a rate renegotiation, or if you decide to borrow a different amount, you want to change one cell and watch the entire schedule update automatically.

Open a fresh sheet in Excel or Google Sheets and label the top left corner "Loan Details". In columns A and B, set up these four input cells:

• Cell A2: Loan Amount | Cell B2: 250000 (or whatever currency amount you're borrowing)
• Cell A3: Annual Interest Rate | Cell B3: 0.055 (representing 5.5%)
• Cell A4: Loan Term (Years) | Cell B4: 30
• Cell A5: Start Date | Cell B5: 2024-10-01

Format cell B3 as a percentage, and cell B2 with your local currency symbol (£, $, or ₹). This is your command center. Everything else we build will point back to these four cells.

---

The Magic Formula: Calculating Your Monthly Payment

Before we build the row-by-row table, we need to know the fixed monthly payment. This is where most people panic, reaching for complex algebraic formulas they haven't thought about since high school algebra.

Fortunately, spreadsheet software has a built-in function for this: PMT.

The syntax looks like this: PMT(Rate, Nper, Pv). Let's translate that into plain English using the cells we just set up:

• Rate: Your periodic interest rate. Since your rate in cell B3 is annual, you need to divide it by 12 for monthly payments (B3/12).
• Nper: The total number of payment periods. If your term in cell B4 is in years, multiply it by 12 (B4*12).
• Pv: The present value, or the total loan amount (-B2). We make this negative because the spreadsheet views a loan as a cash outflow; a negative sign ensures your payment output displays as a clean, positive number.

Create a cell labeled Monthly Payment (say, cell A7) and enter this exact formula in cell B7:

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

Hit enter. If you used our example numbers (£250,000 at 5.5% for 30 years), you should see £1,419.47 staring back at you. That is your fixed monthly anchor. No matter what happens in the schedule below, that payment amount is what leaves your bank account every month.

---

Building the Schedule Columns

Now comes the fun part. Below your control panel, skip a couple of rows and create a table header. This is where your actual amortization schedule lives.

Across Row 10, set up these six column headers:

• Column A: Period (0 to 360)
• Column B: Payment Date
• Column C: Beginning Balance
• Column D: Payment
• Column E: Interest
• Column F: Principal
• Column G: Ending Balance

Let’s populate Row 11 as your starting point (Month 0, before you’ve paid a dime):

• A11: 0
• B11: =B5 (links to your start date)
• C11: Leave blank
• D11: Leave blank
• E11: Leave blank
• F11: Leave blank
• G11: =B2 (links directly to your total loan amount)

Now, move down to Row 12 (Month 1). This is the row you are going to copy and paste all the way down to the end of your loan term. Here is what goes into each cell for Month 1:

• A12 (Period): =A11+1
• B12 (Payment Date): =EDATE(B11, 1) (This automatically bumps the date forward by exactly one month, handling leap years and short months effortlessly).
• C12 (Beginning Balance): =G11 (Your starting balance this month is yesterday's ending balance).
• D12 (Payment): =$B$7 (Lock this reference with dollar signs so it always points to your monthly payment control cell).
• E12 (Interest): =C12 * ($B$3 / 12) (Your beginning balance multiplied by your monthly interest rate).
• F12 (Principal): =D12 - E12 (Your total payment minus the interest chunk leaves what goes to the principal).
• G12 (Ending Balance): =C12 - F12 (Your beginning balance minus the principal reduction).

Highlight cells A12 through G12 and drag them down. For a 30-year loan, you'll drag down 360 rows until your ending balance in column G hits absolute zero.

If you prefer to skip building this from scratch and want to see how the numbers interact instantly, you can also run your scenario through our interactive Amortization Calculator to double-check your spreadsheet's math.

---

A Walkthrough: Sarah’s £200,000 Mortgage

Let’s watch how this spreadsheet behaves in the real world by following Sarah.

Sarah just took out a £200,000 mortgage at an example interest rate of 5% over a 25-year term. She plugs her numbers into her new spreadsheet. Her control panel spits out a monthly payment of £1,169.94.

She looks at Row 12 (her very first month):

• Beginning Balance: £200,000.00
• Interest Charged: £200,000 × (0.05 / 12) = £833.33
• Principal Paid: £1,169.94 - £833.33 = £336.61
• Ending Balance: £200,000 - £336.61 = £199,663.39

Sarah stares at that first row and feels a slight drop in her stomach. Out of her first £1,169.94 payment, £833.33—over 71% of her hard-earned money—went straight to the bank as the cost of borrowing. Only £336.61 actually touched her principal balance.

This is the moment most people feel cheated by the banking system. How is it legal, she thinks, that I'm paying so much interest on day one?

Then she scrolls down to row 120 (Year 10 of her mortgage). Because her beginning balance has dropped, the monthly interest calculation changes:

• Beginning Balance: £145,210.40
• Interest Charged: £145,210.40 × (0.05 / 12) = £605.04
• Principal Paid: £1,169.94 - £605.04 = £564.90

By year 10, the tide has turned. More than half of her monthly payment is now chewing through the principal. She scrolls all the way down to month 300, watches the ending balance tick down to £0.00, and exhales. The spreadsheet worked. She can see the exact trajectory of her debt.

---

Common Traps That Break Your Spreadsheet

Even with a clean build, a few subtle edge cases trip people up. If your spreadsheet starts spitting out weird numbers halfway down the page, check for these three common culprits:

1. Floating-Point Rounding Errors

Computers store decimals in binary, which occasionally leads to tiny rounding discrepancies at the very end of a loan. In month 300, instead of hitting an exact £0.00, your ending balance might read £0.02 or -$0.01.

• The Fix: Wrap your ending balance formula in an IF statement. For example: =IF(C12-F12 < 0.01, 0, C12-F12). This tells the sheet: if the balance is mere pennies, just call it zero and close out the loan.

2. Forgetting Absolute References ($)

If you drag your payment formula down without locking the cell reference (using dollar signs like =$B$7), your payment cell reference will shift down every row. By row 10, your spreadsheet will be trying to pull payment amounts from empty cells down in the footer.

• The Rule: Any cell in your control panel that doesn't change from row to row must have dollar signs protecting it.

3. Mixing Up Annual and Periodic Rates

The single most common error in DIY finance spreadsheets is dividing the loan term by 12 but forgetting to divide the interest rate by 12. If your spreadsheet tells you that you owe more in interest in month one than your entire monthly payment, check your interest rate formula. You likely fed the annual rate straight into a monthly row.

---

What Changes the Answer? (The Power of Overpayments)

The real beauty of building your own amortization spreadsheet isn't just watching the standard path—it's testing what happens when you disrupt it.

What if Sarah decides to throw an extra £100 a month at her principal?

You can modify your principal column to account for extra payments: =D12 - E12 + Extra_Payment_Cell

When Sarah plugs an extra £100 into her model, her 25-year mortgage shrinks dramatically. She stops paying in year 25 and finishes the loan nearly four years early. More importantly, she gets to watch thousands of pounds in future interest charges completely evaporate from her schedule.

That is the emotional payoff of building a clean amortization spreadsheet. It transforms debt from a looming, mysterious cloud into a predictable, solvable math problem. You stop guessing what the bank is doing behind the scenes, and you start seeing the exact levers you can pull to take your financial life back.

Disclaimer: This guide is for educational and informational purposes only and does not constitute formal financial advice. Always verify your loan terms and conditions directly with your lender before making major financial decisions.

---

Frequently Asked Questions

Can I use an amortization spreadsheet for a variable-rate loan?

Standard amortization schedules assume a fixed interest rate for the life of the loan. If you have a variable-rate mortgage or loan, your monthly payment and interest charges will shift whenever your lender adjusts rates. While you can manually update the interest rate in your control panel cell for future rows, a static spreadsheet won't reliably predict future variable rate changes. For variable debt, treat your schedule as a short-term snapshot rather than a 30-year guarantee.

What is the difference between an amortization schedule and a simple interest calculator?

A simple interest calculator figures out interest based on the original principal amount over time. An amortization schedule uses compound and declining-balance math. Every time a portion of your principal is paid off, the interest for the next month is calculated on that smaller remaining balance. This is why early payments are heavily weighted toward interest—the balance is at its highest point.

If you want to run these numbers on the go, download the free Finlaa app to manage your budgets and loans from your phone.