Conditional Drawdown

[gauravks3]

Hi I want to calculate drawdown so that I am in a position to ascertain if the following statements are correct:

SPX
For the period 1900 – 2010, 5% corrections occurred three times per year
on average, 10% corrections occurred once per year
on average, and 20% corrections occurred once every
3.5 years on average

Is there a way to calculate that in excel presuming I have the data. Each type of Drawdown (5%, 10%, 20%) being independent. t. For
example, the market does not typically see four 5% drawdowns and one 10%
drawdown in the same year, but rather those 5% drawdowns may compound into a
single 10% drawdown for the year

 

[Rijnsent]

Hi gauravks3,
I assume you have 2 columns: date & stock value. Next to that, add a column with the year (=YEAR(...)). Next to that, the I would first calculate the drawdown for all days, e.g. in this post http://www.mrexcel.com/forum/excel-questions/702997-drawdown-calculation.html
Next, categorize the drawdowns in the next column, e.g. IF(E2<0.05;"no drawdown";IF(E2<=0.1;"5%-10% dd";"10%+ dd")) and create a pivot table to see how many of each happens every year.
Hope that is a good starting point?
Cheers,
Koen