Drawdown- calculate size and length of multiple drawdown phases (non-VBA)

Hi, please see attached spreadsheet with data (col A, B & C) including expected results (col F & col G).

I have an 'equity curve' (col B) which has multiple peaks and troughs (troughs highlighted). I have calculated the drawdown % and size in col C and Col D, which by definition is the difference between a High and the following lowest Low before a new Equity high is made.

I would love some help working out formula's to calculate the following:

1) The Maximum drawdown for each particular drawdown phase (not just the Maximum drawdown for the entire data series)

2) The length (in days) of each drawdown phase ie. from first drawdown to recovery. Recovery is defined as the point where equity reaches the previous high.

I've scoured many forums and there are similar posts/solutions but I can't quite find the answers to the above.

Any help is greatly appreciated!

thanks very much



(also posted in http://www.ozgrid.com/forum/showthre...241#post737241)

Thanks very much Bernie, that's exactly what i'm looking for!

Much appreciated.

Hi Bernie, (please see attached; expected result in cell H116, your formula 1 added to Col F)

There's one more thing I'd love formula 1 to do. The data series finishes whilst still in a drawdown, therefore no "Max Draw" stat is generated for that particular draw period (C80:C116), and rightly so because by definition a new high has not been made.

However is it possible to add to the formula so that the Max Draw in the current Draw Period shows until a new Equity high has been reached? ie. -118% is produced in cell H116 or H117

Currently the stats produced are reflective of each Draw Period except for the final Draw Period.

Thanks very much

Hi, I need help with calculating the following:
1) The Maximum drawdown for each particular drawdown phase (not just the Maximum drawdown for the entire data series)
2) The length (in days) of each drawdown phase ie. from first drawdown to recovery.

An example spreadsheet would be greatly appreciated!
Thanks!

Thanks for your reply! Opened a new thread here: http://www.excelforum.com/showthread...54#post4426654

Hi Bernie, sorry for revisiting such an old post, but I was wondering if you could assist me tweaking the 2nd formula you provided very slightly.

I would actually like the answers in column I to be 1 less than they currently are, besides the final result which is correct. I have amended the spreadsheet and highlighted the changes.

I simply want to count the the number of negative periods (column C) that occur in a row, within each phase.

Is this possible and hopefully easily done?

Thanks so much in advance, any help is much appreciated!

Great, thanks very much Bernie. I will amend all the formulas except for the very final calculation. It seems to work!

Appreciate your help.

Hi Bernie,

Could you please walk me through the logic of the formulas which you described, and explain what each function does?

I am trying to understand your formula, rather than blindly copying them. I have not been able to figure them out.

Originally Posted by Bernie Deitrick

In F3, array enter (enter using Ctrl-Shift-Enter) the formula

=IF(AND(C2<0,C3=0),MIN(IF(ROW($C$2:C3)>MAX(IF($C$1:C2=0,ROW($C$1:C2))),$C$2:C3)),"-")

and in G3, array enter

=IF(AND(C2<0,C3=0),SUM(IF(ROW($C$2:C3)>MAX(IF($C$1:C2=0,ROW($C$1:C2))),1)),"-")

and copy both down to match your draws.

This was a great help, i have one more question which I will post in a new thread. Thx

Originally Posted by Bernie Deitrick

=IF(AND(C2<0,C3=0),MIN(IF(ROW($C$2:C3)>MAX(IF($C$1:C2=0,ROW($C$1:C2))),$C$2:C3)),"-")

The logic that was required was finding the minimum value in C, restricted to values after the previous zero value, returned only when the values in C go from negative to 0.

So the first part:

AND(C2<0,C3=0)

checks for a negative value in the row above, and a 0 in the current row. (Remember, the formula is written for row 3) - if that is not met, then the formula shows "-"

This part finds the row of the latest 0 value above the current 0 value:

MAX(IF($C$1:C2=0,ROW($C$1:C2)))

Let's call that XXX, so we have this

IF(ROW($C$2:C3)>XXX,$C$2:C3)

which returns an array of values from the rows > XXX, which then is used to find the MIN value

MIN(Array of Values)

You really should download the file as well, then evaluate the formula by parts.

This is really, really excellent. Thank you, Bernie, for working that out and explaining in a clear manner.

Hi, I have an observation - the length of drawdowns calculated here considers the no. of rows which are essentially trading days and not the actual calendar days. Shouldn't actual calendar days be considered? Would be helpful if you could share the formula for that. Thanks!