Risk Latte - When a Stochastic Differential Equation blows up on an Excel spreadsheet

When a Stochastic Differential Equation blows up on an Excel spreadsheet

Team latte
March 25, 2009

Yesterday, while working with a group of CFE trainees we came across an interesting, though obvious, problem. We were developing a Monte Carlo simulation algorithm to model a Geometric Brownian Motion (GBM) and wanted to map the diffusion process on an Excel Spreadsheet.

We used the following stochastic differential equation (SDE) to model the GBM:

Since the above is a GBM the asset values can never be negative. That is the defining property of any GBM. And in the above GBM we are modelling the geometric return process, i.e. the rate of change of the asset value using continuous time.

However, when we bumped the volatility to something around 800% (an absurd level) the random walk started generating negative values in every simulation run. In fact, for volatility levels above 500% (with the initial asset value at say, 45) the walk generated many negative values along the path for each simulation run.

Prima facie, this appeared completely absurd and the results defied theoretical logic.

Then we used the following stochastic integral equation to model the GBM.

This integral equation can be easily derived if we assume , i.e. the lognormal process of the asset price , and then substitute that in the Ito’s lemma. Both the stochastic differential equation and the stochastic integral equation are doing the same thing, i.e. they are modelling the geometric return process (the lognormal asset price path is nothing but the return process).

But now when we input the value of volatility as 800% in the above stochastic integral equation the random walk did not generate any negative values. In fact, no matter, how absurdly high volatility levels we input in the model, the random walk – i.e. the Geometric Brownian Motion – always generated positive values in all simulation runs.

Actually, there is no mystery here. It is simply a mathematical nuance linked to the model’s implementation on an Excel spreadsheet.

Even though both stochastic differential equation (SDE) and the stochastic integral equation model the geometric return (lognormal) process for the asset, when applied onto an Excel spreadsheet the SDE only approximates a geometric process. This is because when we apply the SDE onto to an Excel spreadsheet, we need to transform it into a difference equation. All ordinary differential equations need to be transformed into finite difference equations for them to be implemented onto an Excel spreadsheet.

When the differential equation gets converted into a difference equation the return process in continuous time becomes a discrete process and the return effectively becomes arithmetic. In a difference equation, the left hand side of the SDE above becomes an arithmetic return.

Unless you really blow the volatility to bizarre levels you’ll not notice any difference. You’d think that you have modeled a GBM (and in fact, for all practical purposes, you have). Only when vols become humongous, does the SDE blow up and generate negative values.

This is the reason why most valuation and risk models in banks and elsewhere use the stochastic integral equation as the generating process for any GBM.

Any comments and queries can be sent through our web-based form.

More on Quantitative Finance >>

back to top


More from Articles

Quantitative Finance