一、BS 计算 Opt
1、打开一个空白 Excel 工作表,打开 VBA 编辑器(点击菜单:工具 -> 宏 -> Visual Basic 编辑
器):
2、插入模块(点击 VBA 编辑器菜单:插入 -> 模块):
3、将以下代码复制/粘贴到代码窗口中:
Function CallOpt(stock, exercise, maturity, rate, volatility) As Double
D1 = (Log(stock / exercise) + (rate + (volatility ^ 2) / 2) * maturity) / (volatility *
Sqr(maturity))
D2 = D1 - volatility * Sqr(maturity)
CallOpt = stock * Application.NormSDist(D1)
- exercise * Exp(-rate * maturity) *
Application.NormSDist(D2)
End Function
Function PutOpt(stock, exercise, maturity, rate, volatility) As Double
D1 = (Log(stock / exercise) + (rate + (volatility ^ 2) / 2) * maturity) / (volatility *
Sqr(maturity))
D2 = D1 - volatility * Sqr(maturity)
PutOpt = exercise * Exp(-rate * maturity) * Application.NormSDist(-D2)
- stock *
Application.NormSDist(-D1)
End Function
粘贴完成后如下图:
3、关闭“Visual Basic 编辑器”窗口,回到工作表。此时若查看函数列表,可看到在“用户
定义”类别中增加了两个函数,CallOpt 和 PutOpt:
=CallOpt(stock,exercise,maturity,rate,volatility) 用于计算认购权证的理论价格;
=PutOpt(stock,exercise,maturity,rate,volatility) 用于计算认沽权证的理论价格。
两个函数都是需要 5 个变量,依次为:
stock-正股现价;
exercise-权证行权价;
maturity-权证剩余期限(折算成年,在 Excel 中=(到期日-当前日)/365);
rate-无风险利率(一般取国债的年收益率);
volatility-波动率(一般取正股最近 3 个月的历史波动率);
现在只需要在单元格中输入函数名并依顺序输入各变量,就可轻而易举的算出权证理论价格
了。若还有不明白的,请将下表复制/粘贴到工作表“A1”单元格中试试看。
最后将该 Excel 文件保存起来。记住,以后每次打开该文件,都会出现以下的安全警告,记
得一定要点选“启用宏”,否则自定义函数将不能使用。
二、计算隐含波动率
Unfortunately, 虽 然 BS 公 式 有 analytic form , 但 是 隐 含 波 动 率 并 不 存 在 一 个
closed-form Solution,实际中常用数值方法来计算 implied 波动率。最常用的是
Newton-Raphson 迭代方法。
Interest,
Function ImpliedCallVolatility(UnderlyingPrice, ExercisePrice, Time,
Target, Dividend)
High = 5
Low = 0
Do While (High - Low) > 0.0001
If CallOption(UnderlyingPrice, ExercisePrice, Time, Interest, (High + Low) / 2,
Dividend) > Target Then
High = (High + Low) / 2
Else: Low = (High + Low) / 2
End If
Loop
ImpliedCallVolatility = (High + Low) / 2
End Function
Function ImpliedPutVolatility(UnderlyingPrice, ExercisePrice, Time, Interest, Target,
Dividend)
High = 5
Low = 0
Do While (High - Low) > 0.0001
If PutOption(UnderlyingPrice, ExercisePrice, Time, Interest, (High + Low) / 2,
Dividend) > Target Then
High = (High + Low) / 2
Else: Low = (High + Low) / 2
End If
Loop
ImpliedPutVolatility = (High + Low) / 2
End Function