How to Make a Loan Amortization Schedule in Excel (Without Going Crazy)

It’s 11:45 PM. You’ve got a blank spreadsheet open, the cell borders are glaring white, and you’re staring at a row of column headers—Payment Date, Beginning Balance, Payment, Principal, Interest, Ending Balance—wondering why your cell formulas are spitting out #VALUE! errors instead of actual numbers.

You just want to know how much of your next monthly payment is actually chipping away at your debt, and how much is just lining the lender's pocket in interest. It shouldn’t require a degree in computer science or a sacrificial offering to Microsoft.

If you’re searching for "loan amortization schedule excel," you’re probably sitting on a car loan, a mortgage, or a personal loan, and the official statements your lender sends you feel like they're written in ancient Aramaic. You want control. You want to see the future of your debt laid out row by row, month by month, so you can figure out when you'll finally be free of it.

Let’s turn that blank grid into a clear picture. We’ll build a working schedule, spot the formulas that actually matter, and look at why your spreadsheet might be lying to you.

---

Why Lenders Keep the Math a Secret (And How to Unbreak It)

Ever look at an early loan statement and feel a quiet rage? You make a massive payment, and three-quarters of it vanishes into "interest," while your actual loan balance barely budges.

That isn't a glitch. It's front-loaded amortization. In the early days of a loan, your balance is at its absolute highest, so the monthly interest charge is at its peak. As the months tick by, your balance shrinks, which means the interest slice shrinks too, leaving more room for your principal payment to do the heavy lifting.

Lenders love this structure because they get their profit upfront. But seeing it on a static PDF doesn't help you plan. When you build your own amortization schedule in Excel, you pull back the curtain. You stop guessing what happens if you throw an extra £100 at your balance next month, and you start seeing the exact ripple effect across your timeline.

Before we type a single formula, let's set up the staging area. A good spreadsheet separates your inputs (the numbers you can change) from your outputs (the schedule itself).

---

Setting Up Your Control Panel

Open a fresh Excel sheet. In the top left corner, let's build a neat little control block in cells A1 through B6. This is where your loan parameters live. Changing these numbers later will automatically rewrite your entire payment history—that’s the magic of a dynamic spreadsheet.

Set up these labels and placeholder numbers:

• Cell A1: Loan Amount | Cell B1: 200000 (or your currency equivalent)
• Cell A2: Annual Interest Rate | Cell B2: 0.05 (representing 5%)
• Cell A3: Loan Term (Years) | Cell B3: 30
• Cell A4: Payments Per Year | Cell B4: 12

Now we need to calculate two vital helper metrics right below your inputs. These keep your main table clean and prevent your formulas from turning into giant hairballs.

• Cell A5: Total Payments (Term × Payments Per Year)
  • Formula in B5: =B3*B4 (Result should be 360 for a 30-year mortgage)
• Cell A6: Periodic Interest Rate (Annual Rate ÷ Payments Per Year)
  • Formula in B6: =B2/B4 (Result should be 0.004166...)

With these baseline numbers locked in, you’re ready to tackle the hardest part of the entire build: figuring out your exact monthly payment without hardcoding it.

---

The Magic Formula: Calculating Your Periodic Payment

If you guess your monthly payment, your schedule will never hit zero at the end. It will either leave a few awkward pennies hanging or run out of money three months early. We need Excel’s built-in PMT function to do the heavy lifting.

Find an empty cell, say B7, and label it Monthly Payment.

Enter this exact formula: =PMT(B6, B5, -B1)

Let’s translate that into plain English:

• B6 is your interest rate per period.
• B5 is the total number of payments you’ll make.
• -B1 is your starting loan amount, written as a negative number. (Excel expects cash flows to balance; if you borrow money, it’s a positive cash inflow to you, so the repayment must be styled as a negative outflow. If you omit the minus sign, your payment formula will spit out a negative number, which ruins subsequent math).

For our hypothetical £200,000 loan at 5% over 30 years, cell B7 should output roughly £1,073.64.

If your number matches, congratulations. You’ve just built the beating heart of your spreadsheet.

---

Building the Table Header and Row One

Now, move down to row 9 and set up your table headers across columns A through F:

• Cell A9: Period (or Month Number)
• Cell B9: Beginning Balance
• Cell C9: Payment
• Cell D9: Principal
• Cell E9: Interest
• Cell F9: Ending Balance

Row 10 is where your loan officially begins. Let’s walk through the math for month one.

• Cell A10 (Period): Type 0 (this represents day one, before your first payment).
• Cell F10 (Ending Balance): Reference your original loan amount. Type =B1.

Now, move down to row 11. This is your very first actual payment month.

• Cell A11 (Period): Type =A10+1 (this will automatically count up: 1, 2, 3...).
• Cell B11 (Beginning Balance): Type =F10 (your beginning balance this month is last month's ending balance).
• Cell C11 (Payment): Type =$B$7 (lock this cell using the dollar signs so you can drag the formula down later without it shifting).
• Cell E11 (Interest): Type =B11*$B$6 (your beginning balance multiplied by your periodic interest rate).
• Cell D11 (Principal): Type =C11-E11 (your total payment minus the interest chunk leaves what actually pays down the debt).
• Cell F11 (Ending Balance): Type =B11-D11 (your beginning balance minus your principal reduction).

Take a breath. Look at row 11. For our example, your beginning balance is £200,000. Your interest is roughly £833.33. Your principal is £240.31. And your ending balance is £199,759.69.

If you want to map this out manually for all 360 months, you could keep dragging. But before you do that, we need to talk about why basic spreadsheets usually break around month 120.

---

What Trips People Up: The Floating Point Trap

Here is the secret heartbreak of building financial models in Excel: computers are terrible at rounding decimals.

If you drag your formulas all the way down to row 370 (to cover a 30-year loan), look closely at the very last row. Chances are, your ending balance won’t say 0.00. It will say something ridiculous like -£0.02 or 0.0000000001.

While fractions of a penny won't ruin your life, they break conditional formatting and make your grand totals look messy. More importantly, if you try to build advanced models—like checking your progress against an Amortization Calculator—unrounded decimals will compound into noticeable errors over decades.

How to Fix It: The ROUND Function

To keep your spreadsheet honest, you need to wrap your interest and principal formulas in Excel’s rounding function, telling it to strictly stick to two decimal places.

Update your Interest formula in cell E11 to this: =ROUND(B11*$B$6, 2)

And update your Ending Balance formula in F11 to prevent negative debt anomalies: =IF(B11-D11<0, 0, B11-D11)

This small IF statement is your safety net. It tells Excel: If the remaining balance is about to drop below zero, just make it zero. Your spreadsheet will now cleanly terminate on the exact final month instead of dragging out phantom debt into infinity.

---

Extending the Schedule Without Losing Your Mind

You have row 11 working perfectly. Now it’s time to fill out the rest of your loan term.

1. Highlight cells A11 through F11.
2. Hover your mouse over the bottom-right corner of the selection until your cursor turns into a solid black cross (+).
3. Click and drag downward until you hit the row that matches your total payment count (for a 30-year monthly loan, that's row 370).

Scroll all the way down to the bottom. If you set up your IF statement correctly, row 370 should show a clean 0.00 ending balance.

If you'd rather skip building the grid from scratch and want to run scenarios instantly—like seeing how a lump-sum bonus changes your timeline—you can plug your baseline figures directly into a Loan Prepayment Calculator to compare your custom Excel sheet against automated results.

---

The Hidden Power of Seeing the Whole Schedule

When you scroll through your finished spreadsheet, something shifts psychologically. The debt stops feeling like a mysterious, predatory monster living in the cloud. It becomes a staircase of numbers that you can actually measure.

Look at year five of a 30-year mortgage schedule. Notice how much larger the principal slice has become compared to month one. That is the tipping point where compounding interest starts working for your sanity instead of against it.

If you're managing other kinds of debt alongside a mortgage, the logic is identical. Whether you're tracking a vehicle purchase or planning a payoff strategy using a Car Loan Calculator, seeing the schedule exposes the true cost of borrowing. It answers the question every borrower asks at 2 AM: Is paying this off early actually worth it?

Let’s run a quick scenario to prove why having this spreadsheet matters.

---

Worked Example: What Happens When You Add £100 Extra?

Meet Sarah. Sarah took out a £20,000 car loan at an annual interest rate of 6% over 5 years (60 monthly payments).

Using her control panel, Sarah finds her baseline monthly payment via the PMT function comes out to £386.66.

If she sticks to the schedule for 60 months, she’ll pay a total of £3,199.60 in pure interest over the life of the loan. That’s a lot of money spent just for the privilege of borrowing cash today.

So, Sarah decides to test a tweak in her spreadsheet. What if she changes her monthly payment column from £386.66 to £486.66—adding an extra £100 every single month out of her grocery savings?

1. Because her beginning balance is dropping faster every month, the interest calculation (=ROUND(B11*$B$6, 2)) shrinks faster too.
2. In month 12, instead of £78 going toward interest, only £68 goes toward interest, meaning more of her cash hits the principal.
3. Scroll down her custom Excel table, and watch what happens to the period counter.

Sarah’s loan doesn't last 60 months anymore. The ending balance hits 0.00 right at month 48.

By building the schedule herself, Sarah discovers that throwing an extra £100 a month doesn't just knock a year off her timeline—it saves her nearly £700 in total interest charges. She didn't need a financial advisor to tell her that; the spreadsheet did the math the moment she dragged the formula down.

---

When Your Excel Sheet Isn't Enough

Building your own amortization table is immensely satisfying, but it has limits.

Excel can't account for variable interest rates that shift mid-term without rewriting complex nested IF statements. It also doesn't automatically factor in lender-specific prepayment penalties or annual fee adjustments unless you manually code them into your rows.

If your loan features complex compounding rules—such as daily interest accrual common on certain types of unsecured borrowing—a standard monthly schedule in Excel will give you a very close approximation, but it might miss pennies here and there compared to your bank's institutional software.

For quick check-ins when you're away from your desktop, you can always cross-reference your calculations on the go using the free Finlaa app, ensuring your manual spreadsheet matches real-world lending terms.

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

---

Frequently Asked Questions

Why doesn't my Excel loan schedule hit exactly zero at the end?

This is almost always caused by unrounded decimal places compounding over dozens or hundreds of rows. If you use raw numbers without wrapping your interest and balance calculations in the ROUND(..., 2) function, tiny fractions of a cent will accumulate and leave a small positive or negative balance in your final row.

Can I use the PMT function for loans with weekly or bi-weekly payments?

Yes, but you have to adjust your inputs accordingly. If you make bi-weekly payments, your payments per year (B4 in our setup) should be set to 26 instead of 12. Divide your annual interest rate by 26 to get your periodic rate, and multiply your loan term in years by 26 to get your total payment count. Your PMT formula will then output the exact amount due every two weeks.

How do I add a lump-sum extra payment to a specific month in Excel?

To model an irregular extra payment (like a holiday bonus or tax refund), add an Extra Payment column right next to your standard Payment column in your table. In your Principal formula, simply add that extra payment cell into the sum so Excel knows to strip that extra cash directly off the debt balance for that specific row.