{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Introduction","Basic Black-Scholes Extract","Black-Scholes-Merton P(ITM)"],"not_rendered":{"charts":1,"images":3,"macros":1,"external_links":0,"defined_names":28,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Introduction","index":0,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Basic Black-Scholes Extract","index":1,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Black-Scholes-Merton P(ITM)","index":2,"n_rows":144,"n_cols":12,"rows_kept":144,"cut":false,"hidden":false,"merged":[]}
["Merton's Six Inputs"]
["Stock price (S) at t=0",{"$f":"C2/2","v":20},40]
["Strike price (X)",{"$f":"C3","v":25},25]
["Safe yield at horizon τ=T-t (r)",{"$f":"C4/100","v":0.02},2]
["Dividend yield (ρ)",{"$f":"C5/100","v":0.01},1]
["Option maturity (τ=T-t) years",{"$f":"C6/20","v":1},20]
["Implied volatility (σ)",{"$f":"C7/100","v":0.3},30]
[]
["Merton's Workings"]
["d1",{"$f":"IF(ISERROR((LN(B2/B3)+(B4-B5+0.5*B7^2)*B6)/(B7*SQRT(B6))),\"N/A\",(LN(B2/B3)+(B4-B5+0.5*B7^2)*B6)/(B7*SQRT(B6)))","v":-0.56047850438069913}]
["d2",{"$f":"IF(ISERROR(B10-B7*SQRT(B6)),\"N/A\",B10-B7*SQRT(B6))","v":-0.86047850438069906}]
["N(d1)",{"$f":"IF(ISERROR(NORMSDIST(B10)),\"N/A\",NORMSDIST(B10))","v":0.28757654881407335}]
["N(d2)",{"$f":"IF(ISERROR(NORMSDIST(B11)),\"N/A\",NORMSDIST(B11))","v":0.19476266385060587}]
["Delta = e(-ρ*τ)N(d1)",{"$f":"IF(B6>0,B12*EXP(-B5*B4),MAX(SIGN(B2-B3),0))","v":0.28751903925545808}]
["Merton Call Price",{"$f":"IF(B6>0,B2*EXP(-B5*B6)*B12-EXP(-B4*B6)*B3*B13,MAX(B2-B3,0))","v":0.9216496689693221}]
[]
["P*ever(ITM) (BBS, 2021, p. 127)"]
["α",{"$f":"(1/B7)*(B4-B5-0.5*B7^2)","v":-0.11666666666666665}]
["b",{"$f":"(1/B7)*LN(B3/B2)","v":0.74381183771403259}]
["A*c",{"$f":"IF(B2>B3,\"N/A\",IF(ISERROR(EXP(2*B18*B19)*NORMSDIST(B11-2*B18*SQRT(B6))),\"N/A\",EXP(2*B18*B19)*NORMSDIST(B11-2*B18*SQRT(B6))))","v":0.22301465036089174}]
["N(-d2)",{"$f":"IF(ISERROR(NORMSDIST(-B11)),\"N/A\",NORMSDIST(-B11))","v":0.80523733614939408}]
["P*(ITM)=N(d2)",{"$f":"IF(ISERROR(NORMSDIST(B11)),\"N/A\",NORMSDIST(B11))","v":0.19476266385060587}]
["P*ever(ITM)=N(d2)+A*c",{"$f":"IF(B2>B3,1,IF(ISERROR(B20+B22),\"N/A\",B20+B22))","v":0.41777731421149761}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,"Probability"]
[null,null,null,null,null,null,null,"This table was created using Data > Forecast > What If Analysis > Data Table"]
[null,null,null,null,null,null,null,"First enter the formulae you want to carry over for different input values (highlighted yellow here)."]
[null,null,null,null,null,null,null,"Then highlight these formulae and the input data and use the above menu commands."]
[null,null,null,null,null,null,null,"Then point \"Column Input Cell\" at the variable (CELL B2 in this case)."]
[null,null,null,null,null,null,null,"Graph Title (I used ALT-ENTER to get line break): "]
[null,null,null,null,null,null,null,{"$f":"\"P*(ITM) and P*ever(ITM)\n X=\" & TEXT(B3,\"$00\") &\", τ=T-t=\" & TEXT(B6,\"0.0\") &\", σ=\" & TEXT(B7,\"0.00\")&\", r=\" & TEXT(B4,\"0.00\")&\", ρ=\" & TEXT(B5,\"0.00\")","v":"P*(ITM) and P*ever(ITM)\n X=$25, τ=T-t=1.0, σ=0.30, r=0.02, ρ=0.01"}]
[null,null,null,null,null,null,"S","P*(ITM)","P*ever(ITM)","Difference","Step=",0.5]
[null,null,null,null,null,null,{"$f":"L43","v":0.5},{"$f":"B22","v":0.19476266385060587},{"$f":"B23","v":0.41777731421149761}]
[null,null,null,null,null,null,{"$f":"G44+$L$43","v":1},1.0388486620906552E-27,2.1001524914867578E-27,{"$f":"I45-H45","v":1.0613038293961027E-27}]
[null,null,null,null,null,null,{"$f":"G45+$L$43","v":1.5},1.1042065132836386E-21,2.2356258130399547E-21,{"$f":"I46-H46","v":1.1314192997563161E-21}]
[null,null,null,null,null,null,2,6.9617266031228764E-18,1.4113868498629944E-17,7.1521418955070671E-18]
[null,null,null,null,null,null,2.5,3.2991341957153194E-15,6.6968677857936153E-15,3.3977335900782959E-15]
[null,null,null,null,null,null,3,3.3798006605627803E-13,6.8688447053173407E-13,3.4890440447545603E-13]
[null,null,null,null,null,null,3.5,1.2757427504615596E-11,2.5957569610021729E-11,1.3200142105406133E-11]
[null,null,null,null,null,null,4,2.4036133259576107E-10,4.8962909993400352E-10,2.4926776733824244E-10]
[null,null,null,null,null,null,4.5,2.7275093295605677E-9,5.5624755288252722E-9,2.8349661992647045E-9]
[null,null,null,null,null,null,5,2.1091540028134166E-8,4.3063571486539298E-8,2.1972031458405132E-8]
[null,null,null,null,null,null,5.5,1.2101957261429232E-7,2.4737755297125705E-7,1.2635798035696473E-7]
[null,null,null,null,null,null,6,5.4757762267530802E-7,1.1206197398535615E-6,5.7304211717825348E-7]
[null,null,null,null,null,null,6.5,2.0434627101895266E-6,4.1869117705742687E-6,2.143449060384742E-6]
[null,null,null,null,null,null,7,6.5065232681427297E-6,1.3347459711906796E-5,6.8409364437640662E-6]
[null,null,null,null,null,null,7.5,1.8145315095970996E-5,3.7268846892318273E-5,1.9123531796347277E-5]
[null,null,null,null,null,null,8,4.5243160903825352E-5,9.3041475687781949E-5,4.7798314783956597E-5]
[null,null,null,null,null,null,8.5,1.0253040796657485E-4,2.1112029412889455E-4,1.085898861623197E-4]
[null,null,null,null,null,null,9,2.1401411852613663E-4,4.4125107279301845E-4,2.2723695426688181E-4]
[null,null,null,null,null,null,9.5,4.1596508916635715E-4,8.5877542635457967E-4,4.4281033718822252E-4]
[null,null,null,null,null,null,10,7.5965650138279839E-4,1.5704841593452423E-3,8.1082765796244394E-4]
[null,null,null,null,null,null,10.5,1.3134160295248229E-3,2.7191128475508304E-3,1.4056968180260075E-3]
[null,null,null,null,null,null,11,2.1636021125266067E-3,4.4856645751844282E-3,2.3220624626578215E-3]
[null,null,null,null,null,null,11.5,3.4142329783737272E-3,7.0889789566733474E-3,3.6747459782996202E-3]
[null,null,null,null,null,null,12,5.1851575251393254E-3,1.0782291305120296E-2,5.5971337799809711E-3]
[null,null,null,null,null,null,12.5,7.608828790455476E-3,1.5846877109156533E-2,8.2380483187010564E-3]
[null,null,null,null,null,null,13,1.0825894756665875E-2,2.2583195859596322E-2,1.1757301102930447E-2]
[null,null,null,null,null,null,13.5,1.4979936913545829E-2,3.1300191530424744E-2,1.6320254616878915E-2]
[null,null,null,null,null,null,14,2.021175366130841E-2,4.2303551717230561E-2,2.2091798055922152E-2]
[null,null,null,null,null,null,14.5,2.6653602124875924E-2,5.5883770015399736E-2,2.9230167890523812E-2]
[null,null,null,null,null,null,15,3.4423784452888198E-2,7.2304808281357699E-2,3.7881023828469501E-2]
[null,null,null,null,null,null,15.5,4.3621903902569252E-2,9.1794038073529954E-2,4.8172134170960702E-2]
[null,null,null,null,null,null,16,5.4325034473709746E-2,0.11453397891111297,6.0208944437403225E-2]
[null,null,null,null,null,null,16.5,6.6584957677860615E-2,0.14065616962809885,7.4071211950238233E-2]
[null,null,null,null,null,null,17,8.0426531496207557E-2,0.17023732912867234,8.9810797632464787E-2]
[null,null,null,null,null,null,17.5,9.5847177378747581E-2,0.20329780023082766,0.10745062285208008]
[null,null,null,null,null,null,18,0.11281740618092369,0.23980213541650758,0.1269847292355839]
[null,null,null,null,null,null,18.5,0.13128225558538739,0.27966158142082687,0.14837932583543947]
[null,null,null,null,null,null,19,0.15116348009093566,0.32273815164596931,0.17157467155503364]
[null,null,null,null,null,null,19.5,0.17236231880929559,0.36884993915686159,0.19648762034756601]
[null,null,null,null,null,null,20,0.19476266385060587,0.41777731421149761,0.22301465036089174]
[null,null,null,null,null,null,20.5,0.21823446025424828,0.4692696635051028,0.25103520325085449]
[null,null,null,null,null,null,21,0.24263718436566467,0.52305235785169102,0.28041517348602635]
[null,null,null,null,null,null,21.5,0.26782326855775018,0.57883367542612874,0.31101040686837855]
[null,null,null,null,null,null,22,0.29364136386216277,0.63631145410089096,0.34267009023872819]
[null,null,null,null,null,null,22.5,0.31993935644762617,0.69517929481018159,0.37523993836255543]
[null,null,null,null,null,null,23,0.34656707744494647,0.75513218512505786,0.40856510768011139]
[null,null,null,null,null,null,23.5,0.37337866729043589,0.81587145605622435,0.44249278876578846]
[null,null,null,null,null,null,24,0.40023457484583363,0.87710902402550084,0.4768744491796672]
[null,null,null,null,null,null,24.5,0.42700318768401724,0.93857090313336289,0.51156771544934565]
[null,null,null,null,null,null,25,0.45356210299507776,1,0.54643789700492218]
[null,null,null,null,null,null,25.5,0.47979905864994282,1,0.52020094135005723]
[null,null,null,null,null,null,26,0.5056125512774996,1,0.4943874487225004]
[null,null,null,null,null,null,26.5,0.53091217307034411,1,0.46908782692965589]
[null,null,null,null,null,null,27,0.55561870178304229,1,0.44438129821695771]
[null,null,null,null,null,null,27.5,0.57966397938783898,1,0.42033602061216102]
[null,null,null,null,null,null,28,0.60299061445941948,1,0.39700938554058052]
[null,null,null,null,null,null,28.5,0.62555154190093898,1,0.37444845809906102]
[null,null,null,null,null,null,29,0.64730947139173012,1,0.35269052860826988]
[null,null,null,null,null,null,29.5,0.6682362531868884,1,0.3317637468131116]
[null,null,null,null,null,null,30,0.6883121868433808,1,0.3116878131566192]
[null,null,null,null,null,null,30.5,0.70752529525953256,1,0.29247470474046744]
[null,null,null,null,null,null,31,0.72587058323089482,1,0.27412941676910518]
[null,null,null,null,null,null,31.5,0.74334929664849547,1,0.25665070335150453]
[null,null,null,null,null,null,32,0.75996819556948036,1,0.24003180443051964]
[null,null,null,null,null,null,32.5,0.77573885172490342,1,0.22426114827509658]
[null,null,null,null,null,null,33,0.79067697862470721,1,0.20932302137529279]
[null,null,null,null,null,null,{"$f":"G109+$L$43","v":33.5},0.80480180028948189,1,{"$f":"I110-H110","v":0.19519819971051811}]
[null,null,null,null,null,null,34,0.81813546278374139,1,0.18186453721625861]
[null,null,null,null,null,null,34.5,0.83070249113827543,1,0.16929750886172457]
[null,null,null,null,null,null,35,0.84252929291506706,1,0.15747070708493294]
[null,null,null,null,null,null,35.5,0.85364370856829119,1,0.14635629143170881]
[null,null,null,null,null,null,36,0.86407460786742318,1,0.13592539213257682]
[null,null,null,null,null,null,36.5,0.87385153095056056,1,0.12614846904943944]
[null,null,null,null,null,null,37,0.88300437204465132,1,0.11699562795534868]
[null,null,null,null,null,null,37.5,0.89156310350195056,1,0.10843689649804944]
[null,null,null,null,null,null,38,0.8995575375374214,1,0.1004424624625786]
[null,null,null,null,null,null,38.5,0.90701712289020919,1,9.2982877109790807E-2]
[null,null,null,null,null,null,39,0.91397077355576983,1,8.6029226444230167E-2]
[null,null,null,null,null,null,39.5,0.92044672672762906,1,7.9553273272370939E-2]
[null,null,null,null,null,null,40,0.92647242713485334,1,7.3527572865146662E-2]
[null,null,null,null,null,null,40.5,0.93207443505074394,1,6.7925564949256056E-2]
[null,null,null,null,null,null,41,0.93727835536936954,1,6.2721644630630458E-2]
[null,null,null,null,null,null,41.5,0.94210878529030029,1,5.7891214709699712E-2]
[null,null,null,null,null,null,42,0.94658927831072948,1,5.3410721689270524E-2]
[null,null,null,null,null,null,42.5,0.95074232239183376,1,4.9257677608166239E-2]
[null,null,null,null,null,null,43,0.95458933033766025,1,4.5410669662339753E-2]
[null,null,null,null,null,null,43.5,0.9581506405960023,1,4.1849359403997699E-2]
[null,null,null,null,null,null,44,0.96144552685847029,1,3.855447314152971E-2]
[null,null,null,null,null,null,44.5,0.96449221499889481,1,3.5507785001105185E-2]
[null,null,null,null,null,null,45,0.96730790604355088,1,3.269209395644912E-2]
[null,null,null,null,null,null,45.5,0.96990880401224067,1,3.0091195987759334E-2]
[null,null,null,null,null,null,46,0.97231014760523438,1,2.7689852394765624E-2]
[null,null,null,null,null,null,46.5,0.97452624483702299,1,2.5473755162977008E-2]
[null,null,null,null,null,null,47,0.9765705098336267,1,2.3429490166373301E-2]
[null,null,null,null,null,null,47.5,0.97845550111593271,1,2.1544498884067287E-2]
[null,null,null,null,null,null,48,0.98019296078743645,1,1.9807039212563549E-2]
[null,null,null,null,null,null,48.5,0.9817938541312391,1,1.8206145868760903E-2]
[null,null,null,null,null,null,49,0.98326840919866298,1,1.6731590801337015E-2]
[null,null,null,null,null,null,49.5,0.98462615604094172,1,1.5373843959058275E-2]
[null,null,null,null,null,null,50,0.98587596529668131,1,1.4124034703318689E-2]
[null,null,null,null,null,null,50.5,0.98702608590177465,1,1.2973914098225348E-2]