How to Build Your Own Amortization Worksheet in Excel (Step-by-Step Guide)

You know the routine. It’s past midnight, the house is completely quiet, and you are staring at a blinking cursor in a blank Excel grid. You’ve just clicked through half a dozen dodgy websites offering free downloads, only to find macros that trigger your antivirus, broken formulas that spit out #VALUE! errors, or hidden paywalls demanding your credit card just to see a basic repayment schedule.

You just want to know where your money is actually going.

You want to see how much of next month's payment is vanishing into interest, and how much is actually chipping away at the principal balance of your loan. You want to know what happens if you throw an extra hundred bucks at it every month.

Take a deep breath. You do not need a sketchy template from a stranger's blog, and you certainly don't need a degree in accounting. Building your own amortization worksheet in Excel takes less than ten minutes, and once you build it, you own it. You can tweak it, trust it, and use it to see right through the fog of debt. Let's build it together.

Why Built-in Templates Usually Fail You

Before we start typing formulas into cells, let’s talk about why those downloadable templates on the internet usually make you want to throw your laptop through a window.

Most pre-made spreadsheets are over-engineered. They are bloated with charts you don't care about, conditional formatting that breaks the moment you delete a row, and locked cells protecting formulas you might actually want to customize. Worse, they assume every loan operates on a rigid, unchanging script.

When your lender tacks on a fee, or when you decide to make irregular extra payments starting next summer, those rigid templates break down. You spend more time troubleshooting someone else's broken VLOOKUP formulas than you do actually understanding your loan.

Building your own sheet changes the dynamic entirely. When you lay out the columns yourself, every cell has a purpose that you understand. You become the master of the math, rather than a passenger riding along in a spreadsheet you didn't build. And if you ever want a quick second opinion while you're away from your desktop, you can always cross-check your logic on a clean tool like the Finlaa Amortization Calculator to make sure your columns line up with standard financial math.

Setting Up Your Command Center: The Inputs

Every good spreadsheet starts with a dedicated input block. This is the control panel. If you hardcode your loan amount and interest rate directly into your formulas down in row 50, updating your numbers later becomes a nightmare.

Open a blank Excel workbook and let's set up a clean, professional command center in the top-left corner (Cells A1 through B6).

In Column A, type your labels. In Column B, enter your hypothetical starting figures so we have something concrete to test our formulas against:

• Cell A1: Loan Amount | Cell B1: 200000
• Cell A2: Annual Interest Rate | Cell B2: 0.06 (Format this cell as a percentage)
• Cell A3: Loan Term (Years) | Cell B3: 30
• Cell A4: Payments Per Year | Cell B4: 12

Now, let's calculate the two vital pieces of information Excel needs to build the rest of the schedule: the total number of payment periods, and the periodic interest rate.

In Cell A5, type Total Periods and in Cell B5, enter the formula: =B3*B4 (This gives you 360 total monthly payments for a 30-year loan).

In Cell A6, type Periodic Interest Rate and in Cell B6, enter the formula: =B2/B4 (This breaks your annual 6% rate down into a monthly rate of 0.5%).

The Magic Formula: Figuring Out Your Payment

This is the part where most people reach for a calculator, but Excel has a built-in financial function designed specifically for this. It’s called the PMT function. It looks intimidating, but it is actually remarkably straightforward once you break down its arguments.

Skip down to Cell A8 and type Monthly Payment.

In Cell B8, we are going to write our master payment formula. Click that cell and type: =PMT(B6, B5, -B1)

Let's pause and look at what you just typed, because this is where a lot of people make their first mistake:

• B6 is your periodic interest rate (the monthly rate).
• B5 is the total number of payment periods.
• -B1 is your starting loan amount, expressed as a negative number.

Why the negative sign? In Excel's financial universe, money moving away from you (the loan amount you receive) is positive, and money moving out of your pocket (the payments you make) is negative. If you forget the negative sign on your loan amount, Excel will dutifully calculate your payment as a negative number, which will throw off your subtractions later down the sheet.

Hit Enter. You should see a clean monthly payment of 1199.10. That is your baseline. Every single month, barring extra payments, that is the exact amount required to service the loan and pay it off right down to the penny by month 360.

Building the Schedule Headers

Now that your command center is locked and loaded, it is time to build the actual amortization table below it. Leave a blank row or two for breathing room, and starting in row 11, set up your column headers across columns A through F:

• Cell A11: Payment Number
• Cell B11: Beginning Balance
• Cell C11: Payment
• Cell D11: Principal
• Cell E11: Interest
• Cell F11: Ending Balance

Bold these headers, give them a subtle fill color if you like your spreadsheets looking sharp, and freeze your top panes so your headers stay visible as you scroll down through decades of payments.

You have built the container. Now comes the part where Excel does the heavy lifting.

Writing the Row 1 Formulas (The Starting Point)

The first row of your schedule (Row 12) is slightly different from every row that follows it, because it points directly back to your input command center rather than the row above it.

Let's walk through row 12 step by step:

• Cell A12 (Payment Number): Type 1.
• Cell B12 (Beginning Balance): Type =B1 (This pulls your original £200,000 loan amount straight from your input block).
• Cell C12 (Payment): Type =$B$8 (This locks onto your calculated monthly payment using dollar signs so it doesn't shift when we drag the formula down).
• Cell D12 (Principal): This is where we separate the principal from the interest using Excel’s built-in PPMT function. Type: =PPMT($B$6, A12, $B$5, -$B$1) (This tells Excel: look at the monthly interest rate, look at the current payment number in cell A12, look at the total periods, and tell me how much of this specific payment goes toward the principal).
• Cell E12 (Interest): Similarly, Excel has an IPMT function for interest. Type: =IPMT($B$6, A12, $B$5, -$B$1)
• Cell F12 (Ending Balance): Type your subtraction formula: =B12 - D12 (Your beginning balance minus the principal you just paid off).

Hit Enter. For month 1 on our example loan, you should see an interest payment of 1000.00, a principal payment of 199.10, and an ending balance of 199,800.90.

Take a second to look at that interest number. One thousand pounds out of a twelve-hundred-pound payment going straight to interest in month one. It is a sobering, slightly brutal realization—and it is precisely why people build these spreadsheets. Seeing the raw numbers in black and white changes how you view every financial decision you make from that point forward.

Expanding the Sheet: Row 2 and Beyond

Now comes the magic trick that makes Excel worth its weight in gold.

Row 13 is where your schedule becomes self-sustaining. In Row 13, your beginning balance isn't tied to the top input block anymore; it has to flow directly from the ending balance of Row 12.

Let's enter the formulas for Row 13:

• Cell A13: =A12+1 (This automatically increments your payment number to 2).
• Cell B13: =F12 (Your beginning balance is yesterday's ending balance).
• Cell C13: =$B$8 (Your fixed monthly payment).
• Cell D13: =PPMT($B$6, A13, $B$5, -$B$1)
• Cell E13: =IPMT($B$6, A13, $B$5, -$B$1)
• Cell F13: =B13 - D13

Highlight cells A13 through F13. Grab the bottom-right corner of that selection (the fill handle—your cursor will turn into a small black cross), and drag it down until you hit row 372 (representing payment 360).

Scroll all the way down to the bottom. Look at Cell F372. If your formulas are correct, that ending balance should read precisely 0.00 (or a string of trailing decimal zeros). You have just built a fully functional, dynamic 30-year amortization schedule.

What Trips People Up: Common Spreadsheet Mistakes

Even when you follow the steps, Excel can occasionally throw a tantrum if a single character is out of place. Here are the three most common traps that catch people building their first amortization worksheet, and how to spot them before they drive you mad:

1. The Dreaded Negative Balance at the End

If your final payment leaves you with a tiny negative balance (like -0.02), or if your schedule tries to keep charging you interest past month 360, your rounding or your period counts are slightly out of alignment. Always ensure your total period count in your input block accurately matches the length of your drag.

2. Forgetting Absolute References ($)

If you drag your payment formula down and every cell below row 12 turns into a string of #VALUE! errors, check your dollar signs. If you didn't anchor your interest rate ($B$6) and your total periods ($B$5) with absolute reference anchors, Excel tried to slide those reference cells down the page right along with your rows.

3. Mixing Up Payments Per Year

If you are working on a UK mortgage that compounds interest daily or monthly, or a US loan with standard monthly amortization, make sure your Payments Per Year cell (Cell B4) matches the frequency of your periodic rate. If you input an annual rate of 6% but leave your periodic rate divisor at 1 instead of 12, your spreadsheet is going to calculate monthly payments based on an assumption that you are paying off the entire 6% interest every single month. (Spoiler: your payment will look entirely unaffordable).

Adding Extra Payments to Your Model

A static amortization schedule is interesting, but a dynamic one that lets you test real-life strategies is where your worksheet becomes genuinely powerful. Let's add one more column to make this sheet truly useful: Extra Principal.

Insert a new column between your standard Payment column and your Principal column. Let's call it Cell D11: Extra Payment.

Now, when you want to see what happens if you add an extra £200 to every single monthly payment, you can type 200 into the first row of that column and drag it down.

To make your schedule respect that extra payment, you just need to adjust your Ending Balance formula in Column G (formerly Column F): =B12 - D12 - C12 (Beginning balance minus regular principal minus your extra payment).

Suddenly, your 30-year mortgage shrinks down to 22 years, and you watch thousands of pounds of lifetime interest evaporate right before your eyes. That is the moment the anxiety starts to lift. Debt stops feeling like an endless, immovable monolith and starts looking like a math problem with a clear, definite exit strategy.

A Simpler Way to Start

Building an Excel sheet from scratch is a fantastic exercise—it gives you total control, and there is a quiet satisfaction in watching a complex financial grid snap into place under your own fingers.

At the same time, you don't always have a spare twenty minutes to fiddle with cell coordinates and formatting when you are sitting at your desk trying to make a quick decision. If you ever want to cross-reference your custom spreadsheet against a pre-built tool to verify your totals, or if you just want to run a quick scenario while you're on your phone, you can always test your baseline figures against the interactive Amortization Calculator to make sure your monthly breakdown matches up.

Your spreadsheet is yours now. Save it, back it up, and use it the next time a lender hands you a quote that looks too complicated to untangle. You've got the numbers on your side.

---

Disclaimer: This guide is for general informational and educational purposes only and does not constitute formal financial, tax, or legal advice. Loan terms, interest calculations, and fees vary by lender and jurisdiction. Always review your official loan agreements and consult a qualified professional before making major financial commitments.