Maximum Drawdown in Excel
 

I would like to create a UDF to calculate the maximum drawdown given a
series of returns. I am essentially trying to mimic the second column
he

-2.54% -2.54%
-0.07% -2.60%
0.12% -2.49%
0.11% -2.38%
-1.58% -3.92%
-4.81% -8.54%
-4.06% -12.25%
-0.56% -12.74%
0.32% -12.46%
-0.31% -12.73%
-1.38% -13.94%
-1.37% -15.12%
-0.43% -15.49%
-0.10% -15.57%
0.90% -14.81%
4.47% -11.00%

If the upper left hand cell is A1 (returns entered into column A), then
B1 has the formula
=MIN(0,A1)
Cell B2 (and all the other ones with cell references as appropriate)
have the formula
=MIN(0,(1+B1)*(1+A2)-1)

The maximum drawdown can be obtained by taking the min of the second
column (-15.57%).

Here's my code -- I keep getting a #VALUE! reference. I am trying to
replicate the end result without all the intermediate steps.

Function Drawdown(Returns As Range) As Variant
Dim Cumulative() As Variant
Dim i As Integer
' Starts with Cumulative Returns as 0 if .
If Returns(0) 0 Then
Cumulative(0) = 0
Else
Cumulative(0) = Returns(0)
End If
For i = 1 To (UBound(Returns()))
If (1 + Cumulative(i - 1)) * (1 + Returns(i)) 1 Then
Cumulative(i) = 0
Else
Cumulative(i) = (1 + Cumulative(i - 1)) * (1 + Returns(i)) - 1
End If
Next i
Drawdown = Application.WorksheetFunction.Min(Cumulative)

End Function

Any help is welcome!