一、BS 计算 Opt
1、打开一个空白 Excel 工作表,打开 VBA 编辑器(点击菜单:工具 -> 宏 -> Visual Basic 编辑
器):
2、插入模块(点击 VBA 编辑器菜单:插入 -> 模块):
3、将以下代码复制/粘贴到代码窗口中:
Funcon CallOpt(stock, exercise, maturity, rate, volality) As Double
D1 = (Log(stock / exercise) + (rate + (volality ^ 2) / 2) * maturity) / (volality * Sqr(maturity))
D2 = D1 - volality * Sqr(maturity)
CallOpt = stock * Applicaon.NormSDist(D1) - exercise * Exp(-rate * maturity) *
Applicaon.NormSDist(D2)
End Funcon
Funcon PutOpt(stock, exercise, maturity, rate, volality) As Double
D1 = (Log(stock / exercise) + (rate + (volality ^ 2) / 2) * maturity) / (volality * Sqr(maturity))
D2 = D1 - volality * Sqr(maturity)
PutOpt = exercise * Exp(-rate * maturity) * Applicaon.NormSDist(-D2) - stock *
Applicaon.NormSDist(-D1)
End Funcon
粘贴完成后如下图:
3、关闭“Visual Basic 编辑器”窗口,回到工作表。此时若查看函数列表,可看到在“用户定
义”类别中增加了两个函数,CallOpt 和 PutOpt:
=CallOpt(stock,exercise,maturity,rate,volality) 用于计算认购权证的理论价格;
=PutOpt(stock,exercise,maturity,rate,volality) 用于计算认沽权证的理论价格。
两个函数都是需要 5 个变量,依次为:
stock-正股现价;
exercise-权证行权价;
maturity-权证剩余期限(折算成年,在 Excel 中=(到期日-当前日)/365);
评论1