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

It’s 11:45 PM. You’ve got a spreadsheet open on your laptop, a glowing cursor blinking mercilessly in cell B4, and a cold cup of tea sitting beside you. You just want to know one simple thing: where is my hard-earned money actually going every month?

Maybe you’re staring down a mortgage, a car loan, or a business debt, and the official statements look like ancient hieroglyphics. The bank tells you your monthly payment, but they don't show you the quiet, grinding math happening behind the curtain—how that first payment is mostly padding their pockets with interest, while the principal balance barely budges.

You typed "excel loan amortization" into a search engine because you want to see the whole story in black and white. You want to know what happens if you pay an extra hundred pounds a month, or how fast that balance actually drops three years from now.

Take a breath. You don't need a degree in finance, and you certainly don't need to pay for a complicated subscription tool. Excel already has the exact engine you need hidden right inside it. Let’s build a clean, working amortization schedule together, step by step, so you can finally see the numbers clearly and exhale.

---

Why the Banks Keep It Hidden (And Why You Need to See It)

Before we touch a keyboard, let's talk about what an amortization schedule actually is. It sounds like medical jargon, but it’s remarkably simple.

An amortization schedule is just a chronological table of every single payment you will ever make on a loan. It breaks each payment down into two buckets:

1. Interest: The cost of borrowing the money, calculated based on what you still owe.
2. Principal: The actual chunk of debt you are wiping out, which shrinks the balance for next month.

Here is the secret banks don’t throw ticker-tape parades for: amortization is front-loaded.

In the early days of a long-term loan, the vast majority of your monthly payment goes toward interest. If you borrow £300,000 for a home, your very first payment might be around £1,600. Out of that £1,600, upwards of £1,200 might go straight to interest, leaving a pitiful £400 to actually chip away at the principal.

Seeing that for the first time can feel a bit gut-wrenching. But seeing it also gives you power. When you know when the tide turns—the exact month where more of your money starts hitting the principal than the interest—you stop feeling like you're running on a treadmill. Let's build the tool that shows you that exact turning point.

---

Setting Up Your Excel Sheet: The Inputs That Drive Everything

Open a blank Excel workbook. We are going to build this so that you can change your loan amount, interest rate, or term at any time, and the entire table will instantly recalculate.

In the top left corner (rows 1 through 5), let's create a clean control panel.

• Cell A1: Loan Amount | Cell B1: £250,000 (or whatever currency fits your loan)
• Cell A2: Annual Interest Rate | Cell B2: 5.5%
• Cell A3: Loan Term (Years) | Cell B3: 25
• Cell A4: Payments Per Year | Cell B4: 12

Now, we need to calculate two critical helper cells before we build our table.

• Cell A6: Total Number of Payments | Cell B6: =B3*B4 (This gives us 300 total monthly payments).
• Cell A7: Periodic Interest Rate | Cell B7: =B2/B4 (This breaks your annual rate down into a monthly rate).

This is where many people mess up their first spreadsheet: they forget to divide the annual interest rate by the number of payment periods per year. If your annual rate is 5.5%, your monthly rate isn't 5.5%—it’s roughly 0.458% per month. Excel needs that monthly slice to do the math correctly.

---

The Magic Formula: Finding Your Monthly Payment

Before we list out every month, we need to know the fixed payment amount. This is where Excel's built-in PMT function saves you from having to remember high school algebra.

In Cell A9, type Monthly Payment.

In Cell B9, type this exact formula: =PMT(B7, B6, -B1)

Let's look at why that negative sign is sitting in front of B1. In Excel's financial universe, money leaving your hand (like a loan disbursement) is negative, and money coming back to you is positive. Since the loan amount you entered was positive, putting a minus sign in front of it tells Excel, "Treat this as a cash outflow so my resulting payment comes out as a positive number."

If you set up cells B1 through B7 correctly, cell B9 should instantly populate with your exact monthly payment. For our example of £250,000 at 5.5% over 25 years, your payment comes out to £1,533.57.

(If you ever want to check your math or test out different scenarios on the go without firing up a spreadsheet, you can also pop these numbers right into a structured tool like the Amortization Calculator to see the full breakdown instantly.)

---

Laying Out the Schedule Columns

Now comes the fun part: building the ledger.

In Row 11, set up your column headers:

• Column A: Payment Number
• Column B: Beginning Balance
• Column C: Payment
• ColumnD: Principal
• Column E: Interest
• Column F: Ending Balance

Row 1: Your Very First Month

Let's write the formulas for Month 1 right underneath those headers, starting in row 12.

• Cell A12 (Payment Number): Type 1
• Cell B12 (Beginning Balance): Type =B1 (This pulls in your original £250,000 loan amount from our control panel).
• Cell C12 (Payment): Type =$B$9 (We use dollar signs to lock this cell reference, because every single monthly payment will be this exact same fixed amount).
• Cell D12 (Principal): Type =C12-E12 (Wait, we haven't calculated interest yet! Let's do E12 first).
• Cell E12 (Interest): Type =B12*$B$7 (This takes your beginning balance for the month and multiplies it by your monthly interest rate).
• Now, go back and fix Cell D12 (Principal): =C12-E12 (Your payment minus the interest chunk leaves what goes to the principal).
• Cell F12 (Ending Balance): Type =B12-D12 (Your beginning balance minus the principal you just paid off).

If you’ve typed everything correctly, Month 1 should show that out of your £1,533.57 payment, roughly £937.50 went to interest and £596.07 went to principal, leaving an ending balance of £249,403.93.

Take a second to look at that. You just automated the bank's secret ledger.

---

Filling Out the Rest of the Table Without Losing Your Mind

Now you need to do this for the remaining 299 months. Just kidding. Please don't type formulas 300 times. This is Excel, after all.

For Row 13 (Month 2), the formulas change just slightly because your beginning balance is now last month's ending balance:

• Cell A13: =A12+1
• Cell B13: =F12 (Pulls yesterday's ending balance to be today's starting balance).
• Cell C13: =$B$9
• Cell D13: =C13-E13
• Cell E13: =B13*$B$7
• Cell F13: =B13-D13

Now, highlight cells A13 through F13. Look at the bottom-right corner of your highlighted selection until your cursor turns into a tiny black cross (+). That’s the fill handle.

Double-click that cross, or click and drag it all the way down to Row 311 (Month 300).

Boom. The entire 25-year history of your financial commitment just waterfalls down your screen in less than a second. Scroll down to row 312. Does the ending balance hit exactly £0.00? If it does, your spreadsheet is mathematically airtight.

---

What Trips People Up: Common Excel Amortization Traps

Even with a clean guide, a few sneaky edge cases tend to trip people up when building these sheets. Here’s what to watch out for so you don't stare at error codes at midnight.

1. The Dreaded #VALUE! or #NUM! Errors

If Excel throws a tantrum and fills your columns with error codes, it almost always comes down to one of two things:

• You forgot a minus sign in your PMT formula, resulting in a negative payment value.
• You forgot to anchor your interest rate or payment cell with dollar signs ($B$7 instead of B7), so when you dragged the formula down, Excel started pulling blank cells below your control panel.

2. The Final Penny Problem

Look all the way at the bottom of your amortization table. Sometimes, due to rounding decimals over hundreds of months, Month 300 won't land on a crisp zero—it might leave a balance of one or two cents, or overshoot by a penny.

Don't panic. Banks handle this by simply adjusting the very final payment down by a few cents to clear the ledger. If it bothers you, you can wrap your ending balance formula in an IF statement, but for personal planning, a one-cent discrepancy is entirely normal.

3. Assuming Fixed Rates Last Forever

Life isn't always fixed. If you have an adjustable-rate mortgage (ARM) or a loan where the rate fluctuates, a standard static amortization sheet won't predict the future. This sheet tells you what happens if the rate stays locked right where it is today. Treat it as a baseline baseline scenario, not a crystal ball.

---

Let's Run a Real-World Scenario: Sarah’s Car Loan

Let’s step out of the spreadsheet formulas for a moment and look at how someone actually uses this in real life.

Meet Sarah. Sarah just bought a reliable used car to get to her new job. She took out a loan of £20,000 at an annual interest rate of 7.5% over a term of 5 years (60 months).

When she plugs those numbers into her new Excel template, her monthly payment comes out to £400.76.

At first, Sarah feels pretty good about a £400 monthly commitment. It fits neatly into her budget. But then she scrolls down her newly built Excel schedule to Month 36 (three years into the loan).

She looks at the columns and realizes something sobering:

• Total payments made so far: £14,427.36
• Total interest paid to the bank so far: £3,152.14
• Remaining principal balance: £8,724.74

Even though she is more than halfway through the timeline of her loan payments, she still owes nearly half of the original £20,000 balance because the front-loaded interest ate up so much of her early payments.

Seeing that number makes Sarah pause. She realizes she doesn't want to carry that debt for a full five years and pay over £4,000 in total interest if she can avoid it.

---

The Power of Extra Payments: Beating the Bank at Their Own Game

This is where building your own spreadsheet transforms from a nerdy exercise into a financial superpower. Sarah decides to test a tweak. What if she adds an extra £100 every single month to her car payment, pushing it from £400.76 to £500.76?

How do you model that in Excel?

You can modify your amortization table by adding an "Extra Principal" column, or you can use a shortcut to see the big picture. When you pay extra principal directly against the balance, you are effectively starving the interest calculation for every subsequent month.

If you want to play with prepayment scenarios without redesigning your whole sheet from scratch, tools like the Loan Prepayment Calculator let you toggle extra monthly or annual payments instantly to see how many years vanish from your timeline.

For Sarah, throwing an extra £100 a month at her £20,000 car loan does something remarkable:

• It shrinks her payoff timeline from 60 months down to 46 months.
• It lops 14 months completely off her payment schedule.
• It saves her over £600 in total interest that would have otherwise gone straight to the lender.

Suddenly, that extra £100 a month doesn't feel like a sacrifice—it feels like buying back a year of her life and keeping her hard-earned cash in her own pocket.

---

What If Your Loan is for Education or a House?

The exact same Excel logic scales up or down depending on what you're financing.

If you're dealing with student debt, the interest calculations work identically, though grace periods and income-driven repayment plans can sometimes introduce extra moving parts. If you want to map out how different repayment strategies impact your student loans, you can model them using the Student Loan Payoff Calculator.

If you're looking at a massive property purchase, getting your head around the monthly breakdown before talking to a lender changes the entire conversation. You walk into the bank knowing exactly what you can comfortably afford, rather than letting a loan officer tell you what your budget is. You can test property values and down payment impacts using the Home Loan EMI Calculator.

And if you're buying a vehicle, running the numbers on a shorter term versus a longer term will instantly show you the trap of stretching a car loan out to 72 or 84 months just to get a lower monthly payment. (Spoiler alert: you end up paying for a depreciating asset twice over in interest.) You can check those auto scenarios with the Car Loan Calculator.

---

The Real Reason You Built This Spreadsheet

When you first opened that blank Excel sheet at 11:45 PM, you might have felt a knot in your stomach. Debt has a funny way of making us feel small, as if the numbers are too big to control and the rules are written by people in suits who won't explain them.

But look at your screen now.

You built a grid. You typed in a few simple formulas. And now, every single dollar of your future loan payments is sitting right there in front of you, laid out in neat rows. There are no mysteries left. There are no surprise fees hiding in the dark.

You know what you owe, you know where every penny is going, and more importantly, you know exactly how to change the ending of the story by making extra payments whenever you have room in your budget.

That knot in your stomach? It’s probably starting to loosen up right about now. That is the quiet confidence that comes from clarity. You aren't at the mercy of the schedule anymore—you own it.

Disclaimer: The formulas and examples above are for educational and informational purposes to help you understand how loan math works. Because every financial institution uses slightly different rounding rules and compounding frequencies, your actual lender statements may vary by a few pence or cents.

---

Frequently Asked Questions

Can I use Excel's built-in loan template instead of building my own?

Yes, Excel actually comes with a pre-made "Loan Amortization Schedule" template if you search for it in the New Document menu. However, building your own from scratch (the way we just did) takes about five minutes and teaches you how the underlying math works. When you build it yourself, you can easily customize it with extra columns, conditional formatting, or custom payoff strategies that rigid pre-made templates won't let you touch.

Why doesn't my final payment bring the balance to exactly zero?

This usually happens because of rounding. Interest accrues to fractional decimals (fractions of a cent or penny), but currency is displayed rounded to two decimal places. Over the course of a 300-month loan, these tiny rounding discrepancies accumulate, leaving your final payment off by a penny or two. Lenders automatically adjust the final payment downward by that tiny fraction so your account closes at zero, so you don't need to stress if your sheet ends on £0.01 instead of £0.00.

How do I add extra lump-sum payments into my Excel sheet?

To handle irregular extra payments (like putting a holiday bonus or tax refund toward your principal), you can add an "Extra Payment" column right next to your standard payment column. Then, update your principal formula to subtract both your standard principal portion and your extra payment from the beginning balance: =C12 + ExtraPayment - E12. This immediately drops your beginning balance for the following month and reduces the overall interest you'll pay.

---

Want to run these numbers on the go? Download the free Finlaa app to calculate payments, test amortization schedules, and check your loan scenarios from anywhere.