Calculate drawdown and maximum drawdown in Excel?

[homesickhurdle31]

Hey guys, I'm not sure what happened to my previous post. It didn't show up for some reason.


I am new to the site, just like to say I am a big fan of it!


I am currently working on a project which analyses forex data. I can't post the info here for confidentiality issues.


My question is: How do I calculate drawdown and maximum drawdown in Excel given that I have the equity balances over 11 years? (Say from cell A1:A200)


Thanks!

 

[NARAYANK991]

Hi ,


I am sure that if there are people with the domain knowledge that you are talking about , they will respond ; I have no knowledge of what you are talking about , but if you can explain what your data is like , and what kind of calculations you want to make , then probably I can help.


Even if your project is confidential , you can still use sample data to explain what you are looking for.


Narayan

 

[homesickhurdle31]

Hi Narayan,


Thank you for the reply.


My data is basically a bunch of numbers which represent the closing account balances across 11 years.


Drawdown (in forex terms) is calculated by the decline from the largest number (e.g. cell A1) to the next smallest number (A10) before it jumps to the next largest number (A11). This trend occurs over the 11 years. Investopedia defines it as: The peak-to-trough decline during a specific record period of an investment, fund or commodity. A drawdown is usually quoted as the percentage between the peak and the trough.


I would like to come up with a formula that helps me calculate this. I have tried using the formula from this video: http://www.youtube.com/watch?v=HknGrjlv9-U but the results are not what I am looking for.


Any help would be appreciated.


Thanks!

 

[NARAYANK991]

Hi ,


I could not get an idea from googling , so I have gone with Wikipedia !


Suppose your data is as follows :

[pre]

177.43
147.57    16.83%
179.17
106.25    40.70%
128.87    28.07%
144.89    19.13%
126.65    29.31%
170.25    4.98%
129.09    27.95%
188.19
161.79    14.03%
The left hand column is the data , and the figures in the column to the right are the draw-down figures calculated according to the algorithm given in WIkipedia , which I am reproducing below :

[code]
MDD = 0
peak = -99999
for i = 1 to N step 1
if (NAV[i] > peak)
peak = NAV[i]
else
DD[i] = 100.0 * (peak - NAV[i]) / peak
if (DD[i] > MDD)
MDD = DD[i]
endif
endif
endfor

The above algorithm has been coded in Excel VBA as follows :

Public Sub Calc_Drawdown()
          Dim DD As Variant
          DD = Selection.Value
          MDD = 0
          peak = -99999
          i = 1

          For Each cell In Selection
               If cell > peak Then
                    peak = cell
                    DD(i, 1) = ""
               Else
                    DD(i, 1) = (peak - cell) / peak
                    If DD(i, 1) > MDD Then MDD = DD(i, 1)
               End If
               i = i + 1
          Next
          Selection.Offset(, 1).Value = DD
End Sub

Can you confirm whether these calculations are correct ?

Narayan

 

[homesickhurdle31]

Hi Narayan,


Thank you so much. Yes, this is very helpful.