Question

You have $4,311.89 in a brokerage account, and you plan to deposit an additional $5,000 at the end of every future year until your account totals $210,000. You expect to earn 12% annually on the account. How many years will it take to reach your goal?

Answer 1

Goal Seek settings

Formula sheet

A	B	C	D	E	F	G	H	I
2
3		Interest rate	0.12
4		Future Value required	210000
5
6		Let number of year the account will take to reach the target future value is n years.
7		Cash flow diagram will be as follows:
8		Year	0	1	2	…
9		Cash flow to account	4311.89	5000	5000	5000
10
11		Future value of deposit should be equal to the required FV.
12
13		Future value of deposit at the end of nth year	=$4,311.89*(F/P,12%,n)+$5,000*(F/A,12%,n)
14
15
16		Since Future value of deposit at the end of nth year should be equal to the future value required,
17		$210,000=$4,311.89*(F/P,12%,n)+$5,000*(F/A,12%,n)
18
19		This equation can be solved using goal seek of excel as follows:
20
21		n=	15.0000004637642
22
23		Future Value	=$4,311.89*(F/P,12%,n)+$5,000*(F/A,12%,n)
24			=D9*((1+D3)^D21)+E9*FV(D3,D21,-1,0)	=D9*((1+D3)^D21)+E9*FV(D3,D21,-1,0)
25
26		Hence Number of years in which account will reach the goal is	=D21	Years
27