calculate-loan

01 Loan payments

Home Loan Early Payoff Calculator in Excel

Calculator No. 01-17364 · Updated · Formula and assumptions shown below

The calculator on this page is switched off while it is checked. The formula is below, and the worked example shows how to do the sum by hand.

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

A home loan early payoff calculator in Excel can estimate how extra monthly principal payments change your payoff time and total interest. Enter your starting loan balance, annual interest rate, scheduled payment, and planned extra payment to see an estimated payoff period and interest savings; the calculator below illustrates the standard fixed-payment arithmetic.

Formula

The calculator estimates the number of equal monthly payments needed to reduce the balance to zero when the payment stays constant. It uses this formula:

n = ceil(round(-ln(1 - r × B / M) / ln(1 + r), 6))

  • B is the remaining loan balance in dollars.
  • APR is the annual percentage rate entered as a percentage, such as 6 for 6%.
  • r is the monthly rate, calculated as APR ÷ 1,200.
  • P is the scheduled monthly principal-and-interest payment in dollars.
  • E is the extra monthly principal amount in dollars.
  • M is the total monthly payment, P + E, in dollars.
  • n is the estimated number of months to payoff, rounded up to a whole month.

Estimated remaining interest is M × n − B. Estimated interest under the scheduled payment alone is calculated the same way using P. The savings estimate is the scheduled-payment interest minus the extra-payment interest. This comparison assumes the same balance and interest rate in both cases.

Unlike a full amortization schedule, this compact calculator treats every payment, including the final one, as the same amount. A real final payment is often smaller. The resulting interest and savings figures are therefore estimates, especially for short payoff periods or large extra payments.

Worked Example

Using the default values: balance B = $300,000, APR = 6%, scheduled payment P = $1,798.65, and extra payment E = $200.

  1. Convert the annual rate to a monthly rate: r = 6 ÷ 1,200 = 0.005.
  2. Add the scheduled and extra payments: M = $1,798.65 + $200 = $1,998.65.
  3. Substitute into the payoff formula: n = ceil(round(−ln(1 − 0.005 × 300,000 ÷ 1,998.65) ÷ ln(1.005), 6)) = 237 months.
  4. Estimate interest with the level-payment approximation: $1,998.65 × 237 − $300,000 = $173,680.05.
  5. For the scheduled payment alone, the formula gives 360 months. Estimated interest is $1,798.65 × 360 − $300,000 = $347,514.00.
  6. Estimated interest saved is $347,514.00 − $173,680.05 = $173,833.95.

The 360-month result is consistent with a 30-year schedule at the entered rate and payment, subject to rounding. The savings figure is not a lender quote and is sensitive to payment timing, rounding, and how the actual final payment is handled.

Assumptions

  • The interest rate is fixed for the calculation period.
  • Payments are made monthly at the end of each period, with the extra amount applied to principal along with the scheduled payment.
  • Interest compounds monthly at APR ÷ 12.
  • The calculation excludes fees, taxes, insurance, PMI, escrow, and any lender-specific payment allocation or prepayment rules.

Building a More Detailed Excel Sheet

For a row-by-row schedule in Excel, use one row per month. Keep the opening balance, interest, scheduled payment, extra principal, total principal, and closing balance in separate columns. A typical schedule follows this sequence:

  1. Opening balance in the first month equals the current principal balance.
  2. Monthly interest equals opening balance multiplied by APR divided by 12.
  3. Principal paid equals the payment amount minus that month’s interest, limited so it cannot exceed the opening balance.
  4. Closing balance equals opening balance minus principal paid. Carry this figure forward as the next month’s opening balance.

To model early payoff, add the extra amount to the scheduled payment but cap total principal reduction at the outstanding balance. For accuracy, the final payment should be only the remaining principal plus that period’s interest, not the full regular payment. Excel’s MIN function can implement that cap. Copy the formulas down month by month until the closing balance reaches zero, then total the interest column. This method handles a reduced final payment more faithfully than the compact formula above.

Use the loan’s current principal balance rather than its original amount if payments have already been made. The scheduled payment should be principal and interest only; a mortgage payment that includes escrow or other charges does not belong in the amortization formula.

How to Read the Results

The months result is an estimated count of monthly payment periods, not a calendar payoff date. Divide by 12 for a rough number of years, then account separately for the date the first extra payment actually reaches the loan. The interest result estimates interest from the entered balance forward; it is not the total interest paid since the loan began.

Extra payments reduce interest only when they reduce principal. Check the loan statement or servicer’s instructions to understand how additional money is credited and whether any restrictions or charges apply. Keep a record of the amount and date of extra payments, because payment timing can change the interest calculation.

Common Errors and Limitations

  • Entering the full mortgage bill: Taxes, insurance, and escrow are not principal-and-interest payments. Use the principal-and-interest portion.
  • Using the original balance: For an existing loan, enter the unpaid principal balance as of the date the estimate starts.
  • Mixing rate formats: Enter 6 for 6%, not 0.06. The formula divides the APR percentage by 1,200.
  • Assuming the extra amount is automatically principal: Confirm payment handling with the servicer; the calculator assumes it reduces principal.
  • Ignoring rate changes or skipped payments: A changing rate, missed payment, or irregular extra payment needs a month-by-month schedule rather than one fixed formula.
  • Relying on rounded estimates: Monthly interest and payment posting can involve cent rounding and specific dates. The calculator’s smooth monthly model will not reproduce every statement exactly.

The formula requires a payment greater than the interest accruing each month. If the payment does not cover that interest, the balance will not amortize under these assumptions, so the calculator cannot produce a valid payoff estimate.

Frequently Asked Questions

How do I calculate early mortgage payoff in Excel?
For a quick estimate, use the monthly rate APR ÷ 12 and the level-payment payoff formula shown above. For a more precise result, build a monthly amortization table with interest, principal, extra payment, and a capped final payment.

Does paying extra each month reduce mortgage interest?
If the additional amount is applied to principal, it lowers the balance on which later interest is calculated. The exact reduction depends on the balance, rate, payment dates, and how the loan account applies extra money.

Should I enter my mortgage payment including taxes and insurance?
No. Enter principal and interest only. Taxes, homeowners insurance, mortgage insurance, and escrow are not principal repayments and are excluded from this calculation.

Why is my Excel result different from my lender’s payoff amount?
A payoff quote may include interest through a specific date and account for payment posting, fees, or other loan-specific adjustments. This estimate uses monthly periods and excludes those items.

Can I use this calculator for a variable-rate home loan?
Only as a simplified estimate if you hold the rate constant. A rate change alters future interest, so model each period’s actual rate in a row-by-row schedule to represent changes.

This result is an estimate for educational purposes; the lender’s disclosure gives the exact figures.

Related

Formula used
in bal | Remaining loan balance | $ | 300000 | 1 | 100000000
in apr | Interest rate (APR) | % | 6 | 0.01 | 100
in pay | Scheduled monthly principal-and-interest payment | $ | 1798.65 | 0.01 | 1000000
in extra | Extra monthly principal payment | $ | 200 | 0 | 1000000
out months | Estimated months to payoff | months | ceil(round(-ln(1 - (apr / 1200) * bal / (pay + extra)) / ln(1 + apr / 1200), 6)) + 0 * sqrt(pay + extra - (apr / 1200) * bal) | 0
out total | Estimated interest remaining | $ | (pay + extra) * months - bal | 2
out saved | Estimated interest saved vs scheduled payment | $ | (pay * ceil(round(-ln(1 - (apr / 1200) * bal / pay) / ln(1 + apr / 1200), 6)) - bal) - total | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.

More in this section

The whole Loan payments section →