在Excel中，我需要两个写入公式，用于Black方法，欧洲期权定价；案例

Question

怎么样。我再次需要你的帮助--问题就在这个话题上--以下是我所拥有的：

第一个看涨期权定价公式：

Function CallBS(Spot As Double, Strike As Double, Maturity As Double, Vol As Double, Rf As Double, Dividend As Double) As Double

Dim D1 As Double
Dim D2 As Double

    D1 = (Application.WorksheetFunction.Ln(Spot / Strike) + (Rf - Dividend + Vol * Vol / 2) * Maturity) / (Vol * Sqr(Maturity))
    D2 = D1 - Vol * Sqr(Maturity)

    CallBS = Spot * Application.WorksheetFunction.NormSDist(D1) * Exp(-Dividend * Maturity) _
    - Application.WorksheetFunction.NormSDist(D2) * Strike * Exp(-Rf * Maturity)

End Function

这部分工作得很好。

第二个看跌期权定价公式：

Function PutBS(Spot As Double, Strike As Double, Maturity As Double, Vol As Double, Rf As Double, Dividend As Double) As Double

Dim D1 As Double
Dim D2 As Double

    D1 = (Application.WorksheetFunction.Ln(Spot / Strike) + (Rf - Dividend + Vol * Vol / 2) * Maturity) / (Vol * Sqr(Maturity))
    D2 = D1 - Vol * Sqr(Maturity)
    
    PutBS = Strike * Application.WorksheetFunction.NormSDist(-D2) * Exp(-Rf * Maturity) _
    - Application.WorksheetFunction.NormSDist(-D1) * Spot * Exp(-Dividend * Maturity)

    End Function

这部分也运作得很好。

现在，我需要编写另一个函数，并增加参数:选项类型："c“或"p”，对于看涨期权和看跌期权，选项类型(公式)都是通用的。我是这样开始的：

Function OptnPrcng(OType As String, Spot As Double, Strike As Double, Maturity As Double, Vol As Double, Rf As Double, Dividend As Double) As Double

Dim D1 As Double
Dim D2 As Double
Dim CallBS As Double
Dim PutBS As Double

    D1 = (Application.WorksheetFunction.Ln(Spot / Strike) + (Rf - Dividend + Vol * Vol / 2) * Maturity) / (Vol * Sqr(Maturity))
    D2 = D1 - Vol * Sqr(Maturity)
    
    Select Case OType
        Case "c" Or "C"
            OptnPrcng = Spot * Application.WorksheetFunction.NormSDist(D1) * Exp(-Dividend * Maturity) _
            - Application.WorksheetFunction.NormSDist(D2) * Strike * Exp(-Rf * Maturity)
        Case "p" Or "P"
            OptnPrcng = Strike * Application.WorksheetFunction.NormSDist(-D2) * Exp(-Rf * Maturity) _
            - Application.WorksheetFunction.NormSDist(-D1) * Spot * Exp(-Dividend * Maturity)
        Case Else: MsgBox "Choose |c| for call option or |p| for put option valuation"
    End Select


End Function

但不起作用。它给了我一个#ARG错误。

Answer 1

像这样试试。我真的不知道公式的作用，但它给出了一些结果:)

Option Explicit

Function OptnPrcng(OType As String, _
                Spot As Double, _
                Strike As Double, _
                Maturity As Double, _
                Vol As Double, _
                Rf As Double, _
                Dividend As Double) As Double

Dim D1 As Double
Dim D2 As Double
Dim CallBS As Double
Dim PutBS As Double

    D1 = (Application.WorksheetFunction.Ln(Spot / Strike) + (Rf - Dividend + Vol * Vol / 2) * Maturity) / (Vol * Sqr(Maturity))
    D2 = D1 - Vol * Sqr(Maturity)

    Select Case LCase(OType)
        Case "c":
            OptnPrcng = Spot * Application.WorksheetFunction.NormSDist(D1) * Exp(-Dividend * Maturity) _
            - Application.WorksheetFunction.NormSDist(D2) * Strike * Exp(-Rf * Maturity)
        Case "p":
            OptnPrcng = Strike * Application.WorksheetFunction.NormSDist(-D2) * Exp(-Rf * Maturity) _
            - Application.WorksheetFunction.NormSDist(-D1) * Spot * Exp(-Dividend * Maturity)
        Case Else: MsgBox "Choose |c| for call option or |p| for put option valuation"
    End Select

End Function

我使用Select Case对LCase做了一些修改。