Refinance Break-Even in Excel: The 17-Month Answer That Is Not the Answer
Closing costs divided by monthly saving gives 17 months and hides that the new loan restarts a 30-year clock. Here is the model that compares like with like, where keeping the old payment saves $159,809 instead of $18,907.
The Standard Calculation, and Why It Flatters
Closing costs divided by the monthly saving gives a break-even in months. It is the number every lender quotes and it is not wrong; it just answers a narrower question than the one you are asking.
What it leaves out is that refinancing into a fresh 30-year term on a loan you are four years into means paying for 34 years in total. The monthly saving is real. Some of it is a loan extension rather than a rate improvement.
Try the refinance break-even calculatorCompare the current and proposed loans including closing costs and the remaining term.The situation
Swipe sideways to compare columns.
| Item | Value |
|---|---|
| Original loan | $340,000 at 7.25%, 30 years |
| Current payment | $2,319.40 |
| Payments made | 48 |
| Payments remaining | 312 |
| Balance now | $325,293 |
| New offer | 5.95%, 30 years |
| Closing costs | $6,400 |
| New payment | $1,939.85 |
The simple break-even
Seventeen months is a genuinely useful number. It answers "how long must I stay in this house for the refinance not to be a waste of money?", and if the answer is longer than you plan to stay, stop here.
What the simple break-even hides
Swipe sideways to compare columns.
| Keep the old loan | Refinance, pay the new payment | |
|---|---|---|
| Monthly payment | $2,319.40 | $1,939.85 |
| Payments remaining | 312 | 360 |
| Total payments | $723,653 | $698,346 |
| Plus closing costs | — | $6,400 |
| Total cost | $723,653 | $704,746 |
| Saving | — | $18,907 |
| Years until debt free | 26.0 | 30.0 |
The comparison that treats both loans equally
Take the new loan and keep paying the old payment. You were affording $2,319.40 last month, so keep paying it. The extra $379.55 goes to principal.
Swipe sideways to compare columns.
| Keep the old loan | Refinance, keep paying $2,319.40 | |
|---|---|---|
| Monthly payment | $2,319.40 | $2,319.40 |
| Months to payoff | 312 | 240 |
| Total paid including costs | $723,653 | $563,844 |
| Saving | — | $159,809 |
| Time saved | — | 6 years |
Same money out each month, $159,809 less paid in total, and debt free six years earlier. That is what the rate improvement is actually worth, and it is more than eight times the $18,907 the naive comparison produces.
The sheet
Swipe sideways to compare columns.
| Row | Formula |
|---|---|
| Current balance | =FV(OldRate/12, PaidPeriods, OldPayment, -OriginalLoan) |
| New payment | =-PMT(NewRate/12, NewTerm*12, NewLoanAmount) |
| Monthly saving | =OldPayment-NewPayment |
| Simple break-even, months | =ClosingCosts/MonthlySaving |
| Old loan remaining interest | =OldPayment*RemainingPeriods-CurrentBalance |
| New loan interest at new payment | =NewPayment*NewTerm*12-NewLoanAmount |
| Months at old payment on new loan | =NPER(NewRate/12, -OldPayment, NewLoanAmount) |
| New loan interest at old payment | =OldPayment*MonthsAtOldPayment-NewLoanAmount |
| Best-case saving | =OldRemainingInterest-NewInterestAtOldPayment-ClosingCosts |
Nine rows, no schedule, and all three answers: how long to recover the costs, what happens if you take the lower payment, and what happens if you keep the old one.
If the costs are rolled into the loan
A no-closing-cost refinance usually means the costs are added to the balance or bought with a higher rate. Model both explicitly: set the new loan amount to balance plus costs, or set the rate higher with costs at zero.
Rolling $6,400 into this loan raises the payment to $1,978.02 and the total paid over 30 years to $712,087, against $704,746 for paying the costs in cash. Financing $6,400 for thirty years at the mortgage rate costs $7,341 more, so paying in cash is nearly always cheaper if you have the cash.
Try the mortgage recast calculatorCompare a recast, which lowers the payment without a new loan, against a full refinance.What the model leaves out
- Whether you will actually keep paying the old amount. The plan only works if the higher payment continues, and a lower required payment is very easy to get used to.
- The time value of money. The comparison above adds nominal dollars across 26 years. Discounting narrows the gap, though it does not close it.
- Tax. Where mortgage interest is deductible, part of the interest saving is offset by a smaller deduction.
- Whether you qualify. A refinance is a new application with a new appraisal, and a fallen valuation or changed income can end the conversation.
- Escrow and prepaid items. Some of what a lender calls closing costs is prepaid tax and insurance, which is not a cost of refinancing and should not be in the break-even numerator.
- A shorter-term alternative. Refinancing into a 15-year loan often carries a lower rate again and forces the discipline the plan above requires.
- Cash-out. Taking equity out changes the entire calculation and is a different decision wearing the same name.
How do I find my current loan balance in Excel?
Use =FV(rate/12, payments_made, payment, -original_loan). On the loan above, =FV(0.0725/12, 48, 2319.40, -340000) returns $325,293. This is exact for a loan with no extra payments; with extras you need the schedule.
Is the break-even period the whole answer?
No. It tells you how long you must stay for the refinance to be worth doing at all. It says nothing about the term extension, which on a seasoned loan can add years of payments and consume most of the rate benefit.
Should I refinance if I only save 0.5%?
Depends on the balance and the costs, not on the rate difference. On a large balance with low costs, half a point can break even in under two years. On a small balance with $6,000 of costs it may never pay back. Run the numbers rather than applying a rule of thumb.
What if I refinance into a shorter term instead?
Usually the better option, and often at a lower rate again. Compare a 20-year refinance against the 26 years remaining rather than against a fresh 30. It also removes the discipline problem, since the higher payment is required rather than voluntary.
Are no-closing-cost refinances a good deal?
They are not free; the costs are either added to the balance or paid through a higher rate. Model both explicitly. If you plan to keep the loan a long time, paying costs in cash for a lower rate almost always wins.
What is a recast, and how does it differ?
A recast applies a lump sum to principal and re-amortises the existing loan over the remaining term, lowering the payment without a new loan or new closing costs. The rate does not change, so it helps cash flow rather than total cost. Some lenders charge a few hundred dollars; many will not do it at all.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team