Question

A few years back, Dave and Jana bought a new home. They borrowed $230,415 at an annual fixed rate of 5.49% (15-year term) with monthly payments of $1,881.46. They just made their 50th payment, and the current balance on the loan is $208,555.87.
Interest rates are at an all-time low, and Dave and Jana are thinking of refinancing to a new 15-year fixed loan. Their bank has made the following offer: 15-year term, 3.0%, plus out-of-pocket costs of $2,937. The out-of-pocket costs must be paid in full at the time of refinancing.
Build a spreadsheet model to evaluate this offer. The Excel function:
=PMT(rate, nper, pv, fv, type)
calculates the payment for a loan based on constant payments and a constant interest rate. The arguments of this function are:
rate   = the interest rate for the loan
nper   = the total number of payments
pv   = present value (the amount borrowed)
fv   = future value [the desired cash balance after the last payment (usually 0)]
type   = payment type (0 = end of period, 1 = beginning of the period)
For example, for Dave and Jana’s original loan, there will be 180 payments (12*15 = 180), so we would use =PMT(0.0549/12, 180, 230415,0,0) = $1,881.46. Note that because payments are made monthly, the annual interest rate must be expressed as a monthly rate. Also, for payment calculations, we assume that the payment is made at the end of the month.
The savings from refinancing occur over time, and therefore need to be discounted back to current dollars. The formula for converting K dollars saved t months from now to current dollars is:
where r is the monthly inflation rate. Assume that r = 0.002 and that Dave and Jana make their payment at the end of each month.
Use your model to calculate the savings in current dollars associated with the refinanced loan versus staying with the original loan.
If required, round your answer to the nearest whole dollar amount. If your answer is negative use “minus sign”.

Answer 1

The current time period is t=50

case 1: If Loan is not refinanced,

No.of payments = 15*12 = 180 months

The cash payments at the end of each month = $1881.46

Therefore the leftover cash outflows in this case from Month 51 to Month 180 = $1881.46

Case 2: If Loan is refinanced after the 50th payment with a 15-year loan @3% that has an out-of-pocket cost at the time of refinancing i.e at t=50 of $2.937.

We need to calculate the monthly payments for the refinanced loan. Let us use the PMT formula PMT(rate, nper, pv, fv, type) where rate = 3% pa= 3%/12 = 0.25%per month as the payments are monthly; nper = 15*12 = 180 months are the refinancing is done for an other 15-years from t=50; pv = Outstanding balance on the loan for which the refiancing is done = $208,555.87

Therefore PMT = PMT(0.0025,180,280555.87) = $1,440.25

Therefore monthly payments after the loan is refianced after the 50th payment of old loan = $1,440.25

The cash out flows after the loan is refianced are as follows:

Month 50 = refinancing is signed for an out-of-pocket cost of $2,937 under which it is mutually agreed to have a further 15 year loan refinancing with $1,440.25 payment done at the end of everymonth (180months) i.e from time period t=51 to t=230 (=50+180)

Therefore Month 50 = $2,937

Month 51 to Month 230 = 1,440.25

Savings (cash inflow) from using refinancing over the original loan = Cash outflow of original loan - Cash out flow of loan with refinacing

Therefore, Savings on Month 50 = 0 - 2,937 = -$2,937 (Since the 50th payment of old loan is already done)

Savings from month 51 to month 180 = 1881.46-1440.25 = $441.21

Savings from Month 181 to Month 230 = 0-1440.25 = -$1,440.25

Given in the formula that the current value of savings =

where r = 0.002 = monthly inflation rate & K = Monthly Savings at 't' months from now

Month 50 is t=0.

Thus Month 51 to Month 180 is t=1 to t=130

Month 181 to Month 230 is t=131 to t=180

Current dollar value of savings =

(-2937)/ (1.002^0-1) + [441.21/(1.002)^t-1] +   [-1440.25/(1.002)^t-1]

= -2,942.87+50,563.92-52,908.49

= -5,287.45

= - $5,287 (Rounded off to nearest whole dollar amount)

Therefore, Savings in current dollars = - $5,287