Why does #VALUE error in VBA function occur when creating a ReDim Matrix of Stock prices from a Named Range?

[gpong23]

' Spot = Spot Price of the underlying

' K = Strike Price

' T = Option Maturity in Years

' rf = Interest Rate in decimal (i.e, for 5%, use 0.05)

' vol = Yearly volatility of the underlying in decimal

' n = Number of time steps

' OpType = 'C' for Call and 'P' for Put

' ExType = 'A' for American and 'E' for European

' Dividends are Named Range


Function DRRTree(Spot, K, T, rf, vol, n, OpType As String, ExType As String, Dividends as Variant)


dt = T / n

u = Exp(vol * (dt ^ 0.5))

d = 1 / u

P = (Exp(rf * dt) - d) / (u - d)

Dim ndivs As Double

ndivs = Application.Count(Dividends) / 3
   
' Tree for stock price

Dim S() As Double

Dim Tau() As Double
Dim Div() As Double
Dim Rate() As Double
Dim divsum() As Double

divsum = 0
For I = 1 To ndivs
Tau(I) = Dividends(I, 1)
Div(I) = Dividends(I, 2)
Rate(I) = Dividends(I, 3)
divsum = divsum + Div(I) * Exp(-Rate(I) * Tau(I))
Next I

Dim Temp() As Double
ReDim Temp(n + 1, n + 1) As Double

Temp(1, 1) = Spot - divsum
For I = 1 To n + 1
For j = I To n + 1
Temp(I, j) = Temp(1, 1) * u ^ (j - I) * d ^ (I - 1)
Next j
Next I

ReDim S(n + 1, n + 1) As Double

S(1, 1) = Temp(1, 1) + divsum
For j = 2 To n + 1
For I = 1 To j
If Tau(1) < (j - 1) * dt Then S(I, j) = Temp(I, j) Else: S(I, j) = Temp(I, j) + Div(1) * Exp(-Rate(1) * (Tau(1) - (j - 1) * dt))
Next I
Next j
For Z = 2 To ndivs
For j = 2 To n + 1
For I = 1 To j
If Tau(Z) < (j - 1) * dt Then S(I, j) = S(I, j) Else: S(I, j) = S(I, j) + Div(Z) * Exp(-Rate(Z) * (Tau(Z) - (j - 1) * dt))
Next I
Next j
Next Z

' Calculate Terminal Price for Calls and Puts


Dim Op() As Double

ReDim Op(n + 1, n + 1) As Double


For I = 1 To n + 1

Select Case OpType

    Case "C": Op(I, n + 1) = Application.Max(S(I, n + 1) - K, 0)

    Case "P": Op(I, n + 1) = Application.Max(K - S(I, n + 1), 0)

End Select

Next I



' Calculate Remaining entries for Calls and Puts



For j = n To 1 Step -1

    For I = 1 To j

    Select Case ExType

    Case "A":

        If OpType = "C" Then

            Op(I, j) = Application.Max(S(I, j) - K, Exp(-rf * dt) * (P * Op(I, j + 1) + (1 - P) * Op(I + 1, j + 1)))

        ElseIf OpType = "P" Then

            Op(I, j) = Application.Max(K - S(I, j), Exp(-rf * dt) * (P * Op(I, j + 1) + (1 - P) * Op(I + 1, j + 1)))

        End If

    Case "E":

            Op(I, j) = Exp(-rf * dt) * (P * Op(I, j + 1) + (1 - P) * Op(I + 1, j + 1))

    End Select

    Next I

Next j



DRRTree = Op(1, 1)



End Function

 

[StephenCrump]

Welcome to the Forum!

It's a little hard to diagnose the problem without seeing how you are calling the function.

But you have an immediate problem here:

Dim Tau() As Double
Dim Div() As Double
Dim Rate() As Double
Dim divsum() As Double

You subsequently refer to Tau(i), Div(i) and Rate(i), but you haven't specified the size of the arrays. It looks like you need:

ReDim Tau(1 To ndivs)
ReDim Div(1 To ndivs)
ReDim Rate(1 To ndivs)

And you always subsequently refer to divsum as a scalar, so presumably you should be declaring it instead as

Dim divsum As Double

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.