{"text_format":"1","workbook":"xlsx","n_sheets":29,"sheets":["Misc","M_BS","Binom","M_Barriers","M_Probs","M_Asian","Interest Rate","Perpetual","Asian","Power","Binaries","Barriers","Rainbow","Exchange","parameters","Compound","Merton Jump","CEV","Fixed Dividends","Black Formula","Black-Scholes+Binomial","Output","To-Dos","Revisions","Copyright","Exchange1","M_Power","M_Perpetual","Sheet1"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":52,"comments":13,"pivot_tables":0},"unopened":[{"sheet":"Misc","index":0,"reason":"part_missing"},{"sheet":"M_BS","index":1,"reason":"part_missing"},{"sheet":"Binom","index":2,"reason":"part_missing"},{"sheet":"M_Barriers","index":3,"reason":"part_missing"},{"sheet":"M_Probs","index":4,"reason":"part_missing"},{"sheet":"M_Asian","index":5,"reason":"part_missing"},{"sheet":"Copyright","index":24,"reason":"part_missing"},{"sheet":"Exchange1","index":25,"reason":"part_missing"},{"sheet":"M_Power","index":26,"reason":"part_missing"},{"sheet":"M_Perpetual","index":27,"reason":"part_missing"}],"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":"Interest Rate","index":6,"n_rows":19,"n_cols":8,"rows_kept":19,"cut":false,"hidden":false,"merged":[[1,4,1,6]]}
[]
[null,null,null,null,"Zero Coupon Bonds ($1 Maturity)"]
[null,"Inputs",null,null,null,"Vasicek","CIR"]
[null,"Vasicek (a(b-r)dt + s dz)",null,null,"Bond Price",{"$f":"vasicek(C5,C6,C7,C8,C17,C18)","v":53.870335058660238},{"$f":"cir(C11,C12,C13,C14,C17,C18)","v":0.55373924743726366}]
[null,"a",0.2,null,"Long-Term Yield",-0.37,2.4781780526283315E-2]
[null,"b",0.08,null,"Interest Rate Delta",-2.644183272745535,-8.5766528098847033E-3]
[null,"Risk Premium",0.05,null,"Interest Rate Gamma",12.978766833831481,1.3284045471174065E-2]
[null,"Volatility",0.2,null,"Yield to Maturity",{"$f":"-LN(F4)/C18","v":-0.19932899782183044},{"$f":"-LN(G4)/C18","v":2.9553068776591758E-2}]
[]
[null,"CIR (a(b-r)dt + s r^.5 dz)"]
[null,"a",0.2,null,null,null,null,{"$f":"EXP(-1)","v":0.36787944117144233}]
[null,"b",0.08,null,null,null,{"$f":"EXP(-0.25)","v":0.77880078307140488}]
[null,"Risk Premium",0.05,null,null,{"$f":"EXP(-0.2)","v":0.81873075307798182}]
[null,"Volatility",0.8,null,null,{"$f":"EXP(-0.1)","v":0.90483741803595952}]
[null,null,null,null,null,{"$f":"EXP(0)","v":1}]
[null,"Other",null,null,null,{"$f":"EXP(0.1)","v":1.1051709180756477}]
[null,"Current Interest Rate",0.08,null,null,null,{"$f":"EXP(0.15)","v":1.1618342427282831}]
[null,"Time to Maturity (years)",20,null,null,null,null,{"$f":"EXP(1)","v":2.7182818284590451}]
[null,null,null,null,{"$f":"EXP(-0.18)","v":0.835270211411272}]

{"sheet":"Perpetual","index":7,"n_rows":8,"n_cols":7,"rows_kept":8,"cut":false,"hidden":false,"merged":[[2,4,2,6]]}
[]
[]
[null,"Inputs",null,null,"Perpetual Options"]
[null,"Stock Price",40,null,null,"Call ","Put"]
[null,"Exercise Price",40,null,"Option Price",{"$f":"CallPerpetual($C$4,$C$5,$C$6,$C$7,$C$8)","v":15.222579271973865},{"$f":"PutPerpetual($C$4,$C$5,$C$6,$C$7,$C$8)","v":8.9091160258598876}]
[null,"Volatility",0.3,null,"Exercise at:",118.37354648979131,21.626453510208702]
[null,"Risk-free interest rate",0.08]
[null,"Dividend Yield",0.05]

{"sheet":"Asian","index":8,"n_rows":18,"n_cols":7,"rows_kept":18,"cut":false,"hidden":false,"merged":[[1,4,1,6],[9,4,9,6],[14,4,14,6]]}
[]
[null,"Inputs",null,null,"Geometric Average Price Asian Options"]
[null,"Stock Price",100,null,null,"Call ","Put"]
[null,"Exercise Price",100,null,"Black-Scholes",{"$f":"BSCall($C$3,$C$4,$C$5,$C$6,$C$7,$C$8)","v":42.011334154008708},{"$f":"BSPut($C$3,$C$4,$C$5,$C$6,$C$7,$C$8)","v":9.0433387575726414}]
[null,"Volatility",0.3,null,"Average Price, Continuous",{"$f":"GeomAvgPriceCall($C$3,$C$4,$C$5,$C$6,$C$7,$C$8)","v":18.071610420591014},{"$f":"GeomAvgPricePut($C$3,$C$4,$C$5,$C$6,$C$7,$C$8)","v":6.2439259260472824}]
[null,"Risk-free interest rate",0.08,null,{"$f":"CONCATENATE(\"Average Price, \",TEXT($C$9,\"0\"),\" Prices\")","v":"Average Price, 20 Prices"},{"$f":"GeomAvgPriceCall($C$3,$C$4,$C$5,$C$6,$C$7,$C$8,$C$9)","v":19.035745918595374},{"$f":"GeomAvgPricePut($C$3,$C$4,$C$5,$C$6,$C$7,$C$8,$C$9)","v":6.4080406246633785}]
[null,"Time to Expiration (years)",5,null,"Average Strike, Continuous",{"$f":"GeomAvgStrikeCall($C$3,$C$10,$C$5,$C$6,$C$7,$C$8)","v":26.760437344345782},{"$f":"GeomAvgStrikePut($C$3,$C$10,$C$5,$C$6,$C$7,$C$8)","v":5.6201264424534401}]
[null,"Dividend Yield",0,null,{"$f":"CONCATENATE(\"Average Strike, \",TEXT($C$9,\"0\"),\" Prices\")","v":"Average Strike, 20 Prices"},{"$f":"GeomAvgStrikeCall($C$3,$C$10,$C$5,$C$6,$C$7,$C$8,$C$9)","v":25.808650285721278},{"$f":"GeomAvgStrikePut($C$3,$C$10,$C$5,$C$6,$C$7,$C$8,$C$9)","v":5.4683601832172144}]
[null,"# Prices in Average",20]
[null,"Moneyness for Avg Strike",1,5,"Arithmetic Avg Asian (simple Monte Carlo)"]
[null,"Number of simulations",1000,null,"Arithmetic Average Price",22.266355512285219,5.6363635785000055]
[null,null,null,null,"Arithmetic Average Strike",23.936425828967987,6.1180536639066156]
[null,null,null,null,"Iteration Number",5000]
[]
[null,null,null,null,"Arithmetic Avg Asian (Control Variate)"]
[null,null,null,null,"Arithmetic Average Price",21.885350978707766,5.7789031499548429]
[null,null,null,null,"Arithmetic Average Strike",23.145966739650635,6.3242163331168211]
[null,null,null,null,"Iteration Number",5000]

{"sheet":"Power","index":9,"n_rows":9,"n_cols":7,"rows_kept":9,"cut":false,"hidden":false,"merged":[[1,4,1,6]]}
[]
[null,"Inputs",null,null,"Power Options"]
[null,"Stock Price",40,null,null,"Call ","Put"]
[null,"Exercise Price",40,null,"Price",{"$f":"bscallpower($C$3,$C$4,$C$5,$C$6,$C$7,$C$8,$C$9)","v":2.7847366578216608},{"$f":"bsputpower($C$3,$C$4,$C$5,$C$6,$C$7,$C$8,$C$9)","v":1.9926835900918718}]
[null,"Volatility",0.3]
[null,"Risk-free interest rate",0.08]
[null,"Time to Expiration (years)",0.25]
[null,"Dividend Yield",0]
[null,"Power",1]

{"sheet":"Binaries","index":10,"n_rows":14,"n_cols":7,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,null,null,null,"Binary Options"]
[null,"Inputs",null,null,null,"Call ","Put"]
[null,"Stock Price",11.52,null,"Black-Scholes",{"$f":"BSCall($C$4,$C$5,$C$6,$C$7,$C$8,$C$9)","v":4.2630712248903766},{"$f":"BSPut($C$4,$C$5,$C$6,$C$7,$C$8,$C$9)","v":7.9416941511674999E-2}]
[null,"Exercise Price",7.79,null,"Cash Binary",{"$f":"CashCall($C$4,$C$5,$C$6,$C$7,$C$8,$C$9)","v":0.85903531859797744},{"$f":"CashPut($C$4,$C$5,$C$6,$C$7,$C$8,$C$9)","v":8.2729214986271268E-2}]
[null,"Volatility",0.3,null,"Asset Binary",{"$f":"AssetCall($C$4,$C$5,$C$6,$C$7,$C$8,$C$9)","v":10.954956356768621},{"$f":"AssetPut($C$4,$C$5,$C$6,$C$7,$C$8,$C$9)","v":0.56504364323137835}]
[null,"Risk-free interest rate",0.06,null,"Up and In Cash",{"$f":"CashUICall($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":2.4006687185527533E-4},{"$f":"CashUIPut($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":-1.3877787807814457E-17}]
[null,"Time to Expiration (years)",1,null,"Up and Out Cash",{"$f":"CashUOCall($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":0.85879525172612214},{"$f":"CashUOPut($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":8.2729214986271282E-2}]
[null,"Dividend Yield",0,null,"Up and In Asset",{"$f":"AssetUICall($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":8.4581693021450501E-3},{"$f":"AssetUIPut($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":-2.2204460492503131E-16}]
[null,"Barrier",35,null,"Up and Out Asset",{"$f":"AssetUOCall($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":10.946498187466476},{"$f":"AssetUOPut($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":0.56504364323137857}]
[null,null,null,null,"Down and In Cash",{"$f":"CashDICall($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":0.85903531859797744},{"$f":"CashDIPut($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":8.2729214986271268E-2}]
[null,null,null,null,"Down and Out Cash",{"$f":"CashDOCall($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":0},{"$f":"CashDOPut($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":0}]
[null,null,null,null,"Down and In Asset",{"$f":"AssetDICall($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":10.954956356768621},{"$f":"AssetDIPut($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":0.56504364323137835}]
[null,null,null,null,"Down and Out Asset",{"$f":"AssetDOCall($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":0},{"$f":"AssetDOPut($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":0}]

{"sheet":"Barriers","index":11,"n_rows":12,"n_cols":7,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,null,null,null,"Barrier Options"]
[null,"Inputs",null,null,null,"Call ","Put"]
[null,"Stock Price",1.071,null,"Black-Scholes",{"$f":"BSCall($C$4,$C$5,$C$6,$C$7,$C$8,$C$9)","v":8.6228886507426661E-2},{"$f":"BSPut($C$4,$C$5,$C$6,$C$7,$C$8,$C$9)","v":2.2348820415246329E-2}]
[null,"Exercise Price",1.091,null,"Up & In",{"$f":"CallUpIn($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":1.5669183122713358E-221},{"$f":"PutUpIn($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":-6.0562665993302288E-17}]
[null,"Volatility",0.115,null,"Up & Out",{"$f":"CallUpOut($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":8.6228886507426661E-2},{"$f":"PutUpOut($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":2.2348820415246388E-2}]
[null,"Risk-free interest rate",0.08,null,"Down & In",{"$f":"CallDownIn($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":8.6228886507426661E-2},{"$f":"PutDownIn($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":2.2348820415246273E-2}]
[null,"Time to Expiration (years)",1,null,"Down & Out",{"$f":"CallDownOut($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":0},{"$f":"PutDownOut($C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":5.5511151231257827E-17}]
[null,"Dividend Yield",0,null,"Up Rebate",{"$f":"UR($C$4,$C$6,$C$7,$C$8,$C$9,$C$10)","v":6.9751655787351971E-223}]
[null,"Barrier",45,null,"Down Rebate",{"$f":"DR($C$4,$C$6,$C$7,$C$8,$C$9,$C$10)","v":1}]
[null,null,null,null,"Deferred Up Rebate",{"$f":"URDeferred($C$4,$C$6,$C$7,$C$8,$C$9,$C$10)","v":3.5553874099857792E-223}]
[null,null,null,null,"Deferred Down Rebate",{"$f":"DRDeferred($C$4,$C$6,$C$7,$C$8,$C$9,$C$10)","v":0.92311634638663576}]

{"sheet":"Rainbow","index":12,"n_rows":16,"n_cols":7,"rows_kept":16,"cut":false,"hidden":false,"merged":[[2,4,2,6]]}
[]
[]
[null,"Inputs",null,null,"Rainbow Option"]
[null,"Asset 1",null,null,null,"Call ","Put"]
[null,"Price",100,null,"Price",{"$f":"rainbowcall(C5,C6, C7,C9,C10,C11,C14,C13,C15,C16)","v":122.43528512266532},{"$f":"rainbowput(C5,C6, C7,C9,C10,C11,C14,C13,C15,C16)","v":88.289282252971759}]
[null,"Volatility",0.25]
[null,"Dividend Yield",0]
[null,"Asset 2"]
[null,"Price",100]
[null,"Volatility",0.25]
[null,"Dividend Yield",0]
[null,"Other"]
[null,"Cash Strike",120]
[null,"Correlation",0.5]
[null,"Risk-free rate",0.05]
[null,"Time to Expiration (years)",1]

{"sheet":"Exchange","index":13,"n_rows":16,"n_cols":7,"rows_kept":16,"cut":false,"hidden":false,"merged":[[11,4,11,6]]}
[]
[null,null,null,null,null,"Exchange Option"]
[null,"Inputs",null,null,null,"Black-Scholes"]
[null,"Underlying Asset",null,null,null,"Call ","Put"]
[null,"Price",40,null,"Price",{"$f":"bscallexchange($C$5,$C$6,$C$7,$C$9,$C$10,$C$11,$C$13,$C$14)","v":2.3914115242315823},{"$f":"bsputexchange($C$5,$C$6,$C$7,$C$9,$C$10,$C$11,$C$13,$C$14)","v":2.3914115242315859}]
[null,"Volatility",0.3]
[null,"Dividend Yield",0,null,null,"Binomial"]
[null,"Strike Asset",null,null,null,"Call ","Put"]
[null,"Price",40,null,"Price",{"$f":"binomcallexchange($C$5,$C$6,$C$7,$C$9,$C$10,$C$11,$C$13,$C$14,$C$16,$C$15)","v":2.3617172593310665},{"$f":"binomputexchange($C$5,$C$6,$C$7,$C$9,$C$10,$C$11,$C$13,$C$14,$C$16,$C$15)","v":2.361717259331066}]
[null,"Volatility",0]
[null,"Dividend Yield",0]
[null,"Other"]
[null,"Correlation",0]
[null,"Time to Expiration (years)",0.25]
[null,"# Binomial Steps",20]
[null,"Type (0=Eur, 1=Amer)",1]

{"sheet":"parameters","index":14,"n_rows":20,"n_cols":4,"rows_kept":20,"cut":false,"hidden":true,"merged":[]}
["description for drop-down list","sheet names","SelectedSheet",5]
["Asian Options","Asian"]
["Barrier Options","Barriers"]
["Binary Options","Binaries"]
["Black Pricing Formula","Black Formula",null,"Currently arrays are defined to 11 entries."]
["Black-Scholes and Binomial","Black-Scholes+Binomial"]
["CEV Pricing","CEV"]
["Compound Options","Compound"]
["Exchange Options","Exchange"]
["Fixed Dividend Pricing","Fixed Dividends"]
["Zero Coupon Bonds","Interest Rate"]
["Merton Jump","Merton Jump"]
["Perpetual Options","Perpetual"]
["Power Options","Power",null,"The array \"sheetlistarray\" contains names of spreadsheets"]
["Rainbow Options","Rainbow",null,"The array \"sheetlistdescription\" contains descriptions for drop-down boxes"]
[null,null,null,"The macro \"gotosheet\" selects an entry from sheetlistarray based on value returned from sheetlistdescription"]
[]
[null,null,null,"This sheet will ultimately be hidden, along with revisions sheet"]
[]
[null,null,null,"If adding sheets to drop-down lists, be sure to expand sheetlistarray and sheetlistdescription"]

{"sheet":"Compound","index":15,"n_rows":10,"n_cols":6,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Inputs",null,null,"Compound Option Prices"]
[null,"Stock Price",40,null,"Call on Call",{"$f":"CallOnCall($C$3,$C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":3.4170318464897944}]
[null,"Exercise Price to buy asset",41,null,"Put on Call",{"$f":"PutOnCall($C$3,$C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":0.32097679169363857}]
[null,"Exercise Price to buy option",1.75,null,"Call on Put",{"$f":"CallOnPut($C$3,$C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":2.083901146242523}]
[null,"Volatility",0.3,null,"Put on Put",{"$f":"PutOnPut($C$3,$C$4,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10)","v":0.37550021449217419}]
[null,"Risk-free interest rate",0.08,null,"Critical S for compound call",{"$f":"BSCallImpS($C$3,$C$4,$C$6,$C$7,$C$9-$C$8,$C$10,$C$5)","v":37.360711886137921}]
[null,"Expiration for Option on Option (years)",0.38,null,"Critical S for compound put",{"$f":"BSPutImpS($C$3,$C$4,$C$6,$C$7,$C$9-$C$8,$C$10,$C$5)","v":42.762570071602227}]
[null,"Expiration for Underlying Option (years)",0.75]
[null,"Dividend Yield",0]

{"sheet":"Merton Jump","index":16,"n_rows":11,"n_cols":8,"rows_kept":11,"cut":false,"hidden":false,"merged":[[1,6,1,7],[1,1,1,2]]}
[]
[null,"Inputs",null,null,null,null,"Premiums"]
[null,"Stock Price",101,null,null,null,"Call","Put"]
[null,"Strike Price",100,null,null,"Black-Scholes (no-jump)",{"$f":"BSCall($C$3,$C$4,$C$5,$C$6,$C$7,$C$8)","v":16.378898945232919},{"$f":"BSPut($C$3,$C$4,$C$5,$C$6,$C$7,$C$8)","v":7.6905335838964959}]
[null,"Volatility (diffusion)",0.3,null,null,"Jump",{"$f":"MertonJump(C3,C4,C5,C6,C7,C8,C9,C10,C11)","v":16.44685685116729},7.7584914898308739]
[null,"Risk-free Rate",0.08,null,null,"Implied Volatility from jump price",{"$f":"BSCallImpVol($C$3,$C$4,$C$5,$C$6,$C$7,$C$8,G5)","v":0.30186577083653582},{"$f":"BSPutImpVol($C$3,$C$4,$C$5,$C$6,$C$7,$C$8,H5)","v":0.30186577083653598}]
[null,"Time to Expiration",1]
[null,"Dividend Yield",0]
[null,"Jump frequency (%/year)",0.02]
[null,"Expected ln(jump)",-0.15]
[null,"Volatility of ln(jump)",0.2]

{"sheet":"CEV","index":17,"n_rows":12,"n_cols":6,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Inputs",null,null,"Call","Put"]
[null,"Stock Price",40,"Black-Scholes Price",{"$f":"BSCall($C$3,$C$4,$C$12,$C$6,$C$7,$C$8)","v":9.1917562434997322},{"$f":"BSPut($C$3,$C$4,$C$12,$C$6,$C$7,$C$8)","v":1.5008283670319855}]
[null,"Exercise Price",35,"CEV Price",{"$f":"cevcall($C$3,$C$4,$C$5,$C$6,$C$7,$C$8,$C$9)","v":9.3069846079756502},{"$f":"cevput($C$3,$C$4,$C$5,$C$6,$C$7,$C$8,$C$9)","v":1.6160567315079035}]
[null,"Volatility Parameter",1.897367,"Implied Volatility for CEV Price",{"$f":"BSCallImpVol($C$3,$C$4,0.1,$C$6,$C$7,$C$8,$E4)","v":0.31038347152024492},{"$f":"BSPutImpVol($C$3,$C$4,0.1,$C$6,$C$7,$C$8,$F4)","v":0.31038347152024492}]
[null,"Risk-free interest rate",0.08]
[null,"Time to Expiration (years)",1]
[null,"Dividend Yield",0]
[null,"Beta",1]
[]
[]
[null,"Observed Volatility Implied by CEV Parameters",{"$f":"C5*C3^((C9-2)/2)","v":0.30000006386203487}]

{"sheet":"Fixed Dividends","index":18,"n_rows":18,"n_cols":7,"rows_kept":18,"cut":false,"hidden":false,"merged":[[1,4,1,6],[11,4,11,6],[13,1,13,2]]}
[]
[null,"Inputs",null,null,"Black-Scholes (European)"]
[null,"Stock Price",100,null,null,"Call ","Put"]
[null,"Exercise Price",100,null,"Price",{"$f":"BSCall($C$18,$C$4,$C$5,$C$6,$C$7,0)","v":5.2885926982951368},{"$f":"BSPut($C$18,$C$4,$C$5,$C$6,$C$7,0)","v":13.22890318158899}]
[null,"Volatility",0.23219999999999999,null,"Delta",{"$f":"BSCallDelta($C$18,$C$4,$C$5,$C$6,$C$7,0)*C18/C3","v":0.36228102962682507},{"$f":"BSPutDelta($C$18,$C$4,$C$5,$C$6,$C$7,0)*(C18/C3)","v":-0.53851453884699163}]
[null,"Risk-free interest rate",0.02,null,"Gamma",{"$f":"BSCallGamma($C$18,$C$4,$C$5,$C$6,$C$7,0)*(C18/C3)^2","v":1.5008931088807919E-2},{"$f":"BSPutGamma($C$18,$C$4,$C$5,$C$6,$C$7,0)*(C18/C3)^2","v":1.5008931088807919E-2}]
[null,"Time to Expiration (years)",1,null,"Vega",{"$f":"BSCallVega($C$18,$C$4,$C$5,$C$6,$C$7,0)","v":0.34850737988211983},{"$f":"BSPutVega($C$18,$C$4,$C$5,$C$6,$C$7,0)","v":0.34850737988211983}]
[null,"Fixed dividend amount",5,null,"Theta",{"$f":"BSCallTheta($C$18,$C$4,$C$5,$C$6,$C$7,0)","v":-1.2780714755394957E-2},{"$f":"BSPutTheta($C$18,$C$4,$C$5,$C$6,$C$7,0)","v":-7.4097631208373937E-3}]
[null,"Time until first dividend",0.15,null,"Rho",{"$f":"BSCallRho($C$18,$C$4,$C$5,$C$6,$C$7,0)","v":0.30939510264387371},{"$f":"BSPutRho($C$18,$C$4,$C$5,$C$6,$C$7,0)","v":-0.67080357066288154}]
[null,"Time Between Dividends",0.5,null,"Elasticity",{"$f":"BSCallElast($C$18,$C$4,$C$5,$C$6,$C$7,0)","v":6.8502350302683022},{"$f":"BSPutElast($C$18,$C$4,$C$5,$C$6,$C$7,0)","v":-4.0707421579474277}]
[null,"# Binomial steps",120]
[null,"Type (0=Eur, 1=Amer)",1,null,{"$f":"CONCATENATE(\"Binomial \",IF(OpType=0,\"European\",\"American\"))","v":"Binomial American"}]
[null,null,null,null,null,"Call ","Put"]
[null,"Dividend Information",null,null,"Price",{"$f":"binomoptfixeddivcall(C3,C4,C5,C6,C7,C8,C10,C9,C12,C11)","v":5.9912465649258202},{"$f":"binomoptfixeddivput(C3,C4,C5,C6,C7,C8,C10,C9,C12,C11)","v":13.468763214172863}]
[null,"Number of dividends",null,null,"Delta",0.43314302781030944,-0.55069438611255705]
[null,"Over Life of Option",{"$f":"IF(C9>=C7,0,1+TRUNC((C7-C9)/C10))","v":2},null,"Gamma",2.1286115739810207E-2,1.5391574684094744E-2]
[null,"PV(Dividends)",{"$f":"IF(C16>0,EXP(-C9*C6)*C8*(1+(EXP(C6*C10)-1)^(-1)*(1-EXP(-C6*(C16-1)*C10))),0)","v":9.9204431526183292},null,"Theta",-1.7777481347589358E-2,-7.6193881382324892E-3]
[null,"Prepaid forward price",{"$f":"C3-C17","v":90.079556847381667}]

{"sheet":"Black Formula","index":19,"n_rows":17,"n_cols":9,"rows_kept":17,"cut":false,"hidden":false,"merged":[[1,4,1,6],[11,4,11,6]]}
[]
[null,null,null,null,"Black Formula (European)"]
[null,"Inputs",null,null,null,"Call ","Put",null,"Format string (do not delete)"]
[null,"Futures Price",41,null,{"$f":"black_text_greek(I4)","v":"Price"},{"$f":"blackformula(C4,C5,C6,C7,C8,I4)","v":2.8954816022834251},1.9152829289766728,null,{"$f":"\"CP+PP/CD+PD/CG+PG/CV+PV/CT+PT/CR+PR/CE+PE\"","v":"CP+PP/CD+PD/CG+PG/CV+PV/CT+PT/CR+PR/CE+PE"}]
[null,"Exercise Price",40,null,"Delta",0.58291098642767591,-0.39728768687907934]
[null,"Volatility",0.3,null,"Gamma",6.1784740137378731E-2,6.1784740137378731E-2]
[null,"Risk-free interest rate",0.08,null,"Vega",7.7895111128200231E-2,7.7895111128200231E-2]
[null,"Time to Expiration (years)",0.25,null,"Theta",-1.2170049697285858E-2,-1.2384887762668162E-2]
[null,"# Binomial steps",4,null,"Rho",-7.2387040057085641E-3,-4.7882073224416802E-3]
[null,"Type (0=Eur, 1=Amer)",0,null,"Elasticity",8.254015644474233,-8.5046417506291263]
[]
[null,"Implied Volatility",null,null,{"$f":"CONCATENATE(\"Binomial \",IF(OpType=0,\"European\",\"American\"))","v":"Binomial American"}]
[null,"Observed Call Price",2.4,null,null,"Call ","Put"]
[null,"Call Implied Volatility",{"$f":"BSCallImpVol(C4,C5,C6,C7,C8,C7,C13)","v":0.23619353567956042},null,"Price",{"$f":"BinomCall(C4,C5,C6,C7,C8,C7,C10,C9)","v":2.9042942430877177},{"$f":"BinomPut(C4,C5,C6,C7,C8,C7,C10,C9)","v":1.9240955697809634}]
[null,"Observed Put Price",3,null,"Delta",0.57950494346277026,-0.40069372984398538]
[null,"Put Implied Volatility",{"$f":"BSPutImpVol(C4,C5,C6,C7,C8,C7,C15)","v":0.43886623450472445},null,"Gamma",7.2401911183797074E-2,7.2401911183796838E-2]
[null,null,null,null,"Theta",-1.4447580389503624E-2,-1.4663496234816727E-2]

{"sheet":"Black-Scholes+Binomial","index":20,"n_rows":18,"n_cols":7,"rows_kept":18,"cut":false,"hidden":false,"merged":[[1,4,1,6],[12,4,12,6]]}
[]
[null,null,null,null,"Black-Scholes (European)"]
[null,"Inputs",null,null,null,"Call ","Put"]
[null,"Stock Price",100,null,"Price",{"$f":"BSCall(stock,exer,vol,rint,expire,divd)","v":24.410251007373983},{"$f":"BSPut(stock,exer,vol,rint,expire,divd)","v":17.676660730177232}]
[null,"Exercise Price",120,null,"Delta",{"$f":"BSCallDelta(stock,exer,vol,rint,expire,divd)","v":0.43986129342589914},{"$f":"BSPutDelta(stock,exer,vol,rint,expire,divd)","v":-0.16666936628673429}]
[null,"Volatility",0.3,null,"Gamma",{"$f":"BSCallGamma(stock,exer,vol,rint,expire,divd)","v":2.1324981709572176E-3},{"$f":"BSPutGamma(stock,exer,vol,rint,expire,divd)","v":2.1324981709572176E-3}]
[null,"Risk-free interest rate",0.08,null,"Vega",{"$f":"BSCallVega(stock,exer,vol,rint,expire,divd)","v":0.63974945128716543},{"$f":"BSPutVega(stock,exer,vol,rint,expire,divd)","v":0.63974945128716543}]
[null,"Time to Expiration (years)",10,null,"Theta",{"$f":"BSCallTheta(stock,exer,vol,rint,expire,divd)","v":-8.9421363457130602E-4},{"$f":"BSPutTheta(stock,exer,vol,rint,expire,divd)","v":2.6151144666948871E-3}]
[null,"Dividend Yield",0.05,null,"Rho",{"$f":"BSCallRho(stock,exer,vol,rint,expire,divd)","v":1.957587833521593},{"$f":"BSPutRho(stock,exer,vol,rint,expire,divd)","v":-3.4343597358850655}]
[null,"# Binomial steps",10,null,"Psi",{"$f":"BSCallPsi(stock,exer,vol,rint,expire,divd)","v":-4.398612934258991},{"$f":"BSputPsi(stock,exer,vol,rint,expire,divd)","v":1.6666936628673428}]
[null,"Type (0=Eur, 1=Amer)",1,null,"Elasticity",{"$f":"BSCallElast(stock,exer,vol,rint,expire,divd)","v":1.8019531765282684},{"$f":"BSPutElast(stock,exer,vol,rint,expire,divd)","v":-0.94287811952061606}]
[]
[null,"Implied Volatility",null,null,{"$f":"CONCATENATE(\"Binomial \",IF(OpType=0,\"European\",\"American\"))","v":"Binomial American"}]
[null,"Observed Call Price",5,null,null,"Call ","Put"]
[null,"Call Implied Volatility",{"$f":"BSCallImpVol(stock,exer,vol,rint,expire,divd,C14)","v":{"$error":"#VALUE!"}},null,"Price",{"$f":"BinomCall(stock,exer,vol,rint,expire,divd,OpType,n)","v":27.401442692469818},{"$f":"BinomPut(stock,exer,vol,rint,expire,divd,OpType,n)","v":30.375113662906024}]
[null,"Observed Put Price",3,null,"Delta",0.51627070128482111,-0.38313028630861395]
[null,"Put Implied Volatility",{"$f":"BSPutImpVol(stock,exer,vol,rint,expire,divd,C16)","v":7.9872991524263012E-2},null,"Gamma",3.4458292234140894E-3,7.8035294063885209E-3]
[null,null,null,null,"Theta",-2.8739575415743517E-3,-1.0301538548794377E-3]

{"sheet":"Output","index":21,"n_rows":23,"n_cols":11,"rows_kept":23,"cut":false,"hidden":false,"merged":[]}
["Time (yrs)",1,2,3,4,5,6,7,8,9,10]
[100.00000000000001,139.09681284637804,193.47923344020319,269.12344723492629,374.34213772608632,520.69798271798493,724.27429851610123,1007.4424655013586,1401.3203607733612,1949.1919596031116,2711.2638920657887]
[27.401442692469818,49.250160138775598,86.815450230508674,150.03471839340079,254.34213772608632,400.69798271798493,604.27429851610123,887.44246550135858,1281.3203607733612,1829.1919596031116,2591.2638920657887]
[null,76.337949433685296,106.18365465453593,147.6980793882642,205.4433210643887,285.76511180631627,397.49016274947468,552.89614776240023,769.06091988789956,1069.7392284111049,1487.9731724872831]
[null,15.188385354090256,28.561773505838421,52.56863215196644,94.515062080912571,165.76511180631627,277.49016274947468,432.89614776240023,649.06091988789956,949.73922841110493,1367.9731724872831]
[null,null,58.274825237398957,81.0584245970187,112.74968515793755,156.83121854901685,218.14722654982006,303.43583944356749,422.0695816996552,587.08533613826,816.61699125676489]
[null,null,7.4832639621956929,14.918159475826508,29.115754742311307,55.435243283810188,102.53784593131752,183.43583944356749,302.0695816996552,467.08533613826,696.61699125676489]
[null,null,null,44.485806622294099,61.878339180614063,86.070797642505752,119.72173631218098,166.52911949458857,231.63669767810907,322.19926385284987,448.16890703380631]
[null,null,null,3.0603387152939798,6.5632710220239847,13.83922278294127,28.578202372272798,57.473859121265434,111.63669767810907,202.19926385284987,328.16890703380631]
["American Call",null,null,null,33.959552564493912,47.236655274101466,65.704681981505672,91.393118527122809,127.12491503214045,176.82670514337349,245.96031111569494]
["Strike =  120",null,null,null,0.90901393975103584,2.1247065934245999,4.9268344682247145,11.315259613078178,25.68204091502243,57.42880340349231,125.96031111569494]
["Vol = 30.000%; r = 8.000%",null,null,null,null,25.924026064589142,36.05949401730782,50.157606906605537,69.767632607103081,97.044553354850777,134.98588075760026]
["Exp =  10 years; Div = 5.000%",null,null,null,null,0.14020246151717253,0.35689541229505517,0.9085028460905884,2.3126590954112274,5.8870394458345805,14.985880757600256]
["u = 1.3910; d = 0.7634",null,null,null,null,null,19.789869908361464,27.527078308975227,38.289288597511195,53.259180100689711,74.081822068171775]
["Risk-neutral prob of up = 0.4256    ",null,null,null,null,null,0,0,0,0,0]
["Forward tree",null,null,null,null,null,null,15.107180883637078,21.013607120076465,29.229257768085926,40.656965974059894]
[null,null,null,null,null,null,null,0,0,0,0]
[null,null,null,null,null,null,null,null,11.532512103806249,16.041356777517272,22.313016014842976]
[null,null,null,null,null,null,null,null,0,0,0]
[null,null,null,null,null,null,null,null,null,8.8036832582372515,12.245642825298184]
[null,null,null,null,null,null,null,null,null,0,0]
[null,null,null,null,null,null,null,null,null,null,6.7205512739749729]
[null,null,null,null,null,null,null,null,null,null,0]

{"sheet":"To-Dos","index":22,"n_rows":18,"n_cols":2,"rows_kept":18,"cut":false,"hidden":true,"merged":[]}
["Done","Need to add Psi to the greeks output. "]
["Done","Need to add a field to compute options on futures. This will make rho correct. (should we do this, or add a function??)"]
["Done","Pricing for fixed dividends"]
["Done","Add Black formula spreadsheet"]
["Done","Add CEV calculation"]
["Done","Add Merton Jump calculation"]
[]
[]
[]
[null,"Second edition sheets (first printing) based on options6_05.xls"]
[]
[null,"Still To Do as of version 6_02"]
[]
[null,"Need to add a field to easily permit CRR option output."]
[null,"Look into adding simulation capability (for example, specify a function to be simulated)"]
[null,"Add functions for American implied volatility (with fixed dividends?)."]
[null,"Black Pricing formula for fixed income."]
[null,"Add bond equivalent yields to Black calculation"]

{"sheet":"Revisions","index":23,"n_rows":40,"n_cols":2,"rows_kept":40,"cut":false,"hidden":true,"merged":[]}
["1999-06-01","Fixed at least some of the problems with barrier options"]
["1999-06-01","Introduced \"binomcrr\" function to price as in Cox, Ross, Rubinstein. Regular binom uses forward tree."]
["2000-01-01","Cleaned up barrier calculations, formulas now conform to my presentation."]
["2000-01-01","Cleaned up Asian options, now uses exclusively formulas I've derived"]
["2000-02-01","Added deferred rebate formulas (4l)"]
["2000-02-01","Fixed binomial greeks for cases where u*d <> 1 (4l)"]
["2000-06-01","Added \"TreeType\" public constant when graphing binomial trees (4l)"]
["2000-08-01","Added horiz output orientation to binomial and perpetual array functions (4l)"]
["2000-10-01","Added monte carlo calculation of arithmetic asian options (4m)"]
["2000-10-01","Fixed problem with BSCallImpS and BSPutImpS where small deltas would cause negative stock prices in iteration."]
["2001-05-01","Fixed bscallimpvol problem with small starting vols. Made bsputimpvol a function of bscallimpvol."]
["2001-10-20","Version N"]
["2001-10-20","Improved input descriptions, formatted percents as such"]
["2001-10-21","Navigation drop-downs"]
["2001-11-07","Fixed perpetual formula when in exercise region (02)"]
["2001-12-15","version O"]
["2001-12-15","Rainbow options"]
["2001-12-27","CIR and Vasicek functions"]
["2002-04-20","Version P"]
["2002-04-21","Changed def'n of rho to match formula in chapter 12"]
["2002-05-11","Fixed parameter order for rainbow options"]
["2002-05-11","Set default print areas to avoid needless 2nd page"]
["2002-05-11","Version Q"]
["2002-05-11","Added control variate pricing of asian options"]
["2002-05-11","Removed bermudan example"]
["2002-05-11","Added protection to all sheets (only formula and text cells)"]
["2002-05-11","Shipped optall.xls"]
["2002-06-12","version 5"]
["2002-06-12","Changed overflow check in m_bs from sig^2*t to sig*sqr(t)"]
["2002-06-21","Fixed CIR gamma"]
["Revision A"]
["2007-02-27","Fixed error in CEV function (cevcall=s^exp(-a*t))"]
["Revision B"]
["2007-12-19","Fixed error in CIR function (sign on risk premium in solution)"]
["Revision C"]
["2008-04-15","Fixed error in Vasicek function when a=0 (sign of first term in exponent should be negative)."]
[]
["OPTALL3"]
["2013-11-27","Fixed error in perpetual options (needless evaluatoin of option price would for extreme parameter values cause numerical overflow"]
[null,"Fixed error in binomial, comparing rhat to up and down, rather than ahat"]

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