In five years' time, George and Jane would like to build a small caf on site...

Transcribed Image Text:

In five years' time, George and Jane would like to build a small caf on site at The Treetop Retreat. They expect the cost of the building to be $275,000, and the fittings (tables, chairs, fridges and so on) to cost an additional $30,000. Their consultant at the bank has advised that the maximum George and Jane will be permitted to borrow is $210,000. To save the remaining amount needed to fund the caf, Jane has set up a bank account that earns interest at 5.8% per year compounded monthly as a sinking fund. George and Jane plan to make payments of $1200 per month into that account. In what follows, place all EXCEL working out in the same worksheet and provide a screenshot of this worksheet in this appendix. Additional EXCEL instructions for each part are given below. (a) (4 marks) Use EXCEL to calculate the balance in their sinking fund at the given interest rate of 5.8% per annum after five years if the monthly payments are $1200. By how much will George and Jane fall short of the amount they need at the end of five years, in addition to the amount they can borrow from the bank? 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. A B 1 Sinking Fund (a) 2 3 AA monthly payment (R) 1200 4 AA periodic interest (/) 0.005 5 AA number periods (n) 48 6 AA accumulated amount (S) 64654.79 7 C (b) (4 marks) Use EXCEL to compute the size of the monthly deposits that George and Jane should make into their account, in order to have the required amount in five years. 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 deposit. Make sure the row names include your initials. 9 Sinking Fund (b) 10 11 AA accumulated interest (S) 75000 12 AA periodic interest (i) 0.005 13 AA number periods (n) 48 14 AA monthly payment (R) 1392.01 (c) (4 marks) George and Jane would prefer to use the original deposit amount of $1200. They decide to shop around at other financial institutions for an interest rate that would allow them to accumulate the desired sum in five years with their preferred deposit amount. Use the EXCEL tool GoalSeek to determine the appropriate APR with monthly compounding. 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 GoalSeek to find the required monthly interest rate and then convert it to an APR by multiplying by 12. Make sure that your labels include your initials. Include a screenshot of your spreadsheet after calculation using GoalSeek. A 16 17 Sinking Fund (c) 18 19 AA monthly payment (R) 20 AA periodic interest (i) 1200 0.005 Set cell: NNNNNN 23 24 25 26 123456 21 AA number periods (n) 48 To value: B D E F Goal Seek $B$22 75000 22 AA accumulated amount (S) 64917.40 By changing cell: SBS20 Cancel OK In five years' time, George and Jane would like to build a small caf on site at The Treetop Retreat. They expect the cost of the building to be $275,000, and the fittings (tables, chairs, fridges and so on) to cost an additional $30,000. Their consultant at the bank has advised that the maximum George and Jane will be permitted to borrow is $210,000. To save the remaining amount needed to fund the caf, Jane has set up a bank account that earns interest at 5.8% per year compounded monthly as a sinking fund. George and Jane plan to make payments of $1200 per month into that account. In what follows, place all EXCEL working out in the same worksheet and provide a screenshot of this worksheet in this appendix. Additional EXCEL instructions for each part are given below. (a) (4 marks) Use EXCEL to calculate the balance in their sinking fund at the given interest rate of 5.8% per annum after five years if the monthly payments are $1200. By how much will George and Jane fall short of the amount they need at the end of five years, in addition to the amount they can borrow from the bank? 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. A B 1 Sinking Fund (a) 2 3 AA monthly payment (R) 1200 4 AA periodic interest (/) 0.005 5 AA number periods (n) 48 6 AA accumulated amount (S) 64654.79 7 C (b) (4 marks) Use EXCEL to compute the size of the monthly deposits that George and Jane should make into their account, in order to have the required amount in five years. 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 deposit. Make sure the row names include your initials. 9 Sinking Fund (b) 10 11 AA accumulated interest (S) 75000 12 AA periodic interest (i) 0.005 13 AA number periods (n) 48 14 AA monthly payment (R) 1392.01 (c) (4 marks) George and Jane would prefer to use the original deposit amount of $1200. They decide to shop around at other financial institutions for an interest rate that would allow them to accumulate the desired sum in five years with their preferred deposit amount. Use the EXCEL tool GoalSeek to determine the appropriate APR with monthly compounding. 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 GoalSeek to find the required monthly interest rate and then convert it to an APR by multiplying by 12. Make sure that your labels include your initials. Include a screenshot of your spreadsheet after calculation using GoalSeek. A 16 17 Sinking Fund (c) 18 19 AA monthly payment (R) 20 AA periodic interest (i) 1200 0.005 Set cell: NNNNNN 23 24 25 26 123456 21 AA number periods (n) 48 To value: B D E F Goal Seek $B$22 75000 22 AA accumulated amount (S) 64917.40 By changing cell: SBS20 Cancel OK