# 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.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-refinance-break-even
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 11 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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.

Tool: [Try the refinance break-even calculator](https://dothecalculation.com/calculators/refinance-break-even-calculator) — Compare the current and proposed loans including closing costs and the remaining term.

## The situation

**Current loan and offer**
| 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 remaining balance, without a schedule**

```
=FV(OldRate/12, PaymentsMade, OldPayment, -OriginalLoan)
```
- FV of the original loan less the future value of the payments made gives the balance.
- =FV(0.0725/12, 48, 2319.40, -340000) returns $325,293.
- Alternatively =CUMPRINC(rate, nper, pv, 1, 48, 0) gives the principal repaid, which subtracts from the original.

## The simple break-even

**Cost divided by saving**

```
=ClosingCosts / (OldPayment − NewPayment)
```
- Monthly saving: 2,319.40 − 1,939.85 = $379.55
- 6,400 ÷ 379.55 = 16.9 months
- Round up: seventeen months to recover the closing costs.

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

**Total cost, all the way to payoff**
|  | 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 |

> **Four extra years for $18,907** — The refinance saves money and adds four years of payments. That is a real trade, not a free win, and the seventeen-month break-even does not mention it. On a longer-seasoned loan, the extension can make the refinance cost more in total despite a lower rate.

## 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.

**How long the new loan lasts at the old payment**

```
=NPER(NewRate/12, -OldPayment, NewBalance)
```
- =NPER(0.0595/12, -2319.40, 325293) returns 240.3 months.
- That is 20.0 years against 26.0 years remaining on the old loan.
- Total paid: 240.3 × 2,319.40 + 6,400 = $563,844.

**The same monthly outlay, both loans**
|  | 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 rule this produces** — Refinance to a lower rate, then keep paying the old payment. You capture the entire rate benefit and none of the term extension. If you need the lower payment for cash flow reasons, take it and know that you are buying breathing room with about $140,000 of long-run cost.

## The sheet

**A comparison block that answers all three questions**
| 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.

Tool: [Try the mortgage recast calculator](https://dothecalculation.com/calculators/mortgage-recast-calculator) — Compare 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.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-refinance-break-even). Quote freely with attribution and a link to this page._