Stratégies Options - Asset Path Simulation

Simulating the random walk of an asset on a spreadsheet provides an easy way to analyze it and to price derivatives on it.

I - The goal

It's always interesting to know how far an asset can go with reasonable chances. Simulating the random walk make it possible to see the consequences of such moves in the whole portfolio for instance.

II - How to do it ?

Quick and simple.
Our assumption is that asset returns are normally distributed.
That is,

Thus

Hence

It leads to

■ How to derive µ:
To derive µ, the drift one needs the last n closing prices:

■ How to derive volatility σ (sigma): cf La Volatilité : On Price !

■ How to derive φ : φ is a standardized normal variable.

III - Set up

Here are some closing prices
Day t-15: 100
Day t-14: 102
Day t-13: 98
Day t-12: 99
Day t-11: 104
Day t-10: 102
Day t-9: 103
Day t-8: 100
Day t-7: 98
Day t-6: 101
Day t-5: 97
Day t-4: 95
Day t-3: 96
Day t-2: 94
Day t-1: 95

On a spreadsheet we have :
- In cells C5, "=100"
- In cells C6, "=102"
- In cells C7, "=98"
.....
- In cells C18, "=94"
- In cells C19, "=95"

To derive µ, it's easy to calculate the mean using daily returns and the natural logarithm function.

In cells E4, "Ln(P(t)/P(t-1)"
Then,
- In cells E6, "=ln(C6/C5)"
- In cells E7, "=ln(C7/C6)"
- In cells E8, "=ln(C8/C7)"
.....
- In cells E18, "=ln(C18/C17)"
- In cells E19, "=ln(C19/C18)"

- In cells D21, "µ="
- In cells E21, "=MEAN(E6:E19)"

In cells G4, "(Ln(P(t)/P(t-1))²"
Then,
- In cells G6, "=E6*E6"
- In cells G7, "=E7*E7"
- In cells G8, "=E8*E8"
.....
- In cells G18, "=E18*E18"
- In cells G19, "=E19*E19"

- In cells F21, "Daily Variance ="
- In cells G21, "=MEAN(G6:G19)"
- In cells F22, "Annualized Variance ="
- In cells G22, "=365*G21"
- In cells F23, " Annualized Volatilité ="
- In cells G23, "=sqrt(G22)"

This leads to

Using µ and σ,

For a 1 year simulation, dt=1/365

Starting spot : 95
- In cells B28, "0"
- In cells C28, "95"
....
- In cells B29, "1"
- In cells C29, "=C28*(1+$E$21*(1/365)+$G$23*sqrt(1/365)*LOI.NORMALE.STANDARD.INVERSE(ALEA()))"

- In cells B30, "2"
- In cells C30, "=C29*(1+$E$21*(1/365)+$G$23*sqrt(1/365)*LOI.NORMALE.STANDARD.INVERSE(ALEA()))"

- In cells B31, "3"
- In cells C31, "=C30*(1+$E$21*(1/365)+$G$23*sqrt(1/365)*LOI.NORMALE.STANDARD.INVERSE(ALEA()))"

....

- In cells B392 "364"
- In cells C392, "=C391*(1+$E$21*(1/365)+$G$23*sqrt(1/365)*LOI.NORMALE.STANDARD.INVERSE(ALEA()))"

- In cells B393, "365"
- In cells C393, "=C392*(1+$E$21*(1/365)+$G$23*sqrt(1/365)*LOI.NORMALE.STANDARD.INVERSE(ALEA()))"

"F9" to run a new simulation.

Next : Monte Carlo Simulation : A First Attempt
Previous : Volatility : Trading Formulae

OPTIONS 101 - INDEX
OPTIONS 101 - CHAPTER I
OPTIONS 101 - CHAPTER II
OPTIONS 101 - CHAPTER III
OPTIONS 101 - CHAPTER IV
OPTIONS 101 - CHAPTER V
OPTIONS 101 - CHAPTER VI

Strategies Options