Black-Scholes GreeksThis spreadsheet uses the Merton model for a continuously dividend-paying stockS 100 Current stock priceX 90 Exercise priceT 0.5 Time to maturity of option (in years)r 6.00% Risk-free rate of interestk 2.00% Dividend yieldSigma 35% Stock volatilityd1 0.6303 - =(LN(B2/B3)+(B5-B6+0.5*B72)*B4)/(B7*SQRT(B4)d2 0.3828 - d1-sigma*SQRT(T)N(d1) 0.7357 - Uses formula NormSDist(d1)N(d2) 0.6491 - Uses formula NormSDist(d2)Call price 16.1531 - =B2*EXP(-B6*B4)*B12-B3*EXP(-B5*B4)*B1316.1531 - =bsmertoncall(B2,B3,B4,B5,B6,B7)Put price 4.4882 - =B3*EXP(-B5*B4)*NORMSDIST(-B10)-B2*EXP(-B6*B4)*NORMSDIST(-B9)4.4882 - =bsmertonput(B2,B3,B4,B5,B6,B7)Greeks: Brute forceCall PutDelta 0.7284 - =EXP(-B6*B4)*NORMSDIST(B9) -0.2616 - =-EXP(-B6*B4)*NORMSDIST(-B9)Gamma 0.0195 - =EXP(B92/2-B6*B4)/(B2*B7*SQRT(2*B4*PI() 0.0195 - =EXP(B92)/2-B6*B4)/(B2*B7*SQRT(2*B4*PI()Vega 22.8976 - =B2*SQRT(B4)*EXP(-(B92)/2)*EXP(-B6*B4)/SQRT(2*PI() 22.8976 - =B2*EXP(-(B92)/2-B6*B4)*SQRT(B4)/SQRT(2*PI()Theta -9.9587- =-B2*EXP(-(B92)/2-B6*B4)*B7/SQRT(8*B4*PI()+B6*B2*EXP(-B6*B4)*B12-B5*B3*EXP(-B5*B4)*B13 -6.6984- =-B2*EXP(-(B92)/2-B6*B4)*B7/SQRT(8*B4*PI()-B6*B2*EXP(-B6*B4)*(1-B12)+B5*B3*EXP(-B5*B4)*(1-B13)Rho 28.3446 - =B3*B4*EXP(-B5*B4)*NORMSDIST(B10) -15.3255 - =-B3*B4*EXP(-B5*B4)*NORMSDIST(-B10)Greeks: VBA functionsCall PutDelta 0.7284 - =deltacall(B2,B3,B4,B5,B6,B7) -0.2616 - =deltaput(B2,B3,B4,B5,B6,B7)Gamma 0.0195 - =gamma(B2,B3,B4,B5,B6,B7) 0.0195 - =gamma(B2,B3,B4,B5,B6,B7)Vega 22.8976 - =vega(B2,B3,B4,B5,B6,B7) 22.8976 - =vega(B2,B3,B4,B5,B6,B7)Theta -9.9587 - =Thetacall(B2,B3,B4,B5,B6,B7) -6.6984 - =Thetaput(B2,B3,B4,B5,B6,B7)Rho 28.3446 - =rhocall(B2,B3,B4,B5,B6,B7) -15.3255 - =rhoput(B2,B3,B4,B5,B6,B7)Gamma denominator#NAME? - =gammadenominator(B2,B3,B4,B5,B6,B7)62.03588 - =B2*B7*SQRT(2*B4*PI()#NAME? - =gammanumerator(B2,B3,B4,B5,B6,B7)1.207587 - =EXP(B92/2-B6*B4)Gamma #NAME? - =B49/B46#NAME? - =ggamma(B2,B3,B4,B5,B6,B7)A B C D E1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253布莱克-斯科尔斯希腊字母该电子表对一个连续支付股利的股票使用Merton模型S 100 当前股票价格X 90 执行价格T 0.5 期权到期时间 (年)r 6.00% 无风险利率k 2.00% 股息收益率Sigma 35% 股票波动性d1 0.6303 - =(LN(B2/B3)+(B5-B6+0.5*B72)*B4)/(B7*SQRT(B4)d2 0.3828 - d1-sigma*SQRT(T)N(d1) 0.7357 - 使用公式 NormSDist(d1)N(d2) 0.6491 - 使用公式 NormSDist(d2)看涨期权价格 16.1531 - =B2*EXP(-B6*B4)*B12-B3*EXP(-B5*B4)*B1316.1531 - =bsmertoncall(B2,B3,B4,B5,B6,B7)看跌期权价格 4.4882 - =B3*EXP(-B5*B4)*NORMSDIST(-B10)-B2*EXP(-B6*B4)*NORMSDIST(-B9)4.4882 - =bsmertonput(B2,B3,B4,B5,B6,B7)希腊字母: 强力计算call 看跌期权Delta 0.7284 - =EXP(-B6*B4)*NORMSDIST(B9) -0.2616 - =-EXP(-B6*B4)*NORMSDIST(-B9)Gamma 0.0195 - =EXP(B92/2-B6*B4)/(B2*B7*SQRT(2*B4*PI() 0.0195 - =EXP(B92)/2-B6*B4)/(B2*B7*SQRT(2*B4*PI()Vega 22.8976 - =B2*SQRT(B4)*EXP(-(B92)/2)*EXP(-B6*B4)/SQRT(2*PI() 22.8976 - =B2*EXP(-(B92)/2-B6*B4)*SQRT(B4)/SQRT(2*PI()Theta -9.9587- =-B2*EXP(-(B92)/2-B6*B4)*B7/SQRT(8*B4*PI()+B6*B2*EXP(-B6*B4)*B12-B5*B3*EXP(-B5*B4)*B13 -6.6984- =-B2*EXP(-(B92)/2-B6*B4)*B7/SQRT(8*B4*PI()-B6*B2*EXP(-B6*B4)*(1-B12)+B5*B3*EXP(-B5*B4)*(1-B13)Rho 28.3446 - =B3*B4*EXP(-B5*B4)*NORMSDIST(B10) -15.3255 - =-B3*B4*EXP(-B5*B4)*NORMSDIST(-B10)希腊字母: VBA函数call 看跌期权Delta 0.7284 - =deltacall(B2,B3,B4,B5,B6,B7) -0.2616 - =deltaput(B2,B3,B4,B5,B6,B7)Gamma 0.0195 - =gamma(B2,B3,B4,B5,B6,B7) 0.0195 - =gamma(B2,B3,B4,B5,B6,B7)Vega 22.8976 - =vega(B2,B3,B4,B5,B6,B7) 22.8976 - =vega(B2,B3,B4,B5,B6,B7)Theta -9.9587 - =Thetacall(B2,B3,B4,B5,B6,B7) -6.6984 - =Thetaput(B2,B3,B4,B5,B6,B7)Rho 28.3446 - =rhocall(B2,B3,B4,B5,B6,B7) -15.3255 - =rhoput(B2,B3,B4,B5,B6,B7)Gamma 分母 #NAME? - =gammadenominator(B2,B3,B4,B5,B6,B7)62.03588 - =B2*B7*SQRT(2*B4*PI()#NAME? - =gammanumerator(B2,B3,B4,B5,B6,B7)1.207587 - =EXP(B92/2-B6*B4)Gamma #NAME? - =B49/B46#NAME? - =ggamma(B2,B3,B4,B5,B6,B7)A B C D E1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253Black-Scholes GreeksDelta graphsS 100 Current stock priceX 90 Exercise priceT 0.5 Time to maturity of option (in years)r 6.00% Risk-free rate of interestk 2.00% Dividend yieldSigma 55% Stock volatilityCall price 20.9272 - =bsmertoncall(B2,B3,B4,B5,B6,B7)Put price 9.2623 - =bsmertonput(B2,B3,B4,B5,B6,B7)GreeksCall PutDelta 0.6904 - =deltacall(B2,B3,B4,B5,B6,B7) -0.2996 - =deltaput(B2,B3,B4,B5,B6,B7)Gamma 0.0116 - =gamma(B2,B3,B4,B5,B6,B7) 0.0116 - =gamma(B2,B3,B4,B5,B6,B7)Vega 24.4375 - =vega(B2,B3,B4,B5,B6,B7) 24.4375 - =vega(B2,B3,B4,B5,B6,B7)Theta -14.9467 - =Thetacall(B2,B3,B4,B5,B6,B7) -11.6864-=Thetaput(B2,B3,B4,B5,B6,B7)Rho 24.0570 - =rhocall(B2,B3,B4,B5,B6,B7) -19.6131 - =rhoput(B2,B3,B4,B5,B6,B7)Delta wrt stock price0.6904 -0.2996 - Data table header0 2.3E-242 -0.990020 0.0001 -0.989930 0.0049 -0.985140 0.0326 -0.957450 0.1018 -0.888260 0.2107 -0.779470 0.3410 -0.649080 0.4725 -0.517590 0.5912 -0.3989100 0.6904 -0.2996110 0.7692 -0.2209120 0.8295 -0.1606130 0.8745 -0.1156140 0.9074 -0.0827150 0.9312 -0.0589Delta wrt time to expirationExercise price0.6904 120 100 800.10 0.1737 0.5427 0.9163474460.125 0.2071 0.5476 0.8958003150.15 0.2347 0.5519 0.8788018610.175 0.2579 0.5559 0.8646098520.20 0.2779 0.5595 0.8526272590.25 0.3108 0.5662 0.8335819930.30 0.3371 0.5721 0.8191924070.35 0.3589 0.5774 0.8079944180.40 0.3775 0.5824 0.7990768890.45 0.3935 0.5869 0.7918448540.50 0.4077 0.5912 0.785893450.55 0.4204 0.5952 0.7809374760.60 0.4318 0.5989 0.7767700810.65 0.4422 0.6025 0.7732374940.70 0.4517 0.6059 0.7702229550.75 0.4605 0.6091 0.7676361780.80 0.4687 0.6122 0.7654062250.85 0.4763 0.6152 0.7634765770.90 0.4834 0.6180 0.7618016450.95 0.4901 0.6208 0.7603442471.00 0.4964 0.6234 0.759073763C a ll a n d P u t D e lta s a s F u n c tio n s o f S to c k P ric e1 4 01 2 01 0 08 06 04 02 00-1-0 .8-0 .6-0 .4-0 .200 .20 .40 .60 .81S to c k p ric eDeltaC a ll d e ltaP u t d e ltaC a ll D e lta , T im e to M a tu rity , a n d M o n e y n e s s1 .00 .90 .80 .70 .60 .50 .40 .30 .20 .10 .00 .10 .20 .30 .40 .50 .60 .70 .80 .91 .0T im e to o p tio n e x p ira tio n , TCalldeltaO u t o f th e m o n e yA t th e m o n e yIn th e m o n e yA B C D E F G H I J K L M123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869布莱克-斯科尔斯希腊字母Delta 图S 100 当前股票价格X 90 执行价格T 0.5 期权到期时间(年)r 6.00% 无风险利率k 2.00% 股息收益率Sigma 55% 股票波动性看涨期权价格 20.9272 - =bsmertoncall(B2,B3,B4,B5,B6,B7)看跌期权价格 9.2623 - =bsmertonput(B2,B3,B4,B5,B6,B7)希腊字母call 看跌期权Delta 0.6904 - =deltacall(B2,B3,B4,B5,B6,B7) -0.2996 - =deltaput(B2,B3,B4,B5,B6,B7)Gamma 0.0116 - =gamma(B2,B3,B4,B5,B6,B7) 0.0116 - =gamma(B2,B3,B4,B5,B6,B7)Vega 24.4375 - =vega(B2,B3,B4,B5,B6,B7) 24.4375 - =vega(B2,B3,B4,B5,B6,B7)Theta -14.9467 - =Thetacall(B2,B3,B4,B5,B6,B7) -11.6864-=Thetaput(B2,B3,B4,B5,B6,B7)Rho 24.0570 - =rhocall(B2,B3,B4,B5,B6,B7) -19.6131 - =rhoput(B2,B3,B4,B5,B6,B7)Delta对于股票价格0.6904 -0.2996 - 模拟运算表头0 2.3E-242 -0.990020 0.0001 -0.989930 0.0049 -0.985140 0.0326 -0.957450 0.1018 -0.888260 0.2107 -0.779470 0.3410 -0.649080 0.4725 -0.517590 0.5912 -0.3989100 0.6904 -0.2996110 0.7692 -0.2209120 0.8295 -0.1606130 0.8745 -0.1156140 0.9074 -0.0827150 0.9312 -0.0589Delta对于到期时间执行价格0.6904 120 100 800.10 0.1737 0.5427 0.9163474460.125 0.2071 0.5476 0.8958003150.15 0.2347 0.5519 0.8788018610.175 0.2579 0.5559 0.8646098520.20 0.2779 0.5595 0.8526272590.25 0.3108 0.5662 0.8335819930.30 0.3371 0.5721 0.8191924070.35 0.3589 0.5774 0.8079944180.40 0.3775 0.5824 0.7990768890.45 0.3935 0.5869 0.7918448540.50 0.4077 0.5912 0.785893450.55 0.4204 0.5952 0.7809374760.60 0.4318 0.5989 0.7767700810.65 0.4422 0.6025 0.7732374940.70 0.4517 0.6059 0.7702229550.75 0.4605 0.6091 0.7676361780.80 0.4687 0.6122 0.7654062250.85 0.4763 0.6152 0.7634765770.90 0.4834 0.6180 0.7618016450.95 0.4901 0.6208 0.7603442471.00 0.4964 0.6234 0.759073763看 涨 和 看 跌 期 权 D e lta s 作 为 股 票 价 格 的 函 数1 4 01 2 01 0 08 06 04 02 00-1-0 .8-0 .6-0 .4-0 .200 .20 .40 .60 .81股 票 价 格Delta看 涨 期 权 d e lta看 跌 期 权 d e lta看 涨 期 权 Delta, 到 期 时 间 和 价 值 状 态1 .00 .90 .80 .70 .60 .50 .40 .30 .20 .10 .00 .10 .20 .30 .40 .50 .60 .70 .80 .91 .0期 权 到 期 时 间 , T看涨期权delta虚 值平 价实 值A B C D E F G H I J K L M123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869Black-Scholes GreeksTheta graphsS 100 Current stock priceX 100 Exercise priceT 0.6 Time to maturity of option (in years)r 6.00% Risk-free rate of interestk 2.00% Dividend yieldSigma 35% Stock volatilityCall price 11.7402 - =bsmertoncall(B2,B3,B4,B5,B6,B7)Put price 9.3971 - =bsmertonput(B2,B3,B4,B5,B6,B7)GreeksCall PutDelta 0.5816 - =deltacall(B2,B3,B4,B5,B6,B7) -0.4064 - =deltaput(B2,B3,B4,B5,B6,B7)Gamma 0.0149 - =gamma(B2,B3,B4,B5,B6,B7) 0.0149 - =gamma(B2,B3,B4,B5,B6,B7)Vega 29.7763 - =vega(B2,B3,B4,B5,B6,B7) 29.7763 - =vega(B2,B3,B4,B5,B6,B7)Theta -10.3069 - =Thetacall(B2,B3,B4,B5,B6,B7) -6.4952-=Thetaput(B2,B3,B4,B5,B6,B7)Rho 27.8537 - =rhocall(B2,B3,B4,B5,B6,B7) -30.0247 - =rhoput(B2,B3,B4,B5,B6,B7)Theta wrt stock price-10.3069 -6.4952 - Data table header0 0 5.787820 0.0000 5.392630 -0.0004 5.194640 -0.0256 4.971750 -0.3101 4.489660 -1.4442 3.158070 -3.7463 0.658380 -6.6323 -2.425490 -9.0531 -5.0438100 -10.3069 -6.4952110 -10.3270 -6.7129120 -9.4662 -6.0497130 -8.1755 -4.9566140 -6.8083 -3.7871150 -5.5687 -2.7450Graph title Call and Put Thetas as functions of Stock Price Option Exercise Price = 100Theta wrt time to expirationExercise price-10.3069 120 100 800.10 -6.7911 -23.8069 -5.091587290.125 -8.0069 -21.4424 -5.8532367110.15 -8.7978 -19.6911 -6.4237917040.175 -9.3065 -18.3253 -6.8362391170.20 -9.6273 -17.2207 -7.1273895870.25 -9.9280 -15.5231 -7.4604400490.30 -9.9782 -14.2613 -7.5873510960.35 -9.9041 -13.2737 -7.5983872220.40 -9.7673 -12.4721 -7.5433145070.45 -9.5995 -11.8034 -7.4503747950.50 -9.4177 -11.2338 -7.3360831930.55 -9.2315 -10.7403 -7.2103449050.60 -9.0461 -10.3069 -7.0792145570.65 -8.8645 -9.9218 -6.9464372310.70 -8.6882 -9.5763 -6.814337990.75 -8.5181 -9.2639 -6.6843507760.80 -8.3544 -8.9793 -6.55734120.85 -8.1972 -8.7184 -6.4338080240.90 -8.0463 -8.4779 -6.3140114170.95 -7.9016 -8.2552 -6.1980559351.00 -7.7628 -8.0480 -6.085944958What does a positive theta mean? In general, as time to maturity gets shorter, optionslose value if they are out of the money and gain value if they are in the money.S 60 Current stock priceX 100 Exercise priceT 0.3 Time to maturity of option (in years)r 6.00% Risk-free rate of interestk 2.00% Dividend yieldSigma 35% Stock volatilityCall price 0.0213 - =bsmertoncall(B76,B77,B78,B79,B80,B81)Put price 38.5963 - =bsmertonput(B76,B77,B78,B79,B80,B81)Put theta 4.358016C a ll a n d P u t T h e ta s a s fu n c tio n s o f S to c k P ric e O p tio n E x e rc is e P ric e = 1 0 01 4 01 2 01 0 08 06 04 02 00-1 2-11-1 0-9-8-7-6-5-4-3-2-10123456S to ck p riceThetaC a ll th e taP u t th e taC a ll T h e ta , T im e to M a tu rity, a n d M o n e y n e s s1 .00 .90 .80 .70 .60 .50 .40 .30 .20 .1-2 5-2 0-1 5-1 0-50T im e to o p tio n m a tu rityThetaO u t o f th e m o n e yA t th e m o n e yIn th e m o n e yA B C D E F G H I J K L M N O1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586布莱克-斯科尔斯希腊字母Theta 图S 100 当前股票价格X 100 执行价格T 0.6 期权到期时间(年)r 6.00% 无风险利率k 2.00% 股息收益率Sigma 35% 股票波动性看涨期权价格 11.7402 - =bsmertoncall(B2,B3,B4,B5,B6,B7)看跌期权价格 9.3971 - =bsmertonput(B2,B3,B4,B5,B6,B7)希腊字母看涨期权 看跌期权Delta 0.5816 - =deltacall(B2,B3,B4,B5,B6,B7) -0.4064 - =deltaput(B2,B3,B4,B5,B6,B7)Gamma 0.0149 - =gamma(B2,B3,B4,B5,B6,B7) 0.0149 - =gamma(B2,B3,B4,B5,B6,B7)Vega 29.7763 - =vega(B2,B3,B4,B5,B6,B7) 29.7763 - =vega(B2,B3,B4,B5,B6,B7)Theta -10.3069 - =Thetacall(B2,B3,B4,B5,B6,B7) -6.4952-=Thetaput(B2,B3,B4,B5,B6,B7)Rho 27.8537 - =rhocall(B2,B3,B4,B5,B6,B7) -30.0247 - =rhoput(B2,B3,B4,B5,B6,B7)Theta对于股票价格-10.3069 -6.4952 - 模拟运算表头0 0 5.787820 0.0000 5.392630 -0.0004 5.194640 -0.0256 4.971750 -0.3101 4.489660 -1.4442 3.158070 -3.7463 0.658380 -6.6323 -2.425490 -9.0531 -5.0438100 -10.3069 -6.4952110 -10.3270 -6.7129120 -9.4662 -6.0497130 -8.1755 -4.9566140 -6.8083 -3.7871150 -5.5687 -2.7450图的标题 看涨和看跌期权Thetas作为股票价格的函数 期权执行价格= 100Theta 对于到期时间执行价格-10.3069 120 100 800.10 -6.7911 -23.8069 -5.091587290.125 -8.0069 -21.4424 -5.8532367110.15 -8.7978 -19.6911 -6.4237917040.175 -9.3065 -18.3253 -6.8362391170.20 -9.6273 -17.2207 -7.1273895870.25 -9.9280 -15.5231 -7.4604400490.30 -9.9782 -14.2613 -7.5873510960.35 -9.9041 -13.2737 -7.5983872220.40 -9.7673 -12.4721 -7.5433145070.45 -9.5995 -11.8034 -7.4503747950.50 -9.4177 -11.2338 -7.3360831930.55 -9.2315 -10.7403 -7.2103449050.60 -9.0461 -10.3069 -7.0792145570.65 -8.8645 -9.9218 -6.9464372310.70 -8.6882 -9.5763 -6.814337990.75 -8.5181 -9.2639 -6.6843507760.80 -8.3544 -8.9793 -6.55734120.85 -8.1972 -8.7184 -6.4338080240.90 -8.0463 -8.4779 -6.3140114170.95 -7.9016 -8.2552 -6.1980559351.00 -7.7628 -8.0480 -6.085944958正的theta意味着什么? 总的来说， 随着到期时间越来越短， 如果期权是虚值的，价值减少；如果期权是实值的，价值增加。S 60 当前股票价格X 100 执行价格T 0.3 期权到期时间(年)r 6.00% 无风险利率k 2.00% 股息收益率Sigma 35% 股票波动性看涨期权价格 0.0213 - =bsmertoncall(B76,B77,B78,B79,B80,B81)看跌期权价格 38.5963 - =bsmertonput(B76,B77,B78,B79,B80,B81)看跌期权theta 4.358016看 涨 和 看 跌 期 权 T h e ta s作 为 股 票 价 格 的 函 数期 权 执 行 价 格 = 1 0 01 4 01 2 01 0 08 06 04 02 00-1 2-11-1 0-9-8-7-6-5-4-3-2-10123456股 票 价 格Theta看 涨 期 权 th e ta看 跌 期 权 th e ta看 涨 期 权 Theta, 到 期 时 间 和 价 值 状 态1 .00 .90 .80 .70 .60 .50 .40 .30 .20 .1-2 5-2 0-1 5-1 0-50到 期 时 间Theta虚 值平 价实 值A B C D E F G H I J K L M N O1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586DELTA HEDGING A CALLS, current stock price 40.00X, exercise 45.00r, interest rate 4.00%k, dividend yield 0.00%T, expiration 0.2308 - =12/52 Initial pricing of call using BS formulaSigma 25%BS value 0.51 - =bsmertoncall(B2,B3,B6,B4,B5,B7)Hedging portfolioWeeks untilexpirationTime untilexpiration Stock priceStock =C13*deltacall(C13,$B$3,B13,$B$4,0,$B$7)Bond12 0.2308 40.000 7.98 -7.4711 0.2115 38.414 4.09 -3.8810 0.1923 38.890 4.43 -4.209 0.1731 40.807 8.39 -7.928 0.1538 42.702 14.36 -13.477 0.1346 45.521 26.90 -24.846 0.1154 42.365 11.42 -10.865 0.0962 42.184 9.62 -9.204 0.0769 43.985 17.65 -16.813 0.0577 42.522 8.11 -7.852 0.0385 41.676 2.74 -2.681 0.0192 39.456 0.00 0.000 0.0000 39.608Hedged position payoff 0.00 - =F25Actual call payoff 0.00 - =MAX(C25-B3,0)A t in itia l d a te , th e s to c k a n d b o n d p o s itio n s a re s e t u s in g th e B la c k -S c h o le sfo rm