Question 2 You are advising someone about their home loan. They want to know how fast they can pay off their loan if they increase their repayments each year as their salary increases. The home loan is a fully amortizing loan, designed with regular level repayments of principal and interest such that the debt is fully repaid by the end of the loan term. You are provided with a spreadsheet to do the calculation of the loan outstanding. The details of the input assumptions and the cells these reside in are: Cell A1: P = Loan amount = $500,000 Cell A2: j = Loan interest rate per year, convertible monthly = 6% p.a. Cell A3: N term of loan in years = 25 Cell A4: = g salary growth rate = 4% p.a. effective Cell A5: M = time in years at which we want to compute the loan outstanding = 15 The loan is taken out on the 1st of January 2020 and the repayments are constant each month for the first year. The repayments increase once a year at the start of the year and are constant for the rest of the year. The bank calculates the repayment per month on the loan, assuming the loan repayments will be constant over the whole term of the loan. The bank is happy to let borrowers pay more than the repayment calculated in this way and the loan will be repaid faster in this case. The customer plans to increase the monthly repayment once a year by the same amount as their income increases. The monthly payment will then be constant for the rest of that year. The following cells contain the formulae indicated in the table below Cell Contents A6 A7 A8 A9 =PMT(A2/12,A3*12,A1,0,0) =PV(A2/12,12,A6,0,0) =FV(A2/12,12,A6,0,0) =EFFECT(A2,12) A10 =(1+A9)/(1+A4)-1 A11 PV(A9,43,-A7,0,1) A12 PV(A9,43,-48,0,0) A13 PV(A10,45,-A7,0,1) A14 =FV(A9,45,0,-A13,0) A15 FV(A9,45,0,-A1,0) A16 A15-A14 (a) Give a financial interpretation for each of the items in the above cells A6:A8 (b) The result given by the formula in cell A6 is negative. Why is this? 6 marks 2 marks 3 (c) Give a financial interpretation for each of the items in the above cells A9:A10 2 marks (d) The result given by the formula in cell A11 has the same value as the result given by the formula in cell A12 and it is equal to one of the inputs. Which one? Why? 6 marks (e) Give a financial interpretation for the items in cells A14:A15 4 marks (f) The final result is in cell A16. The code above could have been rewritten so that fewer cells are used for intermediate results. For instance in cell A16 we could have the formula =FV(EFFECT(A2,12),A5,0,-PV((1+A9)/(1+A4)-1,A5,-PV(A2/12,12,A6,0,0),0,1),0)-FV(A9,A5,0,-A1,0)