This is the user manual for the Binomial Option Pricing Calculator.
On this page you can find essential information to get started.
For more details and examples, follow the links to individual sections. You can find full user guide contents in the right sidebar or at the bottom of this page.
Use the Right Version
From the download page you can download two different Excel files, for different versions of Excel:
If you have Excel 2007 or newer, use the default version, BOPC.xlsm.
If you have Excel 2003 or older, use the other file, BOPC_for_Excel_97-2003.xls.
All features, calculations and results are exactly the same in both files. The only difference is graphical design, due to limitations of old Excel versions.
Enable Calculations and Macros
If you have just downloaded the calculator and opened it for the first time, you may find the dropdown boxes empty and calculations not working.
This is when your Excel opens the file in Protected View or a similar restricted regime where macros and calculations are blocked for security. Usually you will see a notice in place or below your top Excel menu, with a button to "Enable Editing", "Enable Macros" or "Enable Calculations" – the wording varies, depending on your Excel version and settings.
Click that button (in some cases you also need to click another button afterwards to enable another thing) and everything should work normally.
Sheets
There are seven sheets:
The sheet Main is where you enter inputs, view results, and control and view the chart.
In the sheet ChartData you can inspect exact values from the chart (X-axis values in column P; Y-axis values for the three series in columns Q, R, S).
The next three sheets contain the binomial trees used in the pricing models.
UndTree is the underlying price tree. Each step is in one column. Moving horizontally from left to right, underlying price goes up; moving diagonally one cell to the right and one cell down, underlying price goes down.
OptTree is the option price tree. The last step shows option payoff at expiration for different levels of underlying price (the price in the same cell in UndPrice sheet). Step zero (cell E4 in OptTree) is the calculated current option price, which you can also see in cell E4 in the sheet Main.
ExDivTree is the tree for underlying price excluding dividends (it is only used with discrete dividends; when working with continuous dividends the numbers in this sheet are exactly the same as those in UndTree).
The next sheet, Lists, contains lists of items for the dropdown boxes, various constants and background calculations that make the models work. Do not change anything in this sheet, unless you want to change the way the calculator works, and know what you are doing.
The last sheet, Preferences, contains settings which users can safely change, such as the preferred units and sign of option theta.
Pricing Options in the Calculator
If you have basic understanding of options, using the calculator should be very simple. It does not require any particular Excel skills, other that entering values in cells and selecting items in dropdown boxes.
First you need to enter inputs. This is done in the Main sheet.
Some inputs, such as underlying price, strike price or volatility, are set by overwriting the values in yellow cells.
Other inputs, such as Call/Put, American/European, are selected in dropdown boxes.
When you have set all your inputs, you can view the resulting option price and Greeks in the green cells E4-J4 in the Main sheet.
If you want to inspect how the option price is calculated at each step in the binomial trees, see the sheets UndPrice and OptPrice.
The Yellow vs. Green Cell Rule
To help users diferentiate between the inputs cell (which you can overwrite) and output cells (which you should not change), this and all other Macroption calculators use a consitent system of cell background colors:
Yellow cells are for inputs. Overwrite the values in yellow cells to change inputs or preferences.
Green cells are for outputs. Do not change them. They usually contain formulas, but even when a green cell contains a fixed value, that value is changed by the calculator (e.g. by a macro), not by the user.
In some sheets, there are also cells with other colors. Generally yellow and red are fixed value cells, while green and blue are formula cells (or cells with fixed values changed by macros).
In this calculator:
- Change preferences by overwriting the values of yellow cells in the sheet Preferences.
- Change option pricing inputs and chart settings by overwriting yellow cells or selecting dropdown boxes in the sheet Main.
- Other cells and other sheets are for viewing only.
Charts and Scenario Analysis
The chart has two possible modes, selected in the Chart Mode dropdown box in cell F6.
The Single Line mode can model the effect of any of the inputs on option price or any of the Greeks. For instance, how option price changes with passing time or how strike selection affects gamma. See how to work with the chart.
The Scenario Analysis mode allows you to display up to three lines in the chart, modeling the combined effect of two different inputs. One input is different for each line, while the other is on the X-axis. For more details and examples see Scenario Analysis.
You can control the chart scale with the buttons to the right of the chart – zoom in or out, or move left or right along the X-axis.
Option Pricing Models
The calculator supports three of the most popular binomial option pricing models:
By default, the calculator uses the Leisen-Reimer model with 21 steps. You can change this in the Main sheet, cell C3 (model) and C4 (steps).
For detailed explanation of the models and which to use, see Models and Number of Steps.
Support
If you have any questions or feedback, or encounter any problems or unexpected behavior, please feel free to contact me.