Trying to create an array to simulate Black Scholes option pricing

[pzelyk]

Hi there,

I'm trying to write a VBA code that creates an array to show the pricing of an options as per the black scholes pricing model. On my sheet I have named the cells for the variables. As well I have name the function in the name manager so that it can be inserted into a chart (my end goal is just to have the visual)

My code is below, when I try and create the array all I get is value errors. Any help would be appreciated.

Option Explicit


Public Function dOne(S, X, Rf, T, V)


dOne = (Log(S / X) + (Rf + 0.5 * V ^ 2) * T) / (V * Sqr(T))








End Function
Public Function dTwo(S, X, Rf, T, V)


dTwo = dOne(S, X, Rf, T, V) - V * Sqr(T)


End Function


Public Function BlackScholes(S As Double, _
X As Double, _
Rf As Double, _
T As Double, _
V As Double, _
pts As Long) As Double()


On Error GoTo Errorhandler


Dim i As Integer
Dim stepSize As Double
Dim DStemp() As Double





ReDim DStemp(0 To pts, 1 To 2)

stepSize = S / 5





If ("B9") = 1 Then



For i = LBound(DStemp, 1) To UBound(DStemp, 1)
DStemp(i, 1) = stepSize * i
DStemp(i, 2) = Application.NormSDist(dOne(S, X, Rf, T, V)) * S - Application.NormSDist(dTwo(S, X, Rf, T, V)) * X * Exp(-Rf * T)


Next i

ElseIf ("B9") = 0 Then

For i = LBound(DStemp, 1) To UBound(DStemp, 1)
DStemp(i, 1) = stepSize * i
DStemp(i, 2) = Application.NormSDist(-dTwo(S, X, Rf, T, V)) * X * Exp(-Rf * T) - Application.NormSDist(-dOne(S, X, Rf, T, V)) * S


Next i

End If




BlackScholes = DStemp
Exit Function

Errorhandler:
BlackScholes = CVErr(xlErrValue)

End Function

 

[RobertSF]

Option Explicit

Public Function dOne(S, X, Rf, T, V)
    dOne = (Log(S / X) + (Rf + 0.5 * V ^ 2) * T) / (V * Sqr(T))
End Function

Public Function dTwo(S, X, Rf, T, V)
    dTwo = dOne(S, X, Rf, T, V) - V * Sqr(T)
End Function

Public Function BlackScholes( _
    S As Double, _
    X As Double, _
    Rf As Double, _
    T As Double, _
    V As Double, _
    pts As Long) As Double()
    
    On Error GoTo Errorhandler
    
    Dim i As Integer
    Dim stepSize As Double
    Dim DStemp() As Double
    
    ReDim DStemp(0 To pts, 1 To 2)
    
    stepSize = S / 5
    
    If ("B9") = 1 Then
    
        For i = LBound(DStemp, 1) To UBound(DStemp, 1)
            DStemp(i, 1) = stepSize * i
            DStemp(i, 2) = Application.NormSDist(dOne(S, X, Rf, T, V)) * S - Application.NormSDist(dTwo(S, X, Rf, T, V)) * X * Exp(-Rf * T)
        Next i
    
    ElseIf ("B9") = 0 Then
    
        For i = LBound(DStemp, 1) To UBound(DStemp, 1)
            DStemp(i, 1) = stepSize * i
            DStemp(i, 2) = Application.NormSDist(-dTwo(S, X, Rf, T, V)) * X * Exp(-Rf * T) - Application.NormSDist(-dOne(S, X, Rf, T, V)) * S
        Next i
    
    End If
    
    BlackScholes = DStemp
    
    Exit Function
    
Errorhandler:
    
    BlackScholes = CVErr(xlErrValue)

End Function

Here's the code, formatted for easier reading. Now, what errors are you getting and on what line? Do you know how to use the debugger?

 

[pzelyk]

Option Explicit

Public Function dOne(S, X, Rf, T, V)
    dOne = (Log(S / X) + (Rf + 0.5 * V ^ 2) * T) / (V * Sqr(T))
End Function

Public Function dTwo(S, X, Rf, T, V)
    dTwo = dOne(S, X, Rf, T, V) - V * Sqr(T)
End Function

Public Function BlackScholes( _
    S As Double, _
    X As Double, _
    Rf As Double, _
    T As Double, _
    V As Double, _
    pts As Long) As Double()
    
    On Error GoTo Errorhandler
    
    Dim i As Integer
    Dim stepSize As Double
    Dim DStemp() As Double
    
    ReDim DStemp(0 To pts, 1 To 2)
    
    stepSize = S / 5
    
    If ("B9") = 1 Then
    
        For i = LBound(DStemp, 1) To UBound(DStemp, 1)
            DStemp(i, 1) = stepSize * i
            DStemp(i, 2) = Application.NormSDist(dOne(S, X, Rf, T, V)) * S - Application.NormSDist(dTwo(S, X, Rf, T, V)) * X * Exp(-Rf * T)
        Next i
    
    ElseIf ("B9") = 0 Then
    
        For i = LBound(DStemp, 1) To UBound(DStemp, 1)
            DStemp(i, 1) = stepSize * i
            DStemp(i, 2) = Application.NormSDist(-dTwo(S, X, Rf, T, V)) * X * Exp(-Rf * T) - Application.NormSDist(-dOne(S, X, Rf, T, V)) * S
        Next i
    
    End If
    
    BlackScholes = DStemp
    
    Exit Function
    
Errorhandler:
    
    BlackScholes = CVErr(xlErrValue)

End Function

Here's the code, formatted for easier reading. Now, what errors are you getting and on what line? Do you know how to use the debugger?

Yes I know how to use debugger. I'm not getting any errors with the code. However if I try to creat a table or an array in excel based on the named formula of this function I get the value error