Loan Payoff Calculator Excel Template

Enter the four inputs: balance, APR, term in years, and extra monthly principal. Keep them together so anyone can change a number and watch the whole sheet update.

Select the APR cell and press Ctrl+1. Choose Percentage with two decimals. Do this before you type the rate, so entering 6.25 becomes 6.25%. In a General cell, the same keystrokes would mean 625%. The format changes how the number looks, while the stored value stays 0.0625.

Go to Formulas > Financial > PMT to open the Function Arguments box. PMT needs three things: the monthly rate, the number of monthly payments, and the loan amount.

Convert the yearly inputs first. The rate is the APR divided by 12, and the payments are the years times 12, which is 300. Enter the loan amount as a negative number, so PMT returns a positive payment. Wrap the result in ROUND to two decimals and you get $1,187.40. Multiply it by 300 payments and subtract the loan to see $176,220.00 of baseline interest.

Add a starting date of November 1, 2026 and use EDATE to move it forward by whole months. EDATE ignores how many days each month has, so every payment lands cleanly on the 1st: Nov-2026, Dec-2026, Jan-2027, and so on.

Instead of typing a different offset on each row, point the offset at the payment number in column B, minus one. Then drag the fill handle down and the dates stay in step with the schedule.

Each month the bank charges interest on the beginning balance. Multiply that balance by the APR divided by 12, and round to cents. The first row gives $937.50.

Principal paid is the total payment minus that interest. The total payment is the scheduled payment plus your extra amount, which is $1,437.40, so principal is $499.90 in month 1. Press F2 in the interest cell to see which cells feed it. The beginning balance of each row then equals the previous row's ending balance.

Ending balance is beginning balance minus principal paid, wrapped in MAX with zero. That stops the number from turning negative if the last payment is larger than what remains.

Next, add the totals row. Select it, open Home > Borders, and pick Top and Double Bottom Border, the classic accounting finish. After 36 payments the balance is $160,262.16, with $19,737.84 paid in principal and $32,008.56 in interest.

The sample table stops at 36 rows. To follow the loan all the way to zero, copy the last row further down. In the final month, also cap the payment at the remaining balance, because MAX only keeps the balance from going below zero.