Black-Scholes option pricing in Excel and VBA

2. The Black-Scholes model in Excel ... Example: The stock price at time 0, six months before expiration date of the option is $42.00, option ... Home xlf::Contents[FrontPage]   contents::Black-Scholes 1.Black-ScholesOptionPricing 2.BSOPMinExcel 3.BSOPMinVBA About Search&Index Black-ScholesoptionpricinginExcelandVBA 1.Black-Scholesmodel AccordingtotheBlack-Scholes(1973)model,thetheoreticalprice\(C\)forEuropeancalloptiononanondividendpayingstockis$$\begin{equation}C=S_0N(d_1)-Xe^{-rT}N(d_2)\end{equation}$$where$$d_1=\frac{log\left(\frac{S_0}{X}\right)+\left(r+\frac{\sigma^2}{2}\right)T}{\sigma\sqrt{T}}$$$$d_2=\frac{log\left(\frac{S_0}{X}\right)+\left(r-\frac{\sigma^2}{2}\right)T}{\sigma\sqrt{T}}=d_1-\sigma\sqrt{T}$$ Inequation1,\(S_0\)isthestockpriceattime0,\(X\)istheexercisepriceoftheoption,\(r\)istheriskfreeinterestrate,\(\sigma\)representstheannualvolatilityoftheunderlyingasset,and\(T\)isthetimetoexpirationoftheoption. FromPut-Callparity,thetheoreticalprice\(P\)ofEuropeanputoptiononanondividendpayingstockis$$\begin{equation}P=Xe^{-rT}N(-d_2)-S_0N(-d_1)\end{equation}$$ 2.TheBlack-ScholesmodelinExcel Example:Thestockpriceattime0,sixmonthsbeforeexpirationdateoftheoptionis$42.00,optionexercisepriceis$40.00,therateofinterestonagovernmentbondwith6monthstoexpirationis5%,andtheannualvolatilityoftheunderlyingstockis20%. ThevaluesusedinthisexamplearesimilartothoseinHull(2009,p294)withS0=42,K(exercise)=40,r=0.1,σ=0.2,andT=0.5.Thissetreturnsc=4.76andp=0.81 Calculationofthecallpricecanbecompletedasa5stepprocess.Step1.d1;2.d2as\(\left[\frac{log\left(\frac{S_0}{X}\right)+\left(r-\frac{\sigma^2}{2}\right)T}{\sigma\sqrt{T}}\right]\);3.N(d1);4.N(d2);andstep5,C.Thevalueford1andd2areshowninrows12and13offigure1.Theprobabilitiesfor\(N(\cdot)\)areestimatedwiththeNORM.S.DISTfunction.Thecallpricefromequation1is$4.08(Figure1row18),andtheputpricefromequation2is$1.09(Figure1row19). SyntaxNORM.S.DIST(z,cumulative).zistheprobabilityvalue,andcumulativeisaLOGICALvalue.TRUEreturnsthecumulativedistributionfunction.FALSEreturnstheprobabilitymassfunction. Fig1:ExcelWebApp#1:-ExcelversionofBlackandScholes'modelforaEuropeantypeoptiononanondividendpayingstock 3.TheBlack-ScholesmodelinVBA Inthisexample,separatefunctionproceduresaredevelopedforthecall(code1)andput(code2)equations.TheExcelNORM.S.DISTfunction,line6incode1and2,requiresthatthedotoperatorsbereplacedbyunderscoreswhenthefunctioniscalledfromVBA. Code1:FunctionBSCallreturnsthecallpriceforaEuropeanoptiononanondividendpayingstock FunctionBSCall(StockAsDouble,ExerciseAsDouble,RateAsDouble,SigmaAsDouble,TimeAsDouble)AsDouble Dimd1AsDouble,d2AsDouble WithApplication d1=(.Ln(Stock/Exercise)+(Rate+(Sigma^2)/2)*Time)/(Sigma*Sqr(Time)) d2=(.Ln(Stock/Exercise)+(Rate-(Sigma^2)/2)*Time)/(Sigma*Sqr(Time)) BSCall=Stock*.Norm_S_Dist(d1,True)-Exercise*Exp(-Rate*Time)*.Norm_S_Dist(d2,True) EndWith EndFunction Code2:FunctionBSPutreturnstheputpriceforaEuropeanoptiononanondividendpayingstock FunctionBSPut(StockAsDouble,ExerciseAsDouble,RateAsDouble,SigmaAsDouble,TimeAsDouble)AsDouble Dimd1AsDouble,d2AsDouble WithApplication d1=(.Ln(Stock/Exercise)+(Rate+(Sigma^2)/2)*Time)/(Sigma*Sqr(Time)) d2=(.Ln(Stock/Exercise)+(Rate-(Sigma^2)/2)*Time)/(Sigma*Sqr(Time)) BSPut=Exercise*Exp(-Rate*Time)*.Norm_S_Dist(-d2,True)-Stock*.Norm_S_Dist(-d1,True) EndWith EndFunction TheBScallandBSPutfunctionsaretestedbythecallingprocedureincode3.OutputissenttotheImmediateWindowwiththeDebug.Printmethod. Code3:MacroTestBSModelcallstheBSCallprocedureandtheBSPutprocedure. SubTestBSModel() DimCallPAsDouble,PutPAsDouble CallP=BSCall(42,40,0.05,0.2,0.5) PutP=BSPut(42,40,0.05,0.2,0.5) Debug.Print"Time:"&Time Debug.Print"====================================" Debug.Print"BSCall(42,40,0.05,0.2,0.5):returns"&Format(CallP,"Currency") Debug.Print"BSPut(42,40,0.05,0.2,0.5):returns"&Format(PutP,"Currency") EndSub Theoutputfromcode3isshownintheImmediateWindowoffigure2. Fig2:BlackandScholes'model-foraEuropeantypeoptiononanondividendpayingstock AcombinationCallandPutprocedureisshowninCode4. Code4:FunctionBSOptioncombinestheBSCallprocedureandtheBSPutprocedureandaddsanOptionalargumentOptType FunctionBSOption(StockAsDouble,_ ExerciseAsDouble,_ RateAsDouble,_ SigmaAsDouble,_ TimeAsDouble,_ OptionalOptTypeAsVariant)AsVariant 'OptTypeTRUE(default)forCall,FALSEforPut Dimd1AsDouble,d2AsDouble DimBSCallAsDouble,BSPutAsDouble IfIsMissing(OptType)ThenOptType=True 'CheckthatVarianthassubtypeBoolean IfVBA.TypeName(OptType)<>"Boolean"ThenGoToErrHandler OnErrorGoToErrHandler WithApplication d1=(.Ln(Stock/Exercise)+(Rate+(Sigma^2)/2)*Time)/(Sigma*Sqr(Time)) d2=(.Ln(Stock/Exercise)+(Rate-(Sigma^2)/2)*Time)/(Sigma*Sqr(Time)) BSCall=Stock*.Norm_S_Dist(d1,True)-Exercise*Exp(-Rate*Time)*.Norm_S_Dist(d2,True) BSPut=Exercise*Exp(-Rate*Time)*.Norm_S_Dist(-d2,True)-Stock*.Norm_S_Dist(-d1,True) EndWith IfOptTypeThen BSOption=BSCall Else BSOption=BSPut EndIf ExitFunction ErrHandler: BSOption=CVErr(xlErrValue)'Return#VALUE!error EndFunction TheBSOptionfunctionfunctionsistestedbythecallingprocedureincode5. Code5:MacroBSOption_TestpassesvaluestoBSOptionfunctionandprintsoutputtotheimmediatewindow SubBSOption_Test() DimPriceAsDouble DimSAsDouble,EAsDouble,RAsDouble,VAsDouble,TAsDouble DimOTAsBoolean S=42:E=40:R=5/100:V=20/100:T=0.5 Price=BSOption(S,E,R,V,T) Debug.Print"================================" Debug.Print"Time:"&Format(Time,"hh:mm:ss")&vbNewLine Debug.Print"S=42:E=40:R=5/100:V=20/100:T=0.5" Debug.Print"BSPrice="&Format(Price,"$0.0000")&vbNewLine S=42:E=40:R=5/100:V=20/100:T=0.5:OT=True Price=BSOption(S,E,R,V,T,OT) Debug.Print"S=42:E=40:R=5/100:V=20/100:T=0.5:OT=True" Debug.Print"[OT=True=Call]" Debug.Print"BSPrice="&Format(Price,"$0.0000")&vbNewLine S=42:E=40:R=5/100:V=20/100:T=0.5:OT=False Price=BSOption(S,E,R,V,T,OT) Debug.Print"S=42:E=40:R=5/100:V=20/100:T=0.5:OT=False" Debug.Print"[OT=False=Put]" Debug.Print"BSPrice="&Format(Price,"$0.0000")&vbNewLine Debug.Print"================================"&vbNewLine EndSub References BlackF,andMScholes,(1973),Thepricingofoptionsandcorporateliabilities,JournalofPoliticalEconomy,Vol81No3pp.637-654. HullJ,(2009),'Options,futures,andotherderivatives',7thed.,PearsonPrenticeHall Relatedmaterial BlackScholesontheHP10bII+financialcalculator DownloadtheExcelfileforthismodule:bs_nondiv.xlsm[29KB] DownloadtheVBAcodeforthismodule:xlf-black-scholes-code.txt[4KB] Developmentplatform:MicrosoftExcel2013Pro64bit. Revised:Tuesday9thofNovember2021-08:36PM,PacificTime(PT) Copyright©2011–2021♦DrIanO'Connor,CPA. | Privacypolicy Comment&Replypolicy