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 S0) 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
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

Binomial Asset Price in a Risk Neutral World

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

variance of a risk-neutral asset and an asset in a risk neutral world are equal

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

Upside and Downside Binomial Factors

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

Cox Ross and Rubenstein Model

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
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 S0 and d u S0. 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 S0 = d u S0 = S0, 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
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: VN = max(X – SN, 0)

Call: VN = max(SN – X, 0)

VN is the option price at the expiry node N, X is the strike or exercise price, SN 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: Vn = e-rΔt(p Vu + ( 1 – p ) Vd )

European Call: Vn = e-rΔt(p Vu + ( 1 – p ) Vd

American Put: Vn = max(X – Sn, e-rΔt ( p Vu + ( 1 – p ) Vd ))

American Call: Vn = max(Sn – X, e –rΔt ( p Vu + ( 1 – p ) Vd ))

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 Binomial Option

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

Binomial Option Pricing in Excel

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


23 thoughts on “Binomial Option Pricing Tutorial and Spreadsheets

  1. Hi I was wondering whether you have any spreadsheets that calculate the price of an option using the binomial option pricing model (CRR) (including dividend yield)..and then a comparison against the black scholes price (for the same variables) could be shown on a graph (showing the convergence)

  2. Do you know how to get the implied volatility of american options through binomial tree? can you point me to a paper illustrating this please.

  3. This stuff is a bit over my head. I’d like to find a way to tell what the delta of any given stock option is. For instance, if you were looking at Puts on Amazon:

    http://finance.yahoo.com/q/op?s=AMZN+Options

    How would you find the delta of the $230 May Puts?

    Is there anything else that would be wise to look at?

    Thanks so much, from an Options Newbie!

    CF

    1. the delta of an option is approximately the probability of being in the money at expiry. using simple statistics will get you close. If your in a pinch for implied volatility you can use historical as a proxy.

      All of these “proxies” and assumptions will get you further from the model delta but will give you an idea.

      As an example. If the stock is trading at 230 and the strike is 230 it makes sense to think that the stock can be higher or lower and therefore the delta is around 50. On the other hand, the 100 strike call will almost 100% be in the money by expiry (using you time to expiry example) so it makes sense that its delta is 1 (or 100 depending on the way you look at delta)

      1. For European options try Delta = OptionPremium / StraddlePremium
        You’ll find that for low div yield American Options this works perfectly fine.

        For longer dated options, I’d always prefer empirical (‘shocked’) methods over analytical methods, as most pricing models account poorly for proportionality of dividends (dDiv/dSpot) , skew, IR/Equity correlation, etc, etc.

  4. Hi Samir, am writing a paper over the Binomial method for my school. I would like to have your permission to copy the two step Binomial graphic onto my paper. It will be referenced following the APA citation guide.

    Thanks in anticipation to your favorable response.

  5. Samir –

    This is good stuff and i hope you make your fair share of money from it.

    I’m trying to figure out the effect of blackout periods on the value of a put option – do you have a spreadsheet that does that?

    Thx in advance.

    Regards,

    Chuck Faunce

  6. Hi, the model works perfect when excercise price is close to stock price and/or Time to maturity is close to number of steps. I’m novice in Binomial models and have experimented by changing Exercise price and/or number of steps substantially. If I have a far out of money Strike price. The value from the Binomial model approaches Zero while B&S value is more “resistant”. If I decreases the number of steps to 1 the value from the Binomial models increases dramatically while B&S value stays the same. Is there somehting that you can say about limitations regarding the Binomial model ? When to use and not to use. ?

    Thanks
    Torbjörn

  7. Do you have any spreadsheets of a binomial tree with a stock that pays quarterly dividends? I can’t seem to find out how to handle that.

    1. There are multiple ways to go about this. The best way is to use a discrete dividend model and enter the actual date the dividend is paid. I have not seen an appropriate model in investexcel.com yet.

      in place of this, simply determine the total dollar value of all quarterly dividends paid between Time=0 and expiration. take this number, divide by current stock price to get dividend yield. Use this yield in the models provided by Samir. The major inaccuracy will come from a mispricing of american premium as a large dividend paid tomorrow vs the same dividend paid one day before expiry will have different effects on the american premium.

      john
      [email protected]

  8. I figured it out now. I just had to add more steps to the model. It works fine now.
    Thank you for a explanatory and relatively simple model.

  9. Hi, Can you place point me to information regarding how to calculate the greeks of these options using the binomial model? I know how to do it for Black-Scholes but not for American
    options. Thanks for any help you can give me, and great work on your spreadsheet.

  10. First of all, I want to say thank you for posting this, particularly the Excel spreadsheet that shows the binomial price tree with guides / illustrations. Extremely helpful.

    Second, I have been playing around with that file, and I believe I discovered one small bust in the spreadsheet. While trying to figure out how the put option pricing equation works in cell E9, I noticed that the formula references B12 (nSteps), but I am pretty sure it is supposed to reference B11 (TimeToMaturity) instead.

    It seems to me that the logic of that formula is that the price of the put option is driven by the price of say buying the call and selling the underlying stock (creating a synthetic put, setting dividends aside for this purpose), and then adjusting this value by discounting the future strike of the put by r for t periods, which I vaguely seem to recall is adjusting for the imputed rate of return on excess cash from the stock sale. In any case, nSteps in principle shouldn’t come into play here.

      1. Also, I think there is a mistake in the “up probability” cell as well. You need to subtract the dividend yield from the interest rate, so the formula should be: =(EXP((B9-B13)*B16)-B18)/(B17-B18)

        Thanks for the spreadsheet!

  11. Hi Samir,

    I enjoyed your binomial lattice excel template. I am using the model to forecast gold prices for a 20 year mine life. How do I derive just the price forecast, instead of discounting as often done.

    Looking forward to your help and I will acknowledge you in my thesis paper

    Regards

    Ken

  12. Hey Samir,
    can I only do 5 steps with the model? Would it be possible to add more steps?

    Thanks and best regards
    Peet

    PS Is the formula already adjusted as proposed by D and Ben West?

Leave a Reply to Halim Cancel reply

Your email address will not be published. Required fields are marked *

What is 4 + 13 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)