请各位帮帮忙,一个小问题,参数不可选的错误,怎么改好呢?
本帖最后由 bryanpunk 于 2013-03-20 18:47:16 编辑 Sub BlackSholesModel()
Dim S
Dim K
Dim r
Dim sigma
Dim t
Dim d1 As Double
Dim d2 As Double
Dim Ex As Date
Dim Se As Date
Dim calloption
Ex = Range("_Ex").Value
Se = Range("_Se").Value
t = (DateDiff("d", Ex, Se)) / 365
Range("_t").Value = t
S = Range("_S").Value
K = Range("_K").Value
r = Range("_r").Value
sigma = Range("_sigma").Value
d1 = (Application.Ln(S / K) + (r + 0.5 * sigma ^ 2) * t) / sigma * t ^ (0.5)
d2 = d1 - sigma * t ^ (0.5)
Range("_d1").Value = d1
Range("_d2").Value = d2
calloption = Application.WorksheetFunction.Norm_Dist(d1) - K * Exp(-r * Range("_t").Value) * Application.WorksheetFunction.Norm_Dist(d2)
Range("_call").Value = calloption
End Sub
Excel里的值是这样的:
S(the spot price of the underlying asset)100.00000000000
K(srike price) 100.00000000000
r(interest rate) 0.05000000000
sigma 0.25000000000
t 0.18904109589
d1 0.02671269469
d2 -0.08198444646
Expiration 23/03/2013
Settlement 31/05/2013
最后问Calloption=?
[解决办法]
楼主
Function NormDist(Arg1 As Double, Arg2 As Double, Arg3 As Double, Arg4 As Boolean) As Double
Excel.WorksheetFunction 的成员