second worst drawdown

Q

quej2003

New Member
Joined
Oct 5, 2010
Messages
49
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!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Giordano Bruno said:
So how are they grouped?
Click to expand...

Wellp first drawdown period has the maximum, so ignoring every other value in that period. Then looking at the next drawdown period and seeing the maxiumum drawdown there I guess, not sure if i fully understood your question i confess!
 
Upvote 0
So for the below, there are 2 drawdown periods, from Apr-2004 to Oct-2004, then the second is Dec-2004 to Apr-2005 ( I failed to include May-2005 but it was positive enough to overcome the drawdown). Esstenially a drawdown period I define as the period from which to overcome a drawdown from the peak (just before the first negative month) to surpassing that peak at a later time. So here the first peak is march-2004, then it drawsdown, and overcomes that in Oct-2004, followed by another postiive number in nov-2004 creating a new peak (so on and so on for more postive returns if they did follow), then drawsdown when negative, etc.

------------------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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
624
smozgur
smozgur
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
366
dilshod_k
D
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A

Forum statistics

Threads
1,224,613
Messages
6,179,898
Members
452,948
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top