Simulation Wizardry

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.