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.
Yahoo Finance - Provides Free Historical Prices of the Stock and Prices of Its Options
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 30th 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
Gupi on July 07, 2019:
Thanks for sharing this useful article.
prabhu on August 11, 2012:
I am getting very high figure for theta and vega for both call and put options. Is there any bug in the program?
monicamelendez from Salt Lake City on August 02, 2012:
As always, you're the Excel genius!
ngureco (author) on May 23, 2012:
Jose,
The risk free interest rate to use should depend on your ingenuity as an option trader.
The period to use to calculate the historical volatility should also be based on your ingenuity.
Usually, many traders will calculate historical volatility based on a period of 30 days and annualize it. If then you input that historical volatility in your calculation, then, the implied volatility is implying an annualized IV calculated based on a period of 30 days.
Jose on May 23, 2012:
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.
ngureco (author) on May 22, 2012:
Inderjeet Bhatnagar, and Jose.
I have again directly copied the formula stated on this article and pasted them into a new spreadsheet in my PC and they are working. The delta for calls is +ve and the delta is -ve for puts. Theta should be -ve for both puts and calls.
And to Jose, make sure the addin is well installed and that your spreadsheet is directed to where you have installed the addin. This is usually ..../appl data/microsoft/addin. Try saving your work in a flashdisk and then the next time you open your file in the flashdisk, excel will ask you where the addin is. And you will direct it to where the jrvarma addin is: usually, ..../appl data/microsoft/addin.
Jose on May 22, 2012:
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
Jose on May 22, 2012:
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
Inderjeet Bhatnagar on May 18, 2012:
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
colmovalor from India on September 17, 2011:
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.
Arun on September 10, 2011:
Very good article and very informative. Thanks.
TradeOptions from Houston on August 11, 2011:
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..
panakaj on March 17, 2011:
it is interesting but little difficult to understand
CanadianInvestor from Toronto, Canada on February 25, 2011:
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.
stock trading newsletter on July 06, 2010:
Not sure i understood all the 'Greek' stuff but informative none the less.
Cassandra Mantis from UK and Nerujenia on March 06, 2010:
Hy, this is an interesting Finance site here, well done on explaining some tricky terms. I enjoyed reading this hub.
Hello, hello, from London, UK on February 28, 2010:
Very informative and I will show it to my son. Thank you.
