1 2 ADVANCED PROBLEM 3-1 FOR SPREADSHEET APPLICATION 3 Future value of a portfolio. Rachel and Richard want to know when their current portfolio will be sufficient for them to retire. They have the following balances in their portfolio: Money market account: $37,000 Government bond mutual fund: $140,000 Large capital mutual fund: $107,000 Small capital mutual fund: $71,000 Real estate trust fund: $87,000 Rachel and Richard believe they need at least $2,000,000 to retire. They assume the money market account grows at 2.5% annually, the government bond mutual fund grows at 5.5% annually, the large capital mutual fund grows at 9.5% annually, the small capital mutual fund grows at 12.0% annually, and the real estate trust fund grows at 4.0% annually. Using a spreadsheet, calculate the end-of-year balance for the portfolio with the assumption that no more funds will be deposited into any of these accounts. How long will it be until they reach the $2,000,000 goal? 4 5 6 Given Data 7 8 9 10 Annual growth rate 2.5% 5.5% 9.5% 12.0% 4.0% Money market account Government bond mutual fund Large capital mutual fund Small capital mutual fund Real estate trust fund 11 12 13 14 15 Solution 14 15 Solution 16 17 Portfolio Total Money Market $37,000.00 Government Bonds $140,000.00 Large Capital $107,000.00 Small Capital $71,000.00 Real Estate $87,000.00 Year 0 1 2 18 19 20 21 3 22 23 4 5 6 24 25 7 26 8 27 9 10 28 29 11 12 30 31 13 32 33 34 35 14 15 16 17 18 19 20 36 37 38 38 20 39 40 How long will it be until Rachel and Richard reach the $2,000,000 goal to retire? years. 41 42 43 Points 44 0 Requirements 1. Start Excel. In cell D19, by using cell references, calculate the future value of the money market account after one year. Use the Excel FV function. Make absolute references to the account balance in cell D18 and the relevant growth rate from the Given Data section. 2. 1 45 Note: The output of the function you typed in this cell is expected as a positive number. In cell E19, by using cell references, calculate the future value of the government bond mutual fund account after one year. Use the Excel FV function. Make absolute references to the account balance in cell E18 and the relevant growth rate from the Given Data 3. section. 1 46 In cell F19, by using cell references, calculate the future value of the large capital mutual fund account after one year. Use the Excel FV function. Make absolute references to the account balance in cell F18 and the relevant growth rate from the Given Data section. 4. 1 47 Note: The output of the function you typed in this cell is expected as a positive number. In cell G19, by using cell references, calculate the future value of the small capital mutual fund account after one year. Use the Excel FV function. Make absolute references to the account balance in cell G18 and the relevant growth rate from the Given Data section. 5. 1 48 Note: The output of the function you typed in this cell is expected as a positive number. In cell H19, by using cell references, calculate the future value of the real estate trust fund account after one year. Use the Excel FV function. Make absolute references to the account balance in cell H18 and the relevant growth rate from the Given Data section. 6. In cell E19, by using cell references, calculate the future value of the government bond mutual fund account after one year. Use the Excel FV function. Make absolute references to the account balance in cell E18 and the relevant growth rate from the Given Data 3. section. 1 46 In cell F19, by using cell references, calculate the future value of the large capital mutual fund account after one year. Use the Excel FV function. Make absolute references to the account balance in cell F18 and the relevant growth rate from the Given Data section. 4. 1 47 Note: The output of the function you typed in this cell is expected as a positive number. In cell G19, by using cell references, calculate the future value of the small capital mutual fund account after one year. Use the Excel FV function. Make absolute references to the account balance in cell G18 and the relevant growth rate from the Given Data section. 5. 1 48 Note: The output of the function you typed in this cell is expected as a positive number. In cell H19, by using cell references, calculate the future value of the real estate trust fund account after one year. Use the Excel FV function. Make absolute references to the account balance in cell H18 and the relevant growth rate from the Given Data section. 6. 1 49 1 50 51 1 Note: The output of the function you typed in this cell is expected as a positive number. In cell range D20:H38, by using cell references, calculate the future values of each of the five accounts at the end of each year for 7. years 2 through 20. Copy the contents from cell range D19:H19 down the columns to row 38. 8. In cell 118, by using cell references, calculate the current value of the total portfolio. Use the Excel SUM function. In cell range 119:138, by using cell references, calculate the future values of the total portfolio at the end of each year for the next 20 9. years. Copy the function cell 118 down the columns to row 38. In cell H40, enter the number of years it will take for Rachel and Richard to reach their goal. Do not use the equal sign when entering 10. a numeric value. 11. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 1 52 1 53 54 0