Setting up an amortization schedule in Excel is straightforward if you understand what the formulas are actually doing
The most common approach uses the PMT function to calculate your monthly payment, then builds out a period-by-period breakdown. Here is the basic setup. Put your loan parameters in a clean section at the top—loan amount in B1, annual interest rate in B2, loan term in years in B3. Then calculate the monthly payment in B4 with =PMT(B2/12,B3*12,B1). That gives you a single fixed payment number that covers both principal and interest for every period. Below that, set up column headers: Period, Opening Balance, Payment, Interest, Principal, Closing Balance. The interest column uses =B2/12*C2, where C2 is the opening balance for that period. The principal column is simply the payment minus the interest. The closing balance subtracts the principal from the opening balance and feeds into the next row as the new opening balance. Drag the formulas down for the full term.
Mortgage Amortization In Excel
Excel will show you the complete picture, but the default output isn't always what you expect. The PMT function returns a negative value because Excel treats outgoing payments as negative cash flows. If your opening balance shows as positive, your payment will display as negative and your interest column will be negative too. You can either enter the loan amount as a negative number from the start, or wrap the payment formula in ABS to make it display positively. I usually just multiply the PMT result by -1 so everything reads the way a real statement would. One thing people miss is that Excel rounds the displayed values but not the underlying calculations unless you force it. If your columns are formatted to show two decimal places, the visual total might not match the actual sum of the period. Add a ROUND function around your interest and principal calculations—ROUND(E2,2) for interest, ROUND(F2,2) for principal—and the sheet will behave more predictably. It won't solve every floating point issue, but it eliminates most of the noise. I spent about three hours last year debugging a schedule where the final balance was off by $4.12. The borrower had been making extra principal payments every six months, which the basic PMT structure doesn't account for. There is no built-in Excel function that adjusts the amortization schedule dynamically when prepayments are introduced. Once you add a prepayment, the entire periodic breakdown shifts because the remaining balance drops faster than the original formula expects. My workaround was to build a separate column for the extra principal and recalculate the remaining periods each time a prepayment occurred, essentially rebuilding the schedule from that point forward. It took longer to set up but it was the only way to get an accurate payoff date and total interest figure.
Adding cumulative interest analysis
The CUMIPMT function lets you pull total interest paid over any range of periods. The syntax is =CUMIPMT(rate,nper,pv,start_period,end_period,type). The start_period and end_period must be consecutive integers starting from 1. If you try to use non-consecutive values like 1 and 3 for a single calculation, the function returns zero because it cannot bridge the gap. This caught me off the first time I tried to calculate total interest for years one and three while skipping year two, expecting a summed result. It does not work that way—you have to call the function twice and add the outputs. Leap years also create a subtle problem with cumulative interest. A thirty-year mortgage spans approximately seven or eight leap years, which adds days that the standard monthly period count does not capture. When I ran a schedule for a 30-year loan that included a leap year in period months 24 and 36, the cumulative interest from CUMIPMT drifted about $18 below what the bank reported. The PMT-based schedule handled it fine because the payment itself absorbs the extra day, but CUMIPMT does not. The fix is to compare the Excel output against the lender's official amortization table at the 60-month and 120-month marks and adjust your expectations rather than chasing a perfect match.
Get the Full Details
Limitations you should know about before building this
The PMT approach only works for fixed-rate, fully amortizing loans with level payments. If your loan has an adjustable rate, an interest-only period, or balloon payment structure, the basic model breaks immediately. You would need to construct separate schedule sections for each rate period or payment type, which means significantly more manual setup. There is no single formula that handles variable rates across a long-term mortgage. Another practical constraint is that Excel's financial functions assume payments occur at the end of each period unless you specify otherwise. The type parameter in PMT and CUMIPMT accepts 0 for end-of-period or 1 for beginning-of-period. Most residential mortgages are end-of-period, but commercial loans sometimes use beginning-of-period payments. If you use the wrong type value, your interest calculations will be slightly off across the entire schedule. Rounding at the final payment is another reality. Even with ROUND applied to every line, the last payment may need a small adjustment because the accumulated rounding differences do not always cancel out evenly. Some people add a manual override in the final row to zero out the closing balance. I find it cleaner to leave the rounding as-is and note the discrepancy in a cell below the schedule. A $0.03 difference on a quarter-million loan is not material, and flagging it keeps the model transparent.
What this approach will not do for you
If you need to model tax implications, insurance escrow, or property tax reserves alongside the principal and interest, you will need to expand the column structure significantly. The basic amortization schedule tracks only the loan repayment. Adding escrow requires a parallel calculation that runs independently and then combines with the P&I payment for a total monthly outflow figure. I usually build this as a separate section on the same sheet rather than mixing it into the amortization rows, which keeps the logic cleaner and makes it easier to audit. The schedule also does not account for late fees, payment holidays, or forbearance periods. If any of those events occur, the whole model becomes invalid for that period unless you manually insert adjustment rows. In practice, lenders handle those adjustments on their end, so the schedule you build in Excel is a projection, not a replacement for the official loan documents. Treat it as a planning tool and cross-check against the lender's amortization table at least once during the life of the loan to make sure you are not drifting from the expected payoff timeline.
