Cox-Ross-Rubinstein Model Formulas for Binomial Option Pricing

Introduction

The Cox-Ross-Rubinstein (CRR) binomial option pricing model is a widely used method for valuing options. This model uses a binomial tree to simulate the possible price movements of the underlying asset over the option's life and calculates the option price from this tree. Here's a detailed look at how the CRR model works, along with the key formulas and steps for implementing it in Excel.

 

Cox-Ross-Rubinstein Model Logic

The CRR model operates on the principle that the product of the up and down moves in the binomial tree equals one:

 

𝑢

⋅

𝑑

=

1

u⋅d=1

 

Where:

 

𝑢

u is the up move multiplier

𝑑

d is the down move multiplier

This ensures that if the price moves up one step and then down one step (or vice versa), it returns to its original level.

 

Binomial Tree Up Move

The formula for the up move multiplier (

𝑢

u) in the CRR model is:

 

𝑢

=

𝑒

𝜎

Δ

𝑡

u=e 

σ 

Δt

​

 

 

 

Where:

 

𝜎

σ is the volatility

Δ

𝑡

Δt is the duration of one step in years

The duration of one step (

Δ

𝑡

Δt) is calculated as:

 

Δ

𝑡

=

𝑡

𝑛

Δt= 

n

t

​

 

 

Where:

 

𝑡

t is the time to expiration in years

𝑛

n is the number of steps used in the model

Binomial Tree Down Move

Given the relationship 

𝑢

⋅

𝑑

=

1

u⋅d=1, the down move multiplier (

𝑑

d) is:

 

𝑑

=

1

𝑢

=

𝑒

−

𝜎

Δ

𝑡

d= 

u

1

​

 =e 

−σ 

Δt

​

 

 

 

Binomial Tree Probabilities

The up move probability (

𝑝

p) in the CRR model is calculated as:

 

𝑝

=

𝑒

(

𝑟

−

𝑞

)

Δ

𝑡

−

𝑑

𝑢

−

𝑑

p= 

u−d

e 

(r−q)Δt

 −d

​

 

 

Where:

 

𝑟

r is the risk-free interest rate

𝑞

q is the dividend yield (or foreign interest rate for currency options)

The down move probability is:

 

1

−

𝑝

1−p

 

Calculating Binomial Trees

Setting Up Input Cells:

 

Input cells for 

𝑆

S (underlying price), 

𝐾

K (strike price), 

𝜎

σ (volatility), 

𝑟

r (risk-free rate), 

𝑞

q (dividend yield), 

𝑡

t (time to expiration), and 

𝑛

n (number of steps).

Calculating 

Δ

𝑡

Δt:

 

Δ

𝑡

=

𝑡

𝑛

Δt= 

n

t

​

 

Calculating 

𝑢

u and 

𝑑

d:

 

𝑢

=

𝑒

𝜎

Δ

𝑡

u=e 

σ 

Δt

​

 

 

𝑑

=

𝑒

−

𝜎

Δ

𝑡

d=e 

−σ 

Δt

​

 

 

Calculating Probabilities:

 

𝑝

=

𝑒

(

𝑟

−

𝑞

)

Δ

𝑡

−

𝑑

𝑢

−

𝑑

p= 

u−d

e 

(r−q)Δt

 −d

​

 

1

−

𝑝

1−p

Building the Binomial Tree:

 

Construct the tree with the up and down moves for each step.

Calculate the option value at each node by working backwards from expiration to the present.

Implementation in Excel

To implement the CRR model in Excel:

 

Input Cells: Create cells for the parameters 

𝑆

S, 

𝐾

K, 

𝜎

σ, 

𝑟

r, 

𝑞

q, 

𝑡

t, and 

𝑛

n.

 

Calculate 

Δ

𝑡

Δt:

 

Use the formula 

=

𝑡

/

𝑛

=t/n in an Excel cell.

Calculate 

𝑢

u and 

𝑑

d:

 

Use the formulas 

=

𝐸

𝑋

𝑃

(

𝜎

∗

𝑆

𝑄

𝑅

𝑇

(

Δ

𝑡

)

)

=EXP(σ∗SQRT(Δt)) for 

𝑢

u and 

=

1

/

𝑢

=1/u for 

𝑑

d.

Calculate Probabilities:

 

Use the formula 

=

(

𝐸

𝑋

𝑃

(

(

𝑟

−

𝑞

)

∗

Δ

𝑡

)

−

𝑑

)

/

(

𝑢

−

𝑑

)

=(EXP((r−q)∗Δt)−d)/(u−d) for 

𝑝

p.

Build the Binomial Tree:

 

Create a grid of cells to represent the tree, filling in the underlying prices and option values at each node.

Work backwards from the expiration date to calculate the option price at each preceding node.

Conclusion

The Cox-Ross-Rubinstein model is a robust method for pricing options, providing a clear and structured way to simulate the underlying asset's price movements and calculate the corresponding option price. By following the steps outlined above, you can effectively implement the CRR model in Excel, making it a valuable tool for option valuation and analysis.