Stratégies Options - Binomial Model: How To Price With A Tree !

I - VARIABLES AND PARAMETERS

Variables
The Spot S
Maturity T
Parameters
The strike K
Annualized volatility σ %
Risk free rate r
Annualized dividend rate q
Number of periods n

II - The First Steep

- Time step T/n
- up factor u
- down factor d=1/u
- "risk neutral " probability p of an up move for each period
- "risk neutral " probability q=1-p of a down move for each period

III - How to do it

We are going to price a european call struck at 100 for an asset at $100, volatility is set to 30%, interest rate are set at 5%

- In cells B2 "Spot S", In cells C2 "100"
- In cells B3 "strike K", In cells C3 "100"
- In cells B4"maturity T", In cells C4 "1"
- In cells B5 "number of period n", In cells C5 "30"
- In cells B6 on inscrit "interest rate r", In cells C6 "0.05"
- In cells B7 "dividend rate q", In cells C7 "0"
- In cells B8 "cost of carry b=r-q", In cells C8 "=C6-C7"
- In cells B9 " dt", In cells C9 "=C4/C5"
- In cells B10 "volatility σ", In cells C10 "30%"

- In cells B13 "up factor u", In cells C13 "=EXP(C10*sqrt(C9))"
- In cells B14 "down factor d", In cells C14 "=1/C13"

"Probabilities"
- In cells B17 "risk neutral probability for an up move p", In cells C17 "=(EXP(C8*C9)-C14)/(C13-C14)"
- In cells B20 "1-p", In cells C20 "=1-C17"

1- Final spots and payoffs
- In cells E4,"=C2*C13^C5"
- In cells F4, "=MAX((E4-$C$2);0)"

Then,
- In cells E6, "=E4*$C$14^2"
- In cells F6, "=MAX((E6-$C$2);0)"

- In cells E8, "=E6*$C$14^2"
- In cells F8, "=MAX((E8-$C$2);0)"

- In cells E10, "=E8*$C$14^2"
- In cells F10, "=MAX((E10-$C$2);0)"

...

- In cells E62, "=E60*$C$14^2"
- In cells F62, "=MAX((E62-$C$2);0)"

- In cells E64, "=E62*$C$14^2"
- In cells F64, "=MAX((E64-$C$2);0)"

It leads to :

2- Option values for each period

- In cells G5, "=EXP(-$C$9*$C$6)*($C$17*F4+$C$20*F6)"
- In cells G7, "=EXP(-$C$9*$C$6)*($C$17*F6+$C$20*F8)"
- In cells G9, "=EXP(-$C$9*$C$6)*($C$17*F8+$C$20*F10)"
...
- In cells G61, "=EXP(-$C$9*$C$6)*($C$17*F60+$C$20*F62)"
- In cells G63, "=EXP(-$C$9*$C$6)*($C$17*F62+$C$20*F64)"

- In cells H6, "=EXP(-$C$9*$C$6)*($C$17*G5+$C$20*G7)"
- In cells H8, "=+EXP(-$C$9*$C$6)*($C$17*G7+$C$20*G9)"
...
- In cells H60, "=EXP(-$C$9*$C$6)*($C$17*G59+$C$20*G61)"
- In cells H62, "=+EXP(-$C$9*$C$6)*($C$17*G61+$C$20*G63)"

...

- In cells AI33, "=EXP(-$C$9*$C$6)*($C$17*AH32+$C$20*AH34)"
- In cells AI35, "=+EXP(-$C$9*$C$6)*($C$17*AH34+$C$20*AH36)"

In cells AJ34, "=EXP(-$C$9*$C$6)*($C$17*AI33+$C$20*AI35)", That's our approximation.

In cells B29, "Option Value"
In cells C29, "=AJ34"

Finally

Our call value is worth 14.1334

3- The put
Changing the payoff only is enough !

- In cells E4, "=C2*C13^C5"
- In cells F4,"=MAX((-E4+$C$2);0)"

Then,
- In cells E6, "=E4*$C$14^2"
- In cells F6, "=MAX((-E6+$C$2);0)"

- In cells E8, "=E6*$C$14^2"
- In cells F8, "=MAX((-E8+$C$2);0)"

- In cells E10, "=E8*$C$14^2"
- In cells F10, "=MAX((-E10+$C$2);0)"

...

- In cells E62, "=E60*$C$14^2"
- In cells F62, "=MAX((-E62+$C$2);0)"

- In cells E64, "=E62*$C$14^2"
- In cells F64, "=MAX((-E64+$C$2);0)"

It leads to :

Finally,

The put value 9.2564

Next : Binomial Model : American Style Options
Previous :Binomial Model : The Tree

Free Pricer to download : ici

Related Pdf :
- BINOMIAL MODEL

OPTIONS PRICING MODEL - INDEX
OPTIONS PRICING MODEL - INDEX
OPTIONS PRICING MODEL - CHAPTER I
OPTIONS PRICING MODEL - CHAPTER II
OPTIONS PRICING MODEL - CHAPTER III

Strategies-options.com