ADVANCED PROBLEM 4-1 FOR SPREADSHEET APPLICATION Future value with an annuity. 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 have 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 Thelma 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, $5,000 to the large capital mutual fund, $6,000 to the small 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 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 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, $5,000 to the large capital mutual fund, $6,000 to the small 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 account?

Given Data Annual growth

money market account 2.5%

Government bond mutual fund 5.5%

Large capital mutual fund 9.5%

Small capital mutual fund 12.0%

Real estate trust fund 4.0%

Can you please provide excel work Thanks.