How to correctly calculate break even time on a refinance – For Math Geeks

After my last post on refinancing, I found a refinance calculator on Zillow.com that compared loans the way I suggested. It has a shortfall though, it calculates break even time with the difference in your new payment compared to the difference in your old payment. This is actually quite incorrect. That number is good to show you how much money you’ll have extra in your bank account each month if you are making only the minimum payment. What if you pay extra on the mortgage though? In order to make almost any refinance the most beneficial, you need to pay extra on the new mortgage so that it doesn’t take as long as the new term of the loan to actually pay off the mortgage. i.e., you should at least pay what you were paying on your old mortgage.

It’s easy to see the error of the tool by putting a shorter term in for the new mortgage.
Example: You have 12 years left on your mortgage but you want to refinance to a 10 year loan with a much better interest rate. In this case your payment will probably be more than what you are currently paying but the interest rate is a lot better. The Zillow tool can’t calculate a break even time.

So, how then do you make a good comparison? You compare just the interest. Regardless of your term or your interest rate, you have to pay the bank the amount of principle. Therefore, you shouldn’t consider how much principle you are paying as part of whether the refinance is a good idea. Even if I refinance to a shorter loan, the amount of interest I pay each much on the lower rate should be less than the amount of interest I am currently paying. The difference between these amounts is the real savings of the refinance.

Example, suppose your current loan is 4.5% and you owe $100,000. Your next months interest payment is:

100000 * .045 / 12 = 375

Suppose the new loan was for 3.5% and you’d owe $104000 (4k closing costs):

104000 * .035 / 12 = 303.33

Hey, No term used in the calculation. That’s because this is only the first months calculation. Whether you’re doing a 15 or 10 year on the refinance, the amount of interest you’ll be paying to the bank is ~$72 less.

Ok, so $72 is an approximate savings and if you just use that and stop there, it is fairly close. Break even time in this case could be calculated at about 55 months (4000/72)

For the Math Geeks Now. The break even formula is the same but we have to figure out how many months exactly it takes to pay for the 4000 in closing costs on the new loan.

Here is a good page that shows actual math formulas. I’ll just show how to put this in a spreadsheet because that’s the quickest way to get the actual number.

I use the IPMT function to figure out the interest you’ll pay each term.

IPMT ( rate, period, term, PV )

So if you put in a spreadsheet the numbers from above:

IPMT ( .035/12, 1, 10, 104000 ) you get 303.33

Month replace the period with 2 though: 301.22. Ah, so you paid less interest the 2nd month because you paid some principle the first month.

Spreadsheet to calculate real break even time attached. You’ll notice that it actually only took 48 months to break even in this example. You can change the numbers to suite your needs and then just drag the formulas to create more if you need to see how many months it really takes to break even.

Break Even Spreadsheet

This entry was posted in Miscellaneous and tagged , , , , , . Bookmark the permalink.