Fufure value with an annuib. Thelma and Thomas want to retire with $2,000,000 in their portfolio. They plan on putting away $20,000 each year into five different accounts ($4,000 per account). They bave set up the following accounts and will make all their contributions at the end of each year. a. Using a spreadsheet, calculate the end-of-year balance for the portfolio with the assumption that Theima and Thomas will not deposit any additional funds into any of these accounts above the $4,000 annual contribution to each account. How long will it be until they reach their $2,000,000 retirement goal? b. Thelma and Thomas decide that making equal contributions to the funds is not to their advantage. They decide on annual contributions of $2,000 to the money market account, $4,000 to the government bond mutual fund, $,000 to the large capital mutual fund, $6,000 to the small capital mutual fund, and $3,000 to the real estate trus fund. How much sooner will they reach their $2,000,000 goal compared to the strategy in part (a)? If they retire at the same time as in part (a), how much more will they have in their retirement account? a. Using a spreadsheet, calculate the end-of-year balance for the portfolio with the assumption that Thelma and Thomas will not deposit any additional funds into any of these accounts above the $4,000 annual contribution to each sccount. How long will it be until they reach their $2,000,000 retirement goah? How long will it be until Theima and Thomas reach their $2,000,000 retirement goal? years. b. Thelma and Themas decide that making equal contributions to the funds is not to their advantage. They decide on annual contributions of $2,000 to the money market account, $4,000 to the government bond mutual fund, $5,000 to the large capital mutual fund, $6,000 to the amall capital mutual fund, and $3,000 to the real estate trust fund. How much sooner will they reach their $2,000,000 goal compared to the strategy in part (a)? If they retire at the same time as in part (a), how much more will they have in their retirement accoun? 1. Start Fxcel. In eell D22, by using cell references, calculate the future value of the moncy market account at the end of year 2 . Use the Excel FV function and assume that all savings are made at the end of the period. Use absolute references to the anneal saving in ecll D21 and the 2. relevant cell from the Glvea Data section. Nete: The output of the function you typed in this cell is expected as a positive number. In cell E.22, by using cell references, calculase the future value of the government bond mutual fusd at the end of year 2 . Use the Excel FV function and assume that all savings are made at the end of the period. Use absolute references to the annual saving in cell 3. 221 and the relevant cell from the Given Data section. Note: The output of the function you typed in this coll is expected as a positive number. In cell F22, by using cell references, ealculate the future value of the large capital mutal fund at the ead of year 2 . Use the Excel rV function and assume that all savings are made at the end of the period. Use absolute references to the annual saving in cell F21 and the 4. relevant cell from the Given Data section. Nete: The output of the function you typed in this cell is expected as a positive number. In cell G22, by using cell references, calculate the future value of the small capital mutaal fund at the end of year 2 . Use the Excel FV function and assume that all savings are made at the end of the period. Use absolute references to the annual saving in cell G21 and the 5. relevant cell from the Given Data section. Note: The output of the function you typed in this cell is expected as a positive number. In cell H22, by using cell references, calculate the future value of the real estate trust fund at the end of year 2 . Use the Excel FV function and assame that all savings are made at the end of the period. Use absolute references to the annoal saving in cell H21 and the 6. relevant cell from the Given Data section. Nete: The output of the function you typed in this cell is expected as a positive number 7. In cell range D23:HS0, by using cell references, calculate the future values of each of the five accounts at the end of each year for years 3 through 30. Copy the contents from cell range D22:H22 down the columns to row 50. 8. In cell 121, by using cell references, calculate the current value of the total portfolio at the end of year 1 . Use the Excel SUM function. 9. In cell rasge 122:150, by using cell referesces, calculate the futare values of the total portfolio at the end of each year for years 2 through 30 . Copy the function cell 118 down the columas to row 50. 10. In cell HS2, enter the nember of years it will take for Thelma and Thomas to reach their goal. Do not use the equal sige when enteriag a numeric value. In cell D59, by using cell references, calculate the futare value of the moncy market account at the end of year 2 . Use the Excel FV function and assume that all savings are made at the end of the period. Use absolute references to the anaual saving in cell D58 and the 11. relevant cell from the Gives Data section. Note: The output of the function you typed in this cell is expected as a positive number. In cell F59, by usine cell references, calculate the future value of the government bead mutual fusd at the end of year 2 . Use the Excel FV functios and assume that all avings are made at the end of the period. Use absolate references to the annual saving in cell 12. 1.58 and the relevant cell from the Given Data soction. 1 Note: The output of the fubction you typed in this cell is expected as a positive eumber In cell F59, by uting celi referescek, calculate the foture value of the large capital mutual fund at the end of year 2 . Use the Excel FV Note: The output of the function you typed in this cell is expected as a positive number. In cell F59, by using cell references, calculate the future value of the government bond mutual fund at the end of year 2 . Use the Excel FV function and assume that all savings are made at the end of the period. Use absolute feferences to the annual saving in cell 12. ES8 and the relevant cell from the Given Data scetion. Note: The output of the function you typed in this cell is expected as a positive number. In cell F59, by using eell references, calculate the future value of the large capital nutual fund at the end of year 2, Use the Excel FV function and assume that all savings are made at the end of the period. Use absolute references to the annual saving in cell F58 and the 13. relevant cell from the Given Data section. Note: The output of the function you typed in this cell is expected as a positive number. In cell G59, by using cell references, calculate the future value of the small capital mutual fund at the end of year 2 . Use the Excel FV function and assume that all savings are made at the end of the period. Use absolute references to the annual saving in cell G58 and the 14. relevant cell from the Given Data section. Note: The output of the function you typed in this cell is cxpectod as a positive number. In cell H59, by using cell references, calculate the future value of the real estate trust fund at the end of year 2 . Use the Exeel FV function and asume that all savings are made at the end of the period. Use absolute references to the annual saving in cell H58 and the 15. relevant cell from the Given Data section. Note: The output of the function you typed in this cell is expected as a positive number. 16. In cell rasge D60:1187, by using cell references, calculate the foture values of each of the five accounts at the ead of each year for years 3 through 30. Copy the contents from cell range D59:H 59 down the columns to row 50. 17. In cell 158, by using cell references, calculate the current value of the total portfolio at the end of year 1 . Use the Excel SUM function. 18. In cell range 159:187, by using cell references, calculate the future values of the total portfolio at the end of each year for years 2 through 30 . Copy the function cell 158 down the columns to row 87. 19. In cell 1889 , caler the number of ycars carlier Thelma and Thomas will reach their goal in part (b) than in (a). Do not use the equal sign when entering a numeric value. In cell H91, by using cetl references, calculate the difference between the accounts in parts (a) and (b) if Thelma and Thomas retire at 20. the same time as in part (a). Notet The oulput of the expression you typed in this cell is expected as a positive number. 21. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed