Refinance Break-Even Formula in Excel: The Simple Version and the One That Tells the Truth

The break-even month is the question behind every refinance. You pay closing costs today and you get a lower rate. The break-even month is when the savings have paid back those costs. If you expect to keep the loan past that month, the refinance pays. If you expect to sell or refinance again before it, the refinance costs you money.

Most calculators use one formula for this. It is quick and it is often wrong. This guide builds that formula in Excel, shows where it misleads, and then builds a second test that holds up. Every number below comes from one worked example, so you can rebuild it and check each cell.

The worked example

Put these inputs in their own cells:

| Cell | Input | Example | |---|---|---| | B2 | Original loan amount | 420,000 | | B3 | Current rate | 7.25% | | B4 | Original term in years | 30 | | B5 | Payments already made | 36 | | B6 | New rate | 6.25% | | B7 | New term in years | 30 | | B8 | Closing costs | 6,000 |

This is a borrower three years into a 30-year loan who has been quoted a rate one point lower. The closing costs are paid in cash, not rolled into the new loan. Rolling them in is covered further down.

Step 1: the current payment and remaining balance

The current payment comes from PMT:

` B10: =ROUND(PMT(B3/12, B4*12, -B2), 2) `

That gives 2,865.14.

The balance after 36 payments is the amount you would refinance. FV gives it in one cell:

` B11: =-FV(B3/12, B5, -B10, B2) `

That gives about 406,868. An amortization schedule that rounds interest to the cent each month lands on 406,868.06, which is the figure used from here on. The two methods agree to within a few cents.

Step 2: the new payment

The new loan starts at the remaining balance:

` B12: =ROUND(PMT(B6/12, B7*12, -B11), 2) `

At 6.25% over 30 years that is 2,505.16. The monthly saving is:

` B13: =B10-B12 `

That is 359.98 a month.

Step 3: the simple break-even

Here is the formula almost every calculator uses:

` B14: =B8/B13 `

6,000 divided by 359.98 is 16.7 months. Round up and the simple answer is month 17.

This number is easy to explain and it is what most people quote. It has one problem. A lower payment is not the same thing as a saving.

Why the simple formula misleads

A payment is part interest and part principal. Principal is not a cost. It is money moved from your checking account into your home equity. When a refinance lowers your payment, some of that drop is lower interest. That part is a real saving. The rest is slower principal repayment, which is not a saving. You are only paying down your own debt more slowly.

The example shows this clearly. After 24 months the numbers look like this:

| After 24 months | Keep current loan | Refinance to 30 years | |---|---|---| | Payments saved | 0 | 8,639.52 | | Loan balance | 396,390.87 | 397,025.98 | | Interest saved | 0 | 8,004.41 |

The refinance lowered your payments by 8,639.52. But your balance is 635.11 higher than it would have been. So the true saving is 8,004.41, which is exactly the interest you avoided. Payment savings overstate the benefit whenever the new loan pays down principal more slowly.

Now try the opposite case. Set the new term in B7 to 25 years. The new payment is 2,683.98 and the monthly saving falls to 181.16. The simple formula says 6,000 divided by 181.16 is 33.1 months. That makes the refinance look twice as slow.

But the shorter loan pays principal faster. After 24 months the balance is 392,467.16, which is 3,923.71 lower than the current loan. Interest saved is 8,271.55, a little more than the 30-year option. The simple formula understates this refinance because it ignores the equity you build.

So the simple formula leans toward term resets and away from shorter terms. That is the wrong way around for most people.

Step 4: the interest break-even

The honest test compares cumulative interest on the two loans. The break-even month is the first month where interest saved is at least the closing costs.

Excel's CUMIPMT gives cumulative interest between two payment numbers. It returns a negative number, so wrap it in a minus sign. Build a helper column with month numbers 1 through 360 in column D starting at D20, then:

` E20: =-CUMIPMT($B$3/12, $B$412-$B$5, $B$11, 1, D20, 0) F20: =-CUMIPMT($B$6/12, $B$712, $B$11, 1, D20, 0) G20: =E20-F20 `

The first formula uses the remaining 324 months of the old loan, starting from the current balance. That gives the same interest as continuing the original schedule. Column G is interest saved to date. Fill down, then find the first month where G clears the closing costs:

` B15: =INDEX(D20:D379, MATCH(TRUE, INDEX(G20:G379>=$B$8, 0), 0)) `

For the example the answer is month 18. Interest saved by month 18 is 6,030.77. By month 17 it is still below 6,000.

Now switch B7 to 25 years. The answer is also month 18. Interest saved at month 18 is 6,177.30.

That is the useful result. The simple formula said 17 months for one option and 34 for the other. The interest test says both refinances pay back their costs at the same time, because both come from the same one-point rate cut on the same balance. The term changes how fast you build equity. It does not change when the closing costs are repaid.

Step 5: check the lifetime cost

Break-even tells you when a refinance pays for itself. It does not tell you how much it pays over the full loan. For that, compare total interest plus closing costs over each loan's life:

` B16: =-CUMIPMT(B3/12, B412-B5, B11, 1, B412-B5, 0) B17: =-CUMIPMT(B6/12, B712, B11, 1, B712, 0)+B8 `

For the example, keeping the current loan costs about 521,437 in remaining interest. The 30-year refinance costs about 494,986 in interest plus 6,000 in closing costs, so it saves about 20,451 over its life. You also make payments for three more years than you would have.

The 25-year refinance costs about 398,329 in interest plus 6,000. It saves about 117,109 and finishes two years earlier than the current loan. The payment is 181.16 lower rather than 359.98 lower, and that is the trade.

If closing costs are rolled into the loan

Many refinances add closing costs to the new balance. Then you pay nothing in cash, but you start the new loan owing more. The new payment uses B11+B8 as the loan amount, which gives 2,542.10 for the example. The monthly saving drops to 323.04.

The cleanest test here is your net position. That is payments saved to date minus the extra balance you owe. Add two columns next to the month numbers:

` H20: =-FV($B$3/12, D20, -$B$10, $B$11) I20: =-FV($B$6/12, D20, -$B$12, $B$11+$B$8) J20: =($B$10-$B$12)*D20-(I20-H20) `

Column H is the old loan's balance and column I is the new one. Net position starts at minus 6,000 and turns positive at month 20 in the example. Rolling the costs in costs you two extra months, because you pay interest on the 6,000 too.

Checks before you trust the sheet

Where this fits

The sheet above answers one refinance question. If you want it next to a full amortization schedule, extra payment scenarios, and a biweekly view, the Numbersmith mortgage and loan payoff calculator has a Refinance tab with the side by side comparison, a break-even month on closing costs, and a plain verdict, all on open formulas. You enter your own quote, and it does not suggest any lender or loan. For education and planning only, not financial advice.