Loan Payoff Schedule Excel: Formula and Example

A loan payoff schedule in Excel shows how each payment is divided between interest and principal, and how the balance declines until the loan is paid off. Enter the starting balance, annual APR, term, and any extra monthly payment to estimate the schedule and payoff totals.

Formula

This calculator gives the scheduled monthly payment, adds the entered extra amount, and estimates interest and total payments across the original term. The standard fixed-payment formula is:

Payment = P × r ÷ [1 − (1 + r)−n]

• P is the starting loan balance in dollars.
• r is the monthly interest rate, calculated as APR ÷ 1,200.
• n is the number of monthly payments, calculated as years × 12.

The scheduled payment is rounded to cents; the extra payment is then added. The interest output is the scheduled-term estimate before considering the extra payment. The total output multiplies the payment including extra by the full scheduled number of months, so it is a simple scheduled-term total, not the actual total for a loan paid off early. For a true payoff schedule with extra payments, calculate interest period by period and stop when the remaining balance reaches zero.

Worked Example

Using the default inputs—$20,000 balance, 8% APR, five years, and no extra payment:

1. Monthly rate: 8 ÷ 1,200 = 0.0066667.
2. Number of payments: 5 × 12 = 60.
3. Scheduled payment: $20,000 × 0.0066667 ÷ [1 − (1 + 0.0066667)⁻⁶⁰] = $405.53 after rounding to cents.
4. Scheduled interest estimate: ($405.53 × 60) − $20,000 = $4,331.80.
5. Scheduled total: $405.53 × 60 = $24,331.80.

The same figures are the calculator outputs for these defaults. Actual lender statements can differ because payment dates, daily interest calculations, rounding, fees, and contract terms may differ.

Assumptions

• The APR and balance stay fixed for the modeled term.
• Payments are level and made monthly, with the first payment one month after the starting balance is measured.
• Interest compounds monthly at APR ÷ 12, as represented by the standard amortization formula.
• Fees, taxes, insurance, PMI, escrow, and any lender-specific charges are not included.
• The extra payment is treated as a monthly amount for this estimate; the outputs do not shorten the term to reflect early payoff.

How a Payoff Schedule Works in Excel

A detailed spreadsheet has one row per payment period. It starts with the opening balance, calculates that period’s interest, applies the payment, and carries the resulting balance to the next row. This lets you see the changing split: early payments generally devote a larger share to interest, while later payments devote more to principal when the rate and payment remain fixed.

A basic Excel layout can use columns for payment number, opening balance, scheduled payment, extra principal, interest, principal paid, and closing balance. For a monthly-rate input in a cell such as B2, a row’s interest is the opening balance multiplied by B2. Principal paid is the payment less interest, plus any extra amount that is actually applied to principal. Closing balance is opening balance less principal paid, and the next row’s opening balance equals the prior row’s closing balance.

Excel’s payment function, PMT, can calculate a level payment when the rate and number of periods are consistent. For example, a monthly rate and total number of months are needed for a monthly schedule. A positive payment may appear negative depending on the spreadsheet’s cash-flow sign convention; entering the loan advance and payments with consistent signs avoids confusion. A formula should also prevent the final payment from exceeding the remaining balance plus interest.

Building an Accurate Spreadsheet Schedule

For each row, calculate interest from the opening balance, not the original loan amount. Then determine how much of the payment reduces principal. An illustrative sequence is:

1. Set the first opening balance equal to the loan balance.
2. Calculate period interest as opening balance × APR ÷ 12 for a monthly model.
3. Subtract period interest from the payment to find principal reduction.
4. Add any extra principal intended for that period.
5. Subtract principal reduction from opening balance to get closing balance.
6. Use closing balance as the next row’s opening balance, and continue until it reaches zero.

For example, at an 8% APR and a $20,000 opening balance, one month’s interest under the monthly-rate assumption is $20,000 × 0.08 ÷ 12, or about $133.33. With the example’s $405.53 scheduled payment, about $272.20 goes to principal before any extra amount. Later interest is lower if the balance has fallen.

Extra Payments, Timing, and Common Errors

Extra payments can change the actual payoff date and interest paid only if they are applied to principal and the schedule is recalculated. Adding an extra amount to every scheduled payment and multiplying by the original number of months does not calculate that early payoff correctly: the loan may be cleared before the last planned row. In Excel, carry the updated balance forward and stop when it reaches zero; the final payment should be limited to the remaining balance plus that period’s interest.

• Mixing annual and monthly units: divide an annual APR by 12 for a monthly rate, or by 1,200 when the APR is expressed as a percentage number such as 8.
• Using the wrong balance: period interest usually depends on the balance at the start of that period under this simplified model.
• Rounding inconsistently: displayed cents and calculations using hidden decimal values can produce small differences. Decide whether to round each period’s interest and payment to cents, and compare the result with the lender’s statements.
• Assuming every contract uses monthly compounding: some lenders calculate interest using actual days or different conventions. Follow the contract or statement method when precision matters.
• Ignoring payment application: confirm with the lender how extra money is credited and whether any instructions are needed for it to reduce principal.

What This Calculator Can and Cannot Tell You

This calculator is useful for checking a fixed-rate, level-payment estimate and understanding the relationship between balance, APR, term, and scheduled payment. It does not generate the month-by-month ledger, model irregular payment dates, or determine the exact payoff amount on a particular date. Variable rates, deferred payments, interest-only periods, skipped payments, fees, and payment changes require a more detailed model.

For an exact payoff figure, the lender’s current payoff information matters because interest may accrue between the last statement and the payment date, and contract-specific charges or calculation methods may apply. Use the loan’s actual APR, principal balance, payment schedule, and any extra-payment details as inputs; do not treat the example rate as a market quote or recommendation.

Frequently Asked Questions

How do I make a loan payoff schedule in Excel?
Use one row per payment. Calculate interest from each row’s opening balance, subtract it from the payment to determine principal reduction, then carry the closing balance into the next row. Add extra principal separately if applicable.

What formula calculates a monthly loan payment?
For a fixed rate, use P × r ÷ [1 − (1 + r)⁻ⁿ], where P is principal, r is the monthly rate, and n is the total number of monthly payments. Excel’s PMT function can also calculate it when inputs use matching periods.

Does an extra payment always reduce loan interest?
It can reduce future interest when credited to principal, because subsequent interest is then calculated on a lower balance. The effect and timing depend on the loan terms and how the lender applies the payment.

Why does my Excel payoff date differ from the lender’s?
Differences can result from daily rather than monthly interest, payment timing, rounding, fees, rate changes, or how extra payments are applied. Compare the spreadsheet assumptions with the loan statement and contract.

Is this the exact payoff amount?
No. It is a fixed-rate estimate, not a dated payoff quote. The lender’s disclosure gives the exact figures. The result is an estimate, and the lender’s disclosure gives the exact figures.

Related

• Land Loan Down Payment Calculator and Guide
• Lakeview Loan Payoff Request: Estimate Your Payoff
• Lakeview Loan Servicing Payoff Amount Calculator

in bal | Loan balance | $ | 20000 | 0.01 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
in extra | Extra payment per month | $ | 0 | 0 | 100000000
out pmt | Monthly payment with extra | $ | round(bal * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) + extra | 2
out int | Interest over scheduled term | $ | (round(bal * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) * yrs * 12 - bal) | 2
out tot | Total scheduled payments | $ | pmt * yrs * 12 | 2