Excel Option Pricer
Excel based Black and Scholes calculator for European stock/index options and American options using dividend yields.
- Theoretical Price and Option Greeks support
- Implied Volatility Calculator
- Configurable Payoff Graphs
- Option Strategy Construction
- VBA used is unlocked and editable
Grab Your Free Copy
Enter your name and email and I'll send the workbook straight to your inbox.
My option pricing spreadsheet will allow you to price European call and put options using the Black and Scholes model.
Understanding the behavior of option prices in relation to other variables such as underlying price, volatility, time to expiration etc is best done by simulation. When I was first learning about options I began building a spreadsheet to help me understand the payoff profiles of calls and puts and also what the profiles look like of different combinations. I've uploaded my workbook here and you're welcome to it.
Simplified
On the "basic" worksheet tab you will find a simple option calculator that generates fair values and option Greeks for a single call and put according to the underlying inputs you select. The white areas are for your user input while the shaded green areas are the model outputs.
Implied Volatility
Underneath the main pricing outputs is a section for calculating the implied volatility for the same call and put option. Here, you enter the market prices for the options, either last paid or bid/ask into the white Market Price cell and the spreadsheet will calculate the volatility that the model would have used to generate a theoretical price that is in-line with the market price i.e. the "implied" volatility.
Payoff Graphs
The PayoffGraphs tab gives you the profit and loss profile of basic option legs; buy call, sell call, buy put and sell put. You can change the underlying inputs to see how your changes effect the profit profile of each option.
Strategies
The Strategies tab allows you to create option/stock combinations of up to 10 components. Again, use the white areas for your user input while the shaded areas are for the model outputs.
Formulas
Theoretical and Greek Prices
Use this Excel formula for generating theoretical prices for either call or put as well as the option Greeks:
- Type
- c = Call, p = Put, s = Stock
- Output
- p = theoretical price, d = delta, g = gamma, t = theta, v = vega, r = rho
- Underlying Price
- The current market price of the stock
- Exercise Price
- The exercise/strike price of the option
- Time
- Time to expiration in years e.g. 0.50 = 6 months
- Interest Rates
- As a percentage e.g. 5% = 0.05
- Volatility
- As a percentage e.g. 25% = 0.25
- Dividend Yield
- As a percentage e.g. 4% = 0.04
A sample formula would look like =OTW_BlackScholes(c, p, 25, 26, 0.25, 0.05, 0.21, 0.015).
Implied Volatility
Same inputs as above except:
- Market Price
- The current market last, bid/ask of the option
Example: =OTW_IV(p, 100, 100, 0.74, 0.05, 8.2, 0.01)
Support
If you're having troubles getting the formulas to work, please check out the support page or send me an email.
If you're after an online version of an option calculator then you should visit Option-Price.com
Much of what I learnt that made this spreadsheet possible was taken from the highly acclaimed book on financial modeling by Simon Benninga — Financial Modeling - 3rd Edition. If you're an Excel junkie, you'll love it.
118 Comments
Peter June 18th, 2011 at 2:11am
Pop up? What do you mean?
shark June 17th, 2011 at 2:25am
where is the pop up
Peter June 4th, 2011 at 6:46am
Hi DevRaj,
You can try my volatility spreadsheet that will calculate the historical volatility that you can use in the option model.
DevRaj June 4th, 2011 at 5:55am
Very useful nice article and the excel is very good
Still one question
How to calculate volatility using (option price, spot price, time )
?
Satya May 10th, 2011 at 6:55am
Hi Peter,
I have just started using the spreadsheet provided by you for option trade. A wonderful easy to use stuff with adequate tips for easy usage.
Thanks for your best efforts to help educate the society.
Regards
Satya
Peter March 28th, 2011 at 4:43pm
It works for any European option - irrespective of the country where the options are traded.
Emma March 28th, 2011 at 7:45am
Do you have it for Irish stocks???
Peter March 9th, 2011 at 9:29pm
Hi Karen, those are some great points!
Sticking to a system/methodology is very hard...it is easy to be distracted by all of the offers out that are out there.
I am looking closely at a few option picking services right now and plan to list them on the site if they prove to be successful.
Karen Oates March 9th, 2011 at 8:51pm
Is your option trading not working because you haven't found that right system yet or because you won't stick to one system?
What can you do to find the right system and then stick to it?
Could a lot of what is not working for you be because of how you are thinking? Your beliefs and mindset?
Working on improving yourself will help all areas of your life.
Peter January 20th, 2011 at 5:18pm
Sure, you can use implied volatility if you like. But the point of using a pricing model is for you have your own idea of volatility so you know when the market is "implying" a value different to your own. Then, you are in a better position to determine if the option is cheap or expensive based on historical levels.
The spreadsheet is really more of a learning tool. To use implied volatilities for the greeks in the spreadsheet would require the workbook to be able to query option prices online and download them to generate the implied volatilities. That's why I have unlocked the VBA code in the spreadsheet so that users can customize it to their exact needs.
Add a Comment