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


quote tvixubs etnsnormdist functionoption strategies straddlestd deviation variancehistorical volatility formulablack scholes option pricing model formuladirectional hedge fundhow to divide big numbers without a calculatorvix spxblack scholes merton calculatorhow to calculate the square root in excel2x s&p etfbest vix etfdebit call spreadbest macd settingswilliam sharpe nobel prizestandard deviation calculator financemacd settingswhat is an etn vs etfmomentum calculatorxiv velocityshareslist of etfharmonic mean excelskewness and kurtosis values for normal distributionput and calls for dummieswac methodcalculate annualized standard deviationdifference between moving average and exponential smoothingmeasure of skewness formulaexcel profit formulapopulation mean equationformula for average deviationproshares short vix short-term futures etfmean sd calculatordividend yield calculationcoefficient of skewnessrsi formulavariance and standard deviation formulasrsi divergenceblack scholes in excelskewness definition in statisticsciti reverse splitstatistics variance calculatorwhat does macd stand foratr indicator explainedcontango forward curvedefine squaringwacc formularcalculation of skewness2x etf listema or smawhat is the formula for variance in excelyahoo finance indexstandard deviation variance calculatorwhat does cboe stand formacd calculation excelmean average deviation calculatordefinition of sample variancewhat is standard deviation in mathsexcel stdev formulavix historicaldow jones industrial average divisoroption pricerdelta of option formulasquare root multiplied by a square rootsquaring negative numberscalculate rate of return in excelexcel cumulative formulathe greeks financecboe paper tradingmean reverting