- HubPages
*»* - Personal Finance
*»* - Investing in Stocks, Bonds, Real Estate, More

# Options Trading – Calculate Options Price - and Calculate Options Greeks Using Excel

## Calculate Multiple Options Greeks Using MS Excel

This hub will not dwell much on basics about options Greeks but willdwell on step by step instructions on how to calculate multiple options Greeksusing MS Excel. It is assumed the reader has the basics of options trading.Many commercial options analysis software can do this and also brokerage sitessuch as OptionVue can provide this information for you may be at a small fee.

## Be Creative

There are a few people who do not like using information from packages of which they do not understand how such information is arrived at. These people would want to be creative and come up with their trading plans that fit their needs. Either way, to trade options successfully, you must understands their dynamic very well, and I believe trying to calculate the options Greeks using Excel should be your first step in understanding the dynamics of options. However, it’s important to realize that trading in options is not for everyone because risks are high especially when you attempt to do something you do not understand fully.

## Options Greeks Need To Be Calculated

Options Greeks cannot simply be read up in your everyday option tables. Options Greeks need to be calculated. You can use an option calculator to calculate Greeks for individual options. You can also use MS Excel to calculate options Greeks for multiple options. This article is meant to show how to calculate options price and how to calculate options Greeks Using Excel.

The options Greeks that need to be calculated are Delta, Gamma, Theta, Vega and Rho.

## Definition of Options Greeks

**Definition of Options Delta** – options delta is a measure of how sensitive an
option price is to a change in the price of underlying security or stock. The
option delta tells you how much the option price will change if the price of a
stock increases or decreases by $1 in price. An options delta is the single
most important options Greeks one needs to understand in options trading. The
units of Delta are dollars.

**Definition
of Options Gamma** - Options Gamma is defined as the rate of change of
options delta with change of price of the underlying security. The units of
Gamma are dollars.

**Definition of Options Theta** – Options Theta is defined as measure of rate of
change of time value with the passage of time. It also called time decay. If
everything was held constant, then the option theta is the loss in price of
option per day. Theta units are dollars.

**Definition of Options Vega** – Vega is the change in the price of an option from a
1% change in implied volatility of options. Vega is the single most important
Greek that is ignored by many inexperienced options traders. Vega is very
important and a slight change in implied volatility results in a very
significant change in price of options. Out of the money options that contain
only extrinsic value, the price is fully determined by Vega and Theta. The
units of Vega are dollars.

**Definition of Rho** –
Rho measures
the estimated change in the options price with a 1% change in Interest Rate. Rho is the only Greek that
may not be very important in pricing of options because a 1% in interest change
may only change the price of an option by about $0.01 which is not very
significant.

**Definition of Stock’s Sigma** - sigma is the standard deviation which is
volatility. It’s the relative rate at which the price of a security moves up
and down. Volatility is found by calculating the annualized standard deviation
of daily change in price. You use the stock’s volatility to get the options
theoretical value which you can then use to compare with the actual price of
the option.

## MS Excel Add-In

Having now defined theoptions Greeks, we now have to turn to MS Excel and add an add-in that containsthe formulas for calculating the Greeks. It’s an Excel Add In (Visual Basic)for Black Scholes. This Excel add-in can also be used in Numerical Integrationand Probability Density Estimation.

1. Download Free this Excel Add In (Visual Basic) forBlack Scholes. Save the file “jrvarma” in Program Files\Microsoft Office\Office\Library, orwherever you wish. To install the add-in, run Excel, go to the Tools Menu andthen click on Add-ins. click Browse, and then go to where you saved your file “jrvarma”.After locating the jrvarma add-in, select the check box next to jrvarma in theAdd-Ins available box. When installed, the jrvarma add-in will adds "J. R. Varma's Add-in" to the Tools Menu on the main Menu Bar. Click on J. R. Varma's Add-in to run theapplications that come with this add-in. If you may have questions regarding add-ins, refer to MS Excel help.

## Black Scholes Option Valuation

1. This add-in uses Black Scholes Option Valuation and will calculate Implied Volatility and Option Greeks. In the interactive interface, you enter the stock price, the option exercise price, the time to maturity, the risk free rate and either the volatility or the option price. When the volatility is given, the add-in computes the option price and vice versa. In addition, all the option Greeks are also calculated.

a. BSCall(s, x, r, sigma, t) computes the call price from the stock price (s), the exercise price (x), the risk free rate (r), the volatility (sigma) and the time to maturity (t).

b. BSPutDelta(s, x, r, sigma, t) gives the put delta from the same parameters.

c. BSCallImplied(s, x, r, price, t) computes the implied volatility from the stock price (s), the exercise price (x), the risk free rate (r), the call price (price) and the time to maturity (t).

d. Similar functions are available in the same format for other option Greeks -gamma, theta, vega, rho for both put and call options.

## Free Historical Prices of the Stock and Prices of Its Options

1. Select the underlying stock, and go to yahoo finance to get free the historical prices of the security and the prices of its options. That information is freely available and can also be obtained from MSN finance and many other websites dealing with finance or stock trading information. You do not have to spend money on this. Remember Excel can import tens of options data all at once, so long as you tell it where to get that information.

## Input Data to Excel

1. Open an MS Excel sheet and in column A, input dates when the security traded

2. In column B, input the stock historical daily closing prices for the last 100 days or so because you want to use that historical price to calculate the volatility of your underlying stock. Current closing price of the stock today is $44.83.

3. In column C, input the call option price, say 5.25, which you can get free from yahoo finance

4. In column D, input the put option price, say 3.58, which you can get free from yahoo finance

5. In column E, input the call/put option expiration date, say 18-Sep-2010

6. In column F, input the risk free interest rate, say 1.52% equals 0.0152

7. In column G, calculate the time to maturity in years by inputting the formula “=(E1-A1)/365

8. In column H, input the call strike price, say 41.

9. In column “I”, input the put strike price, say 46.

## Calculating the Greeks for a Call Option

1. In column “J”, by use of BSCallImplied(s, x, r, price, t), calculate the implied volatility, for the call option, by inserting the formula “=BSCallImplied(B1, H1,F1,C1, G1)*100”

2. In column “K”, by use of BSCallDelta(s, x, r, sigma, t), calculate the Delta, for the call option, by inserting the formula “=BSCallDelta(B1, H1, F1, J1/100, G1)”

3. In column “L”, by use of BSCallGamma(s, x, r, price, t), calculate the Gamma of call option by inserting the formula “=BSCallGamma(B1, H1, F1, J1/100, G1)”

4. In column “M”, by use of BSCallTheta(s, x, r, price, t), calculate the Theta of call option by inserting the formula “=BSCallTheta(B1, H1, F1, J1/100, G1)”

5. In column “N”, by use of BSCallVega(s, x, r, price, t), calculate the Vega of call option by inserting the formula “=BSCallVega(B1, H1, F1, J1/100, G1)”

## Calculating the Statistical Volatility of the Underlying Stock

1. In column “O”,
Insert the formula “=LN(B1/B2)*LN(B1/B2)” and extend it downward to at least
the 30^{th} row.

2. In column “P”, calculate the volatility (sigma) of underlying stock, by inserting the formula “=(SQRT(SUM(O1:O30)/30)*SQRT(252)*100)”.The preferred period for a stock’s volatility is 30 days, you can as well choose 20 days or whatever. There are 252 trading days in a normal US year.

3. In column “Q”, by use of BSCall(s, x, r, sigma, t), calculate the Theoritical Price of the call option by inserting the formula “ =BSCall(B1, H1, F1, P1/100, G1)”

## Calculating the Greeks for a Put Option

1. In column “R”, by use of BSPutImplied(s, x, r, price, t), calculate the implied volatility, for the put option, by inserting the formula “=BSPutImplied(B1, I1,F1,D1, G1)*100”

2. In column “S”, by use of BSPutDelta(s, x, r, sigma, t), calculate the Delta, for the put option, by inserting the formula “=BSCallDelta(B1, I1, F1, R1/100, G1)”

3. In column “T”, by use of BSPutGamma(s, x, r, price, t), calculate the Gamma of put option by inserting the formula “=BSPutGamma(B1, I1, F1, R1/100, G1)”

4. In column “U”, by use of BSPutTheta(s, x, r, price, t), calculate the Theta of put option by inserting the formula “=BSPutTheta(B1, I1, F1, R1/100, G1)”

5. In column “V”, by use of BSPutVega(s, x, r, price, t), calculate the Vega of call option by inserting the formula “=BSPutVega(B1, I1, F1, R1/100, G1)”

6. In column “W”, by use of BSPut(s, x, r, sigma, t), calculate the Theoritical Price of the put option by inserting the formula “=BSput(B1, I1, F1, P1/100, G1)”

That’s it. You now have the Greeks of a call option and a put option of a stock trading at $44.83. You also have the theoretical call option price as well as the put option price.

## Create Several Combinations Using the Option Chain

You have Delta, Gamma, Theta,Vega, Rho,implied volatility, theoretical price and actual price of both a call optionand put option. You also have the statistical volatility of the underlyingstock or security. You have all these in MS Excel. You also have a free sourceof real time option prices which is yahoo finance. Excel can import in to aworksheet the entire option chain of a stock’s options. That’s all you need tocreate several combinations of long puts, long calls, short calls and shortputs options while the excel is at the same time computing the net Greeks ofyour combinations.

## Multiple Greeks

Put this step by step calculation of multiple Greeks using excel in to use. If you are creative, the sky should be the limit in your options trading. Good trading.

^{If you have liked this article, and you would want this page to
keep up and improved, you can help by purchasing some great items from Amazon
by following Amazon links and widgets on this page. A free way to help would be
to link back to this webpage from your web page, blog, or discussion forums.}

^{The Author’s page is designed to help beginners and average
readers make some money as an extra income to supplement what they may be
earning elsewhere - details of which you can find in My Page, if you will.}

## Comments

I am getting very high figure for theta and vega for both call and put options. Is there any bug in the program?

As always, you're the Excel genius!

Thanks! It's working.

But, just 2 questions:

1.If I am calculating the option value for example for Eurex, which risk free of interest would you consider to use? EURIBOR 1Y?

2. Is the implied volatility calculated from the BSCallImplied formula expressed anually or just for the time t I've put in the calculation?

Thanks again.

Hi. I just want to say Thank you for the Add-In.

I've done some calculations but it seems that I don't know how to use it, because it always says Delta=0. The data I am taking to caculate the Call Delta is: StockPrice = 609; Strike = 580; CallPrice = 30.80; Time = 3days (0,0082); RiskRateFree = 5% (0.05)

Can someone help me out, please?

If you want to know me I would love to know more about how to use this software.

jem890@gmail.com (Skype ID: jf.escribano)

Thanks in advance

Hi. I just want to say Thank you for the Add-In.

I've done some calculations but it seems that I don't know how to use it,

because it always says Delta=0. The data I am taking to caculate the Call Delta is: StockPrice = 609; Strike = 580; CallPrice = 30.80;

Time = 3days (0,0082); RiskRateFree = 5% (0.05)

Can someone help me out, please?

I you want to know me I would love to know more about how to use this software.

jem890@gmail.com (skype ID: jf.escribano)

Thanks in advance

Thanks for the painstaking work you have put up here.I am using this spreadsheet to trade Delta Hedging on Nifty(yahoo: NSEI.NS)options.

I just found a bug in my spreadsheet which could be a possible Ctrl+C error or typo:

"In column “S”, by use of BSPutDelta(s, x, r, sigma, t), calculate the Delta, for the put option, by inserting the formula “=BSCallDelta(B1, I1, F1, R1/100, G1)” "

Actually I have directly copied the formula and pasted into the spreadsheet, and I got +ve delta's for Puts. Replaced the BSCallDelta with BSPutDelta, and I am getting -ve delta's(as predicted).

Kindly confirm if I am doing it correctly or should I use the formulae as written.

Regards

Inderjeet Bhatnagar

www.goldleafindia.com

Very nice info, even those who do not know all the details still can trade options provided they stick to trading of one particular one, maybe one index, and follow it long enough to become a habit, he may not be able to explain why it is so, but he knows it will do so.

Very good article and very informative. Thanks.

OEdge is another (free) excel add-in designed to evaluate options based on the Black-Schoeles model if you want to check that out. Oedge seems a bit simpler..

it is interesting but little difficult to understand

Very informative hub, that I'm sure people intent on options trading will appreciate (I'm more of a conservative dividend investor, I guess). I would just like to point out that when you say "However, it’s important to realize that trading in options is not for everyone because risks are high...", I think it's important to realize that some options trading can in fact greatly REDUCE the risk. Think for example if you buy a put for a stock you own. This certainly reduced the risk, although of course at a price.

Not sure i understood all the 'Greek' stuff but informative none the less.

Hy, this is an interesting Finance site here, well done on explaining some tricky terms. I enjoyed reading this hub.

Very informative and I will show it to my son. Thank you.

16