Excel高手请留步：公式和数据表格的问题

      'By:宏fans
      'QQ:1158268815
      'Date:2009-10-30 19:58:46
      '3Q 老朽 for Color VBA Code
Public Function BSOptionValue(iopt, s, X, r, q, tyr, sigma)
      'By:宏fans     QQ:1158268815
' Returns Black-Scholes Value (iopt=1 for call, -1 for put; q=div yld)
' Uses BSDOne fn
' Uses BSDTwo fn
Dim eqt, ert, NDOne, NDTwo
eqt = Exp(-q * tyr)
ert = Exp(-r * tyr)
If s > 0 And X > 0 And tyr > 0 And sigma > 0 Then
NDOne = Application.NormSDist(iopt * BSDOne(s, X, r, q, tyr, sigma))
NDTwo = Application.NormSDist(iopt * BSDTwo(s, X, r, q, tyr, sigma))
BSOptionValue = iopt * (s * eqt * NDOne - X * ert * NDTwo)
Else
BSOptionValue = -1
End If
End Function
      'By:宏fans
      'QQ:1158268815
      'Date:2009-10-30 19:58:46
      '3Q 老朽 for Color VBA Code
Public Function BSDOne(s, X, r, q, tyr, sigma)
      'By:宏fans     QQ:1158268815
' Returns the Black-Scholes d1 value
' Uses BSDTwo fn
BSDOne = BSDTwo(s, X, r, q, tyr, sigma) + sigma * Sqr(tyr)
End Function
      'By:宏fans
      'QQ:1158268815
      'Date:2009-10-30 19:58:46
      '3Q 老朽 for Color VBA Code
Public Function BSDTwo(s, X, r, q, tyr, sigma)
      'By:宏fans     QQ:1158268815
' Returns the Black-Scholes d2 value
BSDTwo = (Log(s / X) + (r - q - 0.5 * sigma ^ 2) * tyr) / (sigma * Sqr(tyr))
End Function

zhaozyuan 发表于 2009-10-30 20:01
      'By:宏fans
      'QQ:1158268815
      'Date:2009-10-30 19:58:46
      '3Q 老朽 for Color VBA Code
Public Function BSOptionValue(iopt, s, X, r, q, tyr, sigma)
      'By:宏fans     QQ:1158268815
' Returns Black-Scholes Value (iopt=1 for call, -1 for put; q=div yld)
' Uses BSDOne fn
' Uses BSDTwo fn
Dim eqt, ert, NDOne, NDTwo
eqt = Exp(-q * tyr)
ert = Exp(-r * tyr)
If s > 0 And X > 0 And tyr > 0 And sigma > 0 Then
NDOne = Application.NormSDist(iopt * BSDOne(s, X, r, q, tyr, sigma))
NDTwo = Application.NormSDist(iopt * BSDTwo(s, X, r, q, tyr, sigma))
BSOptionValue = iopt * (s * eqt * NDOne - X * ert * NDTwo)
Else
BSOptionValue = -1
End If
End Function
      'By:宏fans
      'QQ:1158268815
      'Date:2009-10-30 19:58:46
      '3Q 老朽 for Color VBA Code
Public Function BSDOne(s, X, r, q, tyr, sigma)
      'By:宏fans     QQ:1158268815
' Returns the Black-Scholes d1 value
' Uses BSDTwo fn
BSDOne = BSDTwo(s, X, r, q, tyr, sigma) + sigma * Sqr(tyr)
End Function
      'By:宏fans
      'QQ:1158268815
      'Date:2009-10-30 19:58:46
      '3Q 老朽 for Color VBA Code
Public Function BSDTwo(s, X, r, q, tyr, sigma)
      'By:宏fans     QQ:1158268815
' Returns the Black-Scholes d2 value
BSDTwo = (Log(s / X) + (r - q - 0.5 * sigma ^ 2) * tyr) / (sigma * Sqr(tyr))
End Function

多谢回复！

hehe,这个在option1那个excel文件里面有。
Option Explicit
Option Base 1
Function BSOptionValue(iopt, S, X, r, q, tyr, sigma)
'   Returns the Black-Scholes Value (iopt=1 for call, -1 for put; q=div yld)
'   Uses BSDOne fn
'   Uses BSDTwo fn
    Dim eqt, ert, NDOne, NDTwo
    eqt = Exp(-q * tyr)
    ert = Exp(-r * tyr)
    If S > 0 And X > 0 And tyr > 0 And sigma > 0 Then
        NDOne = Application.NormSDist(iopt * BSDOne(S, X, r, q, tyr, sigma))
        NDTwo = Application.NormSDist(iopt * BSDTwo(S, X, r, q, tyr, sigma))
        BSOptionValue = iopt * (S * eqt * NDOne - X * ert * NDTwo)
    Else
        BSOptionValue = -1
    End If
End Function
Function BSDOne(S, X, r, q, tyr, sigma)
'   Returns the Black-Scholes d1 value
    BSDOne = (Log(S / X) + (r - q + 0.5 * sigma ^ 2) * tyr) / (sigma * Sqr(tyr))
End Function
   
Function BSDTwo(S, X, r, q, tyr, sigma)
'   Returns the Black-Scholes d2 value
    BSDTwo = (Log(S / X) + (r - q - 0.5 * sigma ^ 2) * tyr) / (sigma * Sqr(tyr))
End Function

那个函数是用VBA编写的函数，换了一台机器就得重新编写，不过楼主要实现这个函数也不用费力编程，直接在cell里输入BS公式就好了