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
rknox April 24th, 2009 at 10:55am
Very Cool! Very nicely done. You sir, are an artist. One old hacker (76 years old - started on the PDP 8) to another.
Peter April 6th, 2009 at 7:37am
Hi Ken,
Take a look at the following page:
https://www.optiontradingtips.com/pricing/workbook-support.html
Ken April 6th, 2009 at 5:21am
Hi, What if i am using the Office on Mac? it has an invalid name error (#name?) for all the results cells... thx
giggs April 5th, 2009 at 12:14pm
Ok, it's working now. I saved & closed the excel file, opened again, and the results were there, in the blue areas! FYI, I had enabled all the macros in "Security of the macros" . Can't wait to play with the file now...
giggs April 5th, 2009 at 12:06pm
I don't see the popup. I use Excel 2007 under Vista. The presentation is quite different from the previous versions. I enabled all macros. But I still get the #name error. Any idea?
giggs April 5th, 2009 at 12:00pm
I don't see the popup. I use Excel 2007 under Vista. The presentation is quite different from the previous versions. Any idea?
Admin March 23rd, 2009 at 4:17am
Hi Dissapointed,
The spreadsheet requires Macros to be enabled for it to work. Do you see a popup on the toolbar asking you if you want to enable this content? Just click it and select "enable".
Please send me an email if you need further clarification.
disappointed March 22nd, 2009 at 4:25pm
this model doesn't work, no matter what you put in on the basic page for values, it has an invalid name error (#name?) for all the results cells. Even when you first open the thing, the default values the creator put in don't even work
Add a Comment