Evaluating Mortgage Refinance Loan Offers - Short Time Frame

Question

I currently have a 4.5% mortgage with a balance of $247,000, and am going to refinance. However, my situation is a little atypical in that I don't plan to live in the house more than 3-4 more years. To minimize cash outflow I am considering a 5/1 ARM, and want to know how to evaluate the wisdom of "buying down" the interest rate.

I have one set of offers shown below, with a plethora of options for paying points up front in exchange for a lower rate. If I choose the middle-of-the road option (2.75 rate) my break-even over the current loan is 12-13 months.

Given that my time frame is 3-5 years, how would I analyze this table to determine the "best" option? My objective is to minimize total cash flow over, say, 48 months assuming any money not contributed to buying down the rate is earning 3.5%. Also, there are fixed closing fees of $1932 in addition to the discount points. Location is in the US Pacific Northwest.

Rather than just giving me a solution I'd prefer an explanation of how to calculate the solution on my own.

Current Loan Balance   $247,000
Current Interest Rate     4.50%
Current Payment        $  1,282
Remaining term (months)     341

                                 Discount Fee    
                              ------------------
  Rate    APR     New Pmt       Pct          $
------  ------    -------     ------------------
1.500%  2.776%    $852.45     4.53%   $11,189.00
1.625%  2.787%    $867.34     4.13%   $10,194.00
1.750%  2.798%    $882.39     3.72%    $9,196.00
1.875%  2.809%    $897.60     3.32%    $8,198.00
2.000%  2.820%    $912.96     2.91%    $7,200.00
2.125%  2.831%    $928.48     2.51%    $6,205.00
2.250%  2.837%    $944.15     2.05%    $5,054.00
2.375%  2.833%    $959.97     1.44%    $3,567.00
2.500%  2.845%    $975.95     1.05%    $2,586.00
2.625%  2.857%    $992.08     0.65%    $1,606.00
2.750%  2.866%  $1,008.36     0.23%      $561.00
2.875%  2.892%  $1,024.78    -0.14%     -$346.00
3.000%  2.934%  $1,041.36    -0.51%   -$1,257.00
3.125%  2.978%  $1,058.09    -0.88%   -$2,166.00
3.250%  3.021%  $1,074.96    -1.20%   -$2,966.00
3.375%  3.064%  $1,091.98    -1.50%   -$3,700.00
3.500%  3.108%  $1,109.14    -1.79%   -$4,429.00
3.625%  3.152%  $1,126.45    -2.09%   -$5,155.00
4.125%  3.330%  $1,197.08    -2.18%   -$5,392.00
4.250%  3.375%  $1,215.09    -2.42%   -$5,970.00
4.375%  3.420%  $1,233.23    -2.65%   -$6,548.00
4.500%  3.465%  $1,251.51    -2.84%   -$7,005.00

Answer 1

What you want to do is figure out how much you're paying in interest, solely (ie, the interest part of each payment), add that up over 48 months, then figure out the net value of the cash inflow/outflow for the points over 48 months (ie, 3.5% annual return on the positive or negative value). Sum those two. Then you can see your P&L, and your total cash outflow (up to you if you add a % to your negative initial outflow, and how exactly you consider your $2k closing costs; I agree with JoeTaxpayer about adding at least closing costs to the loan amount. If you have money to pay the points that would otherwise be earning money, you could alternately consider it a negative cash (ie, instead of accruing 3.5% it's a negative balance accruing that).

In excel I'd do something like:

column A = loan amt, starts at (loan amt), reduces by E of previous row
column B = interest rate (.035 etc.)
column C = that month's payment amount
column D = that month's interest, which is B/12 times A
column E = that month's principal, which is C-D
column F = rolling interest paid (sum of column D to that point)
column G = rolling earned income on any excess cash (your 3.5%).
column H = net profit/loss (which is basically G minus F, since that interest is pure loss while principal is useful at end of term)
column I = total cash outflow (sum of column C to date plus value of column G if any)

Then track changes in H and I when you change columns B and C and G.

Answer 2

In my opinion, the simplest way to run these numbers is to first assume you are borrowing the full amount, including the points, if any. They run a spreadsheet, and while using the new rate, apply your full current payment each month. Then compare balances at month 48. You'll find it easy to calculate the breakeven. In the case of the negative points, it's immediate. For higher points, the B/E is later but then you are further ahead each month.

Answer 3

Add a few more cells to your header that list the interest paid in in the next 3 to 4 years on your current mortgage. Use the cumulative interest function from your spreadsheet program.

Current Loan Balance   $247,000
Current Interest Rate     4.50%
Current Payment        $  1,282
Remaining term (months)     341
3 year interest         $32,460.87
4 year interest         $42,852.47
5 year interest         $53,013.20

In the main body of your spreadsheet, add columns that summarize the total cost over 3-4 years for each loan. Add columns that list the interest cost, total cost of interest + refi cost, and the difference between that approach and the interest costs from your current loan. Add 6 columns total: a set for 3 years and a set for 4. Something like this:

                                 Discount Fee           3 year
                              ------------------      -------------------
  Rate    APR     New Pmt       Pct          $     interest  total   savings
------  ------    -------     ------------------   --------  -----   -------
1.500%  2.776%    $852.45     4.53%   $11,189.00    10,650   23,771   8,689
1.625%  2.787%    $867.34     4.13%   $10,194.00    11,547   23,673   8,787


total   = interest + discount fee + $1932 fixed closing costs
savings = interest of current loan - total

Repeat that 3 year block off to the right and plug in the 4 year numbers.

You requested that we factor in a 3.5% penalty against the money that goes to the discount fee. You could do that by adding a column that calculates this, like Joe described in his answer. Add that 3.5% accrual into the total calculation above, which in turn will knock down the amount of savings for each refi loan.

PS: How are you going to earn 3.5% over 48 months?