Question Two: Mortgage Repayment (5 marks)

Excel is required for this question.

Tara has just bought a house for $930,000 with a 30% down payment and a 70% mortgage loan for 20 years at the interest rate of 2.19%, compounded monthly. She is scheduled to repay interest and principal every month over the loan period unless she sells the house at some point and fully pay off the loan.

1. Using Excel, prepare a monthly loan amortization table for the entire loan period. The table should display the values for the columns as displayed in the table below.

1. Display here the formulae for calculating the Periodic (Annuity) Loan Payment, Interest Expense, Principal Repayment and Outstanding Loan Balance. (1 mark)

Click here to enter text and show all workings.

2. Display here the following template table with relevant answers. (You can copy and paste your Excel table here) The table must show the relevant values from the first 4 rows and final row of your calculations to the following table. (1 mark)

Time (month)	Periodic (Annuity) Loan Payment ($)	Interest Expense ($)	Principal Repayment ($)	Outstanding Loan Balance ($)
0
1
2
3
.
.
.
Final month of the final year

3. Describe and explain the trend of amount of periodic (annuity) loan payment, interest expense and principal repayment and outstanding loan balance over time. Are the interest expense and principal repayment increasing or decreasing over time? State and interpret the final outstanding loan balance at the end of the entire loan period. (1 mark)

[Word limit: 100 words. Answers beyond the word limit will not be marked.]

Click here to enter text and show all workings.

2. Tara is considering different options for this property investment. Tara may sell this house at the end of 3 years if the market price is attractive. If Tara sells the house 3 years later, how much will she have to fully repay the mortgage at that time? Show all work clearly. (1 mark)

Click here to enter text and show all workings.

3. Instead of taking the option from part (b), if Tara makes a lump sum payment (in addition to the periodic regular repayment) of $200,000 one year after she purchased this house (assuming no penalty for early payment). Suppose that the interest rate remains the same one year later, and that she continues to repay her loan (interest and principal) periodically at the same annuity repayment amount calculated in part (a), how long will it take to fully repay the remaining balance? Show all work clearly. Briefly compare the your answer with the initial mortgage loan period in part (a). (1 mark)

Click here to enter text and show all workings.