In this tutorial we will create an option pricing spreadsheet, implementing three popular binomial models: Cox-Ross-Rubinstein, Jarrow-Rudd and Leisen-Reimer.
The spreadsheet will calculate prices of American and European options on stocks, indexes and currencies.
The tutorial has six parts:
- (this page) Setting up input cells
- How binomial trees work (the logic)
- Creating binomial trees in Excel
- Cox-Ross-Rubinstein model Excel formulas
- Jarrow-Rudd model Excel formulas
- Leisen-Reimer model Excel formulas
If you are already familiar with binomial models and how binomial trees work, you can safely skip part 2.
If not, you will be able to complete the tutorial even if you know nothing about binomial models at the moment. In fact, this hands-on approach is the best way to learn them.
If you are only interested in one of the models, you can skip the parts for the other two models (part 4/5/6).
All three models share the same logic and most of the work is exactly the same (preparing input cells, building binomial trees – covered in parts 1-3). The only part where the models differ is the exact formulas for binomial tree up and down move sizes and probabilities (parts 4/5/6 for individual models).
Required Excel Skills
You don't need advanced Excel skills to complete this tutorial. You should be comfortable with basic concepts like writing and copying formulas or absolute and relative references.
The hard part of binomial models is the logic and layout of binomial trees; the mathematics is relatively simple. The Excel functions we will use are mostly basic, like
EXP, and a lot of
This tutorial will not use VBA and macros.
Let's Start: Preparing Input Cells
To calculate option prices with binomial models you need a number of inputs, like underlying price, strike price, time to expiration, volatility or interest rate. If you know the Black-Scholes model, you will find the inputs are the same.
It is best to prepare cells for all the inputs right at the beginning, and have all the input cells in one place. It will make the spreadsheet easier to use.
Let's put our inputs in cells
B4-B11 and their labels in column
The inputs are:
UndPrice= current underlying price
CallPut= whether the option is a call (1) or put (2)
AmEur= whether the option is American (1) or European (2)
Strike= the option's strike price
TimeDays= time to expiration as number of days; fractional days will also work
IntRate= the risk-free interest rate (domestic rate for currency options)
Yield= continuous dividend yield (for stocks, indexes) or foreign rate (for currency options)
For detailed explanation and which values to use, see Binomial Option Pricing Model Inputs.
The output which we want to calculate is the option price (
OptPrice) in cell
As in other tutorials and calculators, I use yellow background for input cells and green background for output cells.
Note: If you know how to create combo boxes, you can use them for the
AmEur inputs. Just make sure call is 1, put is 2, and American is 1, European is 2.
Naming the Input Cells
To make our formulas easier to write, understand and debug, it is best to name our input cells. It will allow use to write formulas like:
This is how to make it work:
First, make sure your labels in cells
A4-A11 are exactly like mine (
Select all the input cells and their labels (the selection is
In Excel main menu, go to "Formulas" / "Defined Names" section and click on "Create from Selection".
In the window that pops up, check "Left column". This tells Excel to use the contents in the left column (the labels in column
A) as names for the cells in the right column (the input values in column
B). Click OK.
Now when you select cell
B4 for instance, the small window on the left of the formula bar is showing "UndPrice" instead of "B4".
In formulas, you can now refer to the cell as
UndPrice instead of
1) You can also set or change cell names one by one by overwriting the text in the cell name window or by using Name Manager (as highlighted in the screenshot above).
2) The navigation may be different in other Excel versions. If you can't find it in yours, google "how to name cells in excel [your version]".
Next: Binomial Trees
We have our inputs ready and can start working on the calculations. The central part of any binomial option pricing model is the binomial tree, or more precisely, two trees – underlying price tree and option price tree.
In the next part, we will explain how they work (safe to skip if you already know that).
In the part that follows, we will actually create them in our spreadsheet.