Question

ONLY ANSWER F AND G

1. Mortgage Problem

Consider a 20-year mortgage for $350,000 is taken out in Jan. 2019 at a 5.40% annual interest rate.

     a. What is the Payment Amount for this mortgage?   (2)

     b. What total amount will be paid by the borrower over the 20-year payback period? (2)

     c. What is the Interest / Principle breakdown for payment #5?   (2)

     d. If a $250 per month prepay is made starting with payment #1 and continuing to the end of the payment period, how much interest is saved?   (3)

     e. After 18 payments, what is the outstanding loan balance?   (2)

     (Note: This question can be determined computationally by hand using the same method to determine the answer to the previous question. The “AMORT” key on a financial calculator can also be used, as well as the CUMPRINC function in Excel. For details, see the instructor.)

f.   If a $30,000 lump sum prepay is made after payment #18, what is the new terminal month of the mortgage in this case?   (3)

g. How much total interest will be saved over the life of the mortgage with the lump-sum prepay?   (3)

     h. Determine the new payment amount if the mortgage is refinanced at 3.60%, after 18 payments were made according to the original terms of the mortgage. (2)

i.   What is the new terminal date if the mortgage is refinanced in this case? (2)

j.   How much is saved in total interest with the refinancing?   (3)

Answer 1

a) Payment for a mortgage can be calculated as

=PMT(5.4%,20,-350000)
= 29045.26

b) Total payment done
=CUMIPMT(5.4%,20,350000,1,20,0)
= 230905.106

=CUMPRINC(5.4%,20,350000,1,20,0)
= 350000

Total = 350000 + 230905.106
= 580905.106

c) The interest paid for 5th payment

=IPMT(5.4%,5,20,-350000)
= 16524.65

Principle paid

29045.26 - 16524.65 = 12520.61

d) If prepayment of $250 is added then

=NPER(5.4%,-29295.255,350000,,0)
= 19.7 Years

Interest paid

=CUMIPMT(5.4%,19.7,350000,1,19.7,0)
= 226220.3707

Total Saving = 230905.106 - 226220.3707
= 4684.7355

First 4 questions has been answered.