[**UPDATE**: After reading this post, see here for an updated version of the spreadsheet].

The Black-Scholes option valuation formula for an option paying a continuous dividend yield is the following:

Where

and

Attached is a simple Excel function that calculates the Black-Scholes option value for a specific set of input parameters. Currently, it just calculates the call value – if you use it as an array function, it will return a 4-element array with call value, call delta, put value, put delta, respectively. You could extend it pretty easily to calculate the rest of the Greeks.

Here is the code for the function (To create an Excel function, press `ALT-F11`

in your workbook and select `Insert>Module`

. Note that the dividend yield parameter is optional.

FunctionBlackScholes(SpotPriceAsDouble,ExercisePriceAsDouble,TimeToMaturityAsDouble,RiskFreeRateAsDouble,sigmaAsDouble,Optional DividendYieldAsDouble)AsDouble()Dimd1AsDoubleDimd2AsDoubleDimNd1AsDoubleDimNd2AsDoubleDimResultArray()AsDoubleReDimResultArray(4)AsDoubleIf(IsMissing(DividendYield))Thend1=WorksheetFunction.Ln(SpotPrice/ExercisePrice)+((RiskFreeRate+(0.5*(sigma ^ 2)))*TimeToMaturity)Elsed1=WorksheetFunction.Ln(SpotPrice/ExercisePrice)+((RiskFreeRate-DividendYield+(0.5*(sigma ^ 2)))*TimeToMaturity)EndIfd1=d1/(sigma*(TimeToMaturity ^(1/2)))d2=d1-(sigma*(TimeToMaturity ^(1/2)))Nd1=WorksheetFunction.NormSDist(d1)Nd2=WorksheetFunction.NormSDist(d2)'Call ValueIf(IsMissing(DividendYield))ThenResultArray(0)=(SpotPrice*Nd1)-(ExercisePrice*Exp(-RiskFreeRate*TimeToMaturity)*Nd2)ElseResultArray(0)=Exp(-DividendYield*TimeToMaturity)*(SpotPrice*Nd1)-(ExercisePrice*Exp(-RiskFreeRate*TimeToMaturity)*Nd2)EndIf'Call Delta ResultArray(1)=Nd1 'Put ValueIf(IsMissing(DividendYield))ThenResultArray(2)=Exp(-RiskFreeRate*TimeToMaturity)*ExercisePrice*(1-Nd2)-SpotPrice*(1-Nd1)ElseResultArray(2)=Exp(-RiskFreeRate*TimeToMaturity)*ExercisePrice*WorksheetFunction.NormSDist(-d2)-Exp(-DividendYield*TimeToMaturity)*SpotPrice*WorksheetFunction.NormSDist(-d1)EndIf'Put delta ResultArray(3)=-WorksheetFunction.NormSDist(-d1)BlackScholes=ResultArrayEndFunction

Save this, and set up the input parameters in Excel. Select a range of 4 cells, and then click the f(x) function selection button. Choose from the list of User Defined functions, and then select BlackScholes. Excel will prompt you for the input parameters:

When you are finished inputing the parameters, press `CTRL+SHIFT+ENTER`

to execute the function. Excel will populate the four cells with the calculated option values:

A sample workbook is attached:BlackScholes.xls