Question

How to do this question through excel?

An employee plans to invest $8,000 per year in a retirement fund at the beginning of each of the next 10 years. The employee believes she will earn 14% on her investments in each of the next 7 years and 9% in each of the last 3 years before she retires.

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

Answer 1

Accumulated money in retirement fund $ 155,319.26

Step-1:Future value of investment at the end of 7 years
Future value	=	Annual investment	*	Future value of annuity of 1
	=	$             8,000	*	12.2327602
	=	$     97,862.08
Working;
Future value of annuity of 1	=	((((1+i)^n)-1)/i)*(1+i)			Where,
	=	((((1+0.14)^7)-1)/0.14)*(1+0.14)			i	=	14%
	=	12.23276016			n	=	7
Step-2:Future value of above amount at the end of 10 years
Future value	=	P*(1+i)^n			Where,
	=	97862.08*(1+0.09)^3			P	=	$ 97,862.08
	=	$ 1,26,734.23			i	=	9%
					n	=	3
Note:
Suppose that there is no further investment after year 7.
Step-3:Calculation of future value of annual investment of last 3 years
Future value	=	Annual investment	*	Future value of annuity of 1
	=	$             8,000	*	3.573129
	=	$     28,585.03
Working;
Future value of annuity of 1	=	((((1+i)^n)-1)/i)*(1+i)			Where,
	=	((((1+0.09)^3)-1)/0.09)*(1+0.09)			i	=	9%
	=	3.573129			n	=	3
Step-4:Calculation of accumulated money in retirement fund at the end of 10th year
Accumulated money in retirement fund	=	Value of first 7 years deposit in 10th year	+	Value of last 3 years deposit in 10th year
	=	$ 1,26,734.23	+	$ 28,585.03
	=	$ 1,55,319.26