How to Make an Excel Amortization Schedule (Without Hating Spreadsheets)

It’s usually around 11:43 PM when you find yourself staring at a loan offer or a mortgage statement, wondering where all your money actually goes each month.

You see a big lump sum for your monthly payment, but you know that only a fraction of it is paying down what you actually borrowed. The rest? Gone to interest. You want to see the long-term damage—or the long-term savings of paying an extra fifty bucks a month—so you open up a blank spreadsheet.

Then the blinking cursor mocks you.

If you’ve ever tried to type PMT or IPMT into Excel and watched the cell flash an angry #NUM! or #VALUE! error, you know the quiet panic of trying to build an excel amortization schedule from scratch. It feels like you need a computer science degree just to figure out what you'll owe three years from now.

Take a breath. You don't need to be a financial analyst or a macro wizard to map this out. Let’s walk through building a clean, stress-free loan schedule together, look at the formulas that actually matter, and see how a few tweaks to your numbers can change your entire financial outlook.

---

Why Bother Building Your Own Schedule?

Sure, your lender provides a statement every month. Sometimes they even give you a portal where you can look at a static amortization table.

So why build your own?

Because static tables don't let you play the "what-if" game. They don't show you what happens if you get a year-end bonus and throw an extra £2,000 at the principal. They don't let you model a refinance cleanly side-by-side.

When you build an amortization schedule in Excel, you take the mystery out of the debt. You turn a scary, faceless obligation into a grid of predictable numbers. And once you see how much interest drops off in the later years of a loan, that 2 AM panic tends to fade into a concrete, manageable plan.

Before we open a blank sheet, let's look at the basic anatomy of what we're building. Every good loan schedule does two things:

1. Splits your fixed monthly payment into Principal (what you actually borrowed) and Interest (what the lender charges you for the privilege).
2. Shrinks your remaining balance month by month until it hits zero.

Let’s follow a realistic, hypothetical scenario to see how this works in practice.

---

Setting Up Your Inputs (The Control Center)

Never hardcode your loan amount, interest rate, or term directly into your math formulas. If you want to change the numbers later, you'll break everything.

Instead, reserve the top left corner of your worksheet (say, rows 2 through 6) as your control center. Let's imagine Sarah, who is looking at taking out a £25,000 personal loan to consolidate some higher-interest credit cards and remodel her kitchen.

Here are Sarah's baseline numbers:

• Loan Amount: £25,000
• Annual Interest Rate: 7.5%
• Loan Term (Years): 5 years
• Payments per Year: 12 (monthly)

In your Excel sheet, set these up cleanly:

• Cell B2: 25000 (Loan Amount)
• Cell B3: 0.075 (Annual Interest Rate)
• Cell B4: 5 (Loan Term in Years)
• Cell B5: 12 (Payments per year)

Right below those inputs, we need to calculate two helper metrics that our formulas will rely on every single month:

• Total Number of Payments (B6): =B4*B5 (which gives us 60 months)
• Periodic Interest Rate (B7): =B3/B5 (which gives us 0.00625, or 0.625% per month)

Now Sarah is ready to calculate her monthly payment.

---

The Magic Formula: Calculating the Monthly Payment

To find out what Sarah owes every month, we use Excel’s built-in PMT function.

The syntax for PMT looks like this: =PMT(rate, nper, pv, [fv], [type])

Translating that to Sarah's cells:

• rate is our monthly interest rate (B7)
• nper is the total number of payments (B6)
• pv is the present value, or loan amount (B2)

In cell B8, type: =PMT(B7, B6, -B2)

(Note the minus sign before B2! Without it, Excel returns a negative number because it views the loan payment as cash leaving your pocket. Keeping it positive makes the rest of our schedule much easier to read.)

Excel will output Sarah's monthly payment: £500.91.

Now, if you want to double-check your work or skip manual building entirely when you're in a rush, you can always test these figures against an online tool like the Finlaa Amortization Calculator to see how the principal-to-interest ratio shifts over time. But building it yourself gives you total control over the columns. Let's build those columns now.

---

Building the Table Headers

In row 10 of your spreadsheet, create the headers for your amortization table:

• Column A: Payment Number (0 to 60)
• Column B: Beginning Balance
• Column C: Payment Amount
• Column D: Principal Paid
• Column E: Interest Paid
• Column F: Ending Balance

Row 11 will be your starting point—Month 0—where Sarah hasn't made any payments yet, but the loan has just landed in her account.

• A11: 0
• B11: (Leave blank for a second, or reference =B2)
• C11, D11, E11: Leave blank
• F11: =B2 (Your starting balance of £25,000)

Now, let's build Month 1 in row 12. This is where the real mechanics live.

---

Writing the Formulas for Month 1 (Row 12)

This is the exact point where most people get tripped up by syntax. Let's make it foolproof.

• Column A (Payment Number): Type 1 in cell A12. (In row 13, you'll put =A12+1 so it auto-increments).

• Column B (Beginning Balance): This is simply the ending balance of the previous month. In cell B12, type: =F11

• Column C (Payment Amount): We want this to pull our fixed monthly payment from our control center. To make it easy to drag down later, lock the cell references with dollar signs. In cell C12, type: =$B$8

• Column D (Principal Paid): Excel has a built-in function for this called PPMT (Principal Payment). Its syntax is =PPMT(rate, per, nper, pv).

  • rate is =$B$7
  • per is the specific period we are calculating (cell A12)
  • nper is =$B$6
  • pv is -$B$2

  Put this in cell D12: =PPMT($B$7, A12, $B$6, -$B$2)

  For Month 1, this will return £344.66. That's how much of Sarah's first £500.91 payment is actually shrinking her debt.

• Column E (Interest Paid): Similar to principal, Excel has an IPMT function for interest. Or, even simpler, you can subtract the principal from the total payment. Let's use the dedicated function to keep it clean: =IPMT($B$7, A12, $B$6, -$B$2)

  For Month 1, this returns £156.25 (which is Sarah's beginning balance of £25,000 multiplied by her monthly rate of 0.00625). Notice how £344.66 + £156.25 neatly equals her £500.91 payment.

• Column F (Ending Balance): This is your beginning balance minus the principal paid this month. In cell F12, type: =B12 - D12

  This gives Sarah an ending balance of £24,655.34.

---

Dragging It Down Without Breaking It

Now comes the satisfying part. Select cells A12 through F12.

Grab the little green fill handle in the bottom-right corner of the selection box, and drag it down until you hit row 71 (which represents Month 60).

If your formulas are locked correctly with dollar signs where needed, your sheet will instantly cascade down to a final ending balance of £0.00.

If your final row shows something like -£0.00 or a tiny decimal leftover due to rounding, don't panic—that's a normal spreadsheet quirk. You can tidy up the visual display by wrapping your formulas in a ROUND(..., 2) function, but for practical purposes, your schedule is fully alive and working.

---

What Trips People Up: Common Mistakes to Avoid

Even when you follow the steps, a few sneaky edge cases love to break amortization schedules. Here is what usually goes wrong and how to sidestep it:

1. Hardcoding the Interest Rate Change

If your loan has a variable rate, a static table built with PMT will lie to you after the first rate adjustment. If you're modeling a variable-rate mortgage, you cannot rely on a single PMT formula across all rows; you have to calculate monthly interest dynamically as Beginning Balance * Current Monthly Rate every single month.

2. Forgetting the Sign Convention

Excel is notoriously picky about whether cash is coming or going. If your formulas return #NUM! errors, check your present value (pv) and payment arguments. If your payment is positive, your loan amount usually needs to be entered as a negative number within the financial functions.

3. Ignoring Extra Payments in the Math

If you decide to pay an extra £100 a month toward principal, your standard schedule will break if it rigidly expects the exact same PMT amount every time. To handle extra payments dynamically, your Ending Balance formula needs to subtract both the scheduled principal and any extra payment column you add to the right.

---

The Real Power: Seeing the Interest Shift

Look down your newly minted spreadsheet at Month 1 versus Month 59.

In Month 1 of Sarah's loan, she pays £156.25 in interest and £344.66 in principal. By Month 59—four years and eleven months later—her balance is down to just about £500. In that month, her interest payment drops to less than £3, and almost her entire monthly payment goes straight to clearing the final remnants of the debt.

This is the hidden gravity of amortization. In the early years of any loan, you are heavily weighted toward paying the lender's fee (interest) before you make a real dent in the asset itself.

Once you see that reality laid out in clear rows and columns, your financial decisions change. You realize why making an extra payment in Year 1 saves you vastly more total interest than making that same extra payment in Year 4.

---

Feeling Steadier About the Road Ahead

Debt can feel like a heavy fog—vague, sprawling, and somehow bigger in your head than it is on paper.

Building an excel amortization schedule clears that fog away. It replaces a looming question mark with a finite list of rows. You can see the exact finish line, month by month, dollar by dollar (or pound by pound).

You don't have to guess what your balance will be when your lease ends, or wonder if an extra payment is truly worth making. The numbers are right there in front of you, behaving predictably, waiting for you to test different scenarios until you find the path that lets you breathe a little easier.

Disclaimer: The formulas, calculations, and scenarios discussed here are for educational and informational purposes only and should not be taken as professional financial advice. Always verify your specific loan terms directly with your lender.

---

Frequently Asked Questions

Can I use Google Sheets instead of Excel for this?

Yes, absolutely. Google Sheets uses the exact same financial formula syntax (PMT, PPMT, IPMT) as Microsoft Excel. You can build this exact same schedule in your browser without changing a single formula.

Why does my final payment not equal zero exactly?

Because of monthly rounding to the nearest cent or penny, loans often leave a tiny fractional leftover (like £0.02 or -£0.01) on the final payment line. Lenders handle this automatically by adjusting the final cent of the last payment, but you can also use Excel's ROUND() function to clean up the visual display in your table.

How do I add extra monthly payments to my schedule?

To account for extra principal payments, add an "Extra Payment" column between your scheduled principal and your ending balance. Then, modify your Ending Balance formula to subtract both the scheduled principal and the extra payment amount for that row, which will naturally shorten your total loan term.

---

To run these numbers on the go, check out the free Finlaa app for quick, calculator-first financial tools.