Building a Loan Amortization Schedule in Excel
I spend most of my week building these things for people who don't want to pay a consultant. You'd be surprised how many small business owners still do manual calculations or use sketchy online tools that don't show the full picture. Excel is fine for this. It's not glamorous, but it works reliably if you set it up correctly. The core concept is straightforward. Every payment on an amortizing loan goes partly toward interest and partly toward principal. The interest portion is calculated on the remaining balance, so it starts high and shrinks over time. The principal portion does the opposite. By the end, you've paid back the full amount plus all accrued interest. To build this, you need four inputs: the loan amount, the annual interest rate, the loan term in months, and the payment frequency. From those, you calculate the fixed monthly payment using the PMT function. In Excel, that looks like =PMT(rate_per_period, total_periods, -loan_amount). The negative sign on the loan amount ensures the payment displays as a positive number, which is just a display quirk I've accepted over the years.
Once you have the payment, you build the schedule row by row. Column A is the period number. Column B is the beginning balance. Column C is the payment amount. Column D is the interest portion, calculated as the beginning balance multiplied by the monthly rate. Column E is the principal portion, which is the payment minus the interest. Column F is the ending balance, which is the beginning balance minus the principal payment. Then you copy that row down for the full term. For a 30-year loan at 6.5% annual rate on $250,000, the monthly payment comes out to roughly $1,580. That's about $1,354 in interest in the first month and only about $226 going toward principal. By payment 180, the split is nearly reversed. By payment 360, the interest portion is usually under $30.
A Real Problem I Hit Recently
Last month someone sent me their amortization schedule and the ending balance didn't reach zero. It was off by about $4.73. I traced it back to rounding. Excel's PMT function returns a decimal value like 1580.2347, and if you round the payment display to two decimals but use the unrounded value in your formulas, or vice versa, the schedule drifts. The fix is to use the rounded payment consistently across the entire sheet, then adjust the final payment by the accumulated rounding difference. I add a note in the last row that says something like "Adjusted for rounding" so it doesn't look like an error when someone else reviews the file. Another edge case is loans with irregular periods, like when a borrower makes a mid-cycle payment or has a variable rate adjustment. Standard fixed schedules break down there. For those, I switch to a model where each row recalculates based on the actual balance and rate at that point rather than assuming a flat payment throughout. It takes more setup but it's more accurate when the loan structure isn't clean.
Get the Full Details
What Most People Get Wrong
The most common mistake is confusing annual and monthly rates. If the interest rate is 7% annually, the monthly rate for your formula is 7% divided by 12, not 7%. This throws off every single calculation downstream. I've seen people use the annual rate directly and wonder why their monthly payment is way too high. Another issue is how Excel handles the PMT function's return sign. PMT returns a negative number by convention because it represents cash outflow. If you link that directly into a sum or average without accounting for the sign, your totals will be wrong. The cleanest approach is to store the payment as a positive value right from the start by negating the principal in the PMT formula, as I mentioned earlier. There's also a subtlety with the NPER function that catches people out. If you input the term as years and don't multiply by 12 for monthly payments, you'll calculate a payment for a completely different loan term. Double-check that total_periods equals the number of individual payments, not the number of years.
When to Use This and When Not To
An Excel amortization schedule is ideal for static loans with fixed terms and rates. It takes about 15 to 20 minutes to set up cleanly if you already know what you're doing. Once built, it's editable, auditable, and doesn't require any internet connection or subscription. For basic borrowing decisions, personal loan comparisons, or showing a client what their payments look like over time, it's completely sufficient. It falls apart when you're dealing with complex loan products. If you're modeling adjustable-rate mortgages with periodic and lifetime caps, balloon payments, prepayment penalties, or interest-only periods, Excel can still handle it but the spreadsheet gets complicated fast and error margins increase. In those situations, a dedicated mortgage calculation tool or a financial modeling platform gives you more robustness. The same goes for multi-currency loans or loans with bi-weekmy payment structures that don't follow standard amortization curves. Here's something else beginners miss: amortization schedules in Excel don't account for escrow. Property taxes and insurance are often bundled into monthly payments on real estate loans, but the amortization calculation itself only covers principal and interest. If you need total monthly obligation, you have to add those components separately. I usually build a section at the top of the sheet for escrow estimates and reference it from the payment column rather than baking it into the amortization logic.
The practical outcome of building this yourself is that you understand your loan better than someone who just looks at a monthly payment number. You can see exactly how much you're paying in interest over the life of the loan. On a $250,000 loan at 6.5% over 30 years, that's about $318,000 in total interest, which is more than the original principal. Seeing that number laid out row by row changes how people think about their borrowing decisions. If you want a working template to start from, I keep a basic version available. It covers the core structure with the PMT function, the row-by-row breakdown, and the rounding adjustment I described. You can fill in your own numbers and adapt it from there. The key is testing it against a known result, like a bank statement or a reputable online calculator, before you rely on it for anything serious.
