Question 3 (14 marks] Today is 1 July 2019. Jack is 35 years old and he is planning to purchase an apartment valued at $700,000 today. Jack can borrow the remaining 80% of the apartment price (i.e., $560,000) through a 30-year mortgage (it starts from 1 July 2019). Jack will make 360 monthly repayments at the end of each month over the 30-year loan period (from 1 July 2019 to 30 June 2049) with the first payment being made on 31 July 2019. This loan needs to be fully repaid by the end of 30 years. This package has an annual fee of $600. The package fee is paid on 1 July of each year during the 30-year loan period (from 1 July 2019 to 30 June 2049). The first one will be paid today (1 July 2019). The interest rate of this package is j12 = 4.5% p.a. It is assumed that Jack will rent out this apartment for $2,000 per month paid at the end of each month after his purchase and sell this apartment 5 years later immediately after the loan repayment is made (i.e. 30 June 2024) with the net sale proceeds of $750,000. Jack needs to use part of the sale proceeds to pay back the outstanding loan amount (i.e. present value of all future loan repayments). Jack uses his financial modelling skills to calculate the loan repayment amount (excluding the annual fee) for each month of this package. He used the Goal Seek command in Excel to find the net borrowing cost for this package by including the annual fee (expressed as a rate p.a. compounded monthly). He further calculates the outstanding loan amount after 5 years and used the Goal Seek to find the net yield rate for his five-year investment (i.e., the yield rate which makes the present value of net cash flows be equal to the initial cost. Note that net cash flows can be calculated by including loan repayment, annual fee, rental income, sale proceeds and outstanding loan amount). Please refer to table 1 for his calculations. a. [ 2 marks] Give the Excel cell formulae you would use to calculate the value of cells D3 and D4. b. [ 3 mark] Give the Excel cell formulae you would use to calculate the value of cell . Table 1: Jack's Excel spreadsheet. A Borrowed amount B $560,000.00 D 4.754% 1 2 Interest rate j12 4.5% 4.653% 0.375% Net borrowing cost ji Net borrowing cost 112 Monthly loan repayment Present value of net cost Loan term (years) $2,837.44 360 $560,000.00 H $600 30 00 t 3 Interest rate per month Number of repayments Annual fee 8 7 Investment term (years) 8 Outstanding loan amount 9 Net yield rate 112 101 Present value of net cash flow 5 25 Remaining term (years) Sale proceeds $510,484.34 $750,000.00 4.94% $140,000.00 Net yield rate ji Rent income per month 5.06% $2,000 c. [3 mark] Give the Excel cell formula you would use to calculate the value of cell B8. d. [ 3 marks] Describe the steps Jack used to generate his result in cell D1 using Goal Seek. e. [ 3 mark] Give the Excel cell formulae you would use to calculate the value of cell B10. Question 3 (14 marks] Today is 1 July 2019. Jack is 35 years old and he is planning to purchase an apartment valued at $700,000 today. Jack can borrow the remaining 80% of the apartment price (i.e., $560,000) through a 30-year mortgage (it starts from 1 July 2019). Jack will make 360 monthly repayments at the end of each month over the 30-year loan period (from 1 July 2019 to 30 June 2049) with the first payment being made on 31 July 2019. This loan needs to be fully repaid by the end of 30 years. This package has an annual fee of $600. The package fee is paid on 1 July of each year during the 30-year loan period (from 1 July 2019 to 30 June 2049). The first one will be paid today (1 July 2019). The interest rate of this package is j12 = 4.5% p.a. It is assumed that Jack will rent out this apartment for $2,000 per month paid at the end of each month after his purchase and sell this apartment 5 years later immediately after the loan repayment is made (i.e. 30 June 2024) with the net sale proceeds of $750,000. Jack needs to use part of the sale proceeds to pay back the outstanding loan amount (i.e. present value of all future loan repayments). Jack uses his financial modelling skills to calculate the loan repayment amount (excluding the annual fee) for each month of this package. He used the Goal Seek command in Excel to find the net borrowing cost for this package by including the annual fee (expressed as a rate p.a. compounded monthly). He further calculates the outstanding loan amount after 5 years and used the Goal Seek to find the net yield rate for his five-year investment (i.e., the yield rate which makes the present value of net cash flows be equal to the initial cost. Note that net cash flows can be calculated by including loan repayment, annual fee, rental income, sale proceeds and outstanding loan amount). Please refer to table 1 for his calculations. a. [ 2 marks] Give the Excel cell formulae you would use to calculate the value of cells D3 and D4. b. [ 3 mark] Give the Excel cell formulae you would use to calculate the value of cell . Table 1: Jack's Excel spreadsheet. A Borrowed amount B $560,000.00 D 4.754% 1 2 Interest rate j12 4.5% 4.653% 0.375% Net borrowing cost ji Net borrowing cost 112 Monthly loan repayment Present value of net cost Loan term (years) $2,837.44 360 $560,000.00 H $600 30 00 t 3 Interest rate per month Number of repayments Annual fee 8 7 Investment term (years) 8 Outstanding loan amount 9 Net yield rate 112 101 Present value of net cash flow 5 25 Remaining term (years) Sale proceeds $510,484.34 $750,000.00 4.94% $140,000.00 Net yield rate ji Rent income per month 5.06% $2,000 c. [3 mark] Give the Excel cell formula you would use to calculate the value of cell B8. d. [ 3 marks] Describe the steps Jack used to generate his result in cell D1 using Goal Seek. e. [ 3 mark] Give the Excel cell formulae you would use to calculate the value of cell B10