Stratégies Options - Trinomial Model : On A Spreadsheet !

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
- steady factor m
- "risk neutral " probability p of an up move for each period
- "risk neutral " probability q=1-p of a down move for each period
- "risk neutral " probability m=1-p-q of a steady move for each period

III - How to do it

We are going to price an american 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(2*C9))"
- In cells B14 "down factor d", In cells C14 "=EXP(-C10*SQRT(2*C9))"

"Probabilities"
- In cells B17 "risk neutral probability for an up move p", In cells C17 "=(((EXP(C8*C9/2))-(EXP(-C10*SQRT(C9/2))))/((EXP(C10*SQRT(C9/2))-(EXP(-C10*SQRT(C9/2))))))^2"
- In cells B20 "1-p", In cells C20 "=((-(EXP(C8*C9/2))+(EXP(C10*SQRT(C9/2))))/((EXP(C10*SQRT(C9/2))-(EXP(-C10*SQRT(C9/2))))))^2"
- In cells B23 "risk-neutral probability of a steady move", In cells C23 "=1-C20-C17"

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

Then,
- In cells E5,"=E4*$C$14"
- In cells F5, "=MAX((E5-$C$2);0)"

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

- In cells E7, "=E6*$C$14"
- In cells F7, "=MAX((E7-$C$2);0)"

...

- In cells E63, "=E62*$C$14"
- In cells F63, "=MAX((E62-$C$2);0)"

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

It leads to :

2 - Option values for each period

- In cells G5, "=EXP(-$C$6*$C$9)*($C$17*F4+$C$23*F5+$C$20*F6))"
- In cells G6, "=EXP(-$C$6*$C$9)*($C$17*F5+$C$23*F6+$C$20*F7))"
- In cells G7, "=EXP(-$C$6*$C$9)*($C$17*F6+$C$23*F7+$C$20*F8))"
...
- In cells G62, "=EXP(-$C$6*$C$9)*($C$17*F61+$C$23*F62+$C$20*F63))"
- In cells G63, "=EXP(-$C$9*$C$6)*($C$17*F62+$C$20*F64))"

- In cells H6, "=EXP(-$C$6*$C$9)*($C$17*G5+$C$23*G6+$C$20*G7))"
- In cells H7, "=EXP(-$C$6*$C$9)*($C$17*G6+$C$23*G7+$C$20*G8))"

...

- In cellsH61, "=EXP(-$C$6*$C$9)*($C$17*G60+$C$23*G61+$C$20*G62))"
- In cells H62, "=EXP(-$C$6*$C$9)*($C$17*G61+$C$23*G62+$C$20*G63))"

...

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

In cells AJ34, "=EXP(-$C$6*$C$9)*($C$17*AI33+$C$23*AI34+$C$20*AI35))",

That's our approximation.

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

Finally

Our call value is worth 14.1823
This is the same as for european style !

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 E5, "=E4*$C$14"
- In cells F5, "=MAX((-E5+$C$2);0)"

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

...

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

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

It leads to :

Finally,

The put value 9.3052

Next:Trinomial Model: - American Style Issue-
Previous :Trinomial Model : A First Approach

Download the free pricer here

Related Pdf :
- Between Binomial and Trinomial Option Pricing Models
- The adaptive mesh model: a new approach to efficient option pricing
- Option Pricing: Lattice Models Revisited

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