Binomial Trees Option Pricing in Excel

The binomial option pricing model is a way of finding the fair value of an option, which is a contract that gives you the right to buy or sell something at a fixed price in the future. The model works by dividing the time until the option expires into many small steps, and then assuming that the price of the underlying asset (such as a stock) can only go up or down by a certain amount at each step. This creates a tree of possible prices, where each branch represents a different scenario.

To find the value of the option at each node of the tree, the model uses two basic principles:

The value of the option at expiration is equal to the difference between the strike price (the fixed price you can buy or sell at) and the underlying price, or zero if the option is worthless.
The value of the option before expiration is equal to the expected value of the option at the next step, discounted by the interest rate and the probability of each scenario.

By applying these principles backwards from the end of the tree to the beginning, the model can find the value of the option at the present time. This value depends on the parameters of the option, such as the strike price, the expiration date, the interest rate, the volatility of the underlying price, and the dividend yield.

The binomial option pricing model is useful because it can handle different types of options, such as American options that can be exercised at any time, or Bermudan options that can be exercised at specific times. It is also more accurate than the Black-Scholes formula, which is another popular method for pricing options, especially for longer-term options or options that pay dividends. However, the binomial option pricing model is also more complex and time-consuming to calculate, and it may not work well for options with multiple sources of uncertainty or complicated features.

Basic Theory

1. Option Pricing Basics

Options are financial derivatives that provide the holder with the right, but not the obligation, to buy (call option) or sell (put option) an underlying asset at a predetermined price within a specified period.

2. Binomial Trees

Binomial trees represent a discrete-time, step-by-step model of the underlying asset’s price movement. At each step, the price can move up or down by a certain factor, and this creates a tree-like structure. The key concept is to calculate the option’s value at each node of the tree by considering both the possibility of exercise and the expected future value.

Procedures

1. Define Parameters

Define the following parameters in Excel:

Parameter	Value
$S_0$	100
$K$	105
$T$	1
$r$	0.05
$\sigma$	0.2
$n$	3

2. Calculate Up and Down Factors

Use the following formulas:

$u = e^{(\sigma \sqrt{\Delta t})}$

$d = \frac{1}{u}$

3. Calculate Option Payoffs at Expiration

For a call option at expiration:

$C_{\text{up}} = \max(S_T - K, 0)$

$C_{\text{down}} = \max(S_T - K, 0)$

For a put option at expiration:

$P_{\text{up}} = \max(K - S_T, 0)$

$P_{\text{down}} = \max(K - S_T, 0)$

4. Calculate Option Values Backwards

Starting from the last step of the tree and moving backward:

$C_t = e^{-r\Delta t}(puC_{\text{up}} + (1-p)dC_{\text{down}})$

$P_t = e^{-r\Delta t}(puP_{\text{up}} + (1-p)dP_{\text{down}})$

where $p = \frac{e^{r\Delta t} - d}{u - d}$ .

Scenario: Call Option Pricing

Let’s consider a scenario with the following parameters:

$S_0 = 100$
$K = 105$
$T = 1$ year
$r = 0.05$
$\sigma = 0.2$
$n = 3$ steps in the binomial tree

Excel Calculation

1. Define Parameters in Excel:

Parameter	Value
$S_0$	100
$K$	105
$T$	1
$r$	0.05
$\sigma$	0.2
$n$	3

1. Calculate Up and Down Factors:

$\Delta t = \frac{T}{n} = \frac{1}{3}$

$u = e^{(\sigma \sqrt{\Delta t})}$

$d = \frac{1}{u}$

1. Build Binomial Tree:

Use the up and down factors to create the tree of possible stock prices.

1. Calculate Option Payoffs at Expiration:

$C_{\text{up}} = \max(S_T - K, 0)$

$C_{\text{down}} = \max(S_T - K, 0)$

1. Calculate Option Values Backwards:

$C_t = e^{-r\Delta t}(puC_{\text{up}} + (1-p)dC_{\text{down}})$

$p = \frac{e^{r\Delta t} - d}{u - d}$

$C_t$ is the call option price at time $t$ .

Excel Table

Step	$S_t$	$C_t$
0	100	–
1	120	20
	80	0
2	144	39.06
	96	0
3	172.8	67.19
	115.2	0
4	207.36	102.83
	138.24	0
5	248.83	146.87

Result

The calculated call option price at time $t=0$ is $20. As the option moves closer to expiration, its value increases, and at the final step, it is $146.87.

Other Approaches

Black-Scholes Model: The Black-Scholes model is a continuous-time model that provides a closed-form solution for option pricing. It assumes constant volatility and interest rates.
Monte Carlo Simulation: Monte Carlo simulation involves generating random price paths for the underlying asset to estimate option values. It is particularly useful for complex options and where analytical solutions are challenging.