## 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 **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 C*u *or down to a new level C*d *as 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 –

C* _{uu}* = max (

*u*

^{2}S – K, 0)

C

*= max (*

_{ud}*ud*S – K, 0)

C

*= max (*

_{dd}*d*

^{2}S – 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.

Usagi Cheehi, I would like to access into your excel spreadsheet but password is required, may I have the pw :] ?

Jish BhattacharyaPost authorSend me a note to my email address. It is there on the spreadsheet.

Usagi Cheealright,note sent

riteshplease give me password of ur excel spread sheet

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

hungHELLO.

My name is Hung.

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

best regards,

new1545@yahoo.com

Jish BhattacharyaPost authorEmail me directly for a quote for all the spreadsheets.

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

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

théoHi ! Could I have your password to see the code for the binomial option pricing ? Thanks a lot

Jish BhattacharyaPost authorPassword unlocked. Go ahead and download.

Hq Consulting Inc.Where can I get copy of the binomial option pricing spreadsheet?

Jish BhattacharyaPost authorCheck for the download link towards end of the post.

BrentJish, my Facebook like seems to have made no difference. Can’t download. What am I missing?

Jish BhattacharyaPost authorSorry about that, I have emailed you the excel model.

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

Jish BhattacharyaPost authorPlease tweet or share the post in Facebook first. I will email you right away.

Wan yan Naii have liked and share but still cant find the download button

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

JishPost authorReplied with the attachment.

El OuartassiPlease i need the spread sheet

JishPost authorLike or share in FB/Twitter and the spreadsheet will be ready for download.