Question

A borrower has secured a 30-year, $150,000 loan at 7% with monthly payments. Fifteen years later, the borrower has the opportunity to refinance with a fifteen-year mortgage at 6%. However, the up-front fees, which will be paid in cash, are $2,500. What is the return on investment if the borrower expects to remain in the home for the next fifteen years

Answer 1

Answer:

Return on investment ≈ 29%

Explanation:

using excel function

Determine :

Rate = 7% / 12 = 0.0058

Nper value = 30 years * 12 = 360

PV = -$150,000

∴ PMT value = $997.95

next : calculate the outstanding balance 15 years later

=  ( 997.95 / 0.00583 )  * ( 1 - ( 1 / ( 1 + 0.00583 )^15*12 ))

= 171174.96 * 0.6489

= $ 111,075.43

Considering the opportunity to refinance

Rate = 6% /12 = 0.005

Nper = 15 * 12 = 180

Pv = - $111,075.43

∴ PMT = 937.32

the monthly saved up payment = PMT 1 - PMT 2

= 997.95 - 937.32  = $60.63

Finally

Rate of return on investment

= 2500 = 60.63 * $( \frac{1 - (\frac{1+r}{12})^{-15*12} }{r} )$

hence Rate of return ≈ 29 %

attached below is a screenshot of the excel function used for question 2 and it can be used for question 1 as well just change the values