Binomial Option Pricing Tutorial and Spreadsheets

This tutorial introduces binomial option pricing, and offers an Excel spreadsheet to help you better understand the principles.  Additionally, a spreadsheet that prices Vanilla and Exotic options with a binomial tree is provided.

Scroll down to the bottom of this article to download the spreadsheets, but read the tutorial if you want to lean the principles behind binomial option pricing.

Binomial option pricing is based on a no-arbitrage assumption, and is a mathematically simple but surprisingly powerful method to price options. Rather than relying on the solution to stochastic differential equations (which is often complex to implement), binomial option pricing is relatively simple to implement in Excel and is easily understood.

No-arbitrage means that markets are efficient, and investments earn the risk-free rate of return.

Binomial trees are often used to price American put options, for which (unlike European put options) there is no close-form analytical solution.

Price Tree for Underlying Asset

Consider a stock (with an initial price of S₀) undergoing a random walk. Over a time step Δt, the stock has a probability p of rising by a factor u, and a probability 1-p of  falling in price by a factor d. This is illustrated by the following diagram.

One Step Binomial Model

Cox, Ross and Rubenstein Model

Cox, Ross and Rubenstein (CRR) suggested a method for calculating p, u and d.  Other methods exist (such as the Jarrow-Rudd or Tian models), but the CRR approach is the most popular.

Over a small period of time, the binomial model acts similarly to an asset that exists in a risk neutral world. This results in the following equation, which implies that the effective return of the binomial model (on the right-hand side) is equal to the risk-free rate

Additionally, the variance of a risk-neutral asset and an asset in a risk neutral world match. This gives the following equation.

The CRR model suggests the following relationship between the upside and downside factors.

Rearranging these equations gives the following equations for p, u and d.

The values of p, u and d given by the CRR model means that the underlying initial asset price is symmetric for a multi-step binomial model.

Two-Step Binomial Model

This is a two-step binomial lattice.

Two-Step Binomial Model

At each stage, the stock price moves up by a factor u or down by a factor d.  Note that at the second step, there are two possible prices, u d S₀ and d u S₀. If these are equal, the lattice is said to be recombining. If they are not equal, the lattice is said to be non-recombining.

The CRR model ensures a recombining lattice; the assumption that u = 1/d means that u d S₀ = d u S₀ = S₀, and that the lattice is symmetrical.

Multi-Step Binomial Model

The multi-step binomial model is a simple extension of the principles given in the two-step binomial model. We simply step forward in time, increasing or decreasing the stock price by a factor u or d each time.

Multi-Step Binomial Model

Each point in the lattice is called a node, and defines an asset price at each point in time. In reality, many more stages are usually calculated than the three illustrated above, often thousands.

Payoffs for Option Pricing

We will consider the following payoff functions.

Put: V_N = max(X – S_N, 0)

Call: V_N = max(S_N – X, 0)

V_N is the option price at the expiry node N, X is the strike or exercise price, S_N is the stock price at the expiry node N.

We now need to discount the payoffs back to today. This involves stepping back through the lattice, calculating the option price at every point.

This is done with an equation that varies with the type of option under consideration. For example, European and American options are priced with the equations below.

European Put: V_n = e^-rΔt(p V_u + ( 1 – p ) V_d )

European Call: V_n = e^-rΔt(p V_u + ( 1 – p ) V_d

American Put: V_n = max(X – S_n, e^-rΔt ( p V_u + ( 1 – p ) V_d ))

American Call: V_n = max(S_n – X, e ^–rΔt ( p V_u + ( 1 – p ) V_d ))

N is any node before expiry.

Binomial Option Pricing in Excel

This Excel spreadsheet implements a binomial pricing lattice to calculate the price of an option. Simply enter some parameters as indicated below.

Excel will then generate the binomial lattice for you. The spreadsheet is annotated to improve your understanding.

Note that the stock price is calculated forward in time.  However, the option price is calculated backwards from the expiry time to today (this is known as backwards induction).

The spreadsheet also compares the Put and Call price given by the binomial option pricing lattice with that given by the analytic solution of the Black-Scholes equation; for many time steps in the lattice, the two prices converge.

If you have any questions or comments about this binomial option pricing tutorial or the spreadsheet, then please let me know.

Excel Spreadsheet for Binomial Option Pricing

Pricing Vanilla and Exotic Options with Binomial Tree in Excel

This Excel spreadsheet prices several types of options (European, American, Shout, Chooser, Compound) with a binomial tree. The spreadsheet also calculate the Greeks (Delta, Gamma and Theta). The number of time steps is easily varied – convergence is rapid.

The algorithms are written in password-protected VBA . If you’d like to see and edit the VBA, purchase the unprotected spreadsheet at http://investexcel.net/buy-spreadsheets/.

Excel Spreadsheet to Price Vanilla, Shout, Compound and Chooser Options