Setting Up a Loan Amortization Schedule in Excel
People come to me all the time asking how to build an amortization schedule from scratch because they don't trust the templates online or they need something that fits a non-standard loan term. It's a common enough request that I've lost count of the number of times I've walked someone through this. You don't need to be a spreadsheet power user to get it right, but there are a few traps that catch most beginners. One of the bigger ones is the compounding frequency mismatch between what your loan documents say and what the PMT formula expects. I once spent forty-five minutes debugging a schedule for a contractor who had a biweekly payment loan but was building the spreadsheet on a monthly basis. The total interest came out nearly two thousand dollars off. The fix was switching every input cell to reflect the actual payment frequency rather than trying to force a monthly model to do biweekly math. Before you type anything, you need five pieces of information on hand. The principal amount. The annual interest rate. The total number of payments or the loan term in years. The payment frequency, whether that's monthly, biweekly, semi-monthly, or weekly. And the start date of the first payment. I always recommend putting these five values in their own clearly labeled section at the top of the sheet so you can change them without hunting through formulas later. Put principal in cell B1, rate in B2, periods per year in B3, number of years in B4, and start date in B5. That gives you a clean reference point whenever you or someone else needs to adjust the inputs.
Building Your Excel Spreadsheet For Loan Amortization
The first row of your schedule should have headers. I use Payment Number, Payment Date, Beginning Balance, Payment Amount, Principal, Interest, and Ending Balance. From there you're going to reference your input cells. The payment amount formula is where most people go wrong. If you have monthly payments you'd use the PMT function like this: =PMT(B2/B3, B3*B4, -B1). The rate is divided by the periods per year, the total number of periods is periods per year times the number of years, and the principal is negated so the result comes out positive. Once you lock that in, the actual amortization table becomes mostly mechanical. For the first row, the beginning balance is just your principal. The payment amount copies down from the PMT result. The interest portion uses the IPMT function: =IPMT(B2/B3, 1, B3*B4, -B1). The principal portion uses PPMT: =PPMT(B2/B3, 1, B3*B4, -B1). The ending balance is beginning balance minus principal paid. Every subsequent row follows the same pattern except the beginning balance references the previous row's ending balance. The payment number increments by one. The date advances by your payment interval, which you can calculate with =EDATE(start_date, 1) for monthly or =start_date + 14 for biweekly. There's a nuance people miss with how Excel handles the PMT function and rounding. Excel calculates payment amounts to full decimal precision, but your actual loan statement will round to the nearest cent. Over the life of a thirty-year loan, that discrepancy compounds into a few dollars of phantom interest or a leftover balance at the end. I always add a note in the schedule and typically adjust the final payment to clear any rounding difference. Better yet, I use the ROUND function on each principal and interest calculation: =ROUND(IPMT(...), 2). That keeps the schedule honest with what you'd actually see on a bank statement.
Another thing worth noting is that some loans have balloon payments, adjustment periods, or extra charges tacked onto specific months. A basic amortization table doesn't handle those well without manual overrides. If you're working with an adjustable-rate mortgage, the interest rate changes each period, which means you can't set it and forget it. You'd need to recalculate the PMT whenever the rate resets and adjust the remaining payment amounts accordingly. I've seen people build separate schedules for each adjustment period and then stitch them together, which works but gets messy fast. A cleaner approach is to keep a column for the current rate and rebuild the payment calculation each time the rate changes using a nested IF or a lookup table. If you want a reference point, search for Excel Spreadsheet For Loan Amortization and you'll find plenty of downloadable templates, but the real value is understanding how the pieces connect so you can adapt the template to whatever weird loan structure you're dealing with. The formulas themselves are straightforward. The difficulty is in getting the inputs aligned with reality and catching the edge cases that make a clean schedule fall apart halfway through. If you build it right, changing one input cell should cascade through the entire table without breaking anything. If it breaks, you've got a reference error somewhere in the balance columns or the payment frequency doesn't match what the formulas assume. Those are the two most common points of failure and they're both fixable if you trace the dependencies carefully.
Get the Full Details
