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
t castle January 20th, 2011 at 12:50pm
The Greeks that are calculated on the OptionPage tab of OptionTradingWorkbook.xls appear to be dependent on Historical Volatility. Should not the Greeks be determined by Implied Volatility? Comparing the values of the Greeks calculated by this workbook produces values that agree with, e.g., the values at TDAmeritrade or ThinkOrSwim only if the formulas are edited to replace HV with IV.
Peter January 20th, 2011 at 5:40am
Not yet - do you have any examples you can suggest? What pricing model do they use?
r January 20th, 2011 at 5:14am
anything available for interest rate options?
Peter January 19th, 2011 at 8:48pm
It is the expected volatility that the underlying will realize from now until the expiration date.
general question January 19th, 2011 at 5:13pm
hi, is the historical volatility input annualized vol, or vol for the period from today to expiration date? thanks.
imlak January 19th, 2011 at 4:48am
very good, it solved my proble
SojaTrader January 18th, 2011 at 8:50am
very happy with the spreadsheet
very useful
thanks and regards from Argentina
Peter December 19th, 2010 at 9:30pm
Hi Madhuri, do you have Macros enabled? Please see the support page for details.
madhuri December 18th, 2010 at 3:27am
dear friend,
same opinion i have about the spread sheet that
"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"
-madhuri
MD November 25th, 2010 at 9:29am
Is these formulas will work for indian market? Please answer
Add a Comment