Calculating Growth with Annual Drawdown

[SimonHughes]

I would like to work out today's value of an investment, of say £100,000 made on January 1 2010 which has grown by an annual amount of 14% (we can assume a linear growth rate) and with an annual draw down of 5% of the balance at year end.

So end of year one, I have capital of £114,000 and draw down 5% which is £5,700 leaving £108,300 to grow at 14%.

Looking for a nice formula where I can reference cells with investment value, growth rate and draw down rate (say in cells B1, B2, B3).

 

[Eric W]

If you want to do this in a table, it's pretty easy:

Book5
	A	B	C	D	E	F	G
1	Investment Value	100000		Year	Principal	Draw down	Remaining principal
2	Growth Rate	14%					100000
3	Draw Down	5%		1	114000	5700	108300
4				2	123462	6173.1	117288.9
5				3	133709.3	6685.467	127023.9
6				4	144807.2	7240.361	137566.9
7				5	156826.2	7841.311	148984.9
8				6	169842.8	8492.14	161350.7
9				7	183939.7	9196.987	174742.8
10				8	199206.7	9960.337	189246.4
11				9	215740.9	10787.05	204953.9
12				10	233647.4	11682.37	221965
Sheet7

Cell Formulas
Range	Formula
G2	G2	=B1
E3:E12	E3	=G2*(1+$B$2)
F3:F12	F3	=$B$3*E3
G3:G12	G3	=E3-F3

But if you're looking for formulas where you have a year in B4, and you automatically get the year n values without the whole table, that's possible too, but the formulas are more complicated.

 

[SimonHughes]

Thanks Eric, this table is exactly what I need. Much appreciated

 

[Dave Patton]

What value do you get in 10 years?

You can try the following

T202006a.xlsm
	A	B	C	D
1	Investment	100,000.00		$221,965.03
2	Growth	14%
3	Drawdown	5%
4	Term	10
5	Net Growth	0.083
6		Growth	Drawdown	Balance
7				100,000.00
8	1	14,000.00	5,700.00	108,300.00
7b

Cell Formulas
Range	Formula
D1	D1	=FV(B5,B4,0,-B1)
B5	B5	=(D8-D7)/D7
D7	D7	=B1
B8	B8	=D7*$B$2
C8	C8	=(D7+B8)*$B$3
D8	D8	=D7+B8-C8

 

[Eric W]

Glad we could help.

 

[joeu2004]

Or simply:

=FV((1+14%)*(1-5%)-1, 10, 0, -100000)

PS.... Missed this:

Looking for a nice formula where I can reference cells with investment value, growth rate and draw down rate (say in cells B1, B2, B3).

So the formula is:

=FV((1+B2)*(1-B3)-1, 10, 0, -B1)

Of course, 10 could be in a cell (B4), as well.

 

[Dave Patton]

edit Joeu2004 already posted the more concise version

T202006a.xlsm
	A	B	C	D
1	Investment	100,000.00
2	Growth	14%		$221,965.03
3	Drawdown	5%		$221,965.03
4	Term	10
7b

Cell Formulas
Range	Formula
D2	D2	=FV(B2-((1+B2)*B3),B4,0,-B1)
D3	D3	=FV((1+B2)*(1-B3)-1,B4,0,-B1)