Question

9. An employee plans to invest $40,000 per year in a retirement fund at the beginning of each of the next 12 years. The employee believes she will earn 12% on her investments in each of the first 4 years, 9% in each of the next 4 years, and 6% in each of the final 4 years before she retires.

a. How much money will the employee have in the retirement fund when she retires?

b. What would be the internal rate of return associated with her investments and her final retirement position?

c. Using Solver or Goal Seek determine the annual investment amount that would result in a retirement fund of $1,000,000 at the end of 12 years?

Answer 1

	1	2	3	4	5	6	7	8	9	10	11	12
Opening	40,000	84,800	134,976	191,173	254,114	316,984	385,513	460,209	541,628	614,125	690,973	772,431
Returns	4,800	10,176	16,197	22,941	22,870	28,529	34,696	41,419	32,498	36,848	41,458	46,346
Closing	44,800	94,976	151,173	214,114	276,984	345,513	420,209	501,628	574,125	650,973	732,431	818,777
Return %	12%	12%	12%	12%	9%	9%	9%	9%	6%	6%	6%	6%

Notes: 40,000 is added to opening balance every year. Returns = Return % * Opening balance. Closing balance = Opening balance + Returns

a) As seen in table above, the employee will have $818,777 in the fund on retirement.

b) Cash flows are: -40,000, -40,000, -40,000, -40,000, -40,000, -40,000, -40,000, -40,000, -40,000, -40,000, -40,000, -40,000, +818,777

This results in an IRR of 8.0%.

c) Using goal seek in excel, the annual investment amount required for a retirement fund balance of $1M in 12 years is $48,853.35