{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Sheet1","Sheet2","Index"],"not_rendered":{"charts":1,"images":0,"macros":1,"external_links":0,"defined_names":0,"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":"Sheet1","index":0,"n_rows":32,"n_cols":29,"rows_kept":32,"cut":false,"hidden":false,"merged":[[3,0,3,12]]}
["U",1.0489188845808903,"Strike Price",2500]
["D",0.95336256663885255,"Q",{"$f":"(EXP($D$3)-$B$2)/($B$1-$B$2)","v":0.52650449030633417}]
["S0",2620,"R",{"$f":"0.044/12","v":3.6666666666666666E-3}]
["Time Period (In months)",null,null,null,null,null,null,null,null,null,null,null,null,"Payoff","Probability","PV Factor","EPV"]
[0,1,2,3,4,5,6,7,8,9,10,11,12]
[null,null,null,null,null,null,null,null,null,null,null,null,4647.3367694655526,{"$f":"IF(M6=0,\"\",MAX(M6-$D$1,0))","v":2147.3367694655526},{"$f":"COMBIN($M$5,A5)*($D$2^M5)*(1-$D$2)^A5","v":4.5375868767859767E-4},{"$f":"IF(O6=0,\"\",EXP(-$D$3*12))","v":0.95695395747304668},{"$f":"IF(O6=0,\"\",N6*O6*P6)","v":0.93242982521050033}]
[null,null,null,null,null,null,null,null,null,null,null,4430.5969105727927,null,{"$f":"IF(M7=0,\"\",MAX(M7-$D$1,0))","v":""},null,{"$f":"IF(O7=0,\"\",EXP(-$D$3*12))","v":""},{"$f":"IF(O7=0,\"\",N7*O7*P7)","v":""},null,null,null,null,null,null,null,null,null,null,null,{"$f":"AD1048568","v":0}]
[null,null,null,null,null,null,null,null,null,null,4223.9652424058486,null,4223.9652424058486,1723.9652424058486,{"$f":"COMBIN($M$5,B5)*($D$2^L5)*(1-$D$2)^B5","v":4.8968858968393512E-3},0.95695395747304668,8.0786637618198434,null,{"$f":"T1048569","v":0}]
[null,null,null,null,null,null,null,null,null,4026.9703448933433,null,4026.9703448933428,null,"",null,"",""]
[null,null,null,null,null,null,null,null,3839.1627837860633,null,3839.1627837860628,null,3839.1627837860633,1339.1627837860633,{"$f":"COMBIN($M$5,C5)*($D$2^K5)*(1-$D$2)^C5","v":2.422124482276761E-2},0.95695395747304668,31.039940044809605]
[null,null,null,null,null,null,null,3660.1140852946437,null,3660.1140852946432,null,3660.1140852946437,null,"",null,"",""]
[null,null,null,null,null,null,3489.4157585475177,null,3489.4157585475177,null,3489.4157585475177,null,3489.4157585475177,989.41575854751773,{"$f":"COMBIN($M$5,D5)*($D$2^J5)*(1-$D$2)^D5","v":7.2608754999086214E-2},0.95695395747304668,68.747808102732037]
[null,null,null,null,null,3326.6783636389205,null,3326.67836363892,null,3326.6783636389205,null,3326.67836363892,null,"",null,"",""]
[null,null,null,null,3171.5306231407394,null,3171.5306231407394,null,3171.5306231407394,null,3171.5306231407394,null,3171.5306231407394,671.53062314073941,{"$f":"COMBIN($M$5,E5)*($D$2^I5)*(1-$D$2)^E5","v":0.14692147968605415},0.95695395747304668,94.415252415308046]
[null,null,null,3023.618575051175,null,3023.6185750511745,null,3023.618575051175,null,3023.6185750511745,null,3023.618575051175,null,"",null,"",""]
[null,null,2882.6047652476982,null,2882.6047652476982,null,2882.6047652476982,null,2882.6047652476982,null,2882.6047652476982,null,2882.6047652476982,382.6047652476982,{"$f":"COMBIN($M$5,F5)*($D$2^H5)*(1-$D$2)^F5","v":0.21140685312953769},0.95695395747304668,77.403478666408887]
[null,2748.1674776019327,null,2748.1674776019327,null,2748.1674776019327,null,2748.1674776019327,null,2748.1674776019327,null,2748.1674776019327,null,"",null,"",""]
[{"$f":"B3","v":2620},null,2620,null,2620,null,2620,null,2620,null,2620,null,2619.9999999999995,119.99999999999955,{"$f":"COMBIN($M$5,G5)*($D$2^G5)*(1-$D$2)^G5","v":0.22180924146200659},0.95695395747304668,25.471347770539317]
[null,2497.8099245937938,null,2497.8099245937938,null,2497.8099245937938,null,2497.8099245937938,null,2497.8099245937938,null,2497.8099245937933,null,"",null,"",""]
[null,null,2381.318480686738,null,2381.318480686738,null,2381.3184806867375,null,2381.318480686738,null,2381.3184806867375,null,2381.3184806867375,0,{"$f":"COMBIN($M$5,H5)*($D$2^F5)*(1-$D$2)^H5","v":0.17098051953887944},0.95695395747304668,0]
[null,null,null,2270.2598987320412,null,2270.2598987320407,null,2270.2598987320412,null,2270.2598987320412,null,2270.2598987320407,null,"",null,"",""]
[null,null,null,null,2164.3808039924402,null,2164.3808039924402,null,2164.3808039924402,null,2164.3808039924402,null,2164.3808039924393,0,{"$f":"COMBIN($M$5,I5)*($D$2^E5)*(1-$D$2)^I5","v":9.6103772305490817E-2},0.95695395747304668,0]
[null,null,null,null,null,2063.4396384780962,null,2063.4396384780962,null,2063.4396384780957,null,2063.4396384780953,null,"",null,"",""]
[null,null,null,null,null,null,1967.2061098438237,null,1967.2061098438237,null,1967.2061098438232,null,1967.206109843823,0,{"$f":"COMBIN($M$5,J5)*($D$2^D5)*(1-$D$2)^J5","v":3.8412422972074263E-2},0.95695395747304668,0]
[null,null,null,null,null,null,null,1875.4606659883402,null,1875.46066598834,null,1875.4606659883398,null,"",null,"",""]
[null,null,null,null,null,null,null,null,1787.9939941568557,null,1787.9939941568555,null,1787.9939941568555,0,{"$f":"COMBIN($M$5,K5)*($D$2^C5)*(1-$D$2)^K5","v":1.0363506937888787E-2},0.95695395747304668,0]
[null,null,null,null,null,null,null,null,null,1704.6065434042334,null,1704.6065434042332,null,"",null,"",""]
[null,null,null,null,null,null,null,null,null,null,1625.1080693292427,null,1625.1080693292427,0,{"$f":"COMBIN($M$5,L5)*($D$2^B5)*(1-$D$2)^L5","v":1.6945634635827315E-3},0.95695395747304668,0]
[null,null,null,null,null,null,null,null,null,null,null,1549.3172000412371,null,"",null,"",""]
[null,null,null,null,null,null,null,null,null,null,null,null,1477.0610223690344,0,{"$f":"COMBIN($M$5,M5)*($D$2^A5)*(1-$D$2)^M5","v":1.2699609811344791E-4},0.95695395747304668,0]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(Q6:Q30)","v":306.08892058682824}]

{"sheet":"Sheet2","index":1,"n_rows":101,"n_cols":8,"rows_kept":101,"cut":false,"hidden":false,"merged":[]}
["Temp","Sigma","U","D","EPV"]
[2318,{"$f":"A2/100000","v":2.3179999999999999E-2},{"$f":"EXP((B2)*SQRT(1))","v":1.0234507441022487},{"$f":"1/C2","v":0.97708659235690021},239.58521624138382,null,"Average option price",{"$f":"AVERAGE(E2:E101)","v":325.54466318342332}]
[2706,2.7060000000000001E-2,{"$f":"EXP((B3)*SQRT(1))","v":1.0274294466809542},{"$f":"1/C3","v":0.97330284160186065},248.20784292989183]
[6947,6.9470000000000004E-2,1.0719399025253173,0.93288811960834883,374.0127502981004]
[6193,6.1929999999999999E-2,1.0638878699705496,0.93994868089593109,350.05401653365914]
[3410,3.4099999999999998E-2,1.0346880703618861,0.9664748523197394,266.40310326962458]
[1032,1.0319999999999999E-2,1.0103734348577202,0.98973306848751319,227.74945317126532]
[5399,5.3990000000000003E-2,1.0554740473618325,0.94744158087023511,325.19599125962208]
[5236,5.2359999999999997E-2,1.0537550260476094,0.94898716995995547,320.15676975836715]
[8836,8.8359999999999994E-2,1.09238130852145,0.91543126214188975,434.88487502566011]
[5530,5.5300000000000002E-2,1.0568576244089793,0.94620124499667069,329.26366223128622]
[4072,4.0719999999999999E-2,1.0415604277927177,0.96009791973299818,285.13861165470433]
[4793,4.793E-2,1.049097215949006,0.95320050877783247,306.60428014363254]
[6870,6.8699999999999997E-2,1.07111482649541,0.93360672008612633,371.55400494154355]
[5274,5.2740000000000002E-2,1.0541555290482583,0.94862662334356629,321.32926005835179]
[1571,1.5709999999999998E-2,1.0158340508114081,0.98441275836465569,230.43606991150696]
[6040,6.0400000000000002E-2,1.0622613661222289,0.94138790310193321,345.22945350657375]
[6324,6.3240000000000005E-2,1.0652824763479478,0.93871815429485683,354.1958946926635]
[8555,8.5550000000000001E-2,1.0893160257837324,0.9180072415445536,425.78010590131339]
[8398,8.3979999999999999E-2,1.0876071414484743,0.91944964490413406,420.69873104655318]
[2417,2.4170000000000001E-2,1.0244644620464969,0.97611975529377948,241.55478966317756]
[2661,2.6610000000000002E-2,1.026967207441577,0.97374092644227772,247.13324184179481]
[4417,4.4170000000000001E-2,1.0451600169896256,0.95679128912747735,295.30199623358084]
[3237,3.2370000000000003E-2,1.0328996074666221,0.96814830092992821,261.72851101589617]
[5940,5.9400000000000001E-2,1.0611996357097904,0.94232976185592399,342.08418665469748]
[9056,9.0560000000000002E-2,1.0947871929026431,0.91341952708513974,442.02107920669107]
[2905,2.9049999999999999E-2,1.0294760669916567,0.97136789485763186,253.10652339550688]
[5589,5.5890000000000002E-2,1.0574813543896315,0.94564315091606577,331.10049503362347]
[7701,7.7009999999999995E-2,1.0800528768673665,0.92588059475425366,398.20068857826294]
[9619,9.6189999999999998E-2,1.1009682280860973,0.90829142430239007,460.30854336671598]
[5023,5.0229999999999997E-2,1.051512916536443,0.95101066689116831,313.61248296927067]
[7340,7.3400000000000007E-2,1.0761609151993878,0.92922906405193417,386.59677695615119]
[9061,9.0609999999999996E-2,1.0948419336307951,0.91337385725054088,442.18333829100015]
[7427,7.4270000000000003E-2,1.0770975825868447,0.92842098633098691,389.38970794319488]
[8643,8.6430000000000007E-2,1.0902750457933377,0.91719975052015512,428.63009703748548]
[4360,4.36E-2,1.0445644455339314,0.95733681562256112,293.60785407915591]
[5942,5.9420000000000001E-2,1.0612208599147459,0.94231091544915158,342.14702683086733]
[8208,8.208E-2,1.0855426497678866,0.92119825988764459,414.55538681732395]
[5371,5.3710000000000001E-2,1.0551785559992926,0.94770690165605531,324.32856355226806]
[2697,2.6970000000000001E-2,1.0273369821917173,0.97339044279959952,247.99188104997475]
[7986,7.986E-2,1.0831354181012018,0.92324559172209231,407.38677287841398]
[7320,7.3200000000000001E-2,1.0759457045381315,0.92941492845056473,385.95506701785905]
[2552,2.5520000000000001E-2,1.0258484230337368,0.97480288271312499,244.58683649147071]
[6886,6.8860000000000005E-2,1.0712862185786503,0.93345735496044124,372.06471416537016]
[1956,1.9560000000000001E-2,1.0197525501781513,0.98063005562015959,234.56968446384809]
[7592,7.5920000000000001E-2,1.0788762606039395,0.92689035482179793,394.69300709431604]
[2926,2.9260000000000001E-2,1.0296922796672614,0.97116392901687454,253.63632935170858]
[2153,2.1530000000000001E-2,1.0217634427808764,0.97870011602524742,237.18616818191376]
[2657,2.657E-2,1.0269261295748422,0.97377987685833856,247.03835984708689]
[6718,6.7180000000000004E-2,1.0694879686842977,0.93502688134978929,366.70768288784501]
[5172,5.1720000000000002E-2,1.0530808385939365,0.94959471614277058,318.18534818124897]
[3116,3.116E-2,1.0316505547708632,0.96932046939295935,258.52859183865786]
[2350,2.35E-2,1.0237783007466292,0.97677397466884386,240.10536182656685]
[1113,1.1129999999999999E-2,1.0111921688824708,0.98893170929632501,227.85082206715248]
[8886,8.8859999999999995E-2,1.0929276357461351,0.91497366092065746,436.50617024026388]
[9659,9.6589999999999995E-2,1.1014087034665347,0.90792818038629564,461.60896919970918]
[9073,9.0730000000000005E-2,1.0949733225460081,0.91326425896369945,442.57277260369682]
[2184,2.1839999999999998E-2,1.0220802385489456,0.97839676601096104,237.62345076661404]
[1096,1.0959999999999999E-2,1.0110202808246596,0.98909984197777845,227.82670979599257]
[6997,6.9970000000000004E-2,1.0724760064914027,0.93242179214012677,375.61060771765455]
[9733,9.733E-2,1.1022240475472029,0.90725656206223793,464.01509067903982]
[8018,8.0180000000000001E-2,1.0834820768974436,0.92295020039787379,408.41941716256679]
[8678,8.6779999999999996E-2,1.0906567088465036,0.91687878677940415,429.76396250218482]
[9869,9.869E-2,1.1037240920509228,0.9060235317884705,468.43820627024991]
[5619,5.6189999999999997E-2,1.0577986463873685,0.94535950052047757,332.03558103130075]
[2445,2.445E-2,1.0247513522586251,0.97584648002262075,242.17178723404049]
[7927,{"$f":"A67/100000","v":7.9269999999999993E-2},{"$f":"EXP((B67)*SQRT(1))","v":1.0824965566871716},{"$f":"1/C67","v":0.92379046734371084},405.48345683223982]
[8882,8.8819999999999996E-2,1.0928839195150357,0.91501026059908297,436.37645384580628]
[2943,2.9430000000000001E-2,1.0298673422347013,0.9709988451814654,254.06689295575796]
[1344,1.3440000000000001E-2,1.0135307227824473,0.98664991353661058,228.76393943522257]
[5257,5.2569999999999999E-2,1.0539763378400044,0.94878790357796616,320.80454748145019]
[5095,5.0950000000000002E-2,1.0522702784539215,0.95032618565382165,315.81916379017173]
[3158,3.1579999999999997E-2,1.032083939008186,0.96891344027791182,259.63235570450786]
[2961,2.9610000000000001E-2,1.0300527350411557,0.97082408111857021,254.52438867105695]
[4579,4.5789999999999997E-2,1.0468545484170109,0.95524254206292414,300.14583481115477]
[7841,7.8409999999999994E-2,1.0815660098409172,0.92458526886129266,402.71062284596837]
[3961,3.9609999999999999E-2,1.0404049371338235,0.96116422011112934,281.91924468250664]
[1087,1.0869999999999999E-2,1.0109292930938947,0.98918886496953096,227.81456552191474]
[1355,1.355E-2,1.0136422172940391,0.98654138801513458,228.82985092195366]
[6000,0.06,1.0618365465453596,0.94176453358424872,343.97055720091709]
[7104,7.1040000000000006E-2,1.0736241699762685,0.93142463439706658,379.03324801307264]
[5407,5.407E-2,1.0555584886632285,0.94736578857549769,325.44396004113401]
[9492,9.4920000000000004E-2,1.0995708859365074,0.90944568721305985,456.18058588628645]
[1886,1.8859999999999998E-2,1.0190389731741156,0.98131673696952648,233.71397440862637]
[7009,7.009E-2,1.0726047113343178,0.93230990823823845,375.99423869702395]
[2204,2.2040000000000001E-2,1.0222846750396231,0.97820110622438972,237.90898466313206]
[3923,3.9230000000000001E-2,1.0400096583654352,0.96152953191961921,280.82345162924742]
[2782,2.7820000000000001E-2,1.0282105898572398,0.97256341246090783,250.05152459320914]
[3442,3.4419999999999999E-2,1.0350192235260824,0.96616562984523158,267.27950584925566]
[2873,2.8729999999999999E-2,1.0291466873537722,0.97167878232332783,252.30368488842379]
[1645,1.6449999999999999E-2,1.0165860462129912,0.98368456238920654,231.1139809963025]
[4612,4.6120000000000001E-2,1.0472000674254893,0.95492736403127887,301.13750790090046]
[7611,7.6109999999999997E-2,1.0790812665684042,0.92671426238369314,395.30420007127645]
[4323,4.3229999999999998E-2,1.0441780281807025,0.9576910957821293,292.51114369380224]
[3277,3.2770000000000001E-2,1.0333128499525961,0.96776111905109441,262.79939160501164]
[4883,4.8829999999999998E-2,1.0500418284552264,0.95234301425035073,309.33887659297932]
[1144,1.1440000000000001E-2,1.0115056870476291,0.988625187979702,227.89879860153928]
[9513,9.5130000000000006E-2,1.0998018200697894,0.90925472367061877,456.86306404172331]
[2956,2.9559999999999999E-2,1.0300012336919482,0.97087262353617632,254.39714240885345]
[9310,9.3100000000000002E-2,1.0975714869291004,0.91110238550192657,450.26752388395408]
[4776,4.7759999999999997E-2,1.0489188845808903,0.95336256663885255,306.08892058682824]

{"sheet":"Index","index":2,"n_rows":8,"n_cols":2,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
["Sr No.","Details"]
["i","All cells shaded in this manner are not supposed to be touched as they contain formulas"]
["ii","The 'Temp' column on Sheet2 generates random numbers between 1000 and 10000. This is copied and pasted as values to add some stability to the sheet"]
["iii","We have used the full normal model approach to compute u and d"]
["Step 1","Clear 'A5:O31' in Sheet1 and 'EPV' Column in Sheet2"]
["Step 2","Input values for S0 and Strike on Sheet1. We have entered data for a 1 year European call on Reliance Industries with strike 2500."]
["Step 3","Feel free to generate a fresh set of random numbers in the 'temp' column"]
["Step 4","Select cell 'A18' on Sheet1 and run the Macros named EPVGenerator. The code takes a couple of minutes to run so be patient :)"]