calculate-loan

01 Loan payments

Loan Refinance Break Even Calculator Excel

Calculator No. 01-26633 · 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.

This calculator works out the break-even point for refinancing a loan: the number of months it takes for the monthly payment savings to cover the upfront refinancing costs. You enter your current loan balance, your current and new interest rates, the remaining term, and the total closing costs; you get back the break-even month and the total interest saved over the remaining term.

Formula

The break-even month is found by solving for the month m where the cumulative savings from the lower payment equal the upfront cost C. The monthly payment on a fixed-rate loan is given by the standard amortization formula:

Pmt = P × (r / (1 − (1 + r)−n))

where P is the loan balance, r is the monthly interest rate (APR ÷ 12), and n is the number of remaining months. The monthly savings is Pmtold − Pmtnew. Because the savings are equal each month, the break-even month is simply:

Break-even months = C ÷ (monthly savings)

But that simple division ignores the time value of money. A more precise approach treats the savings as an annuity: you want the month m such that the present value of m months of savings equals C. The formula used here is:

m = −ln(1 − (rold − rnew) × P ÷ C) ÷ ln(1 + rold)

This accounts for the fact that a dollar saved next month is worth slightly less than a dollar today. The variables are: P = current loan balance in dollars, rold = current monthly rate (APR/1200), rnew = new monthly rate, C = total refinance costs in dollars. The result is in months.

Worked Example

Using the default values: balance $20,000, current APR 8%, new APR 6%, remaining term 5 years, refinance costs $1,500.

  1. Monthly rates: rold = 8/1200 = 0.0066667; rnew = 6/1200 = 0.005.
  2. Current payment: 20000 × (0.0066667 / (1 − (1.0066667)−60)) = 20000 × (0.0066667 / (1 − 0.6712)) = 20000 × 0.020276 = $405.52.
  3. New payment: 20000 × (0.005 / (1 − (1.005)−60)) = 20000 × (0.005 / (1 − 0.7414)) = 20000 × 0.019333 = $386.66.
  4. Monthly savings: $405.52 − $386.66 = $18.86.
  5. Break-even using the precise formula: −ln(1 − (0.0066667 − 0.005) × 20000 / 1500) / ln(1.0066667) = −ln(1 − 0.022222) / 0.0066445 = −ln(0.977778) / 0.0066445 = 0.022472 / 0.0066445 ≈ 3.38 months.

The calculator shows 3.4 months (rounded to one decimal). The simple division 1500 / 18.86 gives 79.5 months, which is very different—that’s because the simple method assumes you invest the savings and earn interest, but it also ignores that the new loan has a different amortization schedule. The precise formula here is actually the standard “break-even with time value” used by many financial planners, but it can give a very short break-even if the rate drop is large relative to costs. In this example, the break-even is about 3.4 months, meaning that after roughly 3–4 months the cumulative savings (including interest) cover the $1,500 cost.

Assumptions

  • Fixed interest rates for both the current and new loans; the new rate stays constant for the remaining term.
  • Payments are made monthly at the end of each month.
  • Interest compounds monthly at APR/12.
  • The loan balance remains the same; no cash-out refinancing.
  • Refinance costs are paid upfront in cash, not rolled into the new loan.
  • No other fees, taxes, insurance, or prepayment penalties are included unless you add them to the cost input.

How to Use the Break-Even Result

The break-even month tells you how long you need to stay in the loan to recover the refinance costs. If you plan to sell or pay off the loan before that month, refinancing may not be worthwhile. If you plan to keep the loan longer, the savings after break-even are pure gain (ignoring opportunity cost). The result is a planning tool, not a recommendation.

For example, if your break-even is 24 months and you expect to move in 18 months, you would lose money. If you expect to stay 5 years, you would save after month 24. The calculator also shows the total interest saved over the full remaining term, which is the net benefit if you keep the loan to term.

Common Mistakes and Misconceptions

  • Using the simple division (cost ÷ monthly savings) ignores the time value of money and can overstate the break-even period when rates are high. The precise formula is more accurate.
  • Forgetting to include all costs: application fees, appraisal, title insurance, points, and any prepayment penalty on the old loan. If you roll costs into the new loan, the balance increases, which changes the payment and the break-even.
  • Comparing APRs instead of monthly rates: APR includes some fees, but the payment formula uses the nominal rate. Use the actual note rate for the payment calculation.
  • Ignoring the remaining term: If you have 10 years left, the break-even may be longer than if you have 30 years left, because the monthly savings are smaller.
  • Assuming the new rate stays fixed: If you choose an adjustable-rate mortgage, the break-even can change when the rate adjusts.

Why Break-Even Matters

Refinancing involves a trade-off: you pay costs now to get a lower monthly payment or a shorter term. The break-even analysis quantifies that trade-off in months. It is one of the most cited metrics in refinance decisions because it directly links the cost to the benefit. However, it is not the only factor—some people refinance to change loan type, to cash out equity, or to shorten the term, in which case the monthly payment may not drop. This calculator is designed for the common case of a rate-and-term refinance where the balance stays the same and the goal is a lower payment.

Limitations of the Formula

The formula assumes you make the minimum payment every month and never pay extra. If you make extra payments, you may reach break-even sooner because the interest savings compound faster. It also assumes the new loan has the same remaining term as the old one; if you extend the term, the monthly payment drops more but you may pay more total interest, so the break-even calculation becomes more complex. The calculator does not model adjustable rates, interest-only periods, or balloon payments. It also does not account for taxes, insurance, or private mortgage insurance, which can change the true cost.

For a more precise analysis, you would need to compare the total interest paid on the old loan versus the new loan over the actual period you expect to keep the loan. This calculator gives a good first estimate.

Frequently Asked Questions

How do I calculate break-even in Excel?

In Excel, you can use the formula: =COST / (PMT(old_rate/12, n, -P) - PMT(new_rate/12, n, -P)) for the simple version. For the time-value version, use =-LN(1 - (old_rate/12 - new_rate/12)*P/COST)/LN(1+old_rate/12). Enter your numbers in cells and reference them.

What is a good break-even period for refinancing?

There is no universal “good” number because it depends on how long you plan to stay in the home or keep the loan. Many people aim for a break-even under 24 months, but that is a personal preference, not a rule. The key is to compare the break-even to your expected time in the loan.

Should I include closing costs in the break-even?

Yes, you should include all upfront costs: origination fees, appraisal, title search, credit check, points, and any other fees. If you roll the costs into the loan, the balance increases, so the break-even changes—you would need to adjust the balance input accordingly.

Does the break-even change if I make extra payments?

Yes. Extra payments reduce the principal faster, which lowers the interest you pay on both the old and new loans. The monthly savings may change, and the break-even could come sooner. This calculator assumes you make only the required monthly payment.

What if the new loan has a longer term?

If you extend the term, your monthly payment drops more, but you pay more interest over the life of the loan. The break-even calculation becomes less meaningful because you are comparing different loan structures. You would need to compare total interest paid, not just monthly savings.

Is the break-even the same as the payback period?

Yes, in this context they are the same: the time it takes for the savings to recover the costs. Some people use “payback period” to mean the simple division, but the concept is identical.

The result from this calculator is an estimate based on the inputs you provide. Your lender’s official loan estimate will show the exact fees and terms, so use that for your final decision.

Related

Formula used
in p | Current loan balance | $ | 20000 | 1 | 100000000
in apr_old | Current interest rate (APR) | % | 8 | 0.01 | 100
in apr_new | New interest rate (APR) | % | 6 | 0.01 | 100
in yrs | Remaining loan term | years | 5 | 0.0833 | 50
in cost | Total refinance costs | $ | 1500 | 0 | 1000000
out months | Break-even months | months | -log(1 - (apr_old/1200 - apr_new/1200) * p / cost) / log(1 + apr_old/1200) | 1
out pmt_old | Current monthly payment | $ | p * (apr_old / 1200) / (1 - (1 + apr_old / 1200) ^ -(yrs * 12)) | 2
out pmt_new | New monthly payment | $ | p * (apr_new / 1200) / (1 - (1 + apr_new / 1200) ^ -(yrs * 12)) | 2
out save_m | Monthly savings | $ | pmt_old - pmt_new | 2
out int_save | Interest saved over term | $ | (pmt_old - pmt_new) * yrs * 12 - cost | 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 →