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):  ​$34,000

Government bond mutual fund​ (GB):  ​$130,000

Large capital mutual fund​ (LC):  ​$103,000

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

Real estate trust fund​ (RE):  ​$88,000

Rachel and Richard believe they need at least ​$1,500,000 to retire. The money market account grows at 3.5 % annually, the government bond mutual fund grows at 5.0 % annually, the large capital mutual fund grows at 10.5 % annually, the small capital mutual fund grows at 13.5 % annually, and the real estate trust fund grows at 4.5 % 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,500,000 goal?

How many years or more to reach 1,500,000?

​(Round to the nearest whole​ number.)

Answer 1

We first have to calculate the weighted average return

Weight of MM = 34,000/430,000 = 0.0791

Weight of GB = 130,000/430,000 = 0.3023

Weight of LC = 103,000/430,000 = 0.2395

Weight of SC = 75,000/430,000 = 0.1744

Weight of RE = 88,000/430,000 = 0.2047

Net Cost = 0.0791*3.5% +0.3023*5% + 0.2395*10.5% + 0.1744*13.5% + 0.2047*4.5% = 7.57865%

RRATE = 0.0757865, NPER =? , PV = 430,000 FV = 1,500,000

Where NPER is the number of years

So, we calculate N by using =nper(rate,pmt,pv,fv) in excel

N =nper(0.0757865,0,-430000,1500000) = 17.10 Years

Number of years = 17 Years (Rounded to nearest whole number)