Financial planners have long searched for a tool that provides flexibility in modeling the specific nuances of various clients’ situations. Finding that all-inclusive program has proved elusive. For instance, one software application may be adept at projecting retirement scenarios but very inadequate at estate planning. One may be a good tool for asset allocation but weak in other areas. Even if such a program did exist, it would likely not provide the flexibility to employ Monte Carlo simulation in the areas that you, as practitioner, would desire.

Then there’s the issue of using linear projections to forecast future results. How helpful are these linear projections anyway? There’s considerable agreement that linear forecasts provide limited benefits. Several years ago, we began to see Monte Carlo simulation enter into financial planning programs–albeit on a limited basis –even though it had been a staple in other industries for many years. This article will examine the different ways Monte Carlo simulation can be utilized, specifically with Crystal Ball software, in a financial planning practice.

**Getting Down to Cases**

To begin, let’s assume we have a business owner named Bob who is married to Carol. Both are age 55 with three adult children and seven grandchildren. Their net worth statement is shown in the table on page 92, and their income statement on page 95. Bob will also receive a small pension from his company when he retires.

They have numerous goals. Bob wishes to sell his business in five years and retire with an annual after-tax annual income of $400,000. When Bob retires, they plan to take a two-week cruise in the Mediterranean. They will downsize their residence and purchase a modest condo in the mountains. They wish to fund 25% of their grandchildren’s education and have set up a trust to accomplish this. They will maintain their rental properties but sell their vacation home. They are very concerned about their children’s ability to handle such a large sum of money at their death. They would also like to make gifts to their favorite charities. They would like to reduce their estate tax as much as possible. They would like to be debt free at retirement.

Monte Carlo can be used to address a number of financial planning questions for Bob and Carol, including how much money will be required to fund their annual income need with a high probability of not running out of capital. We can also determine their required rate of return and the amount of risk they need to assume to achieve this nest egg.

After listing all the variables, you should ask the following two questions: Are the variables predictable or unpredictable? Are they significant or insignificant? You should focus your efforts on those variables falling within Quadrant A in the figure below–Significant and Unpredictable. If an assumption is highly predictable or varies little, then Monte Carlo simulation probably won’t add much value. On the other hand, if the assumption is unpredictable, then Monte Carlo simulation could provide a great deal of insight.

Bob and Carol have several variables relevant to their specific situation. A partial list and the quadrant in which each falls is listed in the table on page 96. It’s important to note that the quadrant will vary depending on the situation. For instance, if their future financial security were highly dependent on the price they received from the sale of their residence and/or vacation home, then these items would be deemed significant. In short, we can determine whether the variable is predictable or unpredictable based on the type of variable it is. The determination of its significance is contingent on the client’s dependence on the item.

Let’s assume we will use Monte Carlo simulation around those variables which have higher significance and lower predictability. The list could include any of the items found in Quadrant A.

Even though there are more items we could include, let’s begin with the financial accounts. From the net worth statement we see they have seven accounts. One is a checking/savings account, two are brokerage accounts, each has an IRA, Bob has a 401(k), and Carol has a TSA. For each account, we’ll need to determine the expected return, the standard deviation, and the correlation between them. Once the expected return is established, some constraint is required. This constraint or standard deviation will place a parameter around the mean. After this, we must ask, which is the most appropriate distribution curve for each account?

If you have historical data, you could use CB Predictor to determine the distribution curve that fits best. But unless you have actual return data from each account for some meaningful period of time, you would be forced to use proxies such as large-cap stocks, small-cap stocks, bonds, and cash to build a weighted benchmark portfolio to represent each account. In fact, if you use historical data you are further assuming that the portfolio has tracked closely with this asset class historical data. If the accounts have deviated to any large degree from the historical data, this “tracking error” would yield questionable results. Since future returns are unpredictable, you could use a weighted return for the mean.

The standard deviation is another matter. While past returns have little to no predictive value, past risk does have some usefulness in estimating future risk. To determine the risk on each account, you could calculate the average of the three-, five-, and ten-year numbers of each account as derived from Morningstar Principia. The problem here is that for the past 12-18 months, the three-year standard deviation numbers have been much lower that the five- and ten-year figures. Therefore, you could eliminate the three-year number as it seems to be artificially low and may be unrepresentative of future volatility.

The next step is to determine the relationships among the accounts. Ideally, it would be best to compare the monthly returns of each account for the past several years. However, this is usually not practical since you would have to know the monthly balances of all accounts and all cash flows in and out. Here we may be forced to use historical data.

Using data from Ibbotson’s 2004 SBBI year book, I used as proxies large-cap stocks, intermediate-term government bonds, and cash from 1926-2003 to analyze the correlation between three different portfolios. Portfolio 1 was aggressive (85% stocks, 15% bonds, 5% cash), portfolio 2 was balanced (50% stocks, 40% bonds, 10% cash), and portfolio 3 was conservative (20% stocks, 70% bonds, 10% cash). I then analyzed the correlation between portfolios 1 and 2, 1 and 3, and 2 and 3 using the average of the ten-year rolling periods. There were 69, ten-year rolling periods during this timeframe. To my surprise, I found stronger correlations than expected.

The correlations ranged from a low of 0.82 to a high of 0.99. To digress a bit, it’s important to include correlations between accounts for one very important reason: if accounts are allowed to drift randomly each year, you will understate the risk and produce a false sense of security. For instance, let’s assume we have two portfolios, both consisting of the same 20 stocks. If they were not correlated together, a simulation during a given year could have one portfolio declining by -15% while the other portfolio increases by 20%. In reality, this would not happen given the fact they were identical portfolios. Correlating the accounts will eliminate this possibility, providing a more realistic forecast.

**The Story so Far**

To recap, we have determined the expected return of each account, placed parameters around the return using each ac-counts’ standard deviation, and correlated the accounts. Keeping with the financial assets, let’s depart from cross correlations and look at serial correlations. Serial correlation is the correlation of a variable with itself over successive time intervals. Data I’ve seen suggests that stock returns are random from year to year while T-bills (cash) and inflation follow a trend. Based on this, you can use serial correlation with inflation and cash accounts in your analysis, but not stocks. For example, T-bills have exhibited a high serial correlation, and we would not expect it to vary widely from one year to the next. Inflation, on the other hand, has a slightly lower serial correlation and has made larger moves from year to year. Generally speaking, both of these items have a tendency to follow a trend. One final note here, a cross correlation should be established between inflation and cash.

Let’s move on to Bob and Carol’s real estate. When projecting the probability of their estates, you’ll need to include all non-financial assets as well. Obviously this increases the difficulty. What growth rate should be assumed? What is the dependency of the growth rate on the geographic region in which it is situated? To simplify this, you could assume their residence, rent houses, and vacation home will grow at the rate of inflation. Since inflation is already serial correlated, there’s no need to do anything else here. The rental income may be tied to inflation, remain flat, or may increase in tiers such as every five years. This will depend on the clients’ input. What about the periods when the home is not rented? You could put some parameter around the rental income if it makes sense. The expenses related to rental property will also vary and the resulting net rental income will be affected. This level of attention may not be necessary unless the rental income is a large part of the equation.

Bob’s annual company distributions are definitely a major piece of the puzzle. Here you could use a uniform or triangular distribution on the variables. The future cost of their cruise may not be a significant item in the scheme of things, however you could place a parameter on this as well. The first year of their Social Security, though tied to their income, is still not certain. I use a uniform distribution curve to allow for some variance of the initial amount. The sale price of the residence and vacation home is a function of the growth rate so you may not need to do anything else. The future price of Bob’s company is a real wild card. You should gather data on the past rate of growth year by year, and make a reasonable estimation of the future growth rate. You might choose a triangular or uniform distribution.

With all significant and uncertain variables accounted for, projecting the future value of their estates can yield some useful information such as projecting the probability that their estate will exceed the federal exemption equivalent.

**Will they run out of money?**

After running 1,000 simulations, I asked the following two questions. What’s the probability of running out of money? What’s the probability of achieving the linear projections?

Taxes were not factored into the analysis but are obviously an important factor. Depending on the tax basis of the stock it may not make sense to diversify. Even so, it should be examined so the decision can be made with all of the relevant facts. It should also be noted that the assumed expected return on the aggregated portfolio with the concentration, was 7.46% and 7.19% when the concentration was replaced with a diversified portfolio. As a result of the higher total return on the concentrated portfolio, it yields a higher linear projection, but a projection that is less likely to occur. The tables above contain the results.

The probability of running out of money was improved considerably by diversifying Brokerage account B. I should note that I did not include the proceeds from the sale of their vacation home or the profit from the sale of their residence in the analysis. This would obviously improve their situation to some degree.

The myriad of off-the-shelf financial planning programs contain many limitations. Through the use of Crystal Ball software, the frontier has been expanded significantly. This article only briefly touched on a few of the possible applications in a financial planning mode. As the Wright Brothers made their inaugural flight, not many believed we would have flying machines as large and fast as we have today. It is my sincere hope and dream that the financial planning profession will experience this updraft and the tools of our dreams will become a reality one day.