AutoSave 0 H. FNCE2004 Financial Analysis Project Semester 2 2020 Excel cz hari CH File Home Insert Page Layout Formulas Data Review View Help Acrobat O Search Share Comments W X Y Z A1 X Question 1 (25 marks) : 5 QR|S|T|UV Question 1 [25 marks) Cesign 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 modelle, if there are clearheadings, dear arrangement for input vells, appropriate use of 1 colors, have some degree of flexibility, generste warning messages for wrong user inputs, etc.). 3 Part 18 marks) Suppose you receive $100 at the end of each year for the next three years A. marks if the interest rate is 8% per annum interest paid annually, what is the present value of these cash flows? b. (2 marks) 10 What is the future value in three years of the present value you computed in partla)? 11 12 c. (2 marks) 12 Assume that no withdrawals are made from the savings account until the end of the third year. What is the interest component? 14 15 d. (2 marks) Compute the effective 3 years rate (total interest over 3 years). Hint: EFFECT funcions not appropriate for the port as it is often used to compute un effective annual terest rate from a nominal mterest rate 17 18 Part 2 [6 marks 19 Your uncle as just announced that he is going to give you $15,000 per year at the end of each of the next 4 years 20 71. (2 marks! 22 if the relevant interest rate is 7%, what is the value today of this promisc? 24 D. (2 marks) 25 if the interest rate changes to 8%, what is the value taday of this promise? 26 27.(2 marks) 28 Explain how interest rates influence the value of the promise in parts la) and (b). 34 30 Part 31 mark) 31 Peter borrowed $200,000 to refit his fishing trawler. The loan recuires monthly repayments over 15 years. When he borrowed the money the interest rate was 13.9% per annum, but 18 months later the bank increased the interest rate to 15% per 33 annum, in line with market rates. The bank tells Peter he can increase his monthly repayment (59 as to pay off the Ican by the originally agreed date) or he can extend the term of loan and keep making the same monthly repayment). Calculate: 34. (4 marks 23 The new monthly repayment it Peter accepts the first option. 37.12 maiks) the extra period added to the loan term if Pelet accepts the second uption. 10 CoverPage Q1 25marks 02_40marks Q3_35marks # PU Type here to search 0 G AD ENG (16 PM 14/9/2020 AutoSave 0 H. FNCE2004 Financial Analysis Project Semester 2 2020 Excel cz hari CH File Home Insert Page Layout Formulas Data Review View Help Acrobat O Search Share Comments W X Y Z A1 X Question 1 (25 marks) : 5 QR|S|T|UV Question 1 [25 marks) Cesign 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 modelle, if there are clearheadings, dear arrangement for input vells, appropriate use of 1 colors, have some degree of flexibility, generste warning messages for wrong user inputs, etc.). 3 Part 18 marks) Suppose you receive $100 at the end of each year for the next three years A. marks if the interest rate is 8% per annum interest paid annually, what is the present value of these cash flows? b. (2 marks) 10 What is the future value in three years of the present value you computed in partla)? 11 12 c. (2 marks) 12 Assume that no withdrawals are made from the savings account until the end of the third year. What is the interest component? 14 15 d. (2 marks) Compute the effective 3 years rate (total interest over 3 years). Hint: EFFECT funcions not appropriate for the port as it is often used to compute un effective annual terest rate from a nominal mterest rate 17 18 Part 2 [6 marks 19 Your uncle as just announced that he is going to give you $15,000 per year at the end of each of the next 4 years 20 71. (2 marks! 22 if the relevant interest rate is 7%, what is the value today of this promisc? 24 D. (2 marks) 25 if the interest rate changes to 8%, what is the value taday of this promise? 26 27.(2 marks) 28 Explain how interest rates influence the value of the promise in parts la) and (b). 34 30 Part 31 mark) 31 Peter borrowed $200,000 to refit his fishing trawler. The loan recuires monthly repayments over 15 years. When he borrowed the money the interest rate was 13.9% per annum, but 18 months later the bank increased the interest rate to 15% per 33 annum, in line with market rates. The bank tells Peter he can increase his monthly repayment (59 as to pay off the Ican by the originally agreed date) or he can extend the term of loan and keep making the same monthly repayment). Calculate: 34. (4 marks 23 The new monthly repayment it Peter accepts the first option. 37.12 maiks) the extra period added to the loan term if Pelet accepts the second uption. 10 CoverPage Q1 25marks 02_40marks Q3_35marks # PU Type here to search 0 G AD ENG (16 PM 14/9/2020
