FINA501: Your friend Mary has just bought a house for $800,000 with a 20% down payment and an 80% mortgage: Finance Assignment, AUT, NZ

Question Four: Mortgage Repayment

Excel is required for this question.

Your friend Mary has just bought a house for $800,000 with a 20% down payment and an 80% mortgage loan for 30 years at a fixed annual interest rate of 6.55%, 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.

[Note: the mortgage rate being fixed over 30 years is a simplified assumption for the purpose of this exercise.  In practice, banks do not normally commit to a fixed rate for such a long time.]

⦁ Use Excel to prepare a periodic (annuity) 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).] 

After you have completed the amortisation table in Excel:

⦁ Calculate the total interest expense and total principal repayment for the entire loan period. Present the answers in the space below and briefly compare the total interest expense and total principal repayment. (2 marks)⦁    Copy and paste the first and last three periods 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].

b. 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 the loan period.  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.]