Excel Amortization Table

Building an Amortization Schedule in Excel That Actually Works

I've built more of these than I care to count. The standard tutorial approach — just plug PMT, IPMT, and PPMT into columns and drag them down — works fine for a basic consumer loan with exactly 36 or 60 payments. It breaks down the moment you try to use it for anything that isn't a textbook example. Here's how to actually do it right. Start by laying out your headers. You need: Period, Payment Date, Beginning Balance, Payment, Interest, Principal, Ending Balance. That's the minimum. Anything less and you'll be constantly calculating things in your head. For the Payment column, you're using the PMT function. The formula looks like this: =PMT(rate/nper,pv,-fv,type). Let's say you have a $25,000 car loan at 5.9% annual interest over 60 months. Your rate cell would be 5.9%/12, your nper is 60, and your pv is 25000. You put a minus sign in front of the fv because Excel's financial functions have a cash flow convention that confuses everyone. Money coming in is positive, money going out is negative. The loan amount you receive is positive from your perspective, so you enter it as a positive number in the PV field and Excel returns a negative payment. Put a minus in front to flip it.

The tricky part most people skip is that the payment amount stays constant, but the split between interest and principal changes every single row. Row 1 might be $481.29 total with $122.92 going to interest and $358.37 to principal. Row 2 is almost the same total payment but $121.11 to interest and $360.18 to principal. The payment doesn't change. The allocation does.

The Interest and Principal Calculations

For the Interest column, you're using IPMT. The formula is =IPMT(rate/periods_per_year, period_number, total_periods, present_value). The first argument is the per-period rate. If your annual rate is in cell B1 and you're doing monthly payments, you'd reference B1/12. The period_number is just the row number of your schedule — 1, 2, 3, and so on. For the Principal column, you're using PPMT with the same structure: =PPMT(rate/periods_per_year, period_number, total_periods, present_value). Again, you'll likely want a minus sign in front to make the number positive since Excel returns it as a negative by convention. Here's where the first mistake happens. People write the formulas referencing absolute cells for the rate and total periods, which is correct. But they forget that the present value in the IPMT and PPMT functions should be the ORIGINAL loan amount, not the current remaining balance. These functions calculate based on the full loan terms from day one, not the shrinking balance. The remaining balance comes from your Beginning Balance and Ending Balance columns, which are separate calculations entirely.

Calculating the Remaining Balance

This is the part that actually matters. Your Ending Balance for any given row equals the Beginning Balance minus the Principal payment for that row. Row 1's Beginning Balance is your original loan amount. Row 2's Beginning Balance is Row 1's Ending Balance. You reference the cell above. Drag that formula down and your entire schedule builds itself. The final row should show an Ending Balance of zero, or within a few cents due to rounding. If it's off by more than a dollar, you have an error in your rate division or your period count. Double-check that your annual rate is actually divided by 12, not by 365 or some other number. A lot of people do 5.9%/365 by habit because that's what they use for daily interest calculations on credit cards.

The Specific Problem That Made Me Rethink Everything

About two years ago, I was working on a commercial real estate deal where the borrower had made a partial payment mid-period. The lender's system recorded the payment on the 17th of the month, but the regular schedule had payments due on the 1st. The built-in Excel PMT/IPMT/PPMT functions assume payments land exactly on the period boundary. They don't handle a payment made 16 days into a 30-day period. I spent an afternoon figuring out that the workaround was to split the period. Instead of one row for that month, I created two rows: one for days 1-16 and one for days 17-30. For the first row, I calculated interest as (Beginning Balance × Annual Rate × 16/365). For the second row, I used (New Balance After Partial Payment × Annual Rate × 14/365). The payment amount itself came from prorating the regular PMT. It took about 45 minutes to rebuild that section of the schedule, but it was accurate to the penny. The key insight: Excel's financial functions are built for textbook scenarios. Real loans don't always fit. When they don't, you stop using PMT/IPMT/PPMT and just calculate interest manually using (Balance × Rate × Days/365). It's less elegant but more flexible.

Common Pitfalls and How to Avoid Them

One thing nobody warns you about is the difference between nominal and effective rates. If your loan document says 6% APR compounded monthly, you divide by 12. If it says 6% effective annual rate, you need to convert it first using (1.06^(1/12)-1) as your monthly rate. Using the wrong conversion will make your final payment off by several percent of the total interest cost. I learned this the hard way on a $180,000 home equity line where the discrepancy came out to about $340 in extra interest over the life of the loan. Another issue: rounding. If you format your cells to show two decimal places but the underlying values keep more precision, your schedule will look balanced but the math won't quite add up. Set your Interest and Principal columns to actually ROUND to two decimals using =ROUND(formula,2). Then your Ending Balance will match exactly. Without rounding, you'll see a $0.03 or $0.07 discrepancy at the end that makes you second-guess everything. There's also the matter of what happens when your loan has a balloon payment or a different final payment. Excel's standard amortization assumes equal payments throughout. If your loan structure requires a larger final payment, you either adjust the last row manually or build a conditional formula that checks if the remaining balance is less than the regular payment and adjusts accordingly.

Using the Built-In Template vs. Building From Scratch

Excel has a built-in loan amortization schedule template. Go to File > New and search for "amortization." It'll give you a preformatted table with formulas already in place. It's faster than building from zero but it's rigid. You can't easily adjust it for irregular payment dates, extra payments, or variable rates. I use it as a starting point and then rebuild the calculation columns myself. Takes about 10 minutes and gives you full control. If you need something more flexible, building your own Excel Amortization Table from scratch is worth the upfront time. Once you have the structure down, you can adapt it for any loan type — auto loans, mortgages, student loans, business loans — without fighting the template's limitations. The formulas I described above will work for any fixed-rate loan with regular payments.

When This Approach Fails Completely

Let me be clear about where a simple Excel amortization table stops working. Adjustable-rate mortgages with periodic caps and floors. Loans with graduated payment structures where the payment increases annually. Balloon loans where the final payment is dramatically different from the others. These all require custom logic that the standard PMT/IPMT/PPMT combination can't handle. For those situations, you're better off using a dedicated loan management tool or writing a small VBA script that processes each period individually with its own rate and payment rules. Also worth noting: if you're comparing multiple loan scenarios side by side, the spreadsheet gets messy fast. I found that building a separate input section at the top with toggle switches for different assumptions was cleaner than trying to cram everything into one dense table. It also makes it easier for other people on your team to use without breaking your formulas. Here's a working template I put together that covers the standard fixed-rate scenario with the rounding fixes and balance verification built in. Download it and use it as a starting point, then modify the columns to match whatever your actual loan terms require. The structure matters more than the specific numbers.