excel-求解二叉树模型

ValuationofOptionsusingBinomial&BlackScholesFormula,Toreviewtheillustration,changethevaluesintheredfontTypePutOptionSpot50Strike50Risk-free10%Std.dev.40%Maturity(Year)0.42d10.290Timestep0.08d20.032u1.12N(d1)0.614d0.89N(d2)0.513e(Rf*Time)1.01S*N(d1)30.71p0.51X*N(d2)*exp(-r*t)24.60q0.49PriceofEuropeanCalloption6.12PriceofEuropeanPutOption4.08Time(Years)00.0833330.16666762.990.640.64Sup=S*u56.122.112.165050.004.323.674.493.77Sdown=S*d44.556.666.9639.699.8610.36PricingofanOptionBlack-ScholesformulaBinomialTreeValuationofOptionsusingBinomial&BlackScholesFormula,Toreviewtheillustration,changethevaluesintheredfont0.250.3333330.41666789.070.0079.350.00[p×Optionup+(1-p)×Optiondown]×exp(-r×t)0.00Max[(K–S),p×Optionup+(1-p)×Optiondown]×exp(-r×t)]70.7070.700.000.000.0062.990.00Legend0.00AssetPrice56.1256.12EuropeanOptionPay-off1.300.00AmericanOptionPay-off1.3050.002.662.6644.5544.556.185.456.3839.699.9010.3135.3635.3613.8114.6414.6431.5018.0818.5028.0721.93EuropeanOptionPay-offAmericanOptionPay-offBinomialoptionspricingmodelThebinomialoptionspricingmodel(BOPM)providesageneralisablenumericalmethodforthevaluationofoptions.Eachnodeinthetree,representsapossiblepriceoftheunderlying,ataparticularpointintime.Thispriceevolutionformsthebasisfortheoptionvaluation.Optionvaluationusingthismethodis,asdescribed,athreestepprocess:1.pricetreegeneration2.calculationofoptionvalueateachfinalnode3.progressivecalculationofoptionvalueateachearliernode;thevalueatthefirstnodeisthevalueoftheoption.ThebinomialpricetreeThetreeofpricesisproducedbyworkingforwardfromvaluationdatetoexpiration.Ateachstep,itisassumedthattheunderlyinginstrumentwillmoveupordownbyaspecificfactor(uord)perstepofthetree(where,bydefinition,m≥1and0d≤1)So,ifSisthecurrentprice,theninthenextperiodthepricewilleitherbeSup=S*uandSdown=S*dReferExampleSheet,CellC35ReferExampleSheet,CellE12ReferExampleSheet,CellE13Ateachfinalnodeofthetree—i.e.atexpirationoftheoption—theoptionvalueissimplyitsintrinsic,orexercise,value.Max[(S–K),0],foracalloptionMax[(K–S),0],foraputoption:Where:KistheStrikepriceandSisthespotpriceoftheunderlyingassetThestepsareasfollows:Thebinomialpricingmodelusesadiscrete-timeframeworktotracetheevolutionoftheoption'skeyunderlyingvariableviaabinomialtree,foragivennumberoftimestepsbetweenvaluationdateandoptionexpiration.Thevaluationprocessisiterative,startingateachfinalnode,andthenworkingbackwardsthroughthetreetothefirstnode(valuationdate),wherethecalculatedresultisthevalueoftheoption.Theupanddownfactorsarecalculatedusingtheunderlyingvolatility,σandthetimedurationofastep,t,measuredinyears(usingthedaycountconventionoftheunderlyinginstrument).FromtheIftheunderlyingassetmovesupandthendown(u,d),thepricewillbethesameasifithadmoveddownandthenup(d,u)—herethetwopathsmergeorrecombine.Thispropertyreducesthenumberoftreenodes,andthusacceleratesthecomputationoftheoptionprice.Oncetheabovestepiscomplete,theoptionvalueisthenfoundforeachnode,startingattheonebeforethelasttimestep,andworkingbacktothefirstnodeofthetree(thevaluationdate)wherethecalculatedresultisthevalueoftheoption.Thefollowingformulaisappliedateachnode:BinomialValue=[p×Optionup+(1-p)×Optiondown]×exp(-r×t),ReferExampleSheet,CellK29ForAmericanOptions,thevalueoftheputoptionisMax[(K–S),p×Optionup+(1-p)×Optiondown]×exp(-r×t)]1)Undertheriskneutralityassumption,today'sfairpriceofaderivativeisequaltotheexpectedvalueofitsfuturepayoffdiscountedbytheriskfreerate.Therefore,expectedvalueiscalculatedusingtheoptionvaluesfromthelatertwonodes(OptionupandOptiondown)weightedbytheirrespectiveprobabilities--probabilitypofanupmoveintheunderlying,andprobability(1-p)ofadownmove.Theexpectedvalueisthendiscountedatr,theriskfreeratecorrespondingtothelifeoftheoption.SimilarassumptionsunderpinboththebinomialmodelandtheBlack-Scholesmodel,andthebinomialmodelthusprovidesadiscretetimeapproximationtothecontinuousprocessunderlyingtheBlack-Scholesmodel.Infact,forEuropeanoptionswithoutdividends,thebinomialmodelvalueconvergesontheBlack-ScholesformulavalueasthenumberoftimestepsincreasesThebinomialoptionspricingmodel(BOPM)providesageneralisablenumericalmethodforthevaluationofoptions.Eachnodeinthetree,representsapossiblepriceoftheunderlying,ataparticularpointintime.Thispriceevolutionformsthebasisfortheoptionvaluation.3.progressivecalculationofoptionvalueateachearliernode;thevalueatthefirstnodeisthevalueoftheoption.Ateachstep,itisassumedthattheunderlyinginstrumentwillmoveupordownbyaspecificfactor(uord)perstepofthetree(where,bydefinition,m≥1and0d≤1)Ateachfinalnodeofthetree—i.e.atexpirationoftheoption—theoptionvalueissimplyitsintrinsic,orexercise,value.Thebinomialpricingmodelusesadiscrete-timeframeworktotracetheevolutionoftheoption'skeyunderlyingvariableviaabinomialtree,foragivennumberoftimestepsbetweenvaluationdateandoptionexpiration.Thevaluationprocessisiterative,startingateachfinalnode,andthenworkingbackwardsthroughthetreetothefirstnode(valuationdate),wherethecalculatedresultisthevalueoftheoption.Theupanddownfactorsarecalculatedusingtheunderlyingvolatility,σandthet