Problem 3 - What interest rate should an account offer to help fund a new caf? Roger and Liz have plans to build a caf on site at The Sullivan Habitat in six years' time. They expect the cost of building to be $516,000, and the fittings (tables, chairs, fridges and so on) to cost $55,000 Their consultant at the bank has advised that the maximum Roger and Liz will be permitted to borrow is $370,000, meaning that in order to build the caf, Roger and Liz will need to have saved the remaining amount required. Liz therefore decides to set up a bank account as a sinking fund. Roger and Liz plan to make payments of $2100 per month into the account, which offers 6.9% per annum, compounded monthly In what follows, place all Excel working out in the same worksheet and provide a copy of this worksheet in this appendix. Individual instructions are included below, for each part. (a) (6 marks) Calculate the size of the deposit that Roger and Liz need to save. Using Excel, implement the future value of an annuity formula to determine the balance of their sinking fund at the given interest rate of 6.9% per annum after six years if the monthly payments are $2100. By how much will Roger and Liz fall short of their goal? Provide a copy of the Excel spreadsheet in this appendix. Excel Instructions: Prepare an Excel spreadsheet like the one shown below (not all values shown are correct - you will have to insert correct ones). Cell B6 in the figure below contains the formula for S, the accumulated value. Make sure the row names include your initials. 1 Sinking Fund (a) 2 3 AA monthly payment (R) 4 AA periodic interest in 5 AA number periods (n) 6 AA accumulated amount (5) 7 1200 0.005 48 64654.79 (b) (5 marks) Using Excel, implement the sinking fund formula to compute the size of the monthly payments that Roger and Liz should make in order to save the required deposit amount in six years. Show your working out in the requested spreadsheet in this appendix. Excel Instructions: In the same Excel spreadsheet you constructed for part (a), include calculations as shown below (you will have to insert correct values). Cell B14 contains the formula for R, the regular monthly payment. Make sure the row names include your initials. 9 Sinking Fund (b) 10 11 AA accumulated interest (5) 12 AA periodic interest (0) 13 AA number periods (n) 14 AA monthly payment (R) 75000 0.005 48 1392.01 (C) (4 marks) Roger and Liz would prefer to use the original payment amount of $2100 and decide to shop around at other financial institutions for an interest rate that would enable the desired amount to be achieved. Using the Excel Tool Goal Seek (instructions below), determine the appropriate annual interest rate (to four significant figures) for an investment account offering monthly compounding with the monthly payment of $2100 used in part (a). Excel Instructions: Copy the part of your Excel spreadsheet that you used for part (a) to a new section of the spreadsheet and label it (c). Use Goal Seek to find the required monthly interest rate. In Excel 2007 and 2010, Goal Seek can be found by following: Data Data Tools What If Analysis. In Excel 2003 it is simply Tools Goal Seek. Some versions of Excel on the Mac implement Goal Seek and some do not. Check the Excel booklet (with the orange cover) or ask elsewhere the course coordinator, the forums, the internet). Note that cell B20 must contain a value (for the periodic interest rate), not a formula. Make sure that your labels include your initials. Include a copy of your spreadsheet after calculation using Goal Seek. Coal Seek Set cell: 58522 16 17 Sinking Fund (c) 18 19 AA monthly payment (R) 20 M periodic interest () 21 A number periods (n) 22 M accumulated amount (5) 23 24 25 1200 0.005 48 64917.40 To value 75000 by changing cell 55520 13 Cancel OK 26