{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Start","BlackScholesModel","BinominalModel"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":27,"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":"Start","index":0,"n_rows":7,"n_cols":1,"rows_kept":7,"cut":false,"hidden":false,"merged":[[1,0,1,17]]}
[]
["This program was created to calculate the price of “Call” and “Put” options for non-dividend stocks. "]
["You may use the Black-Scholes Model and/or the Binominal (tree) Model."]
[]
[]
[]
["To calculate the option price, please press the Start button and follow the instructions."]

{"sheet":"BlackScholesModel","index":1,"n_rows":21,"n_cols":5,"rows_kept":21,"cut":false,"hidden":false,"merged":[[2,5,2,8],[2,10,2,11],[2,13,2,15],[7,13,7,15]]}
["Black-Scholes Model for pricing European puts, calls"]
[]
["Input data"]
["Type of option (1 for call, 2 for put)",2]
["Stock price (So)",130.30000305175781]
["Exercise price (X)",125]
["Today's date","2010-04-03"]
["Exercise date","2010-04-19"]
["Riskfree interest rate",2.4999999441206455E-3]
["StDev of annual return",0.20000000298023224]
["Annual Mean",0]
["Duration (trading days)",{"$f":"(ExerciseDate-CurrentDate)+1","v":17}]
[]
["Quantities for Black-Scholes formula"]
["d1",{"$f":"(LN(B5/B6)+(B9+B10^2/2)*((NETWORKDAYS(B7,B8))/250))/(B10*((NETWORKDAYS(B7,B8))/250)^(1/2))","v":1.0134298951119556},null,"N(d1)",{"$f":"NORMSDIST(B15)","v":0.84457256700126182}]
["d2",{"$f":"(LN(B5/B6)+(B9-B10^2/2)*((NETWORKDAYS(B7,B8))/250))/(B10*((NETWORKDAYS(B7,B8))/250)^(1/2))","v":0.97147754056001079},null,"N(d2)",{"$f":"NORMSDIST(B16)","v":0.83434473400760423}]
[]
["Call Option price ",{"$f":"B5*E15-B6*EXP(-B9*((B8-B7)/250))*E16","v":5.7714018661716722}]
[]
["PV (X)",{"$f":"-PV(RiskfreeRate,Duration/250,,ExercisePrice)","v":124.97877832042846}]
["Put Option price ",{"$f":"B18+B20-CurrentPrice","v":0.45017713484230626}]

{"sheet":"BinominalModel","index":2,"n_rows":58,"n_cols":19,"rows_kept":58,"cut":false,"hidden":false,"merged":[]}
["Black-Scholes model for pricing European puts, calls"]
[]
["Input data"]
["Type of option (1 for call, 2 for put)",2]
["Stock price (So)",130.30000305175781]
["Exercise price (X)",125]
["Today's date","2010-04-03"]
["Exercise date","2010-04-19"]
["Riskfree interest rate",2.4999999441206455E-3]
["StDev of annual return",0.20000000298023224]
["Annual Mean",7.9999998211860657E-2]
[]
["Duration (trading days)",{"$f":"(ExerciseDate-CurrentDate)+1","v":17}]
[]
["Parameters for binomial tree"]
["Sub-periods (N)",{"$f":"(ExerciseDate-CurrentDate)+1","v":17}]
["UP factor (U)",{"$f":"EXP((B11*(NETWORKDAYS(B7,B8)/250)/B16)+B10*((NETWORKDAYS(B7,B8)/250)/B16)^(1/2))","v":1.0104360792977987},null,"UP probability",null,{"$f":"(RiskFreeFactor-DownFactor)/(UPfactor-DownFactor)","v":0.48760047227751713}]
["DOWN factor (D)",{"$f":"EXP((B11*(NETWORKDAYS(B7,B8)/250)/B16)-B10*((NETWORKDAYS(B7,B8)/250)/B16)^(1/2))","v":0.99008163296344853},null,"DOWN probability",null,{"$f":"(UPfactor-RiskFreeFactor)/(UPfactor-DownFactor)","v":0.51239952772248287}]
["Risk-Free factor (F)",{"$f":"EXP((B9*(NETWORKDAYS(B7,B8)/250))/B16)","v":1.000006470609025}]
["Future prices",0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]
[0,130.30000000000001,{"$f":"UPfactor*B21","v":131.65982113250317},133.03383346617596,134.42218510151912,135.82502568462195,137.24250642329221,138.67478010335432,140.12200110511773,141.58432542001697,143.06191066742559,144.55491611164544,146.0635026790732,147.58783297554623,149.12807130386929,150.68438368152425,152.25693785856456,153.84590333569653,155.45145138254932]
[1,{"$f":"IF($A22<=B$20,(DownFactor/UPfactor)*B21,\"\")","v":""},{"$f":"IF($A22<=C$20,(DownFactor/UPfactor)*C21,\"\")","v":129.00763677513734},{"$f":"IF($A22<=D$20,(DownFactor/UPfactor)*D21,\"\")","v":130.3539707025443},{"$f":"IF($A22<=E$20,(DownFactor/UPfactor)*E21,\"\")","v":131.71435507757897},{"$f":"IF($A22<=F$20,(DownFactor/UPfactor)*F21,\"\")","v":133.08893653182702},{"$f":"IF($A22<=G$20,(DownFactor/UPfactor)*G21,\"\")","v":134.47786322713284},{"$f":"IF($A22<=H$20,(DownFactor/UPfactor)*H21,\"\")","v":135.88128487156973},{"$f":"IF($A22<=I$20,(DownFactor/UPfactor)*I21,\"\")","v":137.29935273557621},{"$f":"IF($A22<=J$20,(DownFactor/UPfactor)*J21,\"\")","v":138.73221966826111},{"$f":"IF($A22<=K$20,(DownFactor/UPfactor)*K21,\"\")","v":140.1800401138787},{"$f":"IF($A22<=L$20,(DownFactor/UPfactor)*L21,\"\")","v":141.64297012847575},{"$f":"IF($A22<=M$20,(DownFactor/UPfactor)*M21,\"\")","v":143.12116739671222},{"$f":"IF($A22<=N$20,(DownFactor/UPfactor)*N21,\"\")","v":144.61479124885784},{"$f":"IF($A22<=O$20,(DownFactor/UPfactor)*O21,\"\")","v":146.12400267796551},{"$f":"IF($A22<=P$20,(DownFactor/UPfactor)*P21,\"\")","v":147.64896435722451},{"$f":"IF($A22<=Q$20,(DownFactor/UPfactor)*Q21,\"\")","v":149.18984065749436},{"$f":"IF($A22<=R$20,(DownFactor/UPfactor)*R21,\"\")","v":150.74679766502192},{"$f":"IF($A22<=S$20,(DownFactor/UPfactor)*S21,\"\")","v":152.32000319934329}]
[2,"","",127.72809168308343,129.06107217644458,130.40796375993691,131.76891161080005,133.14406242135499,134.53356441481532,135.93756736126383,137.35622259379582,138.78968302483077,140.2381031625942,141.70163912777193,143.1804486703374,144.67469118655544,146.18452773616286,147.71012105972869,149.25163559619548]
[3,"","","",126.46123758889232,127.78099709246779,129.11452971087655,130.46197918143724,131.82349074152248,133.19921114421365,134.58928867411888,135.99387316335631,137.41311600770382,138.84717018291786,140.29619026122171,141.76033242796689,143.23975449846745,144.73461593501065,146.24507786404487]
[4,"","","","",125.20694861858918,126.51361826300817,127.83392441545236,129.16800938760082,130.51601697630863,131.87809247910621,133.25438270986061,134.64503601459987,136.05020228750323,137.47003298705712,138.90468115238107,140.35430141972276,141.81905003912613,143.29908489127294]
[5,"","","","","",123.96510014666336,125.2588097619535,126.56602063337712,127.88687366111388,129.22151121578881,130.57007715381818,131.93271683291508,133.30957712775742,134.70080644581867,136.10655474336468,137.5269735416166,138.96221594308318,140.41243664806305]
[6,"","","","","","",122.73556878368591,124.01644691217287,125.31069238637956,126.61844470898586,127.93984483853278,129.27503520461573,130.62415972323686,131.98736381231683,133.3647944073696,134.7565999773395,136.16293054060475,137.58393768114721]
[7,"","","","","","","",121.5182323640494,122.78640627312896,124.06781494568705,125.36259650076485,126.67089049882475,127.99283795679328,129.32858136326064,130.67826469383945,132.04203342668308,133.42003455816652,134.81241661873062]
[8,"","","","","","","","",120.31296993382983,121.56856562861293,122.83726481963279,124.11920425601515,125.41452211401061,126.72335801188792,128.04585302498333,129.38214970090633,130.73239207490465,132.09672568538929]
[9,"","","","","","","","","",119.11966173876851,120.36280397460128,121.61891974138558,122.88814443191932,124.17061485197013,125.46646923502172,126.77584725717323,128.09889005219472,129.43574022673943]
[10,"","","","","","","","","","",117.93818921237357,119.16900150723266,120.41265865680165,121.66929471100276,122.93904511871403,124.22204674236859,125.51843787270683,126.82835824368222]
[11,"","","","","","","","","","","",116.76843496413898,117.9870396109046,119.21836171239647,120.46253398898071,121.71969054610348,122.98996688874615,124.27349993603075]
[12,"","","","","","","","","","","","",115.61028276788099,116.81680084648748,118.0359102434366,119.26774236272495,120.51242997969179,121.77010725533027]
[13,"","","","","","","","","","","","","",114.46361755018961,115.65816893965631,116.86518676214875,118.08480111835061,119.31714346668652]
[14,"","","","","","","","","","","","","","",113.3283253789954,114.51102876933732,115.70607494604666,116.91359271942065]
[15,"","","","","","","","","","","","","","","",112.20429345224879,113.37526635626996,114.55845962637305]
[16,"","","","","","","","","","","","","","","","",111.0914100867125,112.25076885168171]
[17,"","","","","","","","","","","","","","","","","",109.98956470686444]
[]
["Option values",0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]
[0,{"$f":"IF($A41<=B$20,MAX(ExercisePrice-B21,(PrUp*C41+PrDown*C42)/(1+RiskfreeRate/250)),\"\")","v":0.45841964183821066},0.25047099762415159,0.12102921096329688,4.9704756892033734E-2,1.6275146497806699E-2,3.7756320425739761E-3,4.6439102023767897E-4,0,0,0,0,0,0,0,0,0,0,{"$f":"MAX(ExercisePrice-S21,0)","v":0}]
[1,{"$f":"IF($A42<=B$20,MAX(ExercisePrice-B22,(PrUp*C42+PrDown*C43)/(1+RiskfreeRate/250)),\"\")","v":""},0.65631295718778937,0.37365296326480635,0.18890407403499551,8.1517414753546957E-2,2.8170028466498714E-2,6.9266896749102837E-3,9.063155585094443E-4,0,0,0,0,0,0,0,0,0,0]
[2,{"$f":"IF($A43<=B$20,MAX(ExercisePrice-B23,(PrUp*C43+PrDown*C44)/(1+RiskfreeRate/250)),\"\")","v":""},"",0.92530561272919309,0.54946768848747118,0.29109713236075879,0.13228449105841411,4.838578427300759E-2,1.2655825574757409E-2,1.7687850449302034E-3,0,0,0,0,0,0,0,0,0]
[3,{"$f":"IF($A44<=B$20,MAX(ExercisePrice-B24,(PrUp*C44+PrDown*C45)/(1+RiskfreeRate/250)),\"\")","v":""},"","",1.2829718331306381,0.79534437855145346,0.44222925814184161,0.21212525921594322,8.2387432695663676E-2,2.3016203317291779E-2,3.4519991473103899E-3,0,0,0,0,0,0,0,0]
[4,{"$f":"IF($A45<=B$20,MAX(ExercisePrice-B25,(PrUp*C45+PrDown*C46)/(1+RiskfreeRate/250)),\"\")","v":""},"","","",1.7470241868149317,1.1313849945313412,0.66120533983422269,0.33558818865555562,0.1388868277821069,4.1633990490997912E-2,6.7369961924920495E-3,0,0,0,0,0,0,0]
[5,{"$f":"IF($A46<=B$20,MAX(ExercisePrice-B26,(PrUp*C46+PrDown*C47)/(1+RiskfreeRate/250)),\"\")","v":""},"","","","",2.3329018367921908,1.5788310266429499,0.97107621238432928,0.522776168253545,0.2314355435713297,7.4842856464287635E-2,1.314806747070478E-2,0,0,0,0,0,0]
[6,{"$f":"IF($A47<=B$20,MAX(ExercisePrice-B27,(PrUp*C47+PrDown*C48)/(1+RiskfreeRate/250)),\"\")","v":""},"","","","","",3.0505227405656581,2.1572026033862048,1.3976984323027364,0.80002672424387467,0.38045399189694629,0.1335532085455913,2.5660052829903569E-2,0,0,0,0,0]
[7,{"$f":"IF($A48<=B$20,MAX(ExercisePrice-B28,(PrUp*C48+PrDown*C49)/(1+RiskfreeRate/250)),\"\")","v":""},"","","","","","",3.9006676030066147,2.8799904759448638,1.9664713689129869,1.1993086354188627,0.61541272349993215,0.2362271697187765,5.0078714054404498E-2,0,0,0,0]
[8,{"$f":"IF($A49<=B$20,MAX(ExercisePrice-B29,(PrUp*C49+PrDown*C50)/(1+RiskfreeRate/250)),\"\")","v":""},"","","","","","","",4.872022237320202,3.7493533926270546,2.6965434233144561,1.7549686235616959,0.97625850736205488,0.41337104331048496,9.7734701404051452E-2,0,0,0]
[9,{"$f":"IF($A50<=B$20,MAX(ExercisePrice-B30,(PrUp*C50+PrDown*C51)/(1+RiskfreeRate/250)),\"\")","v":""},"","","","","","","","",5.9404513624177273,4.7512827544090621,3.5926006162601065,2.4960250640307904,1.5119224591136864,0.71373931994797413,0.19074115697463637,0,0]
[10,{"$f":"IF($A51<=B$20,MAX(ExercisePrice-B31,(PrUp*C51+PrDown*C52)/(1+RiskfreeRate/250)),\"\")","v":""},"","","","","","","","","",7.0721826541549699,5.8539798492304564,4.6361743399462121,3.4325478928283575,2.2715047260516896,1.2114394052556905,0.37225456712261157,0]
[11,{"$f":"IF($A52<=B$20,MAX(ExercisePrice-B32,(PrUp*C52+PrDown*C53)/(1+RiskfreeRate/250)),\"\")","v":""},"","","","","","","","","","",8.2315650358610242,7.0129603890953973,5.7816382876035277,4.5374660110192906,3.2803094538965212,2.010033111253847,0.72650006396925448]
[12,{"$f":"IF($A53<=B$20,MAX(ExercisePrice-B33,(PrUp*C53+PrDown*C54)/(1+RiskfreeRate/250)),\"\")","v":""},"","","","","","","","","","","",9.3897172321190112,8.1831991535125184,6.9640897565633963,5.7322576372750547,4.4875700203082118,3.2298927446697263]
[13,{"$f":"IF($A54<=B$20,MAX(ExercisePrice-B34,(PrUp*C54+PrDown*C55)/(1+RiskfreeRate/250)),\"\")","v":""},"","","","","","","","","","","","",10.53638244981039,9.3418310603436936,8.1348132378512474,6.9151988816493883,5.6828565333134833]
[14,{"$f":"IF($A55<=B$20,MAX(ExercisePrice-B35,(PrUp*C55+PrDown*C56)/(1+RiskfreeRate/250)),\"\")","v":""},"","","","","","","","","","","","","",11.671674621004598,10.488971230662685,9.2939250539533447,8.0864072805793512]
[15,{"$f":"IF($A56<=B$20,MAX(ExercisePrice-B36,(PrUp*C56+PrDown*C57)/(1+RiskfreeRate/250)),\"\")","v":""},"","","","","","","","","","","","","","",12.795706547751209,11.624733643730039,10.44154037362695]
[16,{"$f":"IF($A57<=B$20,MAX(ExercisePrice-B37,(PrUp*C57+PrDown*C58)/(1+RiskfreeRate/250)),\"\")","v":""},"","","","","","","","","","","","","","","",13.9085899132875,12.749231148318287]
[17,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,15.010435293135558]