Help with UDF!

[jamescorcoran]

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
here:


-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!

 

[Bob Phillips]

Don't you just want

Function Drawdown(rng1 As Range, rng2 As Range) As Variant
Dim tmp
tmp = (1 + rng1.Value) * (1 + rng2.Value) - 1
If tmp > 0 Then
Drawdown = 0
Else
Drawdown = tmp
End If

End Function

and use like

=Drawdown(A3,B2)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Niek Otten]

I think you have spaces in your cells. At least in what you copied into your
posting there is a trailing space for the positive numbers and two for the
negative ones.
They may not be in your original data, but please check.
If it still doesn't work, post again in this thread. We'll be waiting for
your reply.