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 1 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. The two possibilities are defined to be multiples of the price at the previous period – a multiple of u (for upward movement) and multiple of d (for downward movement). Both u and d are positive, with u > 1 and d < 1 usually. Hence, if the price at the beginning of the period is C, it will be either Cu or Cd at the next period.

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.

binomial-option-pricing-excel-10

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 (CRR) binomial tree.

Note: An American option offers the possibility of early exercise before the expiration date of the 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 equations 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 the 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 traverse 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 below three equations:
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 corresponding 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.

binomial-option-pricing-excel

binomial-option-pricing-excel

The risk neutral probability than becomes,

binomial-option-pricing-excel

binomial-option-pricing-excel

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.

Binomial Option Pricing Excel

Consider a stock with volatility of σ = 20%. The current price of the stock is $62. The dividend yield per annum 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. The parameters are then found for 9 steps as:

Upward Movement or u = exp(0.20*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 parameters;

  • 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

binomial-option-pricing-excel

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

binomial-option-pricing-excel

The accompanied VBA inside 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 take the upper triangle. The downward movement values take 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.

binomial-option-pricing-excel

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, we do not resort to binomial trees for them.

binomial-option-pricing-excel

Download Binomial Option Pricing Excel Spreadsheet

9 Comments

 Add your comment
  1. hi, I would like to access into your excel spreadsheet but password is required, may I have the pw :] ?

  2. please give me password of ur excel spread sheet

  3. Can I have the Password to the file? I’d like to look at the code. Thanks!

  4. HELLO.
    My name is Hung.

    pls tell me the price of vba.excel for binomial tree option online.

    best regards,

    new1545@yahoo.com

  5. your numbers are slightly off…the answers in your “triangle” do not match the “tree” output

  6. I have downloaded all option and volatility sheets. i need their password(s) to run the codes.

Leave a Comment

Your email address will not be published.