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 March 26th, 2012 at 7:42pm
Hi Amitabh,
I suppose for short term trading the payoffs and strategy profiles become irrelevant. You'll just be trading off short term fluctuations in price based off expected movements in the underlying.
Amitabh March 15th, 2012 at 10:02am
Hi Peter
How can this good work of yours be used for intraday or short term trading of options as these options make short-term tops and bottoms. Any strategies for same?
Warm wishes
Amitabh Choudhury
[email removed]
madhavan March 13th, 2012 at 7:07am
First time I am going through any useful write up on option trading. Liked very much. But have to make an indepth study to enter into trading.
Jean charles February 10th, 2012 at 9:53am
Hi Peter,
I have to say your website is great ressource for option trading and carry on. I was looking for your worksheet but for forex underlying instrument. I saw it but You don't offer to download.
[email removed]
Peter January 31st, 2012 at 4:28pm
Do you mean an example of the code? You can see the code in the spreadsheet. It is also written on the Black Scholes page.
dilip kumar January 31st, 2012 at 3:05am
Hi
please give example.
Peter January 31st, 2012 at 2:06am
You can open the VBA editor to see the code used to generate the values. Alternatively you can look at the examples on the black scholes model page.
iqbal January 30th, 2012 at 6:22am
Hi,
How is it that I can see the actual formula behind the cells that you have used to obtain the data? Thank you in advance.
Peter January 26th, 2012 at 5:25pm
Hi Amit, is there an error that you can provide? What OS are you using? Have you seen the Support Page?
amit January 25th, 2012 at 5:56am
hi..
The workbook is not opening....
Add a Comment