- Based on the Black-Scholes option pricing model + its Merton's extension to account for dividends
- Calculates call and put option prices when given the parameters (underlying price, strike price, volatility, interest rate, dividend yield, and time to expiration)
- Calculates option Greeks – delta, gamma, theta, vega, rho
- Simulates and analyses potential scenarios in the market and the effect of individual parameters on option prices and Greeks
- Very simple navigation – you can start using it immediately
- PDF guide explaining option pricing, volatility, and the Black-Scholes model (including formulas for call price, put price, and Greeks)
- Works in all versions of Excel
The main sheet.
Calculating Option Prices and Greeks
You set parameters in the top left corner of the spreadsheet and see the resulting option price for a call and a put in cells H4 and H6.
You can see the Greeks at the top, above the charts.
Simulations and Charts
Here you can set charts. Choose if you want to display charts for a call or a put, what to display on the X axis (underlying price, time to expiration, or any other of the 6 parameters), and what to display on the Y axes of the upper and lower chart (option price or any of the Greeks).
Chart scales are calculated automatically based on the parameters entered, but if you want to analyze a particular part of the chart in a greater detail, you can override the automatic X scale.
On the chart above you can see the impact of underlying price changes on a call option's price and delta. You can also see how it develops with passing time when changing the number of days in cell C19, or see the impact of other parameters when changing the respective input cells in similar way.
This is an example of analysing the impact of passing time on a call option's price and theta. You can analyse any combination of parameters and price or Greeks. Detailed instructions, tips, and examples for simulations and scenario analyses are provided in chapter 7 of the PDF guide.
Formulas and Excel Functions Used
In the bottom part of the spreadsheet you can explore the calculations and Excel functions in detail. They are also described in chapters 9 and 10 of the PDF guide.
Black-Scholes PDF Guide
The Black-Scholes Calculator package also includes a PDF guide, which provides detailed instructions to its use, and also provides other explanation, formulas, tips, and notes for pricing options and using the Black-Scholes model. You can find an overview of the contents below:
BSC.xlsx = default version
BSC_for_Excel_97-2007.xls = Excel 97-2007 version
Every user gets access to both versions plus the PDF guide.