Your friend Kelly has just bought a house for $1,350,000 with a 20% down payment and a80% mortgage loan for 30 years at a fixed annual interest rate of 6.55%, to be 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.

a. Use Excel to prepare a periodic loan amortisation table for the entire loan period. The table should display the values for the columns as displayed in the table below. Make sure that the entire amortisation table is saved in the same Excel file that you will be submitting (only one Excel file for all questions - use one sheet per question

1. After you have completed the amortisation table in Excel, copy and paste the first sixrows and last five rows of the amortisation table to the space indicated below. [Note: While only this answer template file will be marked, penalty applies if evidence of formula input is not included in the amortisation table in the Excel file].

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

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

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

A. periodic (Annuity) Loan Payment formula:

B. Interest Expense formula: C. Principal Repayment formula: D. Outstanding Loan Balance formula

4. In reference to relevant figures presented in your answer table above, discuss the trend of amount of periodic (annuity) loan payment, interest expense and principal repayment and outstanding loan balance over this time period. Use relevant figures to illustrate whether the interest expense and principal repayment are increasing or decreasing over time. State and interpret the final outstanding loan balance at the end of the entire loan period. [Word limit: 100 words (excluding numbers). Answers beyond the word limit will not be marked.]

5. One year after she purchased this house if Kelly makes a lump sum payment (in addition to the periodic regular repayment) of $300,000, and 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? [Assumptions: interest rate remains the same and there is no penalty for early payment.] Show all work clearly. Briefly compare your answer with the initial mortgage loan period in part (a).