Drawdown formula problem

[MonsterMan]

Hi folks, looking for a little help again. I am trying to do a small ‘drawdown’ sheet for my trading account. I use a 3 to 1 risk ratio. I am willing to risk ‘1’ (2% of my account) to make ’3’ (6% gain).

Each time I lose a trade, I lose 2% of my account, so 4 consecutive losing trade, means I have lost 8% in total, then the next trade is a win. If the trade is a win, the ‘Risk to Return Ratio’ will be 3; if I lose, the ‘Risk to Return Ratio’ will be 1.

Is there a formula that will add to a losing trade, concurrently in the ‘drawdown’ Colum until there is a win trade? Once the next trade is a winner, the drawdown will revert to 0%, until the next losing trade. Sure, hope that make sense.

I have uploaded a pic to show how it should look, on the drawdown colum. https://imgur.com/BucWal7

 

[sheetspread]

This worked on your sample and can be altered for additional factors:

Excel 2010
	B	C	D	E
4	Win/Loss	Risk to return ratio	Return	Drawdown
5	W	3	6%	0%
6	W	3	6%	0%
7	L	1	-2%	-2%
8	L	1	-2%	-4%
9	L	1	-2%	-6%
10	L	1	-2%	-8%
11	W	3	6%	0%
12	L	1	-2%	-2%
13	W	3	6%	0%
14	W	3	6%	0%
15	L	1	-2%	-2%
16	L	1	-2%	-4%
17	L	1	-2%	-6%
18	W	3	6%	0%
19	L	1	-2%	-2%
20	W	3	6%	0%
Sheet7 (3)

Cell Formulas
Range	Formula
E5	=MIN(D5,0)*(ROW()-LOOKUP(2,1/($E$4:E4>=0),ROW($E$4:E4)))

 

[MonsterMan]

Hi, thanks that worked perfect!, just how I wanted it.

 

[sheetspread]

Also works on this particular sample:

Excel 2010
	B	C	D	E
4	Win/Loss	Risk to return ratio	Return	Drawdown
5	W	3	6%	0%
6	W	3	6%	0%
7	L	1	-2%	-2%
8	L	1	-2%	-4%
9	L	1	-2%	-6%
10	L	1	-2%	-8%
11	W	3	6%	0%
12	L	1	-2%	-2%
13	W	3	6%	0%
14	W	3	6%	0%
15	L	1	-2%	-2%
16	L	1	-2%	-4%
17	L	1	-2%	-6%
18	W	3	6%	0%
19	L	1	-2%	-2%
20	W	3	6%	0%
Sheet7 (3)

Cell Formulas
Range	Formula
E5	=IF(D5>=0,0,E4-0.02)

 

[MonsterMan]

Spot on with smaller and cleaner code, thankyou!!!