In response to a question the other day, we created a “hair chart” - a line chart showing the results of each iteration of a simulation in a single graph. This kind of chart is interesting in that it shows the wide variance of possible outcomes in a single picture. It can be helpful in explaining how a Monte Carlo simulation works, and why it’s a powerful technique for analysis.

The chart at right was generated from our retirement portfolio example; it shows the outcomes of 100 simulation trials, overlaid with the mean (green) and 5th and 95th percentile results (yellow). The key to this chart is the SimulationValue function. This function can display the results of any cell at any step of the simulation. In constructing the results table, we use this function to find the value of the portfolio in each year at each of 100 trials.

There are some limitations to these charts. For one thing, Excel is restricted to 255 data sets (rows) per chart; this means you can only display that many trials in a single graph. Creating the tables and manipulating the charts can be time-consuming; in this case, the table contains 100 rows, and we’ve made each line black and smooth. We did this with Excel macros, and we wouldn’t recommend doing it by hand (send us an email if you need help constructing the appropriate macros).

Download the spreadsheet and run it with 100 trials. It can be especially instructive to watch the chart generate (make sure “allow screen updates” is checked when you run the simulation).

Example Spreadsheet