CAN YOU PLEASE ATTACH EXCEL AND FORMULAS USED AS SEEN IN INSTRUCTIONS

A D D E H K L M N 0 G 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 goverment 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? 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 Solution Year 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 15 16 17 18 19 20 21 22 23 24 25 26 27 28 0 1 2 3 4 5 6 7 29 30 31 32 8 9 10 11 12 13 14 15 16 17 18 19 20 33 34 35 36 37 38 39 40 41 How long will it be until Rachel and Richard reach the $2,000,000 goal to retire? 22.56456 Jycars. 41 42 43 44 Points 0 1 45 Requirements 1. Start Excel. In cell 019, 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. 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 acxount 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 section. 3. Note: The output of the function you typed in this cell is expected as a positive number. 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. Note: The output of the function you typed in this cell is expected as a positive number. In cell C19, 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 1 1 46 1 47 5. 1 49 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 sextion. 6. 1 1 49 1 SO 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 years 2 through 7. 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 cach year for the next 20 years. Copy 9. 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 cqual sign when entering a numeric 10. . 11. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 1 52 1 0 53 54 55 56 57 58