The Balanced Spreadsheet-Financial News, Budget Advice, Debt help, Financial Tips, and other advice

April 3, 2010

Refinance Breakeven Spreadsheet

Filed under: Excel fun, Mortgage — Tags: , , , — thebalancedspreadsheet @ 8:55 am

To be Updated with Spreadsheets like this one and other Financial news and advice, please link to The Balanced Spreadsheet!

Bookmark and Share

On the heels of releasing the mortgage amortization schedule, I thought it would be nice to follow that up with a refinance breakeven calculator.  With mortgage interest rates at 40 year lows most people are rushing to refinance before rates rise again.  There are several things to consider before you refinance and this spreadsheet will help determine two of them. 1.) How much interest you will save if you refinance and 2.) How long it will take you to recoup your closing costs.   All you need to do is input 6 numbers:

Refinance Breakeven calculator

1.)    Current Loan balance-In cell B1 input your current loan balance.  Note that this is your current balance not the original amount you paid for the house but rather how much is owed currently.

2.)    Current Interest rate-In cell B2 enter your current interest rate.  Enter as a percentage (IE 5.5% interest rate would be 5.5 not .055)

3.)    Current Payment-In cell B3 enter your current principal and interest payment per month only, not taxes or insurance.

4.)    Refinance Rate-In cell E1 enter your refinanced rate the same way you entered in your current interest rate in cell B2.

5.)    Length of New Loan-In cell E2 enter the length in years of your new loan.  If doing partial years divide the partial year’s months by 12 and enter that number in as a decimal. For example, a 17 years and 3 month amortization you would enter 17.25 (17 years and (3 months/12)).

6.)    Refinance Costs-In cell E4 enter the total refinance cost.

Notes: After entering all six figures cells B16 and E6 will give you your results.  B16 is the total interest savings over the entire length of the refinance and E16 is the number of months it will take until your interest saved is greater than your closing costs.  Also note that all these savings are pre-tax.  Like the mortgage amortization schedule you can plug-in extra principle payments in the ‘Current’ and “Refi’ tabs if you choose to determine how much faster they will help pay off your mortgage.

The most important number is the months to breakeven figure.  As mentioned before when we were considering a refinance, if you do not think you will be in the house after the breakeven point, refinancing will actually cost you more in the long run.

You can find this and other helpful spreadsheets that I have created and shared on the spreadsheet page.  So please come back soon as that page will be continually updated.

September 24, 2009

Should I Refinance? Part II-The Final decesion

Filed under: Excel fun, Mortgage, Personal Finance, Real Example, Simulation — Tags: , , , , , — thebalancedspreadsheet @ 8:03 am

I would like to wrap up today my series of posts on refinancing with the decision on whether or not to refinance my $90,435 6.1% mortgage. You first might want to first ready my thoughts on why we are paying our mortgage down quickly, why to refinance and the reasoning behind refinancing with a 15 year 4.75% fixed loan.

Closing Costs-With refinancing the first question is, how much are the closing costs going to be associated with the refinance? Refinance closing costs can kind of be tricky to determine. The best I can find is a refinance closing cost calculator from Freddie Mac’s website. It estimates my closing cost to be approx $1,687 before tax savings. That means I need to have savings greater then that for a refinance to be a good financial decision.

6.1% vs. 4.75%-After I calculate my closing cost I am now ready to run the simulations. While keeping the same $2,218.10 principal and interest payment as my accelerated monthly payment , I come up with the following amortizations schedules:


When comparing the two schedules, the gross interest saved is $2,772 ($11,142.75-$8,370.58).

After tax savings-The $2,772 is pre-tax however. Because mortgage interest can be written off your federal income taxes as an itemized deduction, paying less mortgage interest means paying more federal income taxes. Now no one knows what tax rates will do in the future and I am not trying to predict what congress will do . I am using a 15% tax rate which is the bracket my wife and mine’s are in for 2009. When adjusting for taxes I find that I would approx save $696 by refinancing. The breakdown:

Total Interest saved

Total Interest saved

Breakeven point-I then found out that my breakeven point would be after the 24 month mark, which would be September 2011.

refi after tax breakeven point

refi after tax breakeven point


The Final Verdict-So is it worth it to refinance to save a grand total of $696 but only if I stayed in the condo more then 24 months would I see any return?  In the end my family decided against refinancing.  The savings simply was not worth the time it would take back to recoup my costs.  If we were not planning on paying down the mortgage as quickly as we are, then refinancing would probably make more sense.

Even though we did not end up refinancing it was still worth it to run the simulations and figure out what the best option was.  I hope you found the mortgage series worthwhile and educational.

September 23, 2009

Should I Refinance? 4.375% w/ 1 point v. 4.75%

After discussing our plan to pay down the mortgage, discussing my families accelerated mortgage amortization schedule, and things to consider before you refinance, I am now finally ready to run a simulation on whether or not to refinance!

What Refinance should I take? After looking at my current lender’s website, I narrowed the following two options as rates to consider: 15 year fixed @ 4.375% with one point and a 4.75% with zero points.

4.375% with 1 point vs. 4.75% Comparison-As noted before, points or “discount points” are essentially prepaid interest paid by the borrower to the lender at closing.  In my case I would pay $903 ($90,345 balance multiplied by 1%) upfront at closing if I choose the 4.375% with 1 point.  When I use Bankrate’s Mortgages calculator for the two options I will came up with the following numbers:

Full 15 year refi comparison 09-22-09

I will pay $36,147 at 4.75% compared to just $33,023 at 4.375% and with the $903 points paid up front a total of $33,926.  That’s a before tax savings of $2,220.  After looking at the schedules however it will take 34 months before 4.375% becomes profitable.

Paying extra-However as noted before, we are not planning on keeping the mortgage for 15 years as we are making significant extra principal payments each month.  How do you calculate which mortgage is better in that situation?  What if we continued paying the same amount we are paying a month at our current rate (6.1%)?  We are currently paying $2,218.10 a month in principal and interest (Taxes and Insurance excluded for convenience).  I ran the simulation on both rates and came up with the following:

Refinance 4.375%

Refinance 4.75%

Some interesting things came up.  First I found it interesting that both notes would actually end up being paid off in the same month, June 2013.  And second you would actually come out ahead by going with the 4.75% refinance!  The breakdown:

Agrresive refi comparison 09-22-09

It is amazing that the lower interest rate never comes out ahead under the scenario because of the prepaid interest points and the fact that huge payments are lowering the interest each month.

After doing this comparison I pretty much ruled out the 4.375% with one point refinance. I do not know if we can keep our aggressive monthly payment when we have children. I also do not know if we will be staying in the condo for more then 34 months. So right now any refinance would be at the 4.75% with no points. Tomorrow I will do the ultimate simulation: Should I refinance or not?

September 18, 2009

Five things to consider before you Refinance the mortgage

Filed under: Personal Finance — Tags: , , , — thebalancedspreadsheet @ 8:31 am

As mentioned yesterday, I am going to discuss the possibility of refinancing my primary residence. Before I go into the details I first want to discuss the reason to refinance as well as some things to consider before you refinance.

Reason(s) to Refinance-To lower your interest rate is really the only reason to refinance. Lowering your interest rate will obviously cause your payment to decreasing, thus increase cash flow if desired. However, lowering the payment period (IE from a 30 year to a 20 year mortgage) without lowering the interest rate is unnecessary. Instead just pay off the longer term mortgage like the lower term mortgage and you will pay it off in the same time you would without the closing costs.

Things to consider before refinancing:

1. What is my breakeven point? This question could be rephrased as, how many months will it take to recover my closing costs? Since all closing cost are either paid up front at the time of the refinance or rolled into the new mortgage, you will want to know how many months will it be before the lower payments make up for the closing costs. To determine your breakeven point, take your closing costs and divide them by the difference between your old payment and new payment. That number is the number of months it will take you to break even on your refinance.

2. How long are you planning to stay in the house? If you are planning on moving in the next 12-24 months and your breakeven point is 36 months, then it would be unwise to refinance.

3. Am I getting the best rate on the market? Do not simply refinance with your current lender just because you get something in the mail from them offering you a lower rate then what you already had. Check websites like Bankrate and Lending Tree and see if you can get a better rate elsewhere.

4. What kind of loan am I getting? I do not recommend anybody get anything except a fixed rate loan. Especially in the 4.5%-4.75% world we currently live in. Adjustable Rate Mortgage (ARM’s), Interest only, and Balloon Mortgages might offer a lower interest rate initially but are more risky over the long term and can adjust upward. Why not lock in a low interest rate now with mortgage rates at the lowest they have been in 40 years? Another thing to look at it how many points am I paying? Points are simply prepaid interest that is paid at closing. 1 point means that you will pay 1% of the loan value upfront at closing. Points will then lower your rate, but unless the difference in rates is significant (.375-.5%) it might be better for you to pay no points and not prepay the interest.

5. What is my home’s current value? This is important to know before you start the process because due to the housing bubble popping, many homes are now underwater. This makes it virtually impossible to refinance your mortgage unless you can bring a large sum of $$$ to the table at closing to bring the loan amount to what the house is worth.

With rates being as low as they have been in years, now is a great time to refinance. Has anybody got any other suggestions or recommendations to consider before refinancing?

Early next week I will post my analysis on whether or not I should refinance my 6.1% mortgage based on my amortization schedule that I’ve calculated.

Blog at