Excel User Defined Function
If you are looking to find the max peak to trough drawdown on a return stream, then this is a great tool for you. To use simply follow the steps below:
- Hit ALT + F11 to access the VBA editor
- Insert>New Module
- Copy and paste the code below into the module:
Function MDD(MyArray As Range)
'Copyright Mark Reichert 2011 mark_reichert@cox.net
Dim CurValue As Double
Dim MaxValue As Double
Dim MyCell As Range
Dim CurDD As Double
Dim MaxDD As Double
MaxValue = 0
MaxDD = 0
CurValue = 1000
For Each MyCell In MyArray
CurValue = CurValue * (1 + MyCell)
If CurValue > MaxValue Then
MaxValue = CurValue
Else
CurDD = (CurValue / MaxValue - 1)
If CurDD < MaxDD Then
MaxDD = CurDD
End If
End If
Next MyCell
MDD = MaxDD
End Function
- Hit Save, then ALT + F11 to get back to Excel
- In any excel cell type “=MDD(“ and select the % returns you would like to calculate a maximum drawdown for.
I would be good if a corresponding example with result were included.
ReplyDeleteThank you, this was helpful
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteIn 1st line of code after declarations, MaxValue should be initialized as equal to CurValue. (= 1000)
ReplyDeleteThe present code doesn't give correct results when 1st period return is negative. You can test it by taking 3 periods with returns -1%, -1%, and -1%. DD should be 2.97%, but using your code it comes out to be -1.99%.