国庆福利——BS模型EXCEL计算表
- 2026-11-03 20:40:17
国庆福利——BS模型EXCEL计算表
这里是fairy 的投资探险，说点实在话~ 大家可以把公众号设为星标，才能第一时间收到fairy的更新。
大家好！欢迎来到fairy的投资探险。持续分享投资过程中的点滴心得！
想在网上找个期权BS模型定价计算器都找不到，无奈只能自己手搓了！
效果如下：
图中黄色部分是要输入的值，其它部分是计算结果，可以计算期权价格，也可以根据期权报价反推隐含波动率。
手把手一步步拆解：
中间变量d1公式：
=IF(B7=0,0,(LN(B4/B5)+(B6+B8^2/2)*B7)/(B8*SQRT(B7)))中间变量d2公式：
=B11-B8*SQRT(B7)期权价格：
call=IF(B7=0,0,B4*NORM.DIST(B11,0,1,TRUE)-B5*EXP(-B6*B7)*NORM.DIST(B12,0,1,TRUE))put=IF(B7=0,0,B5*EXP(-B6*B7)*NORM.DIST(-B12,0,1,TRUE)-B4*NORM.DIST(-B11,0,1,TRUE))
Delta：
call=IF(B7=0,0,NORM.DIST(B11,0,1,TRUE))put=IF(B7=0,0,NORM.DIST(B11,0,1,TRUE)-1)
Gamma：
call=IF(B7=0,0,NORM.DIST(B11,0,1,FALSE)/(B4*B8*SQRT(B7)))put=IF($B$7=0,0,NORM.DIST($B$11,0,1,FALSE)/($B$4*$B$8*SQRT($B$7)))
Vege：
call=IF(B7=0,0,(B4*SQRT(B7)*NORM.DIST(B11,0,1,FALSE))/100)put=IF($B$7=0,0,($B$4*SQRT($B$7)*NORM.DIST($B$11,0,1,FALSE))/100)
Theta：
call=IF(B7=0,0,(-B4*NORM.DIST(B11,0,1,FALSE)*B8/(2*SQRT(B7))-B6*B5*EXP(-B6*B7)*NORM.DIST(B12,0,1,TRUE))/365)put=IF(B7=0,0,(-B4*NORM.DIST(B11,0,1,FALSE)*B8/(2*SQRT(B7))+B6*B5*EXP(-B6*B7)*NORM.DIST(-B12,0,1,TRUE))/365)
Rho：
call=IF(B7=0,0,(B5*B7*EXP(-B6*B7)*NORM.DIST(B12,0,1,TRUE))/100)put=IF(B7=0,0,(-B5*B7*EXP(-B6*B7)*NORM.DIST(-B12,0,1,TRUE))/100)
反推隐含波VBA代码：
Function ImpliedVolCall(S As Double, K As Double, r As Double, T As Double, MarketPrice As Double, Optional InitialGuess As Double = 0.5) As DoubleDim sigma As Double, d1 As Double, d2 As Double, price As Double, vega As DoubleDim diff As Double, i As Integersigma = InitialGuessFor i = 1 To 100d1 = (Log(S / K) + (r + sigma ^ 2 / 2) * T) / (sigma * Sqr(T))d2 = d1 - sigma * Sqr(T)price = S * Application.WorksheetFunction.Norm_S_Dist(d1, True) - K * Exp(-r * T) * Application.WorksheetFunction.Norm_S_Dist(d2, True)vega = S * Sqr(T) * Application.WorksheetFunction.Norm_S_Dist(d1, False)diff = price - MarketPriceIf Abs(diff) < 0.00001 Then Exit ForIf vega < 0.00001 Then vega = 0.00001sigma = sigma - diff / vegaIf sigma <= 0 Then sigma = 0.0001Next iImpliedVolCall = sigmaEnd FunctionFunction ImpliedVolPut(S As Double, K As Double, r As Double, T As Double, MarketPrice As Double, Optional InitialGuess As Double = 0.5) As DoubleDim sigma As Double, d1 As Double, d2 As Double, price As Double, vega As DoubleDim diff As Double, i As Integersigma = InitialGuessFor i = 1 To 100d1 = (Log(S / K) + (r + sigma ^ 2 / 2) * T) / (sigma * Sqr(T))d2 = d1 - sigma * Sqr(T)price = K * Exp(-r * T) * Application.WorksheetFunction.Norm_S_Dist(-d2, True) - S * Application.WorksheetFunction.Norm_S_Dist(-d1, True)vega = S * Sqr(T) * Application.WorksheetFunction.Norm_S_Dist(d1, False)diff = price - MarketPriceIf Abs(diff) < 0.00001 Then Exit ForIf vega < 0.00001 Then vega = 0.00001sigma = sigma - diff / vegaIf sigma <= 0 Then sigma = 0.0001Next iImpliedVolPut = sigmaEnd Function
反推看涨隐含波动率
=ImpliedVolCall(B4,B5,B6,B7,F4)反推看跌隐含波动率
=ImpliedVolPut(B4,B5,B6,B7,F6)如何计算年化波动率σ，即历史波动率：
任意股票软件导出日期和收盘价
然后分三步：
1计算每日对数收益率：
=LN(E255/E254)2计算日波动率:
=STDEV.S(F4:F255)3计算年化波动率：
=G255*SQRT(252)这东西有什么用？
太有用了！哈哈~
阅读小贴士
感谢大家对fairy的关注，所以阅读文章后，别忘了动动小手，点赞+在看，系统会第一时间把最新文章推送给你。
本文来自网友投稿或网络内容，如有侵犯您的权益请联系我们删除，联系邮箱：wyl860211@qq.com 。
