Excel Amortization Schedule

Building an Amortization Table Without Losing Your Mind

The PMT function in Excel is useless if you don't pair it with IPMT and PPMT. I learned that the hard way when a client sent me a loan schedule that balanced perfectly on paper but had a $47 discrepancy by month 24. Turns out someone had been manually typing payments instead of referencing the PMT formula. The loan looked fine for six months before the gap widened enough to matter. Here is how you actually build one from scratch.

Excel Amortization Schedule

Set up your header row with these columns: Payment Number, Payment Date, Beginning Balance, Payment Amount, Principal, Interest, Ending Balance, and Cumulative Principal Paid. That last column catches people off guard because nobody thinks they will need it until they are mid-project trying to figure out how much equity they have built after 18 months. For the fixed monthly payment, use =PMT(rate, nper, pv). The rate should be your annual rate divided by 12. The nper is total number of payments, which is loan term in years times 12. The pv is the starting loan amount, entered as a negative so the result comes out positive. I usually wrap it in ABS just to keep my sanity, though that is not strictly necessary if your signs are consistent. The interest portion of each payment comes from =IPMT(rate, per, nper, pv) where per is the current row number. The principal portion uses =PPMT(rate, per, nper, pv) with the same arguments. These two formulas always add up to your PMT result. If they do not, you have a sign error somewhere in your inputs.

Beginning balance for row one is simply your original loan amount. For every subsequent row, reference the ending balance of the row above it. Ending balance is beginning balance minus the principal payment from that row. This chain reaction is where things fall apart if you make a typo in row three and then spend forty-five minutes debugging the rest of the sheet. Date handling is another thing people gloss over. If you want actual calendar dates rather than just payment numbers, use =EDATE(start_date, period_number) for monthly schedules. It handles leap years and varying month lengths correctly, which a simple addition formula does not. The cumulative principal column is just a running SUM from the first payment down to the current row. =SUM($F$2:F2) drag down. Dollar-sign the start, leave the end relative so it expands as you pull the formula down.

Here is the edge case nobody warns you about. If your loan has an irregular first period — which happens with mortgage closings that don't land on the first of the month — the standard PMT formula gives you a wrong payment. I ran into this when a commercial lender sent me a loan commitment with a closing date of the 17th but a first payment due on the 1st of the following month. That shortened first period means accrued interest is less than a full month, but PMT assumes uniform periods. The workaround is to calculate the first payment separately using =PV(rate, nper, -payment, fv) backwards to find the correct first-period amount, then use the standard PMT for the remaining periods. It adds three rows to your sheet but it is the difference between a schedule that is off by a few dollars and one that is accurate enough to submit to an auditor. Another thing that catches people out is floating point precision. Excel stores numbers to about 15 decimal places, and after 360 months of compounding, your ending balance might show as 0.00000003 instead of exactly zero. The fix is not complicated. Use =ROUND(ending_balance_formula, 2) at each step. It keeps the table clean and prevents the final payment from being off by a cent or two. If you are dealing with variable rate loans or loans with prepayment penalties, a static Excel schedule breaks down fairly quickly. You would need to rebuild it every time the rate changes or adjust formulas to account for extra payments. In those situations, a dedicated loan management tool or a VBA macro that recalculates on the fly saves more time than fiddling with nested IF statements. I tried building a full adaptive schedule once with conditional logic for rate changes and prepayments. It took me two days to get right and still broke whenever I tested an unusual combination of extra payments mid-term. A simple PMT-IPMT-PPMT setup with rounded values covers about 90 percent of what you will actually need.