Drawdowns and # of periods of DD.

[crazydragon84]

Hi,

I'm trying to show historical drawn down (not only the max draw down but each time there is a draw down) of return series, and for each of the drawn down to capture # of periods from Peak to Trough and # or periods to recovery.

Draw Down is defined as % decline from previous peak. in the example below, first draw down occured on 1/31/2008 because the total value (growth of $1) dropped from $1 as of 12/31/2007 to $0.93 on 1/31/2008 because of the -7.31% return on 1/31/2008. This drawdown was fully recovered in 2/29/2008 because 8.58% return in February caused the total value to become 1.01, which exceeds the previous peak of $1 (and now 1.01 is the new peak). In this scenario, the number of periods from peak to trough is 1 (from december to january) and number of recovery is 1 (January to February).

Similarly, in the second case, the draw down is -12.01% on 8/31/2008. the number of period from peak to trough is 3 ( May to August 2008) and the number of period to recovery is 7 ( 8/31/2008 to 3/31/2009).

After I capture all of the drawdowns, number of periods from peak to trough, and number of period until recovery, I want to rank each of the drawdown and show them in order (largest draw down, second largest drawn down, third...etc.). I've done it in the past so I know its possible, but I can't remember how I did it. Is there a way to accomplish this without adding like 5 additional columns to calculate it?
[TABLE="width: 375"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]

Date[/TD]
[TD]Return[/TD]
[TD] Growth of $1 [/TD]
[TD]Draw down[/TD]
[/TR]
[TR]
[TD]12/31/2007[/TD]
[TD] [/TD]
[TD] 1.00[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]1/31/2008[/TD]
[TD]-7.31%[/TD]
[TD] 0.93[/TD]
[TD]-7.31%[/TD]
[/TR]
[TR]
[TD]2/29/2008[/TD]
[TD]8.58%[/TD]
[TD] 1.01[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]3/31/2008[/TD]
[TD]5.93%[/TD]
[TD] 1.07[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]4/30/2008[/TD]
[TD]9.01%[/TD]
[TD] 1.16[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]5/31/2008[/TD]
[TD]2.29%[/TD]
[TD] 1.19[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]6/30/2008[/TD]
[TD]-9.72%[/TD]
[TD] 1.07[/TD]
[TD]-9.72%[/TD]
[/TR]
[TR]
[TD]7/31/2008[/TD]
[TD]5.98%[/TD]
[TD] 1.14[/TD]
[TD]-4.33%[/TD]
[/TR]
[TR]
[TD]8/31/2008[/TD]
[TD]-8.03%[/TD]
[TD] 1.05[/TD]
[TD]-12.01%[/TD]
[/TR]
[TR]
[TD]9/30/2008[/TD]
[TD]1.28%[/TD]
[TD] 1.06[/TD]
[TD]-10.88%[/TD]
[/TR]
[TR]
[TD]10/31/2008[/TD]
[TD]-0.47%[/TD]
[TD] 1.05[/TD]
[TD]-11.29%[/TD]
[/TR]
[TR]
[TD]11/30/2008[/TD]
[TD]3.84%[/TD]
[TD] 1.10[/TD]
[TD]-7.88%[/TD]
[/TR]
[TR]
[TD]12/31/2008[/TD]
[TD]-0.23%[/TD]
[TD] 1.09[/TD]
[TD]-8.10%[/TD]
[/TR]
[TR]
[TD]1/31/2009[/TD]
[TD]-4.25%[/TD]
[TD] 1.05[/TD]
[TD]-12.00%[/TD]
[/TR]
[TR]
[TD]2/28/2009[/TD]
[TD]8.35%[/TD]
[TD] 1.13[/TD]
[TD]-4.66%[/TD]
[/TR]
[TR]
[TD]3/31/2009[/TD]
[TD]4.93%[/TD]
[TD] 1.19[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]4/30/2009[/TD]
[TD]-3.90%[/TD]
[TD] 1.14[/TD]
[TD]-3.90%[/TD]
[/TR]
[TR]
[TD]5/31/2009[/TD]
[TD]3.06%[/TD]
[TD] 1.18[/TD]
[TD]-0.96%[/TD]
[/TR]
[TR]
[TD]6/30/2009[/TD]
[TD]-8.69%[/TD]
[TD] 1.08[/TD]
[TD]-9.56%[/TD]
[/TR]
[TR]
[TD]7/31/2009[/TD]
[TD]-1.51%[/TD]
[TD] 1.06[/TD]
[TD]-10.93%[/TD]
[/TR]
[TR]
[TD]8/31/2009[/TD]
[TD]9.92%[/TD]
[TD] 1.16[/TD]
[TD]-2.09%[/TD]
[/TR]
[TR]
[TD]9/30/2009[/TD]
[TD]-2.47%[/TD]
[TD] 1.14[/TD]
[TD]-4.51%[/TD]
[/TR]
[TR]
[TD]10/31/2009[/TD]
[TD]6.79%[/TD]
[TD] 1.21[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]11/30/2009[/TD]
[TD]0.63%[/TD]
[TD] 1.22[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]12/31/2009[/TD]
[TD]-1.37%[/TD]
[TD] 1.20[/TD]
[TD]-1.37%[/TD]
[/TR]
[TR]
[TD]1/31/2010[/TD]
[TD]4.95%[/TD]
[TD] 1.26[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]2/28/2010[/TD]
[TD]-4.06%[/TD]
[TD] 1.21[/TD]
[TD]-4.06%[/TD]
[/TR]
[TR]
[TD]3/31/2010[/TD]
[TD]6.09%[/TD]
[TD] 1.29[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]4/30/2010[/TD]
[TD]-1.09%[/TD]
[TD] 1.27[/TD]
[TD]-1.09%[/TD]
[/TR]
[TR]
[TD]5/31/2010[/TD]
[TD]-2.08%[/TD]
[TD] 1.25[/TD]
[TD]-3.15%[/TD]
[/TR]
[TR]
[TD]6/30/2010[/TD]
[TD]7.27%[/TD]
[TD] 1.34[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]7/31/2010[/TD]
[TD]-9.33%[/TD]
[TD] 1.21[/TD]
[TD]-9.33%[/TD]
[/TR]
[TR]
[TD]8/31/2010[/TD]
[TD]1.31%[/TD]
[TD] 1.23[/TD]
[TD]-8.14%[/TD]
[/TR]
[TR]
[TD]9/30/2010[/TD]
[TD]7.43%[/TD]
[TD] 1.32[/TD]
[TD]-1.31%[/TD]
[/TR]
[TR]
[TD]10/31/2010[/TD]
[TD]6.91%[/TD]
[TD] 1.41[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]11/30/2010[/TD]
[TD]1.34%[/TD]
[TD] 1.43[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]12/31/2010[/TD]
[TD]6.20%[/TD]
[TD] 1.52[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]1/31/2011[/TD]
[TD]-5.40%[/TD]
[TD] 1.44[/TD]
[TD]-5.40%[/TD]
[/TR]
[TR]
[TD]2/28/2011[/TD]
[TD]-1.06%[/TD]
[TD] 1.42[/TD]
[TD]-6.40%[/TD]
[/TR]
[TR]
[TD]3/31/2011[/TD]
[TD]-3.86%[/TD]
[TD] 1.36[/TD]
[TD]-10.01%[/TD]
[/TR]
[TR]
[TD]4/30/2011[/TD]
[TD]2.91%[/TD]
[TD] 1.40[/TD]
[TD]-7.40%[/TD]
[/TR]
[TR]
[TD]5/31/2011[/TD]
[TD]9.95%[/TD]
[TD] 1.54[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]6/30/2011[/TD]
[TD]-3.15%[/TD]
[TD] 1.50[/TD]
[TD]-3.15%[/TD]
[/TR]
[TR]
[TD]7/31/2011[/TD]
[TD]-0.69%[/TD]
[TD] 1.49[/TD]
[TD]-3.82%[/TD]
[/TR]
</tbody>[/TABLE]