simulate the growth of my invested capital given growth rate and capital gain tax

[6diegodiego9]

I would like to simulate the growth of my capital invested in a stock.

Suppose these conditions:
- initial capital of 100.000$ (cell A1 = 100.000$)
- growth rate of +100%/month (cell A2 = 100%) (I know that it's too high to be realistic, it's just to make the numbers of my example simple to understand)
- capital gain tax 26% charged every month (cell A3 = 26%)

Manual calculations:
month 0: 100.000
month 1: 200.000 - ((200.000-100.000)*26%) = 174.000
month 2: 348.000 - ((348.000-174.000)*26%) = 302.760
month 3: 696.000 - ((696.000-348.000)*26%) = 605.520


Is it possible to realize a formula that given the numbers of monthes, returns the value at that month? (eg. given 1 returns 174.000 and given 3 returns 605.520)

 

[Dave Patton]

Excel 2010
	A	B
5	1	$174,000.00
6	2	$302,760.00
7	3	$526,802.40
1b

Cell Formulas
Range	Formula
B5	=FV($A$2-$A$3,A5,0,-$A$1)
B6	=FV($A$2-$A$3,A6,0,-$A$1)
B7	=FV($A$2-$A$3,A7,0,-$A$1)

 

[VBA Geek]

are you sure your 3rd month calc is correct?

Excel 2010
	A	B
1	$ 100,000.00
2	100%
3	26%
4	3	$ 526,802.40
Sheet1

Cell Formulas
Range	Formula
B4	=(1+($A$2*(1-$A$3)))^A4*$A$1

 

[6diegodiego9]

Ah, stupid me and thanks guys!

Correct manual calculations:
month 0: 100.000
month 1: 200.000 - ((200.000-100.000)*26%) = 174.000
month 2: 348.000 - ((348.000-174.000)*26%) = 302.760
month 3: 605.520 - ((605.520-302.760)*26%) = 526.802,4

Thanks guys! you're geniuses!