second worst drawdown

[quej2003]

Hi All,

So I have a series of returns and am happy calculating the maximum drawdown from them, however am unsure as to how to go about calculating the second maximum drawdown, as illustration some numbers:


------------------Returns----------- Price----- Drawdown
<table style="width: 358px; height: 273px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:1938;width:40pt" width="53"> <col style="width:48pt" width="64"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt; width:40pt" align="right" height="20" width="53">Apr-04</td> <td style="vertical-align: top;">
</td><td class="xl67" style="width:48pt" width="64">-3.53%</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl68" style="width:48pt" width="64">96.47</td> <td class="xl73" style="width:55pt" width="73">-3.53%</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl71" style="height:15.0pt" align="right" height="20">May-04</td> <td style="vertical-align: top;">
</td><td class="xl69">-1.00%</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl66">95.51</td> <td class="xl74">-4.49%</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl71" style="height:15.0pt" align="right" height="20">Jun-04</td> <td style="vertical-align: top;">
</td><td class="xl69">-2.32%</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl66">93.29</td> <td class="xl74">-6.71%</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl71" style="height:15.0pt" align="right" height="20">Jul-04
</td> <td style="vertical-align: top;">
</td><td class="xl69">0.28%</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl66">93.55</td> <td class="xl74">-6.45%</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl71" style="height:15.0pt" align="right" height="20">Aug-04
</td> <td style="vertical-align: top;">
</td><td class="xl69">0.41%</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl66">93.93</td> <td class="xl74">-6.07%</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl71" style="height:15.0pt" align="right" height="20">Sep-04</td> <td style="vertical-align: top;">
</td><td class="xl69">3.98%</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl66">97.67</td> <td class="xl74">-2.33%</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl71" style="height:15.0pt" align="right" height="20">Oct-04</td> <td style="vertical-align: top;">
</td><td class="xl69">3.25%</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl66">100.00</td> <td class="xl74">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl71" style="height:15.0pt" align="right" height="20">Nov-04</td> <td style="vertical-align: top;">
</td><td class="xl69">5.58%</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl66">100.00</td> <td class="xl74">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl71" style="height:15.0pt" align="right" height="20">Dec-04</td> <td style="vertical-align: top;">
</td><td class="xl69">-2.18%</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl66">97.82</td> <td class="xl74">-2.18%</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl71" style="height:15.0pt" align="right" height="20">Jan-05</td> <td style="vertical-align: top;">
</td><td class="xl69">-3.46%</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl66">94.44</td> <td class="xl74">-5.56%</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl71" style="height:15.0pt" align="right" height="20">Feb-05</td> <td style="vertical-align: top;">
</td><td class="xl69">3.47%</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl66">97.71</td> <td class="xl74">-2.29%</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl71" style="height:15.0pt" align="right" height="20">Mar-05</td> <td style="vertical-align: top;">
</td><td class="xl69">2.01%</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl66">99.68</td> <td class="xl74">-0.32%</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl71" style="height:15.0pt" align="right" height="20">Apr-05</td> <td style="vertical-align: top;">
</td><td class="xl69">-1.33%</td> <td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td class="xl66">98.35</td> <td class="xl74">-1.65%</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> </tbody></table>
So the maximum drawdown is -6.71 on June-2005 which am happy to get to; the second maximum drawdown is the -5.56% on Jan-2005. Is there a way to ignore the first drawdown period then simply =MIN() ? Or another way perhaps (VBA?)

Thanks for any help!

 

[Giordano Bruno]

Sorry quej2003, I have to sign off now. I would suggest you use a helper column or an array formula.

If no one else has contributed in the mean time, I'll try again later.

 

[moonfish]

Excel Workbook
	A	B	C	D	E
1	*	returns	price	drawdown	*
2	Apr-04	-3.53%	96.47	-3.53%	*
3	May-04	-1.00%	95.51	-4.49%	0
4	Jun-04	-2.32%	93.29	-6.71%	1
5	Jul-04	0.28%	93.55	-6.45%	0
6	Aug-04	0.41%	93.93	-6.07%	0
7	Sep-04	3.98%	97.67	-2.33%	0
8	Oct-04	3.25%	100	*	0
9	Nov-04	5.58%	100	*	0
10	Dec-04	-2.18%	97.82	-2.18%	0
11	Jan-05	-3.46%	94.44	-5.56%	1
12	Feb-05	3.47%	97.71	-2.29%	0
13	Mar-05	2.01%	99.68	-0.32%	0
14	Apr-05	-1.33%	98.35	-1.65%	1
Sheet3

This formula will provide you with an indicator for each local minimum. All you have to do is check which of them is the lowest. As you can see there are actually three drawdowns but I suppose this depends on your definition of a drawdown.

 

[moonfish]

My apologies, it would seem the formula is not being shown properly. I'll repeat it here. Formula in E3:

=IF(AND(D3 < D4,D3 < D2),1,0)

Then drag it down across the entire column.

 

[VoG]

My apologies, it would seem the formula is not being shown properly. I'll repeat it here. Formula in E3:

=IF(AND(D3<d4,d3><d2),1,0)[ code]then="" drag="" it="" down="" across="" the="" entire="" column.[="" quote]="">[/quote]


If your formula contains < add spaces before and after before clicking the Submit Reply button

=IF(X < Y)
</d2),1,0)[></d4,d3>

 

[moonfish]

Thanks VoG, I was struggling on fixing that when you pointed it out to me :P