How to create a Montecarlo simulation in Excel.

Few people know that the fundamentals of the Montecarlo simulations are attributed to Enrico Fermi and Jon Von Neumann, the latter being the creator of the first computer and also mentor to Harry Markowitz at the beginning of his career as a practitioner (this was in the 1940’s).

The name Montecarlo was chosen in honor of the famous Monegasque casino, as the models simulate random data combined with various methodologies.

These simulations are useful in understanding the characteristics of a financial historical series and the associated probabilities that are often difficult to decipher without data computation.

For example, if I invest in a fund that has an average annual return of 5% and a volatility of 7%, what are my chances of having a positive return the following year? And after three years? How about five?

These answers can be obtained through mathematical probability calculations, but also, and perhaps more efficiently, with the Montecarlo simulations that simulate time series with an established average and variance.

ADVANTAGES AND DISADVANTAGES OF THE MONTECARLO SIMULATION.
This simulation technique has some notable advantages:

  1. It’s easy to implement;
  2. It allows for simulating random historical trends;
  3. It allows for a better understanding of the possible results based on the basic characteristics of a given financial instrument;
  4. It removes the so-called “ad-hoc” effect during backtesting.

On the flip side, Montecarlo simulations do not take into account all of the effects that exist in, and make up, the structural characteristics of the financial markets.

I’m talking about the self-correlation phenomenon in a historical series, or rather, the phenomenon that appears when a historical series grows and it tends to keep growing, and if it is declining, it will continue to decline.

This phenomenon is derived from the emotionality of financial markets, when everything grows everyone is euphoric and it continues to grow, creating bubbles; when markets lose value, everyone is worried that it may continue so they sell, only fueling the market decline further.

Another problem with Montecarlo simulations, at least the ones based on a “normal” gaussian distribution of returns as we’ve simulated, is that they do not take into account that volatility changes over time and tends to grow especially when the markets go wrong. If volatility increases, the variability of returns increases and consequently so do any potential drawdowns.

CONCLUSIONS
If you’re looking more or less to better understand what to expect from your investment or diversified portfolio of funds, the Montecarlo simulation is a good bet for gaining an understanding of various scenarios. If instead you want to test timing strategies, the resulting historical data series will not be demonstrative due to the problems highlighted above.

If you’re interested trying it out, you can download the excel sheet of the Montecarlo simulation contacting us. There you’ll find a ready file, complete with macros to run simulations with by simply changing media and variance.