Let’s Go to the Casino!

We’re going to take a trip through the Monte Carlo.


Photo Credit: Jamie Adams

What is Monte Carlo?

Nope, not the one in Monaco with the beautiful beaches, fast cars, and the roulette tables.

Not the one in Las Vegas either.

I’m taking about a statistical technique for simulating a process or phenomenon.  Remember that infernal “Bell curve” from school?  It looks kind of like this:


Photo Credit: Jeremy Kemp

All the Bell or Gaussian distribution does is tell you how likely an event is to occur (think of the vertical axis as probability). When you have a known, stable distribution, you can use that distribution to quickly simulate a whole bunch of events.

For example, it takes me an average of 30 mins to get to work.  Some days, it’s shorter. Some days it’s longer.  Once, a car, 3 cars up from me got T-boned while going almost 60 miles per hour.  It took me almost 55 minutes to get to work that day (and no one was seriously injured).  Do here’s what my commute looks like as a distribution:

My commute duration averages 30 minutes. There’s variation. It’s rare for me to make it in 15 minutes or take more than an hour.

Once we know the distribution of historical values, we can use this information to make some educated guesses about what the future will be like.  Remember, when I looked at the long run returns for CDs, Bonds, and Stocks?  All I did was compare actual annual returns against a medium term average: 7%.

Let’s see what a simple Monte Carlo simulation can tell us about the range of future possibilities for our investment.  As always, this is just a thought experiment.  Any investment decisions you make are your own.

Here’s the histogram showing the past annual S&P returns since 1928:

Folks often tout an average S&P500 return of 7%.  Without understanding variation around the average, it’s easy to be mislead.

Here’s the scenario.  At 20 years of age, your Great Aunt Bessie passed away. She hated stocks, bonds, and even CDs.  “Too much risk for me!” She used to chortle from her rocker. “I keep my money cash and close at hand in case I need it.”  So she left you $1000 of cash out of her under-mattress stash. Let’s assume a 40 year investing horizon.  Let’s also assume that inflation and investing costs are zero!  Is Bessie right?  How much money might you have left if you invest it in the S & P 500?

I’m going to use Excel to do the Monte Carlo simulation.  I’m too cheap to spend money on a fancy software package.  And, this way you can do this on your own without needing fancy software.  Using the long run average return might be misleading, so we’ll stick with more conventional short run data 2006-2015.  The average return for the S &P 500 in this period was 9%.  The amount of variation around that average is described using the standard deviation.  In this case it is 0.187.

Don’t freak out if you’ve never loved statistics (most normal humans don’t).  All we’re doing is describing/summarizing the shape of the distribution. In Excel, we’re going to create a column called year.  Start at year 1 and count up to 40. In the next column, were going to enter a formula to calculate the new balance (warning for the faint of heart: there will be an equals sign coming up shortly). Here’s what the setup looks like thus far:

Excel monte carlo setup1

In cell B6, The formula is:


Plug this into Excel so it looks like this (if you’re really scared of doing this yourself in Excel, you can get to a Google docs version here):

In cell B7 copy the previous formula and make one change.  Switch $B$1 to B6.  It should look like this:


Now drag the formula down to year 40.  You just ran a single simulation of 40 years worth of S&P 500 returns.  That’s about all there is to it.  Keep in mind that your actual values will be different from mine, and they will recalculate every time you make a change to the sheet.  It should look something like this:

Excel monte carlo setup2

Now, are you ready to get really nuts!? Copy/drag the second column out 500 times.  You’ve created a (crude) 500 run simulation of the stock market.  Feel that mathematical prowess coursing through your veins?  This is one of the most useful tools you can get for doing some pretty powerful simulations…and it’s almost free!

Let’s look at the results. Copy the last row (year 40) for all runs and then paste values/transpose the data to a new worksheet.

Excel monte carlo setup 3

Once you have a column of final values, you can re-run the data analysis tool to create a histogram of your results.  Again, your specific results will vary, but from what I saw:

Excel monte carlo results

  • In 3 out of 500 runs, you did lose money.  That is roughly a 0.6 % chance.
  • In 151 out of 500 runs, you ended with between $100 and $1000. That is ~30% chance.
  • In 123 out of 500 runs, you ended with between $1001 and $2000.  That is ~25% chance.
  • In 223 out of 500 runs, you ended with between $2001 and %50,000.  That is a ~45% chance.

Tell Aunt Bessie, “Thank you for the gift of money, but you’ll use math to help make decisions about what to do with it.”  Your odds are better in the market than her 100% certainty of gaining nothing.  We’ll keep using tools like Monte Carlo to help make better decisions about a bunch of upcoming facets of our financial lives.  In the meantime, play around with this approach.  It’s a powerful tool for making better decisions.