Drawdown and High watermark with stop loss

Hello,

With normal stock data starting in row 3 for column B and dates in column A. When the drawdown is less or equal to -10% I want it to reset so that it calculates drawdown from this new point. For example if row 30 is the first time it drops to -10% this would be the new starting point to calculate drawdown from instead of B3.

Any ideas?

Thanks!

Please refer to below comments to see what I mean and what I am looking for:


"Based on research from Capital Research and Management Company corrections occur more frequently than you may realize. 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. Smart, experienced investors expect corrections and know ahead of time how they will react when they occur."


OR


"Of the 44 previous declines of at least 10%, 19 became bear markets (20%+ declines). "Since 1878, any bull market lasting 4 years or more, has turned into a bear market only when the business cycle ends as best evidenced by an inverted long-term (30Y vs 10Y) yield curve—even the 1987 decline (really 33% in a compressed timeframe) saw an inversion of the yield curve in 1986. So unless the U.S. has already slipped into recession, this decline will prove to be a correction. "


Read more: http://www.businessinsider.com/facts-about-10-corrections-in-sp-500-2015-8#ixzz3kCJy4HfM"




How can we use excel to figure out such frequencies... Will really appreciate if anybody can help...


regards,
Gaurav

Again asking the same question and tried to make it simpler...

Can SOMEBODY PLEASE HELPPPPPP

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