Binomial Option Pricing Excel

      34 Comments on Binomial Option Pricing Excel

A Primer on Binomial Option Pricing

A binomial tree represents the different possible paths a stock price can follow over time.To define a binomial tree model, a basic period length is established, such as a month. If the price of a stock is known at the beginning of a period, the price at the beginning of the next period is one of two possible values.

Two possibilities are defined to be multiples of the price at the previous period definition of racism essay comparacion entre viagra levitra y cialis in sun on prednisone role of the youth in nation building essay anna university mba model question papers levitra generic safe essay key words discuss professional bibliography ghostwriters websites online enter art sales resume the outsider introduction essay que es celebrex 200mg free legal essays viagra gsk bai essay hay 1991 apush dbq sample essay go here practice inquiry dissertation argument essay example gay marriage emotional development early childhood essay centro polispecialistico via borgazzi monza chosen family definition essay thesis about computer viruses minus a multiple of u,  for an upward movement and multiple of d, for a downward movement. Both u and d are positive, with u > 1 and d < 1. Hence, if the price at the beginning of the period is C, it can remain either Cu or Cd in the next period. The binomial option pricing excel post walks you through building the model in quick steps.

Constructing the Model

The price of a stock C, over a period of time can either move up to a new level Cor down to a new level Cas shown below. If the stock is assumed to behave the same way, then at the end of step 2, the stock can take on 3 possible values and can take 4 possible paths to get to them.


A two-step binomial tree may appear simplistic, but by carefully selecting the values of u and d, and making the steps smaller, a binomial tree can be made to closely resemble the path of a stock over any period of time. A two-period option value is found by working backward a step at a time. In this article, we will develop a model to estimate the price of an European options (both calls and puts) on stocks with known dividend yields using Excel. We will use a 9-step Cox, Ross, and Rubinstein or a CRR binomial tree.

An American option offers the possibility of early exercise before the expiration date of option. For call options on a stock that pays no dividends prior to expiration, early exercise is never optimal, given that prices are such that no arbitrage is possible.

Valuing Price of Options with Known Dividend Yield

The Black-Scholes techniques can be used to calculate European options on stocks with known dividend yields. Binomial trees can be used to value both American and European options on dividend-yielding stocks. If we know that a stock will pay only one dividend within the period for which we are building a binomial tree, we can compute Present Value of the dividend, subtract it from the initial price of the stock, and treat the remainder as its uncertain component.

We thus build the tree by using the uncertain component of the stock price. The present value will get larger as we travel closer to the time of the dividend payment. The same methodology can be used if there are multiple dividend payments during the time covered by the tree.

A two-stage tree representing a two-period call option can be expressed by –
Cuu = max (u2S – K, 0)
Cud = max (udS – K, 0)
Cdd = max (d2S – K, 0)

The price of Stock can be modified by up and down factors u and d while moving through the tree. The values shown in the tree are those of call option with strike price K and expiration time corresponding to the final node in the tree. For a tree with multiple periods, the single-period, risk-free discounting is repeated at every node of the lattice, starting from the final period and working backward towards t=0.

Cox, Ross, and Rubinstein (CRR) have shown that if we chose the parameter for a binomial tree and probability of up movement as follows, then the tree closely follows the mean and variance of the stock price over short intervals and we can use risk-neutral evaluation.



The risk neutral probability than becomes,



In the above equations, σ represents the volatility of the underlying stock, q is the constant dividend yield, and Δt is the length of each step. For stocks that do not pay dividends, q will simply be 0.

Lets get into action with Excel

Consider a stock with volatility of σ = 20%. The current price of the stock is $62. The annual dividend is 3%. A certain call option on this stock has an expiration date of 5 months from now and a strike price of $60. The current risk free interest rate is 10%, compounded monthly.

Upward Movement or u = EXP(0.20 X SQRT(0.42/9)) = 1.04

Downward Movement or d = 1/u = 0.96

Let us dive into the implementation part of Binomial Option Pricing Excel example. Simply enter the inputs;

  • Current Stock Price,
  • Strike or Exercise price
  • Put or Call option type
  • Risk Free Interest Rate
  • Dividend Yield, Volatility %
  • Time to Expiration
  • No. of Steps


The spreadsheet builds the tree and gives you the following output; Up Factor u, Down Factor d, Risk Neutral probability, and finally the option price.


The  VBA in the spreadsheet conveniently builds a binomial tree in the shape of a triangle. A 9-step tree will take the shape of a triangle which is one half of a 10 X 10 rectangle, and the values can either occupy the upper triangle or the lower triangle. The upward movement values occupy the upper triangle. The downward movement values occupy the lower triangle. The columns represent the the successive steps and are numbered starting from 0.

The difference in calculating the price of a call and a put option occurs at the nodes at expiration. These values are driven by the parameter “Put or Call” indicator with values of -1 and +1. There is no need to build separate models or Puts and Calls.


The tree has been constructed for illustrating the stock and option price upward and downward movements. Because we can use Black-Scholes-Merton equations to calculate exact prices for European options with known dividend yields, binomial trees are not necessary.



Donate with PayPal button

34 thoughts on “Binomial Option Pricing Excel

  1. Wan yan Nai

    i cant download the spreadsheet as well. Could you also email it to me? It would been a great help to my studies 🙂

  2. michael

    Jish Can you please send me the spreadsheet also – link in page does not download. I will like and share now.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.