Monte Carlo simulation of investment account

Question

I'm trying to estimate performance of an investment account over 20 years. The question is, have I set up the Monte Carlo simulation correctly? I've used Excel. I've assumed 8% average return and 13% volatility and a starting account size of $100,000.

I generate a random return using the function norminv(rand(),mean,StDev). I set the mean parameter to \$100,000 * 0.08, and the standard deviation parameter to \$100,000 * 0.13. That results in a random dollar gain or loss that I add to my starting account size. That generates my simulated return for the first year.

On the next row I do exactly the same except I base the randomly generated return on the result in the row above, not on the starting value of \$100,000.

I do that 18 more times in next 18 rows. That gives me one simulation of 20 years of returns.

I then do the same in the 99 adjacent columns. That gives me 100 simulations of 20 years of returns.

Did I do this correctly?

I take the median value of each row as the "representative value" for each year. I calculate the 97.5, and 2.5 percentile for each row to give me a 95% prediction interval. Is that correct?

Answer 1

What you describe sounds like a correct Monte Carlo simulation of an investment whose returns in any given year are 8% + a normal distribution with stddev 13%.

What's less clear (and is a question about finance rather than about statistics) is whether that's what you actually want. Here are a few reasons why it might not be.

• It's generally held that returns are nearer to lognormal than normal. It's certainly clear that they can't be exactly normal, because in your model there's a nonzero (albeit very very small) probability that your asset's value will actually be negative next year, and things like equities and bonds and cash can't actually do that.
  • (And volatility is generally defined in terms of the properties of that lognormal distribution.)
• Real returns have "fatter tails" than normal distributions. To take a drastic example, there's probably a better than one-in-a-million chance that your investment will be worth exactly nothing next year because of some catastrophic failure somewhere, but in the normal-distribution model the probability is very much smaller than that. Less drastically, the probability of your investment halving in value in any given year is about 1/250000 in your model, but worse things happened in the big crash circa 1930.
• Real returns have more complicated statistical structure in other ways. For instance, volatility varies over time, and although it's a pretty good model to say that expected returns aren't correlated from one year to another, volatilities may well be.

The other question is whether 100 simulations are enough for your purpose. Try running them multiple times and see how consistent your results are. (You will get more consistent results with your normal model than you would with something fatter-tailed.)