Spreadsheet: 045192ed-d9dc-4ef5-afb5-e4623be95434
Sheet: Sheet1

Simulation of Stock and Call Option Prices | Unnamed: 1 | Unnamed: 2 | Unnamed: 3 | Unnamed: 4 | Unnamed: 5 | Unnamed: 6 | 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 | Cell Names:
Initial Stock Price | 40 |  | Number of Days to [NAME] |  | 160 |  | B2:B5 | Rf, [NAME], exPr, call
Exercise Price | 38 |  |  | (enter up to 500) |  |  | B10 | StdDev
Call Price, User Input (1) | 4 |  | [NAME], in Years |  | 0.64 |  | B15 | GivenSD
 |  |  |  |  |  |  | [NAME]:B22 | SimSD, SimCall, SimDrift
Call Price, Lower Bound | 2.722640452279812 |  | Run Solver for Implied Volatility |  |  |  | F2:F3 | dC, Time
[NAME] | Pass |  |  | Difference, (1) - (2) | -8.075144179997551e-07
 |  |  |  |  |  | Cell [NAME]:
Implied Volatility | 0.1946283579141939 |  | Choice of Volatility |  | Indicator |  | B7
d1 | 0.5305942986567072 |  |  | Implied Volatility | 1 |  | B8
d2 | 0.374891612325352 |  |  | [NAME] |  |  | B11
Call Price, Computed (2) | 4.000000807514418 |  |  |  |  |  | B12
 |  |  | Choice of Drift |  | Indicator |  | B13
Volatility, User Input | 0.25 |  |  | Expected Price = Exercise Price | 2 |  | B16
d1 | 0.4524664719377525 |  |  | Expected Profit = 0 |  |  | B17
d2 | 0.2524664719377525 |  |  | Risk-free Interest Rate |  |  | B18
Call Price, Computed | 4.627608990854863 |  |  | [NAME] |  |  | [NAME]
 |  |  |  |  | Drift |  | B21
Volatility for Simulation | 0.1946283579141939 |  | Exp. Price = Exercise Price |  | -0.08014577248054777 |  | B22
Call Price for Simulation | 4.000000807514418 |  | Expected Profit = 0 |  | 0.07911675020613632 |  | B24
Drift for Simulation | 0.07911675020613632 |  | Risk-free Interest Rate |  | 0.03 |  | B25
 |  |  | [NAME] |  | 0.055
Stock Price at [NAME] | 47.51483380344541 |  |  |  |  |  | F5
Profit for Buyer | 5.437290958948418 |  |  |  |  |  | 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 | Call Price |  | E40
0.5305942986567072 | 0.374891612325352 |  | 0 | 40 | 4.000000807514418 |  |  | +SimSD*NORMSINV(C40)/dC^0.5),"") |  |  |  |  | copied to E40:E540
0.598616162733601 | 0.4434008099492103 | 0.7975960305850678 | 1 | 40.42201958571483 | 4.291626560842847 |  | F40
0.5586533669077471 | 0.4039268825905322 | 0.2981426213229321 | 2 | 40.16895721215423 | 4.100162010086805 |  |  | *NORMSDIST(B40),IF(D40=Time,MAX(E40-exPr,0),""))
0.4180680726582018 | 0.2638320063236772 | 0.03693341243827941 | 3 | 39.30426167777364 | 3.496116536950414 |  |  |  |  |  |  |  | copied to F40:F540
0.5009963711001937 | 0.3472522870919615 | 0.8450455849825189 | 4 | 39.80819382929235 | 3.827541468894861
0.5483512444161394 | 0.3951007221439041 | 0.7142432472374232 | 5 | 40.09607354895316 | 4.019339810318289
0.5946043645633555 | 0.4418489987463232 | 0.7082490711087939 | 6 | 40.3773129285762 | 4.211033663709465
0.6525097859656774 | 0.5002511868814948 | 0.7544633756718107 | 7 | 40.73081963711405 | 4.460670522421395
0.749394239884577 | 0.5976340336239754 | 0.8778692297267492 | 8 | 41.32876737245781 | 4.904524244257118
0.752347214391293 | 0.6010870431186053 | 0.5008749903542666 | 9 | 41.33983274991795 | 4.903615942179364
0.8522017890097202 | 0.7014433112294463 | 0.8827110866999632 | 10 | 41.95924310548006 | 5.382795342757316
0.8452990829153447 | 0.695043973744948 | 0.4511490435255503 | 11 | 41.90597284612128 | 5.330806572340446
0.7785658419984215 | 0.6288157934475435 | 0.1976126806638918 | 12 | 41.47968933603482 | 4.98399153821325
0.6973807782409741 | 0.5481374994972638 | 0.15385923628019 | 13 | 40.97199462875695 | 4.58363233060296
0.7300689893621024 | 0.5813342070838629 | 0.6415157201345878 | 14 | 41.16514051396991 | 4.721209193601684
0.5975357383583656 | 0.4493111969742283 | 0.0515691983346751 | 15 | 40.35722202339553 | 4.108298998332067
0.5341119125593193 | 0.3863993745751647 | 0.2148831763356007 | 16 | 39.97649630181593 | 3.826412997936355
0.6346608381155555 | 0.4874620844289063 | 0.8802915417254877 | 17 | 40.56938457192331 | 4.243610108879242
0.5541176140628508 | 0.4074344442849743 | 0.1610827283964027 | 18 | 40.08763415783621 | 3.884923287532839
0.5373516287649815 | 0.3911858615509454 | 0.4105899143406377 | 19 | 39.98587861093563 | 3.802885731424187
0.6343023615295671 | 0.4886558349165019 | 0.868817396621437 | 20 | 40.55112972198896 | 4.200373316343221
0.5392497493409788 | 0.3941243210948137 | 0.1248337532954231 | 21 | 39.99019565997511 | 3.785716906302376
0.5696183239694047 | 0.4250158719403492 | 0.6293710553636405 | 22 | 40.16272921144164 | 3.898150776080556
0.5994396902867061 | 0.455362112773764 | 0.6259365282041278 | 23 | 40.33150010675409 | 4.009650467155954
0.6845262588487455 | 0.5409754749735597 | 0.8322177830985771 | 24 | 40.82223983892847 | 4.362518668824283
0.815390628498089 | 0.6723685785884236 | 0.9315522935599221 | 25 | 41.58657278236242 | 4.943704961042162
0.6146028139243039 | 0.4721114599074889 | 0.009061574698192132 | 26 | 40.4040429863756 | 4.032086562208466
0.5196350446677261 | 0.3776763704743969 | 0.1301950388646029 | 27 | 39.85774456011507 | 3.631424113005902
0.4794734025939202 | 0.3380494145724028 | 0.3129006359922295 | 28 | 39.62874854696869 | 3.462763123335844
0.4760855676983771 | 0.3351982950400819 | 0.4747506716491032 | 29 | 39.60739903450288 | 3.437764809677471
0.4041649674994481 | 0.2638164626756514 | 0.1991922036944713 | 30 | 39.20723347567274 | 3.159161982621548
0.5837922972661578 | 0.4439846364765652 | 0.9782441686672092 | 31 | 40.20340542840255 | 3.83528178900783
0.5755850930638815 | 0.4363203766973865 | 0.4513484085976316 | 32 | 40.15261318561915 | 3.78839973457865
0.5778425561057153 | 0.4391229092137816 | 0.4985245359823109 | 33 | 40.16045097797173 | 3.783627227817433
0.5126616922724859 | 0.3744892650556013 | 0.2233306102798238 | 34 | 39.79558833400603 | 3.515117707868118
0.5982745283555021 | 0.4606514966631731 | 0.8239821540931376 | 35 | 40.26378640821269 | 3.83735889431097
0.6507100623554803 | 0.513638628200255 | 0.7100177719732859 | 36 | 40.54877750083183 | 4.036090258664757
0.6715743689094075 | 0.5350567609954362 | 0.5786605734576415 | 37 | 40.65774543585812 | 4.106996429390463
0.6301629264381952 | 0.494201400704864 | 0.3115737577245428 | 38 | 40.42228753156341 | 3.921777879700478
0.6779946091274488 | 0.5425914493086403 | 0.6893159408352147 | 39 | 40.67858408066363 | 4.101891549246098
0.6403618474654197 | 0.5055193656649861 | 0.3275171358842668 | 40 | 40.46522215719145 | 3.93246529339056
0.5494903818793224 | 0.4152109191633778 | 0.1528629552744887 | 41 | 39.96787258125635 | 3.561755296575349
0.6103781719099433 | 0.4766640989166144 | 0.736476536828713 | 42 | 40.2899712046344 | 3.782669590714317
0.6140487268215705 | 0.4809024443888726 | 0.5031504276385848 | 43 | 40.30358805742534 | 3.78196402192232
0.7231581839903182 | 0.5905821238028535 | 0.8734906926308632 | 44 | 40.88451629940897 | 4.205972245186008
1.029116322009796 | 0.897112947265009 | 0.999410142445381 | 45 | 42.56024245357161 | 5.551375109995561
0.873260900194105 | 0.7418327062888578 | 0.04309754414444367 | 46 | 41.68079943528737 | 4.812515516740326
0.8652362401187255 | 0.734385755357003 | 0.4483073638127728 | 47 | 41.62420364634023 | 4.756764135120235
0.8900443237434988 | 0.7597741100660804 | 0.5863263380093544 | 48 | 41.74615117728985 | 4.845491245972852
0.8430972294028732 | 0.7134098831398598 | 0.2944555581873747 | 49 | 41.47935353084402 | 4.620102506730927
0.71331476402822 | 0.5842129166753443 | 0.08002201267381803 | 50 | 40.77800202735027 | 4.061426408118809
0.7590155126693746 | 0.630501831689069 | 0.669812273946339 | 51 | 41.00902457551074 | 4.228525309552563
0.8985793788172065 | 0.7706565684654547 | 0.9207751573241187 | 52 | 41.7372007939042 | 4.797992450869092
1.000177696515481 | 0.8728484986955276 | 0.8423001678880384 | 53 | 42.266365215846 | 5.226592238694693
1.086885054900917 | 0.9601522500449717 | 0.7997130212451502 | 54 | 42.71625595791626 | 5.600150193575622
1.11124372857852 | 0.9851101365586395 | 0.5765369095379689 | 55 | 42.82818788717437 | 5.6875255946271
1.085601654292661 | 0.9600701353620741 | 0.3746725141873526 | 56 | 42.67035015234773 | 5.541780293773815
1.084678490042462 | 0.9597519458086492 | 0.4735396210068525 | 57 | 42.6457650834927 | 5.511152066109904
1.358091555687867 | 1.233772930118786 | 0.9965756875320855 | 58 | 44.09977476167663 | 6.79403442923234
1.246598992750815 | 1.122891273215744 | 0.1167210423391332 | 59 | 43.46814097379517 | 6.214801309557295
1.322947066414702 | 1.19985328476124 | 0.7573033421992987 | 60 | 43.85359403625927 | 6.553267419377029
1.358092745153821 | 1.235615978822681 | 0.6101577293980025 | 61 | 44.01544910812325 | 6.692140578718117
1.406115785971706 | 1.284259159151023 | 0.6566481913812012 | 62 | 44.24516799216133 | 6.894439818614401
1.593289732814132 | 1.472056417635103 | 0.9615193850887076 | 63 | 45.22984686196136 | 7.805296081473664
1.595013101037276 | 1.474406318813057 | 0.4729475561997163 | 64 | 45.20295946068519 | 7.772537361901847
1.566117618102586 | 1.446140640612475 | 0.3568475672031326 | 65 | 45.01010321127438 | 7.583239251658227
1.525415003085165 | 1.406071153906226 | 0.3160295893996006 | 66 | 44.75636103360608 | 7.337402890478259
1.613952783022247 | 1.495245438910656 | 0.7797349702494811 | 67 | 45.1941878575346 | 7.74219142970297
1.666262781488326 | 1.548195373812836 | 0.6606298753142292 | 68 | 45.43612668011021 | 7.964673468707531
1.660799528336405 | 1.543375544566509 | 0.4431984917475831 | 69 | 45.36721286716893 | 7.891918698816838
1.748503773581193 | 1.631726758832677 | 0.7706818153124004 | 70 | 45.79398612091321 | 8.29282607065948
1.791215869804156 | 1.67508942844493 | 0.6205334329919774 | 71 | 45.97836727305094 | 8.463351864447077
1.718381081953197 | 1.602908879272464 | 0.2170765276319058 | 72 | 45.54880978505351 | 8.04398914748549
1.800710838874748 | 1.685896602818749 | 0.7493411113147577 | 73 | 45.93844024348532 | 8.411470976181256
1.840863351389027 | 1.726710874366822 | 0.6071507306621646 | 74 | 46.10354647752034 | 8.56427848633308
1.861623337416085 | 1.748136478179045 | 0.535142460491713 | 75 | 46.16474039359647 | 8.61697414642753
1.79253091210274 | 1.679713597701668 | 0.2302106424705403 | 76 | 45.75819081920587 | 8.217559099297226
1.767732227079259 | 1.655588454903303 | 0.3715838007796792 | 77 | 45.58497684338497 | 8.044029763294304
1.773679247052736 | 1.662213086954547 | 0.4812128877213384 | 78 | 45.56951773259402 | 8.022297809179513
1.662290928756746 | 1.55150652526863 | 0.1347137947733366 | 79 | 44.96504799107211 | 7.436707980187933
1.592755228934695 | 1.482656803580831 | 0.2363065441937771 | 80 | 44.57999299272912 | 7.064128968856046
1.770763121909401 | 1.661354975619544 | 0.9317826000320144 | 81 | 45.41566089394654 | 7.853653019627018
1.856946916946117 | 1.748233432576582 | 0.7445936735410035 | 82 | 45.79578326880947 | 8.213751853990566
1.774503343202539 | 1.666488988170684 | 0.2025467876228735 | 83 | 45.33976415637066 | 7.766807015814841
1.881142424211596 | 1.773831753250455 | 0.7951905386181206 | 84 | 45.81332672810934 | 8.217601982556097
1.929086773759147 | 1.822484431799354 | 0.61917345037274 | 85 | 45.99576366445202 | 8.388388959655124
1.925502164085682 | 1.819612889272341 | 0.4418671554389242 | 86 | 45.92409469727419 | 8.312247649465178
1.809351913779566 | 1.704180540632318 | 0.1338579848418613 | 87 | 45.3127149415068 | 7.713175925601277
1.865757241036781 | 1.76130870376191 | 0.6438296402843084 | 88 | 45.52979860516312 | 7.916322972720927
1.88603835364623 | 1.782317689610356 | 0.5226935871828502 | 89 | 45.57267580376539 | 7.951274083161636
1.986787363680846 | 1.883799717056481 | 0.7663755845567986 | 90 | 45.99338063871696 | 8.35442021845504
2.090407116934042 | 1.988157742528094 | 0.7697180353423321 | 91 | 46.4242295911617 | 8.770201944324121
2.112387271078888 | 2.010881538356099 | 0.5204626073801732 | 92 | 46.46474581737169 | 8.80406225127851
2.107345120748151 | 2.006588518062408 | 0.4311371381594473 | 93 | 46.37679137838823 | 8.71172076633988
2.132977523246385 | 2.032975662293925 | 0.5300425054975979 | 94 | 46.43101651667429 | 8.759122012806408
2.136052648214143 | 2.036811268723843 | 0.4559526260456965 | 95 | 46.37898956102664 | 8.702075907722921
2.47137034933684 | 2.37289532401407 | 0.9945502483357131 | 96 | 47.8669722583553 | 10.16832899289732
2.295021789057877 | 2.197319128799814 | 0.05956408148137715 | 97 | 46.96877357848572 | 9.272559784037526
2.344551215257866 | 2.247627074659741 | 0.595297088090905 | 98 | 47.11976852332687 | 9.416558818779265
2.040014413892146 | 1.943875097065638 | 0.005676456377521788 | 99 | 45.68501604565699 | 7.996754534875699
2.069104663101127 | 1.973756629828016 | 0.5360719423789904 | 100 | 45.74697165808576 | 8.051310719952184
1.936099493879853 | 1.841549366126197 | 0.1231456130072823 | 101 | 45.10958496586985 | 7.422024922958165
1.930018612631897 | 1.836273181450344 | 0.4299388184236346 | 102 | 45.02250535814181 | 7.330650614334488
1.804185146548749 | 1.711251379399249 | 0.1398627747746846 | 103 | 44.43811466057709 | 6.757487257410475
1.871993918897263 | 1.77987896741934 | 0.6494252494051276 | 104 | 44.65928366727979 | 6.964310811544863
2.141602306302503 | 2.050313514575574 | 0.9692703548053689 | 105 | 45.71029860325125 | 7.985056088791019
2.280670081993969 | 2.190214995325805 | 0.808732432366605 | 106 | 46.21541074160421 | 8.477526164778098
2.506569031219434 | 2.416955405507004 | 0.9312297694326146 | 107 | 47.07930865239978 | 9.328734072549963
2.754305509445372 | 2.665541321156907 | 0.9462825253879714 | 108 | 48.03309785876184 | 10.27335589185703
2.716192677115991 | 2.628286133948303 | 0.3199869602792562 | 109 | 47.76883733922693 | 10.0050166304198
2.542781729176244 | 2.455741281447184 | 0.07766347559055431 | 110 | 46.95186838831427 | 9.186543002144148
2.663841765056473 | 2.577676117899049 | 0.7463535962375136 | 111 | 47.34804592146369 | 9.5758189053753
2.684853947638222 | 2.599572074070387 | 0.4803281791612664 | 112 | 47.33069536512407 | 9.55355443562405
2.775696358195798 | 2.691307513148466 | 0.6643448323875107 | 113 | 47.59002740610779 | 9.807139914535078
2.694223973592325 | 2.61073770898787 | 0.223701763713624 | 114 | 47.15838722308271 | 9.371926334084002
2.906308006291853 | 2.823734187276456 | 0.8886422961061451 | 115 | 47.88306501936454 | 10.08985568951799
2.616540361028972 | 2.534889183474878 | 0.01603275914119973 | 116 | 46.64735859461372 | 8.852887304483097
2.401150765795045 | 2.320432775203402 | 0.05321838439202331 | 117 | 45.74051865116689 | 7.946356513960666
2.405519556253186 | 2.325745668204934 | 0.4366369179824749 | 118 | 45.6617912676203 | 7.862819061865594
2.289471088928756 | 2.210652611250501 | 0.1752984699550177 | 119 | 45.15100819131344 | 7.3512889009437
2.572880667800386 | 2.495029324634709 | 0.9461816515287451 | 120 | 46.0652080826349 | 8.253026335506952
2.524579045882031 | 2.447707003877915 | 0.305030492358437 | 121 | 45.78795433596949 | 7.972066760873787
2.503279339402086 | 2.427399236271785 | 0.3675338050859556 | 122 | 45.60860291480689 | 7.788513316368125
2.651231251211054 | 2.576356226935615 | 0.7555026105698843 | 123 | 46.00978215343125 | 8.182501650104278
2.666183230319887 | 2.59232696132781 | 0.4469535041644637 | 124 | 45.94537226533395 | 8.113281203415397
2.480275975272469 | 2.407452711965936 | 0.09221461791914543 | 125 | 45.21171959508383 | 7.378154894945027
2.736323839840414 | 2.664548447902821 | 0.89941243585328 | 126 | 45.93976226544547 | 8.097652834037966
2.576879383303472 | 2.506167389292713 | 0.1238448716986893 | 127 | 45.30159973919645 | 7.456935504345246
3.003045536868232 | 2.933413178684985 | 0.9854117853749687 | 128 | 46.54552982329194 | 8.692418447822341
3.262170161705815 | 3.193634444628203 | 0.8793116130811018 | 129 | 47.23299801100951 | 9.374579079663448
3.316698052951613 | 3.249276812051397 | 0.4998632621792503 | 130 | 47.24416934811537 | 9.381110300551029
3.186175856596498 | 3.119887826502765 | 0.1557994649231464 | 131 | 46.67060061707331 | 8.803224367378967
3.387823271755363 | 3.322688164916654 | 0.7778470343272219 | 132 | 47.12346627789527 | 9.251215396760955
3.776661378821473 | 3.712699973645597 | 0.9548465535537625 | 133 | 48.12785895560921 | 10.25083842051613
3.723267799664191 | 3.660502040198898 | 0.2601949868521904 | 134 | 47.76007887570542 | 9.878525658970432
4.005558200508323 | 3.944011309681593 | 0.8506265713570702 | 135 | 48.3865461564949 | 10.50039636770103
4.14225413912976 | 4.08195074801765 | 0.6030985910634206 | 136 | 48.5541602790266 | 10.66345401183029
4.498198774160227 | 4.439165070322482 | 0.8990459312724086 | 137 | 49.33476499624414 | 11.43950245865999
4.366156712465083 | 4.308420611124716 | 0.1362847214469678 | 138 | 48.6846749320938 | 10.7848663958152
4.59005053999168 | 4.533641882791144 | 0.7093587689878555 | 139 | 49.02810861613414 | 11.12374930437401
4.671724733952574 | 4.616675521275643 | 0.4422301907628645 | 140 | 48.95226894818676 | 11.04336040039306
4.712547371407839 | 4.658892035927268 | 0.3613990180247699 | 141 | 48.75072272170526 | 10.83726466188704
5.11081395562235 | 5.058589686984914 | 0.8724769381206112 | 142 | 49.45044186317524 | 11.53243335705208
5.077743622145042 | 5.026990755783648 | 0.2263488012204926 | 143 | 49.0072514764496 | 11.08469254798595
5.388593734538037 | 5.339356221876652 | 0.7306071441560178 | 144 | 49.39133211954068 | 11.46422213794627
5.43074547960832 | 5.383071462971765 | 0.2998473788322631 | 145 | 49.08508418057787 | 11.15342266897273
5.584265628808351 | 5.53820815306939 | 0.4433704049581959 | 146 | 49.01089855698719 | 11.07468496591819
5.851957702268461 | 5.807575608124228 | 0.5798358015956824 | 147 | 49.14442501854169 | 11.20365880504126
5.57672304844954 | 5.534082111665622 | 0.03714285635987391 | 148 | 48.08805117599675 | 10.14273180078548
5.538829205975942 | 5.498003617198895 | 0.1706072143631641 | 149 | 47.5393960991421 | 9.589523013171323
5.619576215480702 | 5.580650543897863 | 0.2731847282789792 | 150 | 47.19907684213189 | 9.244649496048318
5.784267427789606 | 5.747339293293567 | 0.3366915663152821 | 151 | 46.96612145566802 | 9.007139303084841
5.862027688366831 | 5.827211509275208 | 0.2188344797195079 | 152 | 46.5307545619475 | 8.567217057752302
6.106172384681035 | 6.073604831261679 | 0.3341232198424005 | 153 | 46.29708185816442 | 8.328988455638026
6.89093560032065 | 6.860783904764595 | 0.7643024844971145 | 154 | 46.72059106552333 | 8.747941218287359
7.287012334811765 | 7.259487728473299 | 0.2780404887336867 | 155 | 46.39443439473899 | 8.417227556106809
7.824403150677565 | 7.799784394346872 | 0.2581368671886439 | 156 | 46.03629894209599 | 8.05453456519632
9.115299043856211 | 9.093978575464252 | 0.5539721159119297 | 157 | 46.12436446309809 | 8.13804200099355
12.29121833837967 | 12.27381024883385 | 0.9441496498410653 | 158 | 47.04766648918051 | 9.056785394868065
16.87282815188842 | 16.86051877372308 | 0.3038949068416672 | 159 | 46.76263196850685 | 8.767191694917798
 |  | 0.8991665861938041 | 160 | 47.51483380344541 | 9.514833803445406