# 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 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.  ## 24 thoughts on “Binomial Option Pricing Excel”

1. Usagi Chee

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

1. Jish Bhattacharya Post author

Send me a note to my email address. It is there on the spreadsheet.

2. ritesh

3. Carlos

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

4. hung

HELLO.
My name is Hung.

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

best regards,

new1545@yahoo.com

1. Jish Bhattacharya Post author

Email me directly for a quote for all the spreadsheets.

5. Mike

6. Jonn

7. théo

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

1. Jish Bhattacharya Post author

8. Hq Consulting Inc.

Where can I get copy of the binomial option pricing spreadsheet?

1. Jish Bhattacharya Post author

9. Brent

1. Jish Bhattacharya Post author

Sorry about that, I have emailed you the excel model.

10. 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 🙂

1. Jish Bhattacharya Post author

Please tweet or share the post in Facebook first. I will email you right away.

1. Wan yan Nai

i have liked and share but still cant find the download button

11. michael

1. Jish Post author

Replied with the attachment.

12. El Ouartassi

1. Jish Post author

13. Johan Ekelund
1. Jish Post author