An example of a Monte Carlo simulation risk analysis model for Statistics modeling

Technical difficulty: 2

Techniques used: Monte Carlo simulation in Excel

Model description

Sometimes we wish to super-impose a boundary condition on a stochastic time series because it is impossible for the variable to extend outside the bounding range. Usually this means that the base model is a fairly crude representation of what we believe might happen, otherwise the boundaries become natural consequences of the model's mathematics.

We first have to decide how the variable will behave as it approaches the boundaries. Two examples are:

1. The variable hits the boundary and stays there until a random movement takes it away again;
2. The variable finds it increasingly difficult to approach the boundary, similar to pushing together like poles of two strong magnets.

In this example, we'll model type 1. Type 2. is the inverse of mean reversion: instead of being pulled towards a mean, the variable is being pushed away from a boundary. Example

The following example model shows a bounded random walk model.

The times series is a Lognormal random walk (a common model for stock prices) - column C (cells C12:C31), and is constrained by linear lower (column D) and upper (column E) bounds. As the actual level cannot go beyond the bounds, the model takes the maximum or the minimum values whenever the unconstrained model would pass outside these bounds.

A variation on the model would be to reset the variable (in red) to any constrained value (where the actual (blue) deviates from the base variable).