Using Distributions in Monte Carlo Simulation

Over the past few weeks we’ve been discussing financial planning and the use of Monte Carlo simulation in the process (see Mike Patton on Monte Carlo series of blogs). We’ve also touched on a few limitations of financial planning applications. In short, most have a closed architectural environment which does not provide the user with adequate flexibility.

In this post, we’ll look at a few distributions which can be utilized in MCS and discuss how to select the one which is most appropriate. 

Basic Monte Carlo

In general, Monte Carlo simulation requires three primary ingredients:

the expected return or mean of the assumption

the standard deviation of the assumption

the correlation between each assumption. 

Beyond this, one of the most important factors is the distribution used on each assumption. To clarify, the distribution used in a MCS analysis places a constraint or border around the assumption. Then, when the simulation is run, the assumption will vary based on the constraints of the chosen distribution.

For example, if an investment has an expected return of 10.0% and a standard deviation of 20.0%, the return will fluctuate based on its standard deviation and the distribution chosen. However, if the distribution does not reflect the characteristics of the data, the results will be inaccurate. Here are a few distributions and the situations where they apply. 

Distribution Choices in MCS

Most, if not all, pre-packaged software applications that incorporate MCS have preselected distributions. I use an add-in to Microsoft Excel called Crystal Ball which allows me to utilize MCS on anything that can be modeled in a spreadsheet. Crystal Ball (CB) also contains 22 different distributions. This allows for a great deal of flexibility. 

Let’s assume you have an executive client who receives an annual bonus. Further assume his bonus fluctuates a lot and all he can tell you is that it’s been as low as $50,000 and as high as $200,000. Here’s how you might handle it.