Easy Binomial Trees in Excel - YouTube

Welcome to this tutorial on how to create binomial trees in Excel. Binomial tree is an

options pricing model used to figure out the value of a call or put option at

time zero based on the future value of the stock the potential value of the

stock at these different time periods. This would be the stock value at time 0

period 0. Period one can either go up or down. We only have two options. A stock

moves up or down. It could hypothetically stay the same and the second period go

up again for the second time, so "S" times "U" squared. It can go down from up times

"U" times "D" which is up times down or go down again. And again this branches

all over and over and over in this is what's known as a binomial tree. In Excel

it will look a little something like this. This is a two hundred step binomial

tree I did back in my derivatives class class in college. We have our stock value

here at time zero. All the way over here we have our period 200. If you scroll all the

way down here this is my call

value and we scroll even further we'll see this my put value.

This is kind of how it's gonna look. It's not gonna look quite as nice as this little

graphic here, but this is gonna be the easiest way to do it in Excel where we

can easily take a formula and drag it across to create our trees. In this

example I'm gonna use a 10 period one. Its smaller it's easier to work with. You

don't have to worry about making sure that these two formulas overlap. That's

kind of why I have these "1's" here. I'm using them just as a general template for

your viewing pleasure. We are gonna be looking at my tree to kind of see how

it works, look at the formula, and then we're gonna take that formula and apply it to our own

model. You'll see this formula here is an "if-statement" followed by another "if-

statement". Looks kinda long, a little complicating. I'll show you some visuals to

help us break this formula down. It's basically saying if this logic is true,

do this, and if it's false we're going to do this. In a binomial tree we have three

scenarios. If E15 and E 4 are blank, therefore the cell must be blank.

Here's a formula right here. If the cell before it is black and the cell

before and up his black, therefore this must be blank. That's scenario one and that

is what this code right here is explaining. If E15 is blank then we're gonna

go do this. And if E14 is blank therefore these two things are true so

this must equal blank. This brings us to scenario 2. So if E15 is blank but E14

is not blank, this must be false. If this is true we are going to do

this which is E14 multiplied by 1 + F9. F9 is our percent down

that the stock is gonna go every period, so in this case can be 10% and then our

third scenario would be

e15 is not blank then we must compute e15 multiplied by the percent up which

is this right here. If this is not true we must compute this which is just take

the so take the cell in front of it and multiply by the percent going up which in

this case is 10%. Let's go ahead and apply this to our own tree. So at time

period 0 our stock is equal to 50. That's what we have up here so our formulas

actually starts in the first period and we are going to start doing

that if statement. If the cell in front of it is equal to blank, and if the

cell above it is also equal to blank, therefor this must be blank.

Remember if it is not blank we're going to take this value b12 and multiply it

by the percent down, so 1 + percent down

and then the value if B13 is actually not blank we're gonna take B13

multiplied by one plus the percent going up which is 10% and hit enter this formula we're

gonna drag it all the way to the right and all the way down. What we have

here is the same formula except we are applying it to own spreadsheet. As you see

these also have formulas, but they're blank because remember scenario one. If

E20 is blank, and the cell above it is blank, this cell must be

blank. The reason why this cell is not blank is because remember this is

blank but this is not blank therefore this cell must be the cell in the

adjacent northwest corner multiplied by our percent down, which exactly what this says. If

you want to test this would simply take 55 times 1.1 and then 50 times

.9 and you could also try again to 55 times 1.1 and 55 times .9

You get these values

Now the second step to our formula is to work on our formula for the call option.

Our call formula is simply right here. it's our basic binomial model, so it's

going to be the percent of the probability of the call option up plus

one minus p

call option down all over 1+r which is a risk-free rate. I have all

three of these things right here so all we need is a "Cu" and "Cd" which is

going to be based off of the stock price. Go back to our old formula that I have

to check it out. This is actually working backwards. Now we're looking to

the right, not to the left, and it's saying if C18 is blank this cell must

be blank but if it's not blank we must compute this one formula. Tthis formula

is actually what I have right here. Go ahead and start doing this. If this cell

is blank this must be blank, but if it's not we must compute this formula, which

is going to be he P multiplied by the adjacent cell

by 1-2 actually I'm missing some parenthesis here. Ee have to do this all

over 1+R which means our whole section is in parentheses. Divide by our

risk-free rate percent. Now we're gonna take this we're drag it all the way

over and all the way down. It is blank.

The reason why is simple. It's constantly looking to the right.

There's no values over here so what we have to do at the Max period is find the

payoff of the call option. The payoff is gonna be either our stock price minus

our exercise price, which is 25. Our

stock price over here minus 25. We're gonna do that =MAX, our stock

price, minus our exercise price. Hit F4.

Either you're going to get a payoff, or you're not. We're going to bring this formula down to here. I did

one too many there. And there you have it. Now this formula has

something to pull back on and it's just gonna keep working its way backwards to square

one