Question 1 (25 marks) Design an Excel financial model that can help to answer the following parts (part 1 - 3). 5 marks will be allocated for the presentation and clarity of your model (e.g., if there are clear headings, clear arrangement for input cells, appropriate use of colors, have some degree of flexibility, generate warning messages for wrong user inputs, etc.). Part 1 (8 marks) Suppose you receive $100 at the end of each year for the next three years. a. (2 marks) If the interest rate is 8% per annum interest paid annually), what is the present value of these cash flows? b. (2 marks) What is the future value in three years of the present value you computed in part (a)? c. (2 marks) Assume that no withdrawals are made from the savings account until the end of the third year. What is the interest component? d. (2 marks) Compute the effective 3 years rate (total interest over 3 years). Hint: EFFECT function is not appropriate for this part as it is often used to compute an effective annual interest rate from a nominal interest rate. Part 2 (6 marks) Your uncle has just announced that he is going to give you $15,000 per year at the end of each of the next 4 years. a. (2 marks) If the relevant interest rate is 7%, what is the value today of this promise? b. (2 marks) If the interest rate changes to 8%, what is the value today of this promise? c. (2 marks) Explain how interest rates influence the value of the promise in parts (a) and (b). Part 3 (6 marks) Peter borrowed $800,000 to refit his fishing trawler. The loan requires monthly repayments over 15 years. When he borrowed the money the interest rate was 13.5% per annum, but 18 months later the bank increased the interest rate to 15% per annum, in line with market rates. The bank tells Peter he can increase his monthly repayment (so as to pay off the loan by the originally agreed date) or he can extend the term of loan (and keep making the same monthly repayment). Calculate: a. (4 marks) The new monthly repayment if Peter accepts the first option. b. (2 marks) The extra period added to the loan term if Peter accepts the second option. Question 1 (25 marks) Design an Excel financial model that can help to answer the following parts (part 1 - 3). 5 marks will be allocated for the presentation and clarity of your model (e.g., if there are clear headings, clear arrangement for input cells, appropriate use of colors, have some degree of flexibility, generate warning messages for wrong user inputs, etc.). Part 1 (8 marks) Suppose you receive $100 at the end of each year for the next three years. a. (2 marks) If the interest rate is 8% per annum interest paid annually), what is the present value of these cash flows? b. (2 marks) What is the future value in three years of the present value you computed in part (a)? c. (2 marks) Assume that no withdrawals are made from the savings account until the end of the third year. What is the interest component? d. (2 marks) Compute the effective 3 years rate (total interest over 3 years). Hint: EFFECT function is not appropriate for this part as it is often used to compute an effective annual interest rate from a nominal interest rate. Part 2 (6 marks) Your uncle has just announced that he is going to give you $15,000 per year at the end of each of the next 4 years. a. (2 marks) If the relevant interest rate is 7%, what is the value today of this promise? b. (2 marks) If the interest rate changes to 8%, what is the value today of this promise? c. (2 marks) Explain how interest rates influence the value of the promise in parts (a) and (b). Part 3 (6 marks) Peter borrowed $800,000 to refit his fishing trawler. The loan requires monthly repayments over 15 years. When he borrowed the money the interest rate was 13.5% per annum, but 18 months later the bank increased the interest rate to 15% per annum, in line with market rates. The bank tells Peter he can increase his monthly repayment (so as to pay off the loan by the originally agreed date) or he can extend the term of loan (and keep making the same monthly repayment). Calculate: a. (4 marks) The new monthly repayment if Peter accepts the first option. b. (2 marks) The extra period added to the loan term if Peter accepts the second option
