Spreadsheet: 0022a31c-92c9-4201-9bb9-e106769777d9
Sheet: Sheet1

Simulation of Stock and Put Option Prices | Unnamed: 1 | Unnamed: 2 | Unnamed: 3 | Unnamed: 4 | Unnamed: 5 | Cell Names: | Unnamed: 7 | Unnamed: 8 | Unnamed: 9 | Unnamed: 10 | Unnamed: 11 | Unnamed: 12 | Unnamed: 13
Risk-free Int Rate | 0.03 |  | Number of Days in a Year |  | 250 |  | B2:B5 | Rf, [NAME], exPr, put
Initial Stock Price | 40 |  | Number of Days to [NAME] |  | 125 |  | B10 | StdDev
Exercise Price | 39.5 |  |  | (enter up to 500) |  |  | B15 | GivenSD
Put Price, User Input (1) | 2 |  | [NAME], in Years |  | 0.5 |  | [NAME]:B22 | SimSD, SimPut, SimDrift
Put Price, [NAME] | 38.91192161432097 |  |  |  |  |  | F2:F3 | dC, Time
Put Price, Lower Bound | 0 |  | Run Solver for Implied Volatility
[NAME] | Pass |  |  | Difference, (1) - (2) | 9.25187393363558e-09 | Cell [NAME]:
 |  |  |  |  |  |  | B6
Implied Volatility | 0.2254525855094171 |  | Choice of Volatility |  | Indicator |  | B7
d1 | 0.2527050485315185 |  |  | Implied Volatility | 1 |  | B8
d2 | 0.09328599648176969 |  |  | [NAME] |  |  |  | put<=0),"Fail","Pass")
Put Price, Computed (2) | 1.999999990748126 |  |  |  |  |  | B11
 |  |  | Choice of Drift |  | Indicator |  | B12
Volatility, User Input | 0.25 |  |  | Expected Price = Exercise Price | 2 |  | B13
d1 | 0.2443974989710203 |  |  | Expected Profit = 0 |  |  | B16
d2 | 0.06762080367438342 |  |  | Risk-free Interest Rate |  |  | B17
Put Price, Computed | 2.268581638654076 |  |  | [NAME] |  |  | B18
 |  |  |  |  | Drift |  | [NAME]
Volatility for Simulation | 0.2254525855094171 |  | Exp. Price = Exercise Price |  | -0.02515756441372015 |  | B21
Put Price for Simulation | 1.999999990748126 |  | Expected Profit = 0 |  | -0.1306897520333885 |  | B22
Drift for Simulation | -0.1306897520333885 |  | Risk-free Interest Rate |  | 0.03 |  | B24
 |  |  | [NAME] |  | 0.02 |  | B25
Stock Price at [NAME] | 35.06468760459803 |  |  |  |  |  | F5
Profit for Buyer | 2.405086275562232 |  |  |  |  |  | F8
 |  |  |  |  |  |  | F20
 |  |  |  |  |  |  | F21
 |  |  |  |  |  |  | F22
 |  |  |  |  |  |  | A39
 |  |  |  |  |  |  |  | (SimSD*((Time-D39)/dC)^0.5),"") |  |  |  |  | copied to A39:A540
 |  |  |  |  |  |  | [NAME] |  |  |  |  |  | copied to [NAME]:B540
 |  |  |  |  |  |  | E39
 |  |  |  |  |  |  | F39
 |  |  |  |  |  |  | C40 |  |  |  |  |  | copied to C40:C540
 |  |  |  |  |  |  | D40 |  |  |  |  |  | copied to D40:D540
d1 | d2 | Random | Day | Stock Price | Put Price |  | E40
0.2527050485315185 | 0.09328599648176969 |  | 0 | 40 | 1.999999990748126 |  |  | +SimSD*NORMSINV(C40)/dC^0.5),"") |  |  |  |  | copied to E40:E540
0.3664764633533893 | 0.2076963679913163 | 0.905731193059716 | 1 | 40.73160311157685 | 1.715368230177942 |  | F40
0.4781822335661005 | 0.3200436765664318 | 0.9000304396750849 | 2 | 41.45696157815408 | 1.463562663925087 |  |  | *NORMSDIST(-B40),IF(D40=Time,MAX(exPr-E40,0),""))
0.4210735514959628 | 0.2635791460820373 | 0.2765733675312951 | 3 | 41.08220899699592 | 1.578127530258485 |  |  |  |  |  |  |  | copied to F40:F540
0.4758160112498469 | 0.3189684028414481 | 0.7396943638700251 | 4 | 41.43436832332231 | 1.455167964207725
0.4007315688184636 | 0.2445334356984327 | 0.2162722174058862 | 5 | 40.94768459458343 | 1.608183476599839
0.4091006098667602 | 0.253554663867093 | 0.5526277154614065 | 6 | 40.99939631696217 | 1.582513327689075
0.3320176348697078 | 0.1771266220778746 | 0.212786678079405 | 7 | 40.51092159274224 | 1.748149552320955
0.3394854459333838 | 0.1852521474196441 | 0.5496584861830849 | 8 | 40.55774158321866 | 1.722842863602366
0.5191815456824904 | 0.3656087782490135 | 0.9760918013943523 | 9 | 41.6924693441723 | 1.335864764090047
0.5601718907751211 | 0.4072625077277578 | 0.6825190557376555 | 10 | 41.94946042162861 | 1.252189501380673
0.5151369003044113 | 0.3628937922480078 | 0.3271918643238285 | 11 | 41.65651307378111 | 1.330748673990684
0.623658001781805 | 0.4720840974399882 | 0.8827319940371083 | 12 | 42.34217464949582 | 1.127649788910595
0.6520413844284713 | 0.5011396514888834 | 0.6294203774307268 | 13 | 42.51551976715589 | 1.07445046175423
0.5632056603179582 | 0.4129791063039651 | 0.182076282370951 | 14 | 41.94272967901464 | 1.222154836650967
0.5955417782370336 | 0.4459934514069829 | 0.6449388102199958 | 15 | 42.13929159020136 | 1.158829322907209
0.6923589022664169 | 0.5434918925415753 | 0.8512201968114814 | 16 | 42.74316835501656 | 0.9939779689908121
0.7478298466064255 | 0.5996472865287814 | 0.7266037010159545 | 17 | 43.08508913573511 | 0.9056522837160426
0.7932022526975039 | 0.6457073184186851 | 0.6888555073534759 | 18 | 43.36170196058423 | 0.8370905024076123
0.876385360333269 | 0.7295812726358838 | 0.8098458351446496 | 19 | 43.8801434752316 | 0.725042410314451
0.9095979829139739 | 0.7634880082667581 | 0.6394551884613993 | 20 | 44.07655232680533 | 0.6814130164337975
0.8242787673328885 | 0.6788662189811094 | 0.1962085917195919 | 21 | 43.51513216250784 | 0.7825120084212749
0.8818792371083213 | 0.737167476200968 | 0.7268808208695445 | 22 | 43.86374928472751 | 0.7065220534763021
0.9258885046625782 | 0.7818809414179505 | 0.6773335711689603 | 23 | 44.12500065792395 | 0.6515327552081329
0.9422267080357527 | 0.7989268029471451 | 0.5704745247177929 | 24 | 44.20925862676258 | 0.6297198306808651
0.9548148286277339 | 0.8122260937110077 | 0.5549682121657837 | 25 | 44.26882641759957 | 0.6124880158130237
0.8216142674827852 | 0.6797402675677167 | 0.09444786508896463 | 26 | 43.42009092357477 | 0.7641025099376098
0.8171040423731687 | 0.6759483964405693 | 0.4893476266285208 | 27 | 43.37646715582261 | 0.7653769950023968
0.8505048270320408 | 0.7100712095987572 | 0.6356744660475915 | 28 | 43.56435808646534 | 0.7196823977779729
0.9620309147728825 | 0.8223230573269024 | 0.8662363774171677 | 29 | 44.23095162799688 | 0.5906344550677334
1.012052657598764 | 0.8730743500867827 | 0.6906403981585711 | 30 | 44.51813135613042 | 0.5371515645477176
1.033529679196806 | 0.8952847715667482 | 0.5853149838870229 | 31 | 44.62726788218389 | 0.5135833163839179
0.9754523620925883 | 0.8379447658937174 | 0.2897266678549615 | 32 | 44.24837342046393 | 0.566303901832069
1.042539799397731 | 0.9057734894411477 | 0.7428333623107939 | 33 | 44.63384739870155 | 0.4989620282004923
0.9511815057091899 | 0.8151605217916755 | 0.1930640242573487 | 34 | 44.05815484067284 | 0.5836519083362282
0.8593289001017523 | 0.7240573487961021 | 0.1943308796238974 | 35 | 43.49274875922842 | 0.6793015038551733
0.9479780671953562 | 0.8134601237105346 | 0.8024025399279362 | 36 | 43.9957568224765 | 0.5794614033254364
1.036415776183316 | 0.9026556862979762 | 0.7991450336088142 | 37 | 44.49718187276215 | 0.4916576986271615
1.085651054243742 | 0.9526531363168711 | 0.6783801100941691 | 38 | 44.76406865345276 | 0.446228070904243
1.031543102173923 | 0.8993117492366144 | 0.3082541788651396 | 39 | 44.41780695454354 | 0.4894470421202168
1.0432706097477 | 0.9118102916803092 | 0.5444611437130336 | 40 | 44.46082438254548 | 0.475879608806661
0.9511249410538029 | 0.8204402068530248 | 0.1999110512746829 | 41 | 43.90283656119105 | 0.5574023170228273
1.039524245372703 | 0.9096197255133982 | 0.791790396899757 | 42 | 44.38679333332976 | 0.4725678050021624
1.061459271556993 | 0.9323396804539192 | 0.5795500838960685 | 43 | 44.48623985528925 | 0.4507972658974957
1.086633459878819 | 0.9583035984537659 | 0.5898329662297721 | 44 | 44.60267864673958 | 0.4272568898613782
0.9913776374479899 | 0.8638423958081909 | 0.1967705186557409 | 45 | 44.03583082797593 | 0.5046329104419245
1.06302925215529 | 0.9362936123893963 | 0.7391074681796788 | 46 | 44.41216431952475 | 0.4398878697012201
1.156753754926516 | 1.030822794024842 | 0.795909405232037 | 47 | 44.91098772705155 | 0.3662580505474882
1.313089402659215 | 1.187968295565458 | 0.9139483956707715 | 48 | 45.76539445590753 | 0.2672200925525052
1.368113914274675 | 1.243807937076811 | 0.6786634258090194 | 49 | 46.04040686196204 | 0.2369011370960075
1.443580714388216 | 1.320095247656845 | 0.7370156514312236 | 50 | 46.42961824988181 | 0.200622528217314
1.484299299963353 | 1.361639832246373 | 0.6277826624492737 | 51 | 46.61681726126381 | 0.1823865355801568
1.360053356603912 | 1.238225488087052 | 0.1381237583555257 | 52 | 45.8700344038234 | 0.2351924451815144
1.382183376030094 | 1.261192822373581 | 0.56641355711462 | 53 | 45.95085458554295 | 0.2228141281926019
1.191014631967573 | 1.070867228329966 | 0.05121236760296766 | 54 | 44.86510352978277 | 0.3245840256679413
1.194689262037362 | 1.075390967298474 | 0.5076418801896674 | 55 | 44.84934669913417 | 0.3196248489756588
1.339203169943007 | 1.22076007113875 | 0.882604216118708 | 56 | 45.58714010277067 | 0.2374678227327722
1.486798277118538 | 1.369216594101566 | 0.8840604517515009 | 57 | 46.34197739513325 | 0.1724601481187138
1.67936300848811 | 1.562649098829269 | 0.9388344478407831 | 58 | 47.34468793000086 | 0.1110328218046552
1.488117982969504 | 1.37227834711631 | 0.05509783090168519 | 59 | 46.24966897074639 | 0.1689367855974493
1.537987305352313 | 1.423028592062066 | 0.6441072321540231 | 60 | 46.4649358642716 | 0.1501046840555684
1.398480393770829 | 1.284409405837448 | 0.124563950052484 | 61 | 45.67908876335274 | 0.2008617866386455
1.393193102553728 | 1.280016802849038 | 0.4719459953461338 | 62 | 45.60478634055836 | 0.2012286456469798
1.378414341699553 | 1.266139859551584 | 0.4422980550618281 | 63 | 45.48209120726968 | 0.2056055396796657
1.350545747273722 | 1.239180385206592 | 0.4027601484487224 | 64 | 45.29440731812386 | 0.2157912601007954
1.303419077841247 | 1.192970318703394 | 0.3473362711798815 | 65 | 45.01349021921494 | 0.2353911296239763
1.193804590067038 | 1.084280104577081 | 0.1929389497297356 | 66 | 44.43261176106511 | 0.289983304304446
1.133025623050871 | 1.024433277793074 | 0.3150506464733775 | 67 | 44.10099889281709 | 0.3228741302922122
0.9875441749667726 | 0.8798920408711735 | 0.1326965839846954 | 68 | 43.37908527595244 | 0.4187369492937716
0.8814136866519605 | 0.7747100479975766 | 0.2116925541732757 | 69 | 42.85995268842115 | 0.5000439665794687
0.7238941620214007 | 0.6181475260047697 | 0.1214000395673169 | 70 | 42.12573635927001 | 0.6445043494762785
0.7556983573754983 | 0.6509174642910132 | 0.5963923482802016 | 71 | 42.24617862290886 | 0.6057103355708069
0.7835840430249595 | 0.6797778771082339 | 0.583434831191826 | 72 | 42.34683428421564 | 0.5724478479590491
0.8796410324427358 | 0.776818833433576 | 0.7573927821656001 | 73 | 42.74366917304363 | 0.481200842552953
0.8431279465174897 | 0.7412992220050628 | 0.396388168889624 | 74 | 42.55727308517385 | 0.5068471531913961
0.7854641042418007 | 0.6846386428613725 | 0.3417354988513672 | 75 | 42.28416075888601 | 0.5525171697328908
0.8855838400216199 | 0.7857717255799116 | 0.7593937134778965 | 76 | 42.68431140849243 | 0.4607285721276053
0.9042720076125138 | 0.8054836342274178 | 0.5498292910552376 | 77 | 42.73390619717635 | 0.4408494526118787
0.8985263202861978 | 0.8007724086364759 | 0.4817916415295445 | 78 | 42.67943638787347 | 0.4401086806600301
1.011614229131359 | 0.914905843923198 | 0.7765078090812159 | 79 | 43.11780386698516 | 0.3552000220027915
0.9971271163322415 | 0.9014756851023922 | 0.4550815664549657 | 80 | 43.02161696782953 | 0.3602493570874135
0.8766584235022709 | 0.7820757568922251 | 0.2076789356336812 | 81 | 42.49831477947472 | 0.4407392142222513
1.000135798888927 | 0.9066341125414844 | 0.7888888044066569 | 82 | 42.9606153120715 | 0.3492797576877571
0.8728856621482621 | 0.7804776003973304 | 0.19988091178719 | 83 | 42.42139012624315 | 0.4323450253702781
1.288330793913569 | 1.197029455460551 | 0.9959960103512975 | 84 | 44.02858273199569 | 0.1949551153309663
0.9785053851669524 | 0.8883243509631855 | 0.02267497371775384 | 85 | 42.76316024726243 | 0.3489253321191255
0.8147569401394459 | 0.7257103037245344 | 0.1489100961258523 | 86 | 42.10672157019641 | 0.4585693688651276
0.8531076438272897 | 0.7652100444086598 | 0.5909890606272399 | 87 | 42.21872284038825 | 0.4233477150564404
0.6934869407193195 | 0.6067535993199107 | 0.1632333489435256 | 88 | 41.60614094477311 | 0.5447044346446663
0.6929248733707316 | 0.6073716324206959 | 0.4994293374984418 | 89 | 41.57932140373963 | 0.5371347606226351
0.5983140865156442 | 0.5139574533217848 | 0.2882383030039968 | 90 | 41.22374449568355 | 0.6146347750891472
0.7026556901708951 | 0.6195128847662672 | 0.7313214089874913 | 91 | 41.56194953992274 | 0.5125591702816639
0.7733039606111496 | 0.6913929685691758 | 0.657069453612932 | 92 | 41.77625271236495 | 0.4488069300284803
0.8340418587867954 | 0.7533814896824529 | 0.631223998854181 | 93 | 41.95013908965274 | 0.3981269166826369
0.77903496120224 | 0.6996449135212035 | 0.3740167462468031 | 94 | 41.73236240981489 | 0.4296380945269984
0.6248241534960647 | 0.5467250869360493 | 0.1960421579757254 | 95 | 41.20044770655922 | 0.5426218662747839
0.4672643813521072 | 0.3904779976353688 | 0.1985894218104889 | 96 | 40.68063332940425 | 0.6777280057334973
0.4346523186287901 | 0.3592014521589962 | 0.4376243472161949 | 97 | 40.56433163580393 | 0.697762386100079
0.3399056578405398 | 0.2658143778017177 | 0.3175982286255969 | 98 | 40.26569872672206 | 0.7834998140147018
0.1544374541854428 | 0.0817311800095532 | 0.178947033488944 | 99 | 39.71647712986004 | 0.9850440589438136
0.1100487717634924 | 0.03875440430512933 | 0.4294011343900985 | 100 | 39.59112931871264 | 1.021226990651691
0.003318496611098055 | -0.06653543211189203 | 0.3175182617744143 | 101 | 39.29953537372481 | 1.140156096721729
0.1111768071170177 | 0.04279365213872603 | 0.7177818565141323 | 102 | 39.59905145415934 | 0.9764818707882519
-0.09592375917899126 | -0.162803804121661 | 0.1778028177597051 | 103 | 39.05648049821239 | 1.224838102178687
0.0692359895897693 | 0.003893622489380244 | 0.7957505636177762 | 104 | 39.49483496139898 | 0.9816955633063671
-0.1509681712154266 | -0.2147357920353261 | 0.1753860666334459 | 105 | 38.94851462177028 | 1.241482107122152
-0.1974989867153932 | -0.2596519753143251 | 0.4497111183204723 | 106 | 38.85411621345819 | 1.273329699246904
-0.4168738092968556 | -0.4773690861251125 | 0.1980990029482292 | 107 | 38.36294179300019 | 1.556555221701359
-0.2323995352285189 | -0.2911903767370048 | 0.8077882168865884 | 108 | 38.81743596161914 | 1.249639724017737
0.09854426575979365 | 0.04150877179310318 | 0.9209840235772964 | 109 | 39.5820078976971 | 0.8220415274615718
-0.09635564448491893 | -0.1515800240538454 | 0.2394651238085714 | 110 | 39.15995330214828 | 1.006745956715346
-0.07147487829215315 | -0.1248266976193451 | 0.5655618534507862 | 111 | 39.22773996908712 | 0.9440328399539055
-0.1779540919964753 | -0.2293651915010552 | 0.376437242336022 | 112 | 39.02764752233124 | 1.02659832352299
-0.0972070788233701 | -0.1466012655159181 | 0.6421546652880119 | 113 | 39.20637317076066 | 0.8989754767322609
-0.681814014944194 | -0.7291053482492169 | 0.03107322937258861 | 114 | 38.1535399211665 | 1.55403645033191
-1.10286175678856 | -1.147952273890444 | 0.1216259570200595 | 115 | 37.5005458036832 | 2.065217998301563
-1.804880996485969 | -1.847657616960987 | 0.03089837955064456 | 116 | 36.4922183835594 | 2.986824345462125
-2.062878173325398 | -2.103208357877569 | 0.3591540525261623 | 117 | 36.28234112700234 | 3.190098386118244
-1.849031561711653 | -1.88675699494655 | 0.8418184474739451 | 118 | 36.78144665367009 | 2.702648764338051
-2.583480214448512 | -2.618407178810007 | 0.08428798734033927 | 119 | 36.04389870698166 | 3.429594017449396
-2.895747009227839 | -2.927630819637789 | 0.4651202822330802 | 120 | 35.97646355799676 | 3.500468617016907
-3.028342225234775 | -3.05685997221812 | 0.6835821534710469 | 121 | 36.19976197747904 | 3.281636701891543
-3.62971029187108 | -3.654407385217354 | 0.4321820642395222 | 122 | 36.08915500383761 | 3.396658257560922
-4.498129153492233 | -4.518294245768319 | 0.4939618748590409 | 123 | 36.05884426698804 | 3.431677377924892
-6.409159485150401 | -6.423418358642074 | 0.5045847303921245 | 124 | 36.04224129434447 | 3.453018990049756
 |  | 0.0297402386344594 | 125 | 35.06468760459803 | 4.435312395401972