Black-Scholes Excel Formulas and How to Create a Simple ...

Black-Scholes Inputs ... σ = volatility (% p.a.). r = continuously compounded risk-free interest rate (% p.a.). q = continuously compounded dividend yield (% p.a.). Macroption Get a Calculator Sign In Tutorials Calculators Services About Contact Tutorials Calculators Services About Contact Sign In Get a Calculator All»TutorialsandReference»Black-ScholesModelBlack-ScholesExcelFormulasandHowtoCreateaSimpleOptionPricingSpreadsheetYouareinTutorialsandReference»Black-ScholesModelBlack-ScholesFormula(d1,d2,CallPrice,PutPrice,Greeks)Black-ScholesModelAssumptionsBlack-ScholesInputs(Parameters)Black-ScholesExcelFormulasandHowtoCreateaSimpleOptionPricingSpreadsheetBlack-ScholesModelHistoryandKeyPapers MoreinTutorialsandReference Options101:BeginnerTutorialOptionPayoffExcelTutorialOptionStrategiesOptionGreeksBlack-ScholesModelBinomialOptionPricingModelsVolatilityVIXandVolatilityProductsTechnicalAnalysisStatisticsforFinanceOtherTutorialsandNotesGlossaryThispageisaguidetocreatingyourownoptionpricingExcelspreadsheet,inlinewiththeBlack-Scholesmodel(extendedfordividendsbyMerton).Hereyoucangetaready-madeBlack-ScholesExcelcalculatorwithchartsandadditionalfeaturessuchasparametercalculationsandsimulations. Onthispage:Black-ScholesinExcel:TheBigPictureBlack-ScholesInputsBlack-Scholesd1andd2Black-ScholesOptionPriceExcelFormulasN(d1),N(d2),N(-d2),N(-d1)CallOptionPricePutOptionPriceBlack-ScholesGreeksinExcel Black-ScholesinExcel:TheBigPicture IfyouarenotfamiliarwiththeBlack-Scholesmodel,itsassumptions,parameters,and(atleastthelogicof)theformulas,youmaywanttoreadthosepagesfirst(overviewofallBlack-Scholesresourcesishere). BelowIwillshowyouhowtoapplytheBlack-ScholesformulasinExcelandhowtoputthemalltogetherinasimpleoptionpricingspreadsheet.Therearefoursteps: Designcellswhereyouwillenterparameters. Calculated1andd2. Calculatecallandputoptionprices. CalculateoptionGreeks. Black-ScholesInputs FirstyouneedtodesignsixcellsforthesixBlack-Scholesparameters.Whenpricingaparticularoption,youwillhavetoenteralltheparametersinthesecellsinthecorrectformat.Theparametersandformatsare: S0 =underlyingprice(USDpershare) X =strikeprice(USDpershare) σ =volatility(%p.a.) r =continuouslycompoundedrisk-freeinterestrate(%p.a.) q =continuouslycompoundeddividendyield(%p.a.) t =timetoexpiration(%ofyear) Underlyingpriceisthepriceatwhichtheunderlyingsecurityistradingonthemarketatthemomentyouaredoingtheoptionpricing.Enteritindollars(oreuros/yen/poundetc.)pershare. Strikeprice,alsocalledexerciseprice,isthepriceatwhichyouwillbuy(ifcall)orsell(ifput)theunderlyingsecurityifyouchoosetoexercisetheoption.Ifyouneedmoreexplanation,see:Strikevs.MarketPricevs.UnderlyingPrice.Enteritalsoindollarspershare(itmusthavesameunitsasunderlyingprice,alsowiththesamecontractorlotmultipliers). Volatilityisthemostdifficultparametertoestimate(alltheotherparametersaremoreorlessgiven).Itisyourjobtodecidehowhighvolatilityyouexpectandwhatnumbertoenter–neithertheBlack-Scholesmodel,northispagewilltellyouhowhighvolatilitytoexpectwithyourparticularoption(formoreonthat,seethevolatilitytutorials,particularlyhistoricalandimpliedvolatility).Beingabletoestimate(=predict)volatilitywithmoresuccessthanotherpeopleisthehardpartandkeyfactordeterminingsuccessorfailureinoptiontrading.Theimportantthinghereistoenteritinthecorrectformat,whichis%p.a.(percentannualized). Risk-freeinterestrateshouldbeenteredin%p.a.,continuouslycompounded.Theinterestrate’stenor(timetomaturity)shouldmatchthetimetoexpirationoftheoptionyouarepricing.Youcaninterpolatetheyieldcurvetogettheinterestrateforyourexacttimetoexpiration.Interestratedoesnotaffecttheresultingoptionpriceverymuchinthelowinterestenvironmentthatwe’vehadintherecentyears,butitcanbecomeveryimportantwhenratesarehigher(formoredetailsontheeffectofinterestratesonoptionpricesseetheoptionrhotutorial). Dividendyieldshouldalsobeenteredin%p.a.,continuouslycompounded.Iftheunderlyingstockdoesn’tpayanydividend,enterzero.Ifyouarepricinganoptiononsecuritiesotherthanstocks,youmayenterthesecondcountryinterestrate(forFXoptions)orconvenienceyield(forcommodities)here. Timetoexpirationshouldbeenteredas%ofyearbetweenthemomentofpricing(now)andexpirationoftheoption.Forexample,iftheoptionexpiresin24calendardays,enter24/365=6.58%.Alternatively,youcanmeasuretimeintradingdaysratherthancalendardays.Iftheoptionexpiresin18tradingdaysandthereare252tradingdaysperyear,youwillentertimetoexpirationas18/252=7.14%.Youcanalsobemorepreciseandmeasuretimetoexpirationtohoursorevenminutes.Inanycaseyoumustalwaysexpressthetimetoexpirationas%ofyearinorderforthecalculationstoreturncorrectresults(itisveryeasyinExcel–justdividethenumberofdaystoexpirationbythenumberofdaysperyear). Iwillillustratethecalculationsontheexamplebelow.TheparametersareincellsA44(underlyingprice),B44(strikeprice),C44(volatility),D44(interestrate),E44(dividendyield),andG44(timetoexpirationas%ofyear). Note:Itisrow44,becauseIamusingthe Black-ScholesCalculatorforscreenshotsandithaschartsintherowsabove.Youcanofcoursestartinrow1orarrangeyourcalculationsinacolumn. Black-Scholesd1andd2 Whenyouhavethecellswithparametersready,thenextstepistocalculated1andd2,becausethesetermsthenenterallthecalculationsofcallandputoptionpricesandGreeks.Theformulasford1andd2are: Alltheoperationsintheseformulasarerelativelysimplemathematics.TheonlythingsthatmaybeunfamiliartosomelesssavvyExcelusersarethenaturallogarithm(LNExcelfunction)andsquareroot(SQRTExcelfunction). Thehardestthingwiththed1formulaismakingsureyouputthebracketsintherightplaces.Thisiswhyyoumaywanttocalculateindividualpartsoftheformulainseparatecells,asIdointheexamplebelow: FirstIcalculatethenaturallogarithmoftheratioofunderlyingpriceandstrikeprice(thisiswhytheymusthavethesameunits)incellH44: =LN(A44/B44) ThenIcalculatetherestofthenumeratorofthed1formulaincellI44: =(D44-E44+POWER(C44,2)/2)*G44 ThenIcalculatethedenominatorofthed1formulaincellJ44.Anotherreasonwhyyoumaywanttocalculated1inseparatepartsisthatthistermwillalsoentertheformulaford2: =C44*SQRT(G44) NowIhaveallthethreepartsofthed1formulaandIcancombinethemincellK44togetd1: =(H44+I44)/J44 Finally,Icalculated2incellL44: =K44-J44 Black-ScholesOptionPriceExcelFormulas TheBlack-Scholesformulasforcalloption(C)andputoption(P)pricesare: Thetwoformulasareverysimilar.Therearefourtermsineachformula.Iwillagaincalculatetheminseparatecellsfirstandthencombinetheminthefinalcallandputformulas. N(d1),N(d2),N(-d2),N(-d1) PotentiallyunfamiliarpartsoftheformulasaretheN(d1),N(d2),N(-d2),andN(-d1)terms.N(x)denotesthestandardnormalcumulativedistributionfunction–forexample,N(d1)isthestandardnormalcumulativedistributionfunctionforthed1thatyouhavecalculatedinthepreviousstep. InExcelyoucaneasilycalculate thestandardnormalcumulativedistributionfunctionsusingtheNORM.DISTfunction,whichhas4parameters: NORM.DIST(x,mean,standard_dev,cumulative) x=linktothecellwhereyouhavecalculatedd1ord2(withminussignfor-d1and-d2) mean=enter0,becauseitisstandardnormaldistribution standard_dev=enter1,becauseitisstandardnormaldistribution cumulative=enterTRUE,becauseitiscumulative Forexample,IcalculateN(d1)incellM44: =NORM.DIST(K44,0,1,TRUE) Note:ThereisalsotheNORM.S.DISTfunctioninExcel,whichisthesameasNORM.DISTwithfixedmean=0andstandard_dev=1(thereforeyouenteronlytwoparameters:xandcumulative).Youcanuseeither;I’mjustmoreusedtoNORM.DIST,whichprovidesgreaterflexibility. TheTermswithExponentialFunctions Theexponents(e-qtande-rtterms)arecalculatedusingtheEXPExcelfunctionwith-q*tor-r*tasparameter. Icalculatee-rtincellQ44: =EXP(-D44*G44) ThenIuseittocalculateXe-rtincellR44: =B44*Q44 Analogically,Icalculatee-qtincellS44: =EXP(-E44*G44) ThenIuseittocalculateS0e-qtincellT44: =A44*S44 NowIhavealltheindividualtermsandIcancalculatethefinalcallandputoptionprice. CallOptionPrice IcombinethefourtermsinthecallformulatogetcalloptionpriceincellU44: =T44*M44-R44*O44 PutOptionPrice IcombinethefourtermsintheputformulatogetputoptionpriceincellU44: =R44*P44-T44*N44 Black-ScholesGreeksinExcel Hereyoucancontinuetothesecondpartofthistutorial,whichexplainsExcelcalculationoftheGreeks:delta,gamma,theta,vega,andrho: ContinuetoOptionGreeksExcelFormulas OryoucanseehowalltheExcelcalculationsworktogetherintheBlack-ScholesCalculator.Explanationofthecalculator’sotherfeatures(parametercalculationsandsimulationsofoptionpricesandGreeks)areavailableinthecalculator’suserguide. All»TutorialsandReference»Black-ScholesModelBlack-ScholesFormula(d1,d2,CallPrice,PutPrice,Greeks)Black-ScholesModelAssumptionsBlack-ScholesInputs(Parameters)Black-ScholesExcelFormulasandHowtoCreateaSimpleOptionPricingSpreadsheetBlack-ScholesModelHistoryandKeyPapers MoreinTutorialsandReference Options101:BeginnerTutorialOptionPayoffExcelTutorialOptionStrategiesOptionGreeksBlack-ScholesModelBinomialOptionPricingModelsVolatilityVIXandVolatilityProductsTechnicalAnalysisStatisticsforFinanceOtherTutorialsandNotesGlossary Haveaquestionorfeedback?Sendamessage.Ittakeslessthanaminute. Topofthispage Home Tutorials Calculators Services About Contact Byremainingonthiswebsiteorusingitscontent,youconfirmthatyouhavereadandagreewiththeTermsofUseAgreementjustasifyouhavesignedit.TheAgreementalsoincludesPrivacyPolicyandCookiePolicy.Ifyoudon'tagreewithanypartofthisAgreement,pleaseleavethewebsitenow.Anyinformationmaybeinaccurate,incomplete,outdatedorplainwrong.Macroptionisnotliableforanydamagesresultingfromusingthecontent. ©2021Macroption..