- 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.
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 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.
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.