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 ! in their portfolio: Money market account: $37,000 Government bond mutual fund: $140,000 Large capital mutual fund: $107.000 Small capital mutual fund: $71,000 Real estate trust fund: 587,000 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 government 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 there 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 Money market account Government bond mutual fund Large capital mutual fund Small capital mutual fund Real estate trust fund Annual growth rate 2.5% ཇར་ 9.5 12.0%

Answer 1

Solution:

Calculating end of year value with growth rate given for individual investment types. Following is the details:

For First Year Growth vale:

Investment Type	Annual growth rate	Growth value for First year
Money Market	2.50%	37,000 * 1.025
Government Bond	5.50%	140000 * 1.055
Large Capital MF	9.50%	107000 * 1.095
Small Capital MF	12.00%	71000 * 1.12
Real Estate Trust Fund	4.00%	87000 * 1.04

Likewise we will calculate subsequent year's growth value by multiplying last year value with growth rate.

Year	Money Market Account	Government Bond Mutual Fund	Large Capital Mutual Fund	Small Capital Mutual Fund	Real Estate Trust Fund	Total Return Value
0	$        37,000.00	$              140,000.00	$ 107,000.00	$     71,000.00	$ 87,000.00	$      442,000.00
1	37,925.00	147,700.00	117,165.00	79,520.00	90,480.00	$      472,790.00
2	38,873.13	155,823.50	128,295.68	89,062.40	94,099.20	$      506,153.90
3	39,844.95	164,393.79	140,483.76	99,749.89	97,863.17	$      542,335.57
4	40,841.08	173,435.45	153,829.72	111,719.87	101,777.69	$      581,603.82
5	41,862.10	182,974.40	168,443.55	125,126.26	105,848.80	$      624,255.11
6	42,908.66	193,037.99	184,445.68	140,141.41	110,082.75	$      670,616.50
7	43,981.37	203,655.08	201,968.02	156,958.38	114,486.06	$      721,048.92
8	45,080.91	214,856.11	221,154.98	175,793.39	119,065.51	$      775,950.90
9	46,207.93	226,673.20	242,164.71	196,888.59	123,828.13	$      835,762.56
10	47,363.13	239,140.22	265,170.35	220,515.22	128,781.25	$      900,970.18
11	48,547.21	252,292.94	290,361.54	246,977.05	133,932.50	$      972,111.23
12	49,760.89	266,169.05	317,945.88	276,614.30	139,289.80	$ 1,049,779.92
13	51,004.91	280,808.35	348,150.74	309,808.01	144,861.40	$ 1,134,633.40
14	52,280.03	296,252.80	381,225.06	346,984.97	150,655.85	$ 1,227,398.72
15	53,587.03	312,546.71	417,441.45	388,623.17	156,682.08	$ 1,328,880.44
16	54,926.71	329,736.78	457,098.38	435,257.95	162,949.37	$ 1,439,969.19
17	56,299.88	347,872.30	500,522.73	487,488.90	169,467.34	$ 1,561,651.15
18	57,707.37	367,005.28	548,072.39	545,987.57	176,246.04	$ 1,695,018.65
19	59,150.06	387,190.57	600,139.26	611,506.08	183,295.88	$ 1,841,281.85
20	60,628.81	408,486.05	657,152.50	684,886.81	190,627.71	$ 2,001,781.88

Using simple interest growth rate calculation we can state that by end of 20th year Rachel and Richard will achieve their desired retirement target of $2,000,000.

p.s: all investment values in table are in 2 decimal points.