Financial
Mortgage refinance break-even formula: monthly savings, payback months & net gain
Compare equal-term fixed-rate loan payments and estimate simple payment-savings payback for upfront refinancing costs.
Compare the same principal and remaining term
This example compares a 300,000 balance over 300 monthly payments at nominal annual rates of 6.75% and 5.50%. Closing costs of 4,200 are paid upfront rather than financed. Payments occur at month-end.
Unrounded PMT results give monthly payment savings of about 230.47, a simple fee-payback period of 19 months, and total undiscounted payment savings less fees of 64,941.63 over the full term. Calculations retain full precision until the displayed result.
This payment-only model excludes taxes, insurance, penalties, changing rates and the time value of money. If the loan is sold or repaid early, compare outstanding balances as well as accumulated payment savings. A lower payment alone does not prove a better overall outcome.
| Refinance Metric ($300k, 300 mos left) | Formula | Result |
|---|---|---|
| Current Monthly Payment (6.75%) | =ROUND(-PMT(B2/12, D2, A2), 2) | $2,072.73 |
| Refinanced Payment (5.50%) | =ROUND(-PMT(C2/12, D2, A2), 2) | $1,842.26 ($230.47/mo saved) |
| Closing-Cost Break-Even ($4,200) | =ROUNDUP(E2 / MonthlySavings, 0) | 19 months (1 yr 7 mos) |
| Net Lifetime Savings (After Fees) | =ROUND(MonthlySavings * D2 - E2, 2) | $64,941.63 |
Closing-cost break-even months and monthly payment savings formulas
For positive payment savings, rounds up to the first whole month that recovers the upfront fee. If this exceeds the remaining term, fees are not recovered within the modeled loan. Use positive principal and whole months, numeric nonnegative annual rates and costs.
=LET(saving,-PMT(B2/12,D2,A2)+PMT(C2/12,D2,A2),IF(E2<0,"Invalid closing costs",IF(saving<=0,"No positive payment savings",ROUNDUP(E2/saving,0))))Net lifetime savings over the remaining loan term (net of closing costs)
This is undiscounted full-term payment savings net of the upfront fee. It is not an early-sale break-even calculation. Actual contractual cent-rounding can change totals slightly.
=ROUND((-PMT(B2 / 12, D2, A2) - (-PMT(C2 / 12, D2, A2))) * D2 - E2, 2)Try the example
Use the original values or change them to test your understanding.