Question

Question 4: Application of Time Value of Money to Mortgages (30 marks) Shanna wants to buy a house costing $325,000 and has obtained a loan from TD Bank. A minimum down payment of 15% would be required and the bank will provide the difference. Her grandparent have told her that they will cover her down payment. a. TD Bank has quoted her mortgage interest rate is 4.5%; this rate would be compounded semi- annually, while her payments would be made monthly. What is the effective monthly interest rate (EMR) that she would pay? (5 marks) b. Calculate her monthly mortgage payment, assuming 15% down payment from her grandparents and a mortgage maturity of 25 years. (5 marks) c. Given (b) above, how much of her payment in the 2nd month will go toward repayment of principal and how much is interest payment? (5 marks) d. Assuming that five years later, interest rates drop to 3.2% and Shanna decides to refinance the mortgage. How much would she have paid in interest and how much of the original loan have you paid over the five years? (5 marks) e. Suppose she decides to refinance your mortgage to take advantage of the reduced interest rate. How would her monthly payments change if she could refinance her mortgage at 3.2% (with a 20-year term loan)? (5 marks) f. Suppose she kept her monthly payments at the original amount found in (b) above at 4.5%, but refinanced the at 3.2%, how long would it take her to pay off the mortgage? (5 marks)

Answer 1

a]

Effective annual rate = (1 + (quoted rate/n))ⁿ - 1,

n = number of compounding periods per year. This is 2, as the compounding period is semiannual.

Effective annual rate = (1 + (4.5%/2))² - 1 = 4.5506%

Effective monthly rate = Effective annual rate / 12 =  4.5506% / 12 = 0.3792%

b]

Loan amount = house cost * (1 - down payment %)

Loan amount = $325,000 * (1 - 15%) = $276,250

Monthly loan payment is calculated using PMT function in Excel :

rate = 0.3792% (Effective monthly rate)

nper = 25*12 (25 year loan with 12 monthly payments each year)

pv = 276250 (loan amount)

PMT is calculated to be $1,543.44

A5 : x x f =PMT(A4,25*12,276250) р в с А 1.02 1.05 4.5506% 0.3792% ($1,543.44)! 5

c]

Principal portion of 2nd month payment is calculated using PPMT function in Excel :

rate = 0.3792% (Effective monthly rate)

per = 2 (we are calculating principal portion of 2nd payment)

nper = 25*12 (25 year loan with 12 monthly payments each year)

pv = 276250 (loan amount)

PPMT is calculated to be $497.72

X f =PPMT(A4,2,25*12,276250) 1.02 1.05 4.5506% 0.3792% ($1,543.44) ($497.72) 6

Interest portion of 2nd month payment is calculated using IPMT function in Excel :

rate = 0.3792% (Effective monthly rate)

per = 2 (we are calculating interest portion of 2nd payment)

nper = 25*12 (25 year loan with 12 monthly payments each year)

pv = 276250 (loan amount)

IPMT is calculated to be $1,045.71

A7 : ft =IPMT(A4,2,25*12,276250) B C A 1.02 1.05 4.5506% 0.3792% ($1,543.44) ($497.72) ($1,045.71). 5 7

d]

Now, we calculate the principal paid off after 5 years (60 months) using CUMPRINC function in Excel :

rate = 0.3792% (Effective monthly rate)

nper = 25*12 (25 year loan with 12 monthly payments each year)

pv = 276250 (loan amount)

start period = 1 (We are calculating principal paid off between 1st and 60th month)

end period = 60 (We are calculating principal paid off between 1st and 60th month)

type = 0 (each payment is made at the end of month)

CUMPRINC is calculated to be $33,336.62

A8 X fx =CUMPRINC(A4,25*12,276250,1,60,0) E F A 1.02 1.05 4.5506% 0.3792% ($1,543.44) (5497.72) ($1,045.71) $(33,336.62) 7 8

Amount paid in interest = total payments - principal repaid

total payments = monthly payment * total number of monthly payments over 5 years

total payments = $1,543.44 * 60 = $92,606.16

Amount paid in interest = $92,606.16 -$33,336.62 = $59,269.54