ࡱ>   {~  !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz|}~Root Entry FM=ZWorkbookr_VBA_PROJECT_CUR"popMVBAp u'ɀ Worksheet"&Tools&WindowChart"&Tools&Window(Visual Basic Module"&Tools&Window\pRobert McDonald Ba=8     ThisWorkbook=JM:*8X@"1Arial1Arial1Arial1Arial1Arial1Arial1Arial1 Arial1Arial1 Arial1 Arial1Arial1QTahoma1Tahoma1Arial1Arial1QTahoma"$"#,##0_);\("$"#,##0\)!"$"#,##0_);[Red]\("$"#,##0\)""$"#,##0.00_);\("$"#,##0.00\)'""$"#,##0.00_);[Red]\("$"#,##0.00\)7*2_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_).))_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)?,:_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)6+1_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_) 0.0% mm/dd/yy 0.000% 0.0000% 0.00000% 0.000000% 0.000000000 0.00000000 0.0000000 0.000000 0.00000 0.0000 0.000                + ) , *                "@ @  "@   " @     @ "@   @ " @   ""@ @  "   & &  "   @     #    @   # &    #"@ @ #"@  #" @  & "  @@   @  @     @    #  @     # & "  A "@   A " @  ! #  "@ @  "@  @ "@  @ " @   #"@ @  #"@   #" @  #  #@@  "@ @  #@  "@   # @   " @  !@@ !@  @ #"@@   "@@   " @  #""@ @   "@  &  " @  @  & "  @@   @ A @ A   @   A  @  A  @ "@ @  @    @@   @  @     @     @        @ "@  #X #@@ #@ # @  #P "X #p@@  `  @  H #p@  `   `@@  `@  ` @   `  #p @   `"@ @  `"@   `" @  !P #p  `  ` " @  #x  h +p""@ @   `  @  `   `  " " "  " "X "  8 ``i̜̙3f3333f3ffff333ff333f33f33BBB\I8jzLOyV#aXw rmcd - Personal ViewT8 Q1-S\JB|$/%'Xw Robert McDonald - Personal View` BMisc DM_BS $FBinomG M_BarriersNIM_ProbsJM_AsianxL Interest Rated\ Perpetual boAsian gPowerxBinaries[BarriersRainbowExchangeg parametersCompound Merton Jump ACEV/Fixed DividendsZ Black FormulayBlack-Scholes+BinomialUOutputTo-Dos Revisions7 Copyright Exchange14M_Power M_Perpetual>   ArithmeticAsians! ArithmeticAsiansCV# asian_aapcall: %asian_aapcallcv:" asian_aapput: $asian_aapputcv:# asian_aascall: %asian_aascallcv:" asian_aasput: $asian_aasputcv:%asian_appcallcv:$asian_appputcv: asian_div:# asian_gapcall: " asian_gapput: # asian_gascall: " asian_gasput: # asian_iterout: %asian_iteroutcv:asian_k:%asian_moneyness: " asian_numavg:# asian_numiter: asian_r:asian_s:asian_t: asian_vol: AssetCall AssetDICall AssetDIPut AssetDOCall AssetDOPutAssetPut AssetUICall AssetUIPut AssetUOCall AssetUOPut BinomCall binomcallexchange)Binomial_Call_Delta< )Binomial_Call_Gamma< )Binomial_Call_Price< #binomoptfixeddivcall"binomoptfixeddivputBinomPutbinomputexchangeblack_text_greek blackformulaBSCall BSCallDelta BSCallElastbscallexchange BSCallGamma BSCallImpS BSCallImpVol bscallpower BSCallPsi BSCallRho BSCallTheta BSCallVegaBSPut BSPutDelta BSPutElast bsputexchange BSPutGamma BSPutImpS BSPutImpVol bsputpowerBSputPsiBSPutRho BSPutTheta BSPutVega CallDownIn CallDownOut CallOnCall CallOnPut CallPerpetualCallUpIn CallUpOutCashCall CashDICall CashDIPut CashDOCall CashDOPutCashPut CashUICall CashUIPut CashUOCall CashUOPutcevcallcevputcirdivd:DR DRDeferredexer:expire:GeomAvgPriceCallGeomAvgPricePut GeomAvgStrikeCallGeomAvgStrikePut gotosheet GraphBinomCall GraphBinomPut MertonJumpn: OpStyle: OpType:   ;  ;  ;  ;  ;   ;  ;  ; PutDownIn PutDownOut PutOnCallPutOnPut PutPerpetualPutUpInPutUpOut rainbowcall rainbowput ReturnToBSSheetrint:# selectedsheet: sheetlist:( sheetlistarray;. sheetlistdescription;stock:testout:treetype:UR URDeferredvasicekVest< vol:TDg       3  @@  wzInputs Stock PriceCall PutExercise Price VolatilityRisk-free interest rateDividend YieldPerpetual Options Option Price Exercise at:Barrier OptionsPricePowerUp & InUp & Out Down & In Down & Out Up RebateBarrier Down RebateExchange Option Black-ScholesBinomial Strike AssetOther Correlation# Binomial StepsType (0=Eur, 1=Amer)DeltaGammaVegaThetaRho# Binomial steps ElasticityObserved Call PriceCall Implied VolatilityObserved Put PricePut Implied Volatility Time (yrs)Underlying AssetImplied Volatility8Fixed at least some of the problems with barrier optionsExercise Price to buy assetExercise Price to buy option Call on Call Put on Call Call on Put Put on PutCritical S for compound callCritical S for compound putICleaned up barrier calculations, formulas now conform to my presentation.# Prices in AverageAverage Price, Continuous%Geometric Average Price Asian OptionsAverage Strike, ContinuousMoneyness for Avg StrikeCompound Option PricesDCleaned up Asian options, now uses exclusively formulas I've derived Cash Binary Asset BinaryUp and In CashUp and Out CashUp and In AssetUp and Out AssetDown and In AssetDown and Out AssetDown and In CashDown and Out CashBinary OptionsDeferred Up RebateDeferred Down Rebate#Added deferred rebate formulas (4l)3Fixed binomial greeks for cases where u*d <> 1 (4l) Forward treeBAdded "TreeType" public constant when graphing binomial trees (4l)MAdded horiz output orientation to binomial and perpetual array functions (4l)Arithmetic Average PriceNumber of simulationsArithmetic Average StrikeIteration Number>Added monte carlo calculation of arithmetic asian options (4m)nFixed problem with BSCallImpS and BSPutImpS where small deltas would cause negative stock prices in iteration.aFixed bscallimpvol problem with small starting vols. Made bsputimpvol a function of bscallimpvol.description for drop-down list sheet names SelectedSheet Asian OptionsAsianBarriersBinariesBlack-Scholes and Binomial+Currently arrays are defined to 11 entries.Compound OptionsCompoundExchange OptionsExchange Perpetual Power Options9The array "sheetlistarray" contains names of spreadsheetsJThe array "sheetlistdescription" contains descriptions for drop-down boxeslThe macro "gotosheet" selects an entry from sheetlistarray based on value returned from sheetlistdescription@This sheet will ultimately be hidden, along with revisions sheetBlack-Scholes+Binomial^If adding sheets to drop-down lists, be sure to expand sheetlistarray and sheetlistdescription Version N7Improved input descriptions, formatted percents as suchNavigation drop-downsTime to Expiration (years)'Expiration for Option on Option (years)(Expiration for Underlying Option (years)4Fixed perpetual formula when in exercise region (02) version ORainbow optionseIntroduced "binomcrr" function to price as in Cox, Ross, Rubinstein. Regular binom uses forward tree.Rainbow OptionRainbow OptionsRainbowRisk-free rateAsset 1Asset 2 Cash StrikeVasicekCIR and Vasicek functionsCIR (a(b-r)dt + s r^.5 dz)Vasicek (a(b-r)dt + s dz)abTime to Maturity (years) Risk PremiumCIR Bond PriceZero Coupon BondsYield to MaturityCurrent Interest RateZero Coupon Bonds ($1 Maturity) Interest Rate Strike = 40 Version P)Fixed parameter order for rainbow options2Set default print areas to avoid needless 2nd page)Arithmetic Avg Asian (simple Monte Carlo)&Arithmetic Avg Asian (Control Variate).Added control variate pricing of asian options3Changed def'n of rho to match formula in chapter 12 Version QLong-Term YieldInterest Rate DeltaInterest Rate GammaBlack-Scholes (European)Removed bermudan example<Added protection to all sheets (only formula and text cells)Vol = 30.000%; r = 8.000% European Call version 5Shipped optall.xls9Changed overflow check in m_bs from sig^2*t to sig*sqr(t)Fixed CIR gamma&Need to add Psi to the greeks output. wNeed to add a field to compute options on futures. This will make rho correct. (should we do this, or add a function??)XLook into adding simulation capability (for example, specify a function to be simulated)7Need to add a field to easily permit CRR option output.Add Black formula spreadsheetPsiAdd CEV calculationAdd Merton Jump calculation Strike PriceVolatility (diffusion)Risk-free RateTime to ExpirationPremiumsCallJumpCEV Futures PriceBlack Formula (European)Format string (do not delete)Fixed dividend amountTime until first dividendTime Between DividendsNumber of dividends PV(Dividends)Prepaid forward priceDividend InformationOver Life of OptionJump frequency (%/year)Volatility of ln(jump)Expected ln(jump) Merton Jump CEV Pricing Black FormulaBlack Pricing FormulaFixed Dividend PricingFixed DividendsBetaVolatility ParameterBlack-Scholes Price CEV Price Implied Volatility for CEV Price-Observed Volatility Implied by CEV Parameters"Implied Volatility from jump priceDoneBlack-Scholes (no-jump)FAdd functions for American implied volatility (with fixed dividends?).Pricing for fixed dividends'Black Pricing formula for fixed income.Still To Do as of version 6_02/Add bond equivalent yields to Black calculationExp = .25 years; Div = 0.000%u = 1.0833; d = 0.9324$Risk-neutral prob of up = 0.4813 ?Second edition sheets (first printing) based on options6_05.xls+ ,z,,MP--?. /|01za23H|45T ;7 68 8 <9 ?:&;)<=>r>8u?@~CA u'ɀ&A Page &P"PD& In??wn> P@jzLOyV? In Down & Out Up Rebate&A Page &P"POyV??@Q1-S\JB|$/? In Down & Out Up Rebate&A Page &P"P|$/?? u'ɀ&A Page &P"P|$/??> P@jzLOyV? In Down & Out Up Rebate&A Page &P"POyV??@Q1-S\JB|$/? In Down & Out Up Rebate&A Page &P"P|$/?? u'ɀ&A Page &P"P|$/??> P@jzLOyV? In Down & Out Up Rebate&A Page &P"POyV??@Q1-S\JB|$/? In Down & Out Up Rebate&A Page &P"P|$/?? u'ɀ&A Page &P"P|$/??> P@jzLOyV? In Down & Out Up Rebate&A Page &P"POyV??@Q1-S\JB|$/? In Down & Out Up Rebate&A Page &P"P|$/?? u'ɀ&A Page &P"P|$/??> P@jzLOyV? In Down & Out Up Rebate&A Page &P"POyV??@Q1-S\JB|$/? In Down & Out Up Rebate&A Page &P"P|$/?? u'ɀ&A Page &P"P|$/??> P@jzLOyV? In Down & Out Up Rebate&A Page &P"POyV??@Q1-S\JB|$/? In Down & Out Up Rebate&A Page &P"P|$/?? u'ɀ PY  dMbP?_*+%MHP LaserJet 4/4M PSS odXXLetterPRIV0''''\KhCj"dXX??U} "} "}  "} I"} $"}  "} "           @  @ @  @       EE   3#/ 4{ 4E T~& 0a!̳R-? !5'#$$$$$$Bbj5S? !5'#\$ $ $ $ $$BE 0~ 4@ 1c d?  1~ $@ 1eu f2--  1~  1eJdd? f΄?  2~ $@ 2%gDݭ? xDDAD%hg[xԕ? DAD E7' -!* E U} ' *!! E 0~ @ *!! E 1~ $@ -** 1~  -** 2 S㥛? -** 7' -** V' *** J~ i@ *** 2~ ]? *!! -* -** ** ******************-********************************8 &>rhh| 266,0(,,**0(  0Nd ds 03 <] ` vv 0 6\A@dv]\   < Go to Sheet:<= d\ 0 c $@xs]t D D\9f \# \#>@<d 7 Sheet14 u'ɀ ae  dMbP?_*+%MHP LaserJet 4M Plus?dXXLetter.HP LaserJet 4M PlusP181812sxT[n0 1?eUKזs?bH=,n79D Ӷǵ㼕@k6(h!0$E0[혈m.)^4: vv{<;ȯ8AV: NH w[ ~ :} (H,U߫C鏬h,<:bwf-+غt{9-w+rCJTÐrZN-ʧ4gT1h^"=#w.8 #w/ˏ]l֫BN-['ڨq֗~RLbH"dXX??U} m"} "}  "} "} "} $ "                       _\   J~ FD@= ` ` K~ GD@ J <m %@ !0"#M$$$$$B6m %@ !0"#y$$$$$B K~ H>@ L >T@ :M53@ E K~ H @* L~ I @**** **** * * ** % ***j4Bh&.  E (  x  6\XPP? i ]4@\L b`{8Iv -<.Continuously compounded annual dividend yield< -pentNd ds 3 / a ] `vv  6@\A@dv]@\  < Go to Sheet:<= d\  c $@xs]t H% %x\9f x\# x\#xx  6\XPP? r ]4@\ )* BcC̑X ,<-Continuously compounded annual interest rate< ,DyRobert McDonaldnRobert McDonaldn>@<d@jzLOyV d@<! MHP LaserJet 4/4M Plus PS 600W odXLetterhese are formulas for newPRIV''''?"dX??@Q1-S\JB|$/ d@<. MNHP LaserJet 4M PluspsXXLetter 7 6''''"dXX?? 7 Sheet2 u'ɀ s'~  dMbP?_*+%&A Page &PMHP LaserJet 4/4M PSS odXXLetterPRIV0''''\KhCj"dXX??U} m"} I"} I "} I"} "} m"} "} "                         ******** _ \* 7 * N~ FY@ *= ` `* P~ GY@* J=<DsE@ '#1$$$$$$B=6]E1"@ E'#=$$$$$$B* P~ H>@* K6==ׇU2@ '#b$$$$$$B=8 1@ '#c$$$$$$B* P~ H @*BK|,Average Price, D0A0 PricesBPAverage Price, 20 PricesB=| &' 3@ ,#b$$$$$$$BB8Zmס@ ,#c$$$$$$$B* Pm~ G@* K8==]:@ '#d$$ $$$$B=8!<{@ '#e$$ $$$$B** P~ H*CL|-Average Strike, D0A0 PricesBPAverage Strike, 20 PricesB> P9@ ,#d$$ $$$$$BB:*>@ ,#e$$ $$$$$B *** P5~ G4@*===* * P9 G?*@   * * RO~ ]@@ * 0N +_/D6@ &䢋@ * * 1P *f7@ 'Fx@ **** 2Q~ ,@ ( ****///****   0N+ \5@&:ɘ@* 1P*m^%7@'^C]K@^ 2Q~ ,@( /// /// /// /// ///2 BP!1BTbF8.<20  /  *0 (  ~  <`y XPP? i ]4@`y y6e[ <This is the fraction of the average used to compute the payoff of an average strike option. For example, if this is .9, then the payoff to an average strike call is S(T)-.9A(T). < G~~  <ăy XPP? d ]4@ăy  @<d@jzLOyV d@<" &A Page &PMHP LaserJet 4/4M Plus PS 600W odXLetter@)wx<w0sT0PRIV''''?"dXX??@Q1-S\JB|$/ d@< &A Page &PMHP LaserJet 4/4M Plus PS 600W odXLetter@)wx<w0sT0PRIV''''?"dXX?? 7 Sheet1 u'ɀ  A  dMbP?_*+%MHP LaserJet 4M Plus?dXXLetter.HP LaserJet 4M PlusP181812sxT[n0 1?eUKזs?bH=,n79D Ӷǵ㼕@k6(h!0$E0[혈m.)^4: vv{<;ȯ8AV: NH w[ ~ :} (H,U߫C鏬h,<:bwf-+غt{9-w+rCJTÐrZN-ʧ4gT1h^"=#w.8 #w/ˏ]l֫BN-['ڨq֗~RLbH"dXX??U} I"} I"}  "} "} I "} "        %% 3# c  0~ D@/ 4 4 1~ D@ Z B[()!G@ ,#8$$$$$$$BBY`"? ,J,#D$$$$$$$B 1~ >@ ?== 1~  @ ?== 1m~ ? ?== 1~  ?== 2 ~ ? ?== ?== * 4B,,,,, = J.@(    s 8Py@Text 1"`! ]@Py\$ x<yThis computes the price of an option where the payoff to the call is max{s^p - k^p,0], with the put defined analogously.<xxx  6yXPP? ,ZP ]4@y@% 0l@HZ2X -<.Continuously compounded annual dividend yield< -xlstNd ds 3 , < ] `%vv  6yA@dv]y&  < Go to Sheet:<= d\  c $@xs]t <'xJ JPy9f Py# Py#xx  6\XPP? ,ZP]4@\(  f6b L<X ,<-Continuously compounded annual interest rate< ,Robert McDonaldnRobert McDonaldn>@<d@jzLOyV d@<!   "??<@Q1-S\JB|$/ d@<   "??< 7 Sheet3 u'ɀ Υ  dMbP?_*+%MHP LaserJet 4M Plus?dXXLetter.HP LaserJet 4M PlusP181812sxT[n0 1?eUKזs?bH=,n79D Ӷǵ㼕@k6(h!0$E0[혈m.)^4: vv{<;ȯ8AV: NH w[ ~ :} (H,U߫C鏬h,<:bwf-+غt{9-w+rCJTÐrZN-ʧ4gT1h^"=#w.8 #w/ˏ]l֫BN-['ڨq֗~RLbH"dXX??U} "} "}  "} $"} $"} $ "                      / 4F 4* 3#= A A* 0~ @ 0=<b @ J'#1$$$$$$B=6@`KT? '#=$$$$$$B*  ** 1~ X@ 1<==dA7}? '#P$$$$$$B=8Tھ-? '#U$$$$$$B**** 1~ >@ 1===$5%@ '#$$$$$$B=8-&? '#!$$$$$$B*  ** 1~ @ 1>B=y/? ,#V$$$$$$$ BB8 ,#W$$$$$$$ B*  ** 1m~ ? 1?B=?{? ,#X$$$$$$$ BB8Tھ-? ,#Y$$$$$$$ B* 1~  1@B=D7QS? ,#"$$$$$$$ BB8 ,##$$$$$$$ B* 2~ A@ 1AB =YP %@ ,#$$$$$$$$ BB 80&? ,#%$$$$$$$ B ** 1DB =dA7}? ,#Q$$$$$$$ BB 8Tھ-? ,#R$$$$$$$ B * 1EB = ,#S$$$$$$$ BB 8 ,#T$$$$$$$ B * ** 1BB =$5%@ ,#$$$$$$$ BB 8-&? ,#$$$$$$$ B * * 2CB > ,#$$$$$$$ BB : ,# $$$$$$$ B *r &H   P (   x  6yXPP? i ]4@y, jpšnISx -<.Continuously compounded annual dividend yield< -9ľ9tNd ds  3  b<] ``,vv   6DyA@dv]Dy-  < Go to Sheet:<= d\   c $@xs]t .O O|y9f |y# |y#xx  6yXPP? i ]4@y/ fV%:`L"~XX ,<-Continuously compounded annual interest rate< ,xRobert McDonaldnRobert McDonaldn>@<d7 Sheet10 u'ɀ   dMbP?_*+%&A Page &PMHP LaserJet 4M Plus?dXXLetter.HP LaserJet 4M PlusP181812sxT[n0 1?eUKזs?bH=,n79D Ӷǵ㼕@k6(h!0$E0[혈m.)^4: vv{<;ȯ8AV: NH w[ ~ :} (H,U߫C鏬h,<:bwf-+غt{9-w+rCJTÐrZN-ʧ4gT1h^"=#w.8 #w/ˏ]l֫BN-['ڨq֗~RLbH"dXX??U} I"} $"} m "} "} "} "                                   / 4 4 3#/ 4 4 0~ @ 0=5@֕@ '#1$$$$$$B=6OɲXA? '#=$$$$$$B 1~ X@ 1B7uC^5? ,#N$$$$$$$ BB8Gz ,#z$$$$$$$ B 1~ >@ 1B7G@ ,#O$$$$$$$ BB8OɲXA? ,#{$$$$$$$ B ** 1~  @ 1B7A֕@ ,#I$$$$$$$ BB8OɲXA? ,#u$$$$$$$ B ** 1m~ ? 1B7м ,#J$$$$$$$ BB8 *,#v$$$$$$$ B ** 1~  1=7.Z> '#$$$$$$ B8* 2~ F@ 1= 7? '#^$$$$$$ B 8 * 1G= 7i <> '#$$$$$$ B 8 ** 2H= 9FJ+? '#_$$$$$$ B : * **** *** ******************************-*******-******************************************************************8^ ">Yq" * "`$(  $x $ 6yXPP? ]i? ]4@y2 kvL[,o -<.Continuously compounded annual dividend yield< -tNd ds $3 3  -] `$3vv $ 64yA@dv]4yT4  < Go to Sheet:<= d\ $ c $@xs]t 4+ +ly9f ly# ly#xx $ 6̼yXPP? ]i? ]4@̼y`6 q&UIe,hX ,<-Continuously compounded annual interest rate< ,NRobert McDonaldnRobert McDonaldn>@<d@jzLOyV Z@<& &A Page &P"P??<@Q1-S\JB|$/ Z@< &A Page &PMHPLJ4 (home)@w XX@MSUDHP LaserJet 4M Plus<d "dXX??7 Sheet4 u'ɀ \  dMbP?_*+%MHP LaserJet 4M Plus?dXXLetter.HP LaserJet 4M PlusP181812sxT[n0 1?eUKזs?bH=,n79D Ӷǵ㼕@k6(h!0$E0[혈m.)^4: vv{<;ȯ8AV: NH w[ ~ :} (H,U߫C鏬h,<:bwf-+غt{9-w+rCJTÐrZN-ʧ4gT1h^"=#w.8 #w/ˏ]l֫BN-['ڨq֗~RLbH"dXX??U} "} "}  "} I"} "} "          @  @ @@       EE %% 3# t E Tx&/ 4 4E 0 ~ Y@ W UXܛ^@ +?#|$$@$$$ $ $ $ $$B UYxbV@ ?#}$$@$$$ $ $ $ $$B 1~ ? -**E 2~  -!*E Uy' *!!E 0 ~ Y@ -** 1~ ? -** 2~  -** V ' *** Jz~ F^@ *** 1~ ? *!! 1w~ @ -** 2m~ ? ***************************-*************************0 4H,626,,(,,,,  *p,(  ,Nd ds ,3 <C] `9vv , 6yA@dv]y:  < Go to Sheet:<= d\ , c $@xs]t :, ,(y9f (y# (y#xx , 6yXPP? i ]4@y(< |3.N/sOX -<.Continuously compounded annual dividend yield< -9|9xx , 6hyXPP?  x ]4@hy< Bx@Q:X -<.Continuously compounded annual dividend yield< -9D9Robert McDonaldn Robert McDonaldn>@ 7 Sheet13 u'ɀ jv  dMbP?_*+%MHP LaserJet 4M Plus?dXXLetter.HP LaserJet 4M PlusP181812sxT[n0 1?eUKזs?bH=,n79D Ӷǵ㼕@k6(h!0$E0[혈m.)^4: vv{<;ȯ8AV: NH w[ ~ :} (H,U߫C鏬h,<:bwf-+غt{9-w+rCJTÐrZN-ʧ4gT1h^"=#w.8 #w/ˏ]l֫BN-['ڨq֗~RLbH"dXX??U} "} "}  "} I"} "} "           @  @ @       EE/ 44 3#/ 4/E T)&/ 4 4E 0 ~ D@ W GXB!@ 1#4$$$$$ $ $ $ B GYB!@ 1#@$$$$$ $ $ $ B 1~ >@ ?==E 2~ ? A=E U'/ 4 4E 0 ~ D@ W QX2ι@ -;#'$$$$$ $ $ $ $$B QY1ι@ ;#.$$$$$ $ $ $ $$B 1~  -** 2~  -** V '  0~  *!! 1m~ ? -** K~ 4@ *** 2~ ?************************-*************************0 ,:H,HH,,(,,, **((  (x ( 6T XPP?  x ]4@T @ F)^֣DD  -<.Continuously compounded annual dividend yield< -9D9xx ( 6 XPP? x ]4@ @ xO X -<.Continuously compounded annual dividend yield< -9|9tNd ds (3 <C] `Bvv ( 6 A@dv] @C  < Go to Sheet:<= d\ ( c $@xs]t C<. X.T 9f T # T #Robert McDonaldX Robert McDonaldX>@<d@jzLOyVd@< "??<@Q1-S\JB|$/d@<   "??<  7 Sheet5 u'ɀ   dMbP?_*+%MFHP LaserJet 4/4M PSS odXXLetterPRIV0''''\KhCj"dXX??U} } I } $      U V W~ @ X Y  Z F [   ] \ h   ^ _ ` a        b c  d u v e f g i(T8*&**(  H^ ds # Z] `|Gvv  6d A@dv]d H  < Go to Sheet:< d\  c $@xs]t |H. . 9f  #  #>@7 Sheet11 u'ɀ -  dMbP?_*+%MHP LaserJet 4M Plus?dXXLetter.HP LaserJet 4M PlusP181812sxT[n0 1?eUKזs?bH=,n79D Ӷǵ㼕@k6(h!0$E0[혈m.)^4: vv{<;ȯ8AV: NH w[ ~ :} (H,U߫C鏬h,<:bwf-+غt{9-w+rCJTÐrZN-ʧ4gT1h^"=#w.8 #w/ˏ]l֫BN-['ڨq֗~RLbH"dXX??U} $"} ")} $ "} "} "} $ "                       D 4 M:/ J~ FY@ N.GOc$(@ 1#K$$$$$$$$ B ** K,~ GV@ P/GQ6"@`? $/1#w$$$$$$$$ B ** K-~ Gw@ P0GQVuik? 1#L$$$$$$$$ B ** K~ H>@ P1GQ| 6@ 1#x$$$$$$$$ B ** K~ H @ P2HQCLsU@ 2#6$$$$DD$ $B KnGEGr?) R3HS>!0V@ 2#B$$$$DD$ $B KoGJ{/L? L~ I ; ; ; ;  &v  $    $ ( 333ff3 ~  <X  XPP? 0 ]4@X HL Հ?g[ONCZ <This is total time to expiration for underlying option. For example, if compound option expires in one year, and one year after that the underlying expires, we set this equal to 2.< P 0xx  6 XPP? `i3 ]4@ DM ;<"JcK( ro6X -<.Continuously compounded annual dividend yield< -99tNd ds 3   ] `Mvv  6 A@dv] 8O  < Go to Sheet:<= yd\  c $@xs]t tP0 05 9f 5 # 5 #xx  6  XPP? (i ]4@  P 27"PX ,<-Continuously compounded annual interest rate< , @Robert McDonaldnRobert McDonaldn Robert McDonaldn>@<d@jzLOyVd@< "??<@Q1-S\JB|$/d@< "??<7 Sheet9 u'ɀ    dMbP?_*+%"??<U} mj} ${}  j} mj} $ {} ${} $ j         }~ @Y@   ~ Y@ w=tC`0@ 1'#1$$$$$$B=~Pt6@ '#=$$$$$$B ~ >@ w1w er0@ !D6#i$$$$$$$$ $ B @tL@  ~  @ wB_Q? ,#7$$$$$$$BB_Q? ,#C$$$$$$$B ~ ? ~  ~ @ ~ . ~ 4@080   $8(  8 8 B@H  XPP?*<E]4@@H T HM1WLlU | <This is the continuously compounded expected jump, alpha_J, in the book. If the expected jump is k=Y-1, this is ln(Y)=ln(1+k). < ~  ` 8 BH  XPP?* < ]4@H lT Z&$eI_̳b C<DThis is the volatility of ln(Y), where Y-1 is the expected jump.. <Y A  CtNd ds 83  ZZ] `Tvv 8 6I A@dv]I W  < Go to Sheet:<= d\ 8 c $@xs]t XXo |20I 9f 0I # 0I # Robert McDonald| Robert McDonald|>@7 Sheet18 u'ɀ C$)  dMbP?_*+%MBhp deskjet 5100 series5100 ser!@d߀ odBeRLdEXCELArialHBeںں\\FIN8344M1\hp deskjet 5100 series,LocalOnly,DrvConvertVWK*XX"dXX??U} j} {} $ j}  {} $ j           | | 0~ D@ w=!-b"@ 4P'#1$$$ $$$B=~0ڃSe? '#=$$$ $$$B 1~ A@ wBz-"@ ,#Z$$$$$$$BB-L^? ,#[$$$$$$$B 1t'[? wFn<}R? 0#7$$?$$$$BFs<}R? 0#C$$?$$$$B 1~  @ 1m~ ? 1~  2~ ?   1 evw33?DDD {  g 4 C  <(  < < 06  @"`N]`6 Z (<)The input "volatility parameter" is not the observed volatility of the stock, but rather the parameter "sigma" in the CEV formula. Observed volatility of the stock is sigma*S^((beta-2)/2) So if we observe volatility of sigma_0, we need to set sigma so that sigma=sigma_0*S^((2-beta)/2) <(tNd ds <3 % `] `[vv < 6c A@dv]c d\  < Go to Sheet:<=  d\ < c $@xs]t \8R TR c 9f c # c #~~ < <J XPP?Ci}]4@J p^ ɬ? !GK5U A<BThis is the CEV parameter sigma_0, not the observed volatility. <p A`Robert McDonald >@7 Sheet17 u'ɀ BP  dMbP?_*+%MHP LJ2300 DuplexC odXXLetter DINU"4$IUPHdLetter [none] [none]Arial4Pd?ADMINISTRATOR<Automatic>     Type new Quick Set name herej i k k m m -"dXX??U} m"} $"} "} m "} "} "} "                                @            .$   0~ Y@/ 4 4 1~ Y@ 0 ;5xe'@ %#1$$$$$B;6H7V3u*@ %#=$$$$$BE 1)Ǻ? 1G7/? 1#2$$$$$BDDH8b; 2#>$$$$$BDD 1~ @ 1L7fe? 6#5$$$$$BDDL8fe? 6#A$$$$$BDD 1m~ ? 1;7ۈM? %#<$$$$$B;8ۈM? %#H$$$$$B 1~ v@ 1 ;7 , %#;$$$$$B;8.&Y~ %#G$$$$$B 1~ v.@ 1!;7%"!? %#:$$$$$B;86 9w %#F$$$$$B 1~ v? 2#; 9$עf@ %#3$$$$$B; :g5pH %#?$$$$$B 1"~ ^@ ;// * 2~ ?U 7| tV? Binomial Cl EuropeanAmericanBBPBinomial European *****  **?; 4 4 ***  ? 0  <ԃV @  !I  ;#+$$$$$$$ $$ $ B  6nd*@  !I  ;#,$$$$$$$ $$ $ B *** x &? 1=x⻈?  8 7NI   *** yN'@8DD !DDD BB? 1=m ̕?  8 7/?   *** y'N,SD#@uD ]DDADDD ADDD A B? 2 >XB4  :g5   *** z!(VkuV@ DD?;<<**?3@*k?AA**?==**-**** -*** -***)******)**********>j 04B6>xI 0      D& 4(  4Nd ds 43 _i] `avv 4 66 A@dv]6 ,b  < Go to Sheet:<= l d\ 4 c $@xs]t bX X, 9f , # , #xx 4 6e XPP? Z]4@e 8d 27"PX ,<-Continuously compounded annual interest rate< ,@xx  4 6e XPP?  i]4 @e d 627"PX L<MSpecify whether binomial formula computes European or American option price <  Llla  4 0Lf  @"`%x] `Lf e <This spreadsheet values options when dividends are a fixed amount, paid at regular intervals. Some comments: First, European option valuation is done by substituting the prepaid forward price (displayed under "dividend information") for the stock price. Second, with this substitution, delta (computed in the usual way) is the number of prepaid forward contracts required to hedge the option, not the number of shares. In order to correct this, multiply delta by the ratio of the stock price to the prepaid forward price, and gamma by the square of this ratio. This is done manually in the above greek calculations. Finally, as usual, when you set the binomial calculation to European, you should see approximately the same answer for the binomial and Black-Scholes calculations.<Robert McDonaldp  Robert McDonaldp>@  7 Sheet16 u'ɀ co  dMbP?_*+%MBhp deskjet 5100 series5100 ser!@d߀ odBeRLdEXCELArialHBeںں\\FIN8344M1\hp deskjet 5100 series,LocalOnly,DrvConvertVWK*XX"d??U} m"} $"} "} m "} "}  "} "                                           .$/ 4 4 k 0~ D@0@| ! #/$BPricel0.)@ !5 '#0$$$$$$Bm@2? B"/| ,)CP+PP/CD+PD/CG+PG/CV+PV/CT+PT/CR+PR/CE+PE,)CP+PP/CD+PD/CG+PG/CV+PV/CT+PT/CR+PR/CE+PE 1~ D@1| Deltan 4? o)mٿ  1~ >@1| Gamman9U?? o9U?? u 1~  @1<| Vegan\? o\?  1m~ ?1| Thetanw6숿 oX?]  1"~ @1| RhoncQ} oɜs  2~  20|  Elasticity p8. @  qXc!  / *** .* $U /| 葏? Binomial Cl EuropeanAmericanBBPBinomial European ***** 0$~  n@ ; 4 4 **** 1%B B2Y;? ,#7$$$$$$$ B 0  r.;@  !?  1#&$$$$$$$ $B  mQgo?  !?  1#-$$$$$$$ $B  **** 1&~  @ 1s_M?  okYPٿ  **** 2'BC Yc? ,#C$$$$$$$B 1sDʳ?  o3ʳ?  **** 2 t;j  q>0&  **** ;<<***?AA**?==***-*****-*****-**********))8 LTT8z^      @(  @Nd ds @3 _ii] `ivv @ 6P A@dv]P 4j  < Go to Sheet:<= d\ @ c $@xs]t j  9f  #  #xx @ 6 XPP? i ]4@ @l 3C2VrX ,<-Continuously compounded annual interest rate< ,@xx  @ 6D XPP? i ]4 @D l n\L흁z X L<MSpecify whether binomial formula computes European or American option price < Llla  @ 0  @"`i] ` m <The Black formula provides the price of an option on a futures contract. It is the Black-Scholes formula with the dividend yield replaced by the risk-free rate. This changes the formula for rho, and psi is not defined. The formulas here are defined as an array function.<  @ 0  @"` c] ` |o <The format string must be a sequence of two-letter entries, separated by "+" for a space and "/" for a new line. For example, "CP" is the call price, "CD" the call delta, etc. To see how this works, you can experiment by changing the first "CP" to "CD". <Robert McDonald   Robert McDonald >@ 7 Sheet19 u'ɀ    dMbP?_*+%&A Page &PMHP LJ2300 DuplexC odXXLetter DINU"4$IUPHdLetter [none] [none]Arial4Pd?ADMINISTRATOR<Automatic>     Type new Quick Set name herej i k k m m -"dXX??U} m"} $"} "} m "} "}  "} "                                            .$/ 4 4 0~ D@ 0 =l()!G@ '#1##`###a#]B=m`"? '#=##`###a#]B 1~ D@ 1=nd? '#2##`###a#]B=o6iڿ '#>##`###a#]B 1~ >@ 1=n ? '#5##`###a#]B=o ? '#A##`###a#]Bu 1~  @ 1=ntu? '#<##`###a#]B=otu? '#H##`###a#]B 1m~ ? 1 =n0s_16 '#;##`###a#]B=oWN䁿 '#G##`###a#]B 1~  1!=nB? '#:##`###a#]B=oy3<8 '#F##`###a#]B 1"~ @ 1= n:&ӭ '#9##`###a#]B= oHu `? '#E##`###a#]B 2~  2#= ps @ '#3##`###a#]B= q\ '#?##`###a#]B ***  / ***** .* $U /| ? Binomial Cl EuropeanAmericanBBPBinomial European **** 0$~  @ ; 4 4 **** 1%BB{? ,#7##`###a#]$ B 0 r9v(@ !?1#&##`###a#]#l#jB mji? !?1#-##`###a#]#l#jB **** 1&~  @ 1sLS? ofY#ڿ **** 2'BCw:Nr? {,#C##`###a#]$B 1s?0? o?0? **** 2 t=&j q% *;<<****?AA***?==***-*****-*****-**** ***)):s >T8zV 0       #  t( $   r  00 ?e-],0 rX{H 9g $<Draw Binomial Tree for Call<rr  0 ?v], rt{l 9h $<Draw Binomial Tree for Put<xx  6 XPP? Gi ]4@ t |:0F:X -<.Continuously compounded annual dividend yield< -9hl9tNd ds  3 k-] `$uvv   6< A@dv] < Tv  < Go to Sheet:<= d\   c $@xs]t  v{ {t 9f t # t #xx  68 XPP? i ]4 @8 `x 6-#*_HZ.ɗX ,<-Continuously compounded annual interest rate< ,@xx  6 XPP?  x ]4 @ x q3QO ;[X L<MSpecify whether binomial formula computes European or American option price < Lllarr  0  @!]` y (<Copyright 2006 Addison Wesley Longman. These spreadsheets accompany Derivatives Markets by Robert L. McDonald and are intended for educational use.<(FxZ t Robert McDonaldRobert McDonald  Robert McDonald>@<d@jzLOyVd@< &A Page &PM"HP LaserJet 4P/4MP PostScript Vd[ odXRZZce"d??@Q1-S\JB|$/}@< &A Page &PM"HP LaserJet 4P/4MP PostScript Vd[ odXRZZce"d?? 7 Sheet6 u'ɀ 6  dMbP?_*+%&A Page &PMHP LaserJet 4 Plus4C odXXLetterDINU"4e "dXX??U} } m }  m } m } m } #m } $$m       (????~ @@|nE@48+`xG@lI@ 9cA@uB@LngD@k??`9?>06@@jC>ُA@  09;>@ ~      K$,0VZHH66$ P8(    Jx   @?Text 1<]@x \~ dH<eBlack = Stock Price Red Italic= Option Price, no exercise Green Bold Italic = Option Price, exercise<H : iKLdll  s * ?K`], X( 9~ $<Return<&>@@jzLOyVd@< &A Page &PMHP LaserJet 4/4M Plus PS 600W odXLetter,l GrPRIV''''?"dXX??@Q1-S\JB|$/@<&A Page &PMHPLJ4 (home)@w XX@MSUDHP LaserJet 4M Plus<d "dXX??7 Sheet7 u'ɀ p  dMbP?_*+%"XX??U                      >@  7 Sheet12 u'ɀ   dMbP?_*+%" ??U} $      ~ @ +~ @ s~ @ 4~ @ ;~ @ I~ @ J~ @ L~ `@ M~ @ R~ @ S~ @ T~ (@ j~ (@ k~  (@ l~ @*@ p~ /@ q~ /@ r~ 0@ |~ >@ ~ >@ ~ `A@ ~ `A@ ~ `A@ ~ `A@ ~ `A@ ~ `A@ ~ `A@ ~ `E@ ~ `E@ ~ F@ @D>@  @jzLOyVd@<" "??<@Q1-S\JB|$/d@< "??<7 Sheet8 u'ɀ&A Page &P"P??> P@jzLOyV?&A Page &P"POyV??@Q1-S\JB|$/?&A Page &P"P|$/?? u'ɀ"P|$/??> P@jzLOyV?"zOyV??@Q1-S\JB|$/?"0-S|$/?? u'ɀ"0-S|$/??> 0-S@jzLOyV?"zOyV??@Q1-S\JB|$/?"0-S|$/?? u'ɀ"0-S|$/??> 0-S@jzLOyV?"zOyV??@Q1-S\JB|$/?"0-S|$/?? M_BS __SRP_e4D__SRP_fu Copyright."*p  !"#$%&'()+,-./0123456789:;<=>?@ABCEGIJKLMNOPQRSTUVXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~ p:fx B  H B P0SX   B \ B  :J(hB p,SxB ,S B $+$+  'H-h ~ 908 @PX B ` 'hV  B 6I(` h  x   B 6I B \X B `"#h B Z B "#8 B @H      !"#$%&'()*+,-/0123<56789:;.>?@ABCDEH\JKLMNOPQRSTUVWXYZ[G]^_`abcdefghijklmnopqrstuvwxyz{|~Z8[wxMEP> LL<0X h``(0k@  I h2  14  P`H I"  priI$ e prI& I( ( I*  of  14 0X`Pp!|+ I2 @I4 I6 (KI8 I*" 0I:$  14 <``X0%k I2 I4 I6 I8 I* 8I:$  14 @h``)k I2 I4 I6 I8  I* @I: $  14 Bp`h#k "I2 I4 I6 I8P$ (I* HI:($  14 Hx`p`(k %I2) I4 I6 I8 0I* PI:0$  14 J`x8k )I2 I4 I6 I8@ 8I* XI:8$ European call option price 14 P`5\k 7I2 I4  I6 @I8 `I* I:`$ European put option price 14 T ` H<k ?I2 ( I4 H I6 h I8  I*  I: $ European call delta 14 V ` =k( KI2> H I4 h I6  I8  I*  I: $ European put delta 14 X( ` x?lkH OI2 h I4  I6  I8  I*  I: $ European call gamma 14 ZH `( @ A\kh SI2  I4B  I6  I8  I*  I: $ European put gamma 14 \h `H`-lk WI2  I4  I6  I8 I* (I:$ European call vega 14 ^`hB\k [I2 I4 I6 I8 (I* HI:($ European put vega 14 `` Lk _I2 I4 I6 (I8 HI* hI:H$ European call rho 14 b`HNk cI2 I4 (I6 HI8 hI* I:h$ European put rho 14 d`Ck gI2 (I4D HI6 hI8 I* I:$ European call theta 14 f`Ik( kI2 HI4 hI6I I8 I* I:$ European put theta 14 h`:kH nI2 hI4 I6 I8 I* I:$  j`7lkH qI2 hI4 I6 I8 I* I:$  l0`(pP kH  uI2 hI4 I6 I8 I* I:$ European call elasticity 14 rX`0PQ kp ~I2 I4 I6 I8 I* I:$ European put elasticity 14 v`xx+k I2 I4 I6 I8 I* 8I:$ XIx( $Implied volatility for European call 14 `h2Lk I2 I4 (I6 HI8 hI* I:h$ I"( #Implied volatility for European put 14 ``XUk8 I2 XI4 xI6 I8 I* I:$ Ix8( :Implied stock price for a given European call option price 14 `xSk I2 I4 I6 I8  I* @I: $ `Ix( 9Implied stock price for a given European put option price 14 ( XhpPH x 0h`X`h%XX!x@X@Jl h@@\ @L @< 0BHN@ , @  @h @8 @P @ @ @ @ @ @