Option Greeks Excel Formulas

Black-Scholes Greeks Excel Formulas

This is the second part of the Black-Scholes Excel guide covering Excel calculations of option Greeks (delta, gamma, theta, vega, and rho) under the Black-Scholes model. I will continue in the example from the first part to demonstrate the exact Excel formulas. See the first part for details on parameters and Excel formulas for d1, d2, call price, and put price.

Here you can find detailed explanations of all the Black-Scholes formulas.

Here you can see how everything works together in Excel in the Black-Scholes Calculator.

Black-Scholes Calculator

Delta in Excel

Delta is different for call and put options. The formulas for delta are relatively simple and so is the calculation in Excel.

Black-Scholes formula for call option delta Black-Scholes formula for put option delta

I calculate call delta in cell V44, continuing in the example from the first part, where I have already calculated the two individual terms in cells M44 and S44:

=M44*S44

Black-Scholes Calculator

The calculation of put delta is almost the same, using the same cells. Just add minus one and don’t forget the brackets:

=S44*(M44-1)

Black-Scholes Calculator

Gamma in Excel

The formula for gamma is the same for calls and puts. It is slightly more complicated than the delta formulas above:

Black-Scholes formula for gamma

Notice especially the second part of the formula:

Standard normal probability density function

You will find this term in the calculation of theta and vega too. It is the standard normal probability density function for -d1. In Excel the formula looks like this:

=EXP(-1*POWER(K44,2)/2)/SQRT(2*PI())

… where K44 is the cell where you have calculated d1 (see first part).

Alternatively, you can use the NORM.DIST Excel function, which I have also explained in the first part. The only difference from the first part is that the last parameter (cumulative) is now FALSE. Don’t forget the minus sign before K44:

=NORM.DIST(-K44,0,1,FALSE)

These two formulas must return the same result.

In the example from the Black-Scholes Calculator I use the first formula. The whole formula for gamma (same for calls and puts) is:

=EXP(-1*POWER(K44,2)/2)/SQRT(2*PI())*S44/(A44*J44)

Black-Scholes Calculator

Theta in Excel

Theta has the longest formulas of all the five most common option Greeks. It is different for calls and puts, but the differences are again just a few minus signs here and there and you must be very careful. Theta is very small for many options, which makes it often hard to detect a possible error in your calculations.

Black-Scholes formula for call option theta Black-Scholes formula for put option theta

Although it looks complicated, all the symbols and terms in the formulas should be already familiar from the calculations of option prices and delta and gamma above. One exception is the T at the beginning of the formulas.

T is the number of days per year. You can choose either calendar days (T=365 or 365.25) or trading days (T=252 or something similar, depending on where you trade). Based on your selection, the interpretation of theta will then be either option price change in one calendar day or option price change in one trading day.

Call Option Theta

The whole formula for call theta in our example is in cell X44. It is long and uses several (10) other cells, but there is no high mathematics:

=(-(A44*EXP(-1*POWER(K44,2)/2)/SQRT(2*PI())*C44*S44/
(2*SQRT(G44)))-(D44*R44*O44)+(E44*A44*M44*S44))/
IF($C$20=2,’Time Units’!$D$4,’Time Units’!$D$3)

Black-Scholes Calculator

The last line of the formula in the screenshot above is the T. Cell C20 in the calculator contains a combo where users select calendar days or trading days. Cells D3 and D4 in the sheet Time Units contain the number of calendar and trading days per year. If you want to keep it simple, you can replace the whole last line of the formula with a fixed number, such as 365.

You can again find the explanation of all the individual cells in the first part or see all these Excel calculations directly in the calculator.

Put Option Theta

Analogically to call theta, the formula for put theta in cell AD44 is:

=(-(A44*EXP(-1*POWER(K44,2)/2)/SQRT(2*PI())*C44*S44/
(2*SQRT(G44)))+(D44*R44*P44)-(E44*A44*N44*S44))/
IF($C$20=2,’Time Units’!$D$4,’Time Units’!$D$3)

Black-Scholes Calculator

Vega in Excel

The formula for vega is the same for calls and puts:

Black-Scholes formula for vega

There is nothing new. You can again see the familiar term at the end.

In the calculator example I calculate vega in cell Y44:

=EXP(-1*POWER(K44,2)/2)/SQRT(2*PI())*S44*A44*SQRT(G44)/100

Black-Scholes Calculator

Rho in Excel

Rho is again different for calls and puts. There are two more minus signs in the put rho formula.

Black-Scholes formula for call option rho Black-Scholes formula for put option rho

In the calculator example I calculate call rho in cell Z44. It is simply a product of two parameters (strike price and time to expiration) and cells that I have already calculated in previous steps:

=B44*G44*Q44*O44/100

Black-Scholes Calculator

I calculate put rho in cell AF44, again as product of 4 other cells, divided by 100. Make sure to put the minus sign to the beginning:

=-B44*G44*Q44*P44/100

Black-Scholes Calculator

More about Option Greeks in Excel

You can also use Excel and the calculations above (with some modifications and improvements) to model behaviour of individual option Greeks and option prices in different market situations (changes in the Black-Scholes model parameters). That is beyond the scope of this guide, but you can find it in the Black-Scholes Calculator and PDF Guide.


Related pages


uvxy proshareshow to calculate intrinsic value of stock optionscalculating kurtosisgeorge soros fund holdingsrsi interpretationcv calculation excelmean mode median calculatorderivation of variance formulacot reportscall option strike pricestandard deviation formula derivationintrinsic stock priceproshares.comimplied volatility definitionblack scholes pricerput option payoffbears and bulls meaningoversold definitionweighted index definitionput straddleannualizestocks at all time lowscalculator for variancecost of debt calculator waccformula waccgoal seek formula in excel 2010what is sampling variationcalculating the sample variancecalculate option deltacumulative normal distribution excelthe straddle positionarithmetic mean calculation formulasec 13f listlong straddle optionstheta option greekoptions on vixcurrent djia divisoryahoo finance stock quotes in excelvix averages&p futures symbolvariance calculator excelmacro hedge fund definitionmsft yahoogreeks calculatorintrinsic value of a put optionexcel stdevaverage true range formulaunbiased standard deviation calculatoryahoo market indicessharpe ratiosstandard deviation calculator financehedge fund forex strategyexcel formula explainedmeasure of skewness formulahedge fund 13f filingsgeometric average excelvariance formula excelinverse vix etfhow is implied volatility calculatedhow to interpret kurtosisblack scholes option pricing model examplecalculate median on excelcalculating formulas in excelending inventory formula accountingstdev calculationsquare root multiplied by square rootskewness kurtosis normal distributionexcel formula for medianblack scholes thetaput and call options for dummiesskewness excelnotepad font sizewhen do vix options expirepercent relative standard deviation calculatorwhat is a macro hedge fund