How to Build Your Own Loan Amortization Table in Excel (Step-by-Step)
30 July 2026
How to Build Your Own Loan Amortization Table in Excel (Step-by-Step)
It’s 11:45 p.m. You’ve got a spreadsheet open, a half-empty mug of tea on your desk, and a loan statement staring you down from the screen.
You already know the monthly payment. What’s keeping you up is the invisible machinery behind it—the quiet, heavy chunk of your hard-earned cash that vanishes into interest every single month, while the actual balance barely budges. You look at the amortization schedule the lender provided, and it feels about as transparent as ancient hieroglyphics.
You don't need a finance degree to crack this open. You just need Excel, a few basic formulas, and about ten minutes. Building your own loan amortization table in excel does more than just organize numbers; it hands you the steering wheel. Once you see how the math works under the hood, those monthly bills stop looking like an endless mountain and start looking like a puzzle you can actually solve.
Let’s build one together, step by step, so you can finally see where every last dollar goes.
Why You Need Your Own Spreadsheet (Instead of Trusting the Lender)
Lenders give you amortization schedules, sure. But they give you the version that suits them.
When you build your own table, you get to play what-if. What happens if you throw an extra $100 at the principal every month? What if you refinance down the road? A static PDF from your bank can't answer those questions. Excel can.
Before we type a single formula, let’s get our workspace ready. Think of this table as a timeline of your loan’s life, broken down row by row from month one to the very last payment.
Step 1: Setting Up Your Loan Inputs
First things first: we need a control center. This is where you plug in the basic facts of your loan so the rest of the spreadsheet can do the heavy lifting.
Open a blank Excel workbook and type the following labels in column A, starting at row 1:
- A1: Loan Amount
- A2: Annual Interest Rate (%)
- A3: Loan Term (Years)
- A4: Payments Per Year
Now, let’s add some example numbers in column B so we have something concrete to work with. Let’s pretend you’re looking at a car loan, or perhaps a personal consolidation loan—say, $25,000 borrowed at an annual interest rate of 7.5% over a term of 5 years (60 monthly payments).
- B1:
25000 - B2:
7.5%(Type it as0.075or7.5%) - B3:
5 - B4:
12(Since we’re making monthly payments)
Step 2: Calculating the Monthly Payment
Before we map out the schedule, Excel needs to know the exact monthly payment using the PMT function. This formula takes three primary arguments: the periodic interest rate, the total number of payments, and the present value (the loan amount).
In cell A6, type: Monthly Payment
In cell B6, enter this formula:
=PMT(B2/B4, B3*B4, -B1)
Here’s why we write it that way:
- B2/B4 converts your annual interest rate into a monthly rate (7.5% divided by 12).
- B3*B4 calculates the total number of payment periods (5 years multiplied by 12 months = 60 payments).
- -B1 is the negative of your loan amount. (We make it negative because Excel views loans as cash outflow; if you don't make it negative, your payment formula will return a negative number, which can mess up later math).
Hit Enter. Excel should spit out a monthly payment of $500.81.
Now you have your baseline. If you want to check your math against other scenarios or see how different terms affect your monthly cash flow, you can also cross-reference these figures using our free Car Loan Calculator to see how adjustments change your outlook.
Step 3: Setting Up Your Table Headers
Now we build the actual amortization schedule. Go down to row 9 and set up your column headers across columns A through F:
- A9: Payment Number
- B9: Beginning Balance
- C9: Payment
- D9: Principal
- E9: Interest
- F9: Ending Balance
These six columns are the heartbeat of your loan. Every single row will represent one month of your financial life.
Step 4: Filling in Row 1 (Month 0 and Month 1)
This is where people usually get tripped up. A proper amortization table needs a "Month 0" row to establish your starting point before any payments are made, followed by Month 1.
Row 10 (Month 0 - Starting Point)
- A10:
0 - B10: Leave blank
- C10: Leave blank
- D10: Leave blank
- E10: Leave blank
- F10:
=$B$1(This pulls your original loan amount of $25,000 as your starting ending-balance).
Row 11 (Month 1 - Your First Real Payment)
This is where the magic happens. We are going to write formulas for row 11 that will look at the ending balance of the previous month (row 10) and calculate everything fresh.
- A11 (Payment Number):
=A10+1(This turns into 1) - B11 (Beginning Balance):
=F10(Pulls the ending balance from last month) - C11 (Payment):
=$B$6(Locks in your fixed monthly payment of $500.81 using dollar signs so it doesn't shift when we drag it down) - D11 (Principal paid):
=C11-E11(Wait, we need the interest first! Let's do interest next).
Let's do E11 (Interest paid) first:
=B11*($B$2/$B$4)
What this does: It multiplies your current beginning balance by your monthly interest rate. For month one, $25,000 multiplied by (7.5% / 12) gives you $156.25 in interest.
Now, go back to D11 (Principal paid):
=C11-E11
What this does: It takes your total monthly payment and subtracts the interest chunk. Whatever is left over goes straight toward shrinking your actual debt. For month one, $500.81 minus $156.25 leaves $344.56 going toward the principal.
Finally, calculate F11 (Ending Balance):
=B11-D11
What this does: It takes your beginning balance and subtracts the principal you just paid off. For month one, $25,000 minus $344.56 leaves an ending balance of $24,655.44.
Take a breath. You just built the core engine of your spreadsheet. If you want to check how this breaks down over a longer mortgage timeline, you can also reference an Amortization Calculator to compare your Excel output against standard financial software.
Step 5: Dragging Down the Formulas (The Home Stretch)
Now comes the satisfying part. You don't need to type formulas 60 times.
- Highlight the cells from A11 through F11.
- Hover your mouse over the bottom-right corner of cell F11 until your cursor turns into a small black cross (+).
- Click and drag straight down until you reach row 70 (which represents payment 60, since row 10 was month zero).
Boom. Excel instantly calculates all 60 months of your loan lifecycle. Scroll down to row 70. Look at the ending balance in column F—it should land precisely at $0.00 (or within a few pennies due to standard rounding).
If your final row doesn't hit zero, don't panic. That’s a common quirk we’ll look at in a moment.
What Trips People Up: Common Mistakes to Avoid
Even when you follow the steps, spreadsheets can be finicky beasts. Here are the three most common traps people fall into when building a loan amortization table in excel, and how to dodge them.
1. Forgetting Absolute References (The Dollar Signs)
If you drag your formulas down and suddenly get a wall of #VALUE! errors or zeroes, you probably forgot the dollar signs ($) in your payment or interest rate formulas.
Without dollar signs, Excel assumes you want the cell reference to move down the page as you drag. So instead of looking at cell B2 for the interest rate, it looks at B3, then B4, dragging your inputs down into empty space. Always lock your input cells using absolute references (like $B$1 or $B$2).
2. The Final Penny Rounding Glitch
Sometimes, because of how fractions and rounding work with currency, your 60th payment might leave you with a remaining balance of 3 cents, or take your balance slightly below zero.
Lenders handle this by adjusting the very last payment so it zeroes out cleanly. If you want your spreadsheet to behave like a pro lender, you can wrap your payment and principal formulas in an IF statement that checks if the remaining balance is smaller than your standard monthly payment. For most personal tracking, though, being off by a few cents on month 60 won't hurt your feelings.
3. Mixing Up Annual and Monthly Rates
This is the classic rookie error. If your loan has a 6% annual rate, typing 0.06 directly into the interest formula for a monthly schedule will completely wreck your numbers—you’ll be paying down the loan at a yearly rate every single month! Always divide your annual rate by your payment frequency (usually 12 for monthly payments).
The Real Power: Modeling Extra Payments
Now that your table is built, let's look at why doing this yourself is so rewarding.
Let's go back to our example. In month one, out of your $500.81 payment, $156.25 went to the bank as interest, while $344.56 went toward your balance. The bank takes its fee upfront based on what you owe right now.
This reveals the secret lever of debt: every dollar you pay extra today strips away interest tomorrow.
If you decide to add an extra $50 to your payment every single month, you can adjust your monthly payment column or add an "Extra Payment" column to your spreadsheet. When you watch that interest column shrink month over month, something changes psychologically. The debt stops feeling like an immovable monolith and starts looking like a countdown timer that you control.
If you're managing a larger debt like a mortgage, you can test these exact scenarios using a Loan Prepayment Calculator to see how shaving even a small amount off your principal cuts years off your timeline.
A Smarter Way to Look at Your Debt
Building a loan amortization table in excel takes about ten minutes, but it changes how you view your liabilities for the rest of your financial life.
When you see the numbers laid out plain and clear—row by row, dollar by dollar—the mystery evaporates. You realize that debt isn't an arbitrary penalty box; it's a mathematical equation. And equations can be solved, optimized, and beaten.
Take a few minutes to set yours up tonight. Pour another cup of tea, plug in your actual loan details, and watch the mystery fade away. Once you see the exact path from your current balance down to zero, you'll close that laptop feeling a whole lot steadier than you did when you opened it.
Frequently Asked Questions
Can I use this same template for a mortgage or a student loan?
Yes. The underlying math of amortization applies to almost all installment loans—whether it’s a car loan, personal loan, or mortgage. The only thing that changes is the scale of the numbers and the duration. If you are tracking student debt specifically, you can also compare your Excel model against a dedicated Student Loan Payoff Calculator to account for grace periods or income-driven repayment adjustments.
What if my loan has variable interest rates?
A standard amortization table assumes a fixed interest rate. If your loan has a variable or adjustable rate, your fixed PMT formula will only work until the first rate adjustment. To model a variable rate in Excel, you’ll need to manually update the interest rate input cell at the specific row where the rate changes, and Excel will recalculate all the remaining rows based on the new rate.
Why doesn't my total interest paid match what the bank quoted?
Banks sometimes calculate interest daily rather than monthly, or they may include upfront origination fees or mandatory insurance products in the overall cost of credit. Your Excel sheet calculates pure compounding interest based on the exact inputs you provided. If there's a slight discrepancy, check your loan agreement to see if there are hidden administrative fees baked into your total cost.
Disclaimer: This article is for informational and educational purposes only and does not constitute financial advice. Always review your specific loan agreements and terms with your lender before making major financial decisions.
For quick calculations on the go, you can also use the free Finlaa app to model your loans, mortgages, and savings right from your phone.
