How to Build an Amortization Schedule with a Fixed Monthly Payment in Excel

It’s past midnight, your laptop screen is casting a pale blue glow across an empty room, and you’re staring at a blank Excel sheet that refuses to do what you want it to do. You just want to see where your money actually goes each month—how much is quietly slipping away as interest, and how much is actually chipping away at the principal balance of your loan. You type in a formula, hit enter, and get a wall of error codes or, worse, a compounding spiral of numbers that clearly makes no mathematical sense.

You take a deep breath, rub your eyes, and wonder why something as fundamental as a fixed monthly payment feels like trying to decipher ancient code.

If you’ve been hunting around for how to build an amortization schedule with fixed monthly payment excel templates that don't break, you are in the exact right place. We are going to strip away the jargon, throw out the overly complex macros, and build a clean, bulletproof schedule together. By the time you close this tab, you’ll not only have a working spreadsheet, but you’ll actually understand how every single cell breathes.

---

Why Standard Loan Tables Drive Us Crazy

Most tutorials online drop a massive chunk of ready-made code or a pre-built template in your lap, tell you to fill in the blanks, and wave goodbye. But the moment you change your loan term from 3 years to 5, or pop in a different interest rate, the whole thing shatters. Cells throw #VALUE! errors, your ending balance stays stubbornly positive on the final month, and you’re back to square one.

The frustration usually comes from a misunderstanding of how a fixed payment operates under the hood. When you take out a fixed-rate loan—whether it’s a car note, a personal loan, or a mortgage—your monthly payment is a locked-in, unchanging number. But the guts of that payment shift every single month.

At the start, you are paying mostly interest because the bank calculates that fee based on the enormous balance sitting in your account. By the end of the loan lifecycle, that ratio flips entirely.

To build an amortization schedule that survives changes and lets you sleep at night, you need a setup that respects this mechanical shift. You don't need a degree in data science; you just need four basic columns and one powerful Excel formula.

---

Setting Up Your Control Panel

Before we touch a single row of data, we need to build a control panel at the very top of your worksheet. Think of this as the cockpit of your loan. If you hardcode numbers straight into your formulas down below, you’ll paint yourself into a corner every time you want to test a different scenario.

Open a fresh Excel sheet and type these labels into columns A and B:

• Cell A1: Loan Amount
• Cell A2: Annual Interest Rate
• Cell A3: Loan Term (Years)
• Cell A4: Payments Per Year

Now, let’s add some realistic hypothetical numbers so we have something to test. Let’s say you’re looking at a loan of $25,000 at an annual interest rate of 6.5% over a term of 5 years (60 months).

• Cell B1: 25000
• Cell B2: 0.065 (Format this cell as a percentage)
• Cell B3: 5
• Cell B4: 12

Pro-Tip: If you ever want to run these numbers quickly on the go without wrestling with spreadsheet formulas, you can also drop figures into a dedicated Amortization Calculator to check your math instantly.

---

The Magic Formula for Your Fixed Payment

Now we need to calculate that steady, predictable monthly payment that stays identical month after month. In Excel, this is handled by the PMT function.

In Cell A6, type Monthly Payment.

In Cell B6, we are going to write our formula. The PMT function requires three core arguments: the interest rate per period, the total number of payment periods, and the present value (the loan amount).

Click on Cell B6 and paste this exact formula:

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

Let’s break down why this works so you aren't just blindly trusting a string of letters:

• B2/B4: We take our annual interest rate (6.5%) and divide it by the number of payments per year (12) to get the exact monthly interest rate.
• B3*B4: We multiply the loan term in years (5) by 12 to get the total number of individual monthly payments (60).
• -B1: We reference our total loan amount, but we stick a negative sign in front of it. Excel treats money flowing out of your pocket as a negative number; by making the loan amount negative, our resulting monthly payment comes out as a clean, positive number.

Hit enter. Cell B6 should proudly display $489.10. That is your fixed monthly payment for the life of the loan.

---

Building the Schedule Grid

With our control center locked in, it’s time to construct the actual table. Leave a couple of blank rows for breathing room, and in row 9, set up your column headers:

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

Let’s walk through setting up the first row of data (Row 10), because this is where the engine starts running.

Month 0 (The Starting Point)

Before your first payment leaves your account, your loan sits at its full size.

• In Cell A10, type 0.
• In Cell F10 (Ending Balance for Month 0), type =B1 (pointing directly to your original loan amount of $25,000). Leave columns B, C, D, and E blank for this row.

Month 1 (The First Real Test)

Now we hit Month 1 in Row 11. This is the row you will drag down to create the entire schedule, so getting this right is critical.

• Cell A11 (Period): Type 1.
• Cell B11 (Beginning Balance): Type =F10. (This pulls the ending balance from the previous month).
• Cell C11 (Payment): Type =$B$6. (We use absolute dollar signs so that when we drag this formula down, every single row looks at our locked-in monthly payment of $489.10).
• Cell D11 (Interest): Type =B11*($B$2/$B$4). (This calculates how much interest the bank is taking this month by multiplying your current beginning balance by your monthly interest rate).
• Cell E11 (Principal): Type =C11-D11. (This figures out how much of your fixed payment is actually eating away at the debt, taking your total payment and subtracting the interest chunk).
• Cell F11 (Ending Balance): Type =B11-E11. (Your beginning balance minus the principal portion you just paid off).

Hit enter. For Month 1, your Interest (Cell D11) should be $135.42, your Principal (Cell E11) should be $353.68, and your Ending Balance (Cell F11) should be $24,646.32.

Take a second to look at those numbers. That is the exact moment the mystery starts to clear up. Out of your first $489.10 payment, more than a quarter of it vanished straight into interest. Seeing that in black and white can sting a little, but it also gives you total clarity.

---

Dragging Down and Troubleshooting the Edge Cases

Now for the satisfying part. Highlight cells A11 through F11. Grab the little green fill handle in the bottom-right corner of the selection box, and drag it down until you hit row 69 (which gives you 60 total rows for your 60 months).

If you’ve set up your absolute references ($B$6, $B$2, etc.) correctly, your entire 5-year schedule will instantly snap into place.

Scroll all the way down to Month 60 (Row 70). Look at your final Ending Balance in column F.

Does it read $0.00? Or does it show a frustrating fraction of a penny like $0.01 or -?

What Trips People Up: The Floating Penny Problem

Computers are ruthlessly precise with decimals, while human currencies round to two decimal places. Because of this tiny rounding discrepancy, amortization schedules very frequently end on Month 60 with a balance of one single cent, or a negative fraction.

If your final row doesn't land precisely on zero, here is the quick fix that separates amateur spreadsheets from professional ones. Instead of letting Excel guess, you can wrap your final ending balance formula in an IF statement.

For advanced users who want total cleanliness, modify your Ending Balance formula in column F to check if the remaining balance is smaller than your monthly payment:

=IF(B11-E11<0, 0, B11-E11)

This ensures that the final month gracefully hits absolute zero without leaving you owing a phantom ghost penny to an imaginary lender.

---

A Worked Example: Following Sarah’s Car Loan

Let’s look at how this plays out in the real world with a different scenario so you can see how flexible this spreadsheet really is. Meet Sarah. Sarah just bought a reliable used car to make her morning commute bearable.

She takes out a $18,000 car loan at an annual interest rate of 7.2% over a term of 4 years (48 months).

Let’s plug Sarah’s numbers into our control panel:

• Loan Amount (B1): 18000
• Annual Interest Rate (B2): 0.072
• Loan Term (B3): 4
• Payments Per Year (B4): 12

Her PMT formula instantly spits out her fixed monthly obligation: $432.84.

If Sarah looks at Month 1 of her newly generated schedule:

• Beginning Balance: $18,000.00
• Payment: $432.84
• Interest Charged: $18,000 × (0.072 / 12) = $108.00
• Principal Paid: $432.84 - $108.00 = $324.84
• Ending Balance: $18,000 - $324.84 = $17,675.16

Now, fast forward two years. Let’s look at Month 25 of Sarah’s schedule:

• Beginning Balance: $10,124.38
• Payment: $432.84
• Interest Charged: $10,124.38 × (0.006) = $60.75
• Principal Paid: $432.84 - $60.75 = $372.09
• Ending Balance: $9,752.29

Notice what happened here? Back in Month 1, Sarah was paying $108 in interest and only $324 toward the actual car. By Month 24, because her beginning balance has shrunk, her monthly interest slice has dropped to roughly $60, meaning more than $372 of her exact same $432.84 payment is now actively shredding her principal balance.

This is the hidden momentum of fixed payments. The math quietly starts working harder in your favor the longer you stick with it. If Sarah wanted to check similar auto scenarios without building a fresh sheet every time, she could also punch her figures into a Car Payment Calculator to see how tweaking her down payment shifts the math.

---

What Changes the Answer? (Common Mistakes to Avoid)

When people build these sheets and find their numbers don't match their bank statement down to the penny, it’s almost always due to one of three hidden variables. Here is what trips people up:

1. Day-Count Conventions: Banks don’t all calculate daily interest the exact same way. Some use a 360-day year convention while others use 365. Your Excel model assumes a standard periodic compounding model, which can occasionally create pennies of variance compared to an institutional lender's commercial software.
2. Skipping the Negative Sign in PMT: If you forget to put a minus sign in front of the present value argument in the PMT function (-B1), Excel will output a negative payment. While mathematically consistent, it makes building subsequent row formulas confusing when you're trying to subtract negative numbers.
3. Forgetting Absolute References: If you drag your payment formula down without locking the control panel cells with dollar signs ($B$6 instead of B6), your formulas will cascade downward and break immediately on row two.

---

Taking Control of Your Numbers

Building an amortization schedule with fixed monthly payments in Excel isn't about becoming a spreadsheet wizard—it's about removing the anxiety of the unknown. When debt is just an abstract balance hanging over your head, it feels enormous and uncontrollable.

When you break it down row by row, cell by cell, you suddenly realize it's just a finite sequence of predictable steps. You can see the exact month the interest starts shrinking faster than the principal. You can see the finish line clearly marked in row 60 or row 48.

Take it one cell at a time, lock in your reference points, and let the math do the heavy lifting for you.

Disclaimer: The figures and schedules demonstrated above are for informational and educational purposes to help you understand how amortization mechanics work. Always verify exact payoff figures directly with your lender before making major financial moves.

---

Want to run these numbers on the go? Check out the free Finlaa app for fast, reliable calculators right in your pocket.

---

Frequently Asked Questions

Why does my Excel amortization schedule not match my bank's statement?

Minor discrepancies of a few cents—or occasionally a dollar or two—usually come down to rounding rules and daily interest accrual methods. Lenders often calculate interest daily based on the exact number of days in a billing cycle (which varies by month, since February has fewer days than March), whereas a standard Excel amortization schedule assumes uniform monthly periods.

How do I modify this schedule if I want to make extra monthly payments?

To factor in extra payments, you can add a new column called "Extra Principal" right next to your regular principal column. In your Ending Balance formula, simply subtract both the regular principal and the extra principal from your beginning balance. You'll instantly see how dropping an extra $50 or $100 into the pot each month hacks months—and thousands in interest—off the life of your loan.

Can I use this same sheet for a variable-rate loan?

Not without significant modifications. The standard PMT function and the steady row formulas built above assume your interest rate is locked in place. If your rate adjusts periodically (like an adjustable-rate mortgage or certain commercial loans), you would need to manually update the interest rate variable in your control panel—or rewrite the interest calculation formula for every adjustment period where the rate changes.