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
Gina September 2nd, 2011 at 3:04pm
If you look at Dec 2011 PUTs for netflix - I have a put spread - short 245 and long 260 - why doesn't this reflect a profit of 15 instead of 10?
Any idea?
Gina
Mahajan September 2nd, 2011 at 6:58am
Hi Peter,
First of all tons of thanks for providing the useful excel.I am very new to options (previously i was trading in commodities futures).Can you please help me in understanding, how i can use these calculations for future trading(silver,gold,etc) ?
If there is any link please provide me the same.
Thanks again for enlightening thousand of traders.
Cheers,
Mahajan
Peter August 26th, 2011 at 1:41am
Hi Edwin,
There isn't currently a sheet specifically for calendar spreads, however, you're welcome to use the formulas provided to build your own with the parameters needed.
You can email me if you like and I can try and help you with an example.
Edwin CHU (HK) August 26th, 2011 at 12:59am
I am an active options trader with my own trade boob, I find your worksheet "Options Strategies quite helpful, BUT, can it cater for calendar spreads, I caanot find a clue to insert my positions when faced with options and fut contracts of different months?
Look forward to hearing from you soon.
Peter June 28th, 2011 at 6:28pm
Oh, use the Contact Form.
Sunil June 28th, 2011 at 11:42am
on which mail id should i send ?
Peter June 27th, 2011 at 7:07pm
Hi Sunil, send me an email and we can take it the conversation offline.
Sunil June 27th, 2011 at 12:06pm
Hi Peter, many thanks. I had gone through the VB functions but they use many inbuild excel functions for calculations. I wanted to write the program in Foxpro (old time language) which does not have the inbuild functions in it and hence was looking for basic logic in it. Never the less, the excel is also very useful, which i don't think anyone else has also shared on any site.
I went through the complete material on Options and you have really done a very good knowledge sharing on Options. You have really discussed in depth near about 30 strategies....Hats off. Thanks
Peter June 27th, 2011 at 6:06am
Hi Sunil, for Delta and Implied Volatility the formulas are included in the Visual Basic provided with the spreadsheet at the top of this page. For Historical Volatility you can refer to the page on this site on calculating volatility. However, I am not sure on the profit probability - do you mean the probability that the option will expire in the money?
Sunil June 26th, 2011 at 2:24am
Hi Peter,
How do i calculate the following. I want to write a program to run it on various stocks at a time and do first level scanning.
1. Delta
2. Implied volatility
3. Historical Volatility
4. Profit Probability.
can you please guide me on the formulas.
Add a Comment