Question

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? (MM):??$33,000

Government bond mutual fund? (GB):??$125,000

Large capital mutual fund? (LC):??$108,000

Small capital mutual fund? (SC):??$75,000

Real estate trust fund? (RE):??$84,000

Rachel and Richard believe they need at least $1,600,000 to retire. The money market account grows at 2.0% annually, the government bond mutual fund grows at 6.0% annually, the large capital mutual fund grows at 10.5%? annually, the small capital mutual fund grows at 13.0% ?annually, and the real estate trust fund grows at 5.0% annually. With the assumption that no more funds will be deposited into any of these? accounts, how long will it be until they reach the ?$1,600,000 goal?

Rachel and Richard will need to invest their accounts for _____ or more years to reach $1,600,000. (Round to the nearest whole? number.)

Answer 1

In order to calculate the number of years required to accumulate the required amount for requirement, we need to use the Goal Seek function in Excel. Below are the steps mentioned:

Step 1: Record all the above given values and rates of return on Excel sheet. The basic relation between FV and PV is FV = PV * (1 + r)ⁿ. Here, we need to calculate n.

For now, assume a random value of n (say 10) and calculate FV for each of the investments:

Step 2: Now, we need to apply goal seek function. (Remember to have all the values in FV table linked by the formula and Number of Years). Go to the 'Data' tab option and select drop-down 'What-If Analysis', then choose 'Goal Seek' Function. This would open up a small dialog box.

Step 3: Once set, press OK. It will calculate the value of N (yellow cell), such that total retirement fund value is $1600000. Answer = 16 Years