Blog #36 Asset Allocation on a Spreadsheet

Okay guys – time to roll up your sleeves and start long term investment planning. Success in investment depends less on IQ than the discipline to save consistently and manage risk.  To put it starkly – to be successful in investment, you need to take time and take risk.  In fact, time and risk taking are mutually dependent. Young investors have the luxury of time and the capacity for risk taking whereas retirees don’t. This is why I often remind undergraduates in my personal finance class to start investing early even if this means taking baby steps. The second thing I ask them is to do is to pay attention to on asset allocation because this has far more impact on their future wealth than decisions about which stock to buy or how to predict short-term market movements.

I will now share an approach to lifetime asset allocation that I have used in my own investment planning.  I call this method, Goal-based Asset Allocation or GBA. This approach has the following themes:

  • The sole purpose of investing is to meet your personal finance goals.
  • Quantifying your goal(s) is the first step in investment planning.
  • The more ambitious your goal is, the earlier you should start investing.
  • Fixing the time horizon, the more ambitious is your goal, the more risk tolerant you need to be to achieve the required returns.
  • Risk must always be managed by diversifying across asset classes (this is the gist of asset allocation), and after that, by diversifying within each asset class, e.g. by investing in exchange traded funds for stocks or bonds.
  • If the investment horizon is long (e.g. accumulating funds for retirement decades from now), risk can be managed dynamically using an asset allocation glide path.
  • A common rule of thumb for constructing glide path is to “invest 100 minus your age” in risky assets, and the rest in safe assets. To use a two-asset example, this rule implies that a 30-year old should allocate 70% of his investment money in stocks (risky) and the balance of 30% in cash and quality bonds (safe). Similarly, a 55-year old should allocate 45% of his wealth in stocks and 55% in cash/bonds and so on. The intuition is simple: we are usually less risk tolerant as we get older.

I will now use the case mentioned in my previous blog to illustrate the nuts and bolts of building an asset allocation glide path. The case is about a 30-year person (let’s call him John), who is looking to accumulate a retirement fund by 65.

John’s goal?  First, to be able to spend $48,000 (in today’s dollars) in his first year in retirement. Second, that his expenses should increase each year to keep up with inflation or cost of living.

John’s main worry? Actually, its a bunch of worries.  First, how much he needs to save over time for his retirement goal. Second, how to invest his savings productively in asset classes with different return-risk characteristics. Third, how to design an effective asset allocation glide path. Fourth, how to measure his chance of “success” , where success means not experiencing a cash deficit year during his retirement period (i.e., success = zero shortfall risk).

To address John’s concerns, I will use a two-asset asset allocation plan to keep things simple. Generalizations to more than two asset classes are conceptually straightforward). The two assets in question are stocks (based on a diversified world market ETF) and a bond ETF. All calculations are done using Excel.

Let’s dig in.

Here is the spreadsheet template to input the major planning variables.

Asset Allocation Template

The $135,065 figure at the top is the future value of $48,000 (the amount John wants to spend in his first year of retirement), calculated by compounding $48,000 at 3% (the assumed inflation rate) over 35 years. I assume that the inflation rate is slightly higher (at 4%) during John’s retirement as shown above.

The rest of the template says that:

(a) John will seed his investment with a capital of $100,000

(b) He will invest an additional $24,000 from his salary at the end of the year

(c) He will contribute 5% more to his portfolio each year until he is 64,

(d) All investment monies will be split between stocks and bonds.

(e) The expected return and volatility for stocks are 6% and 20% respectively, and the expected return and risk for bonds are 3% and 2% respectively.

(f) Allocations to stocks starts at 70%, falls to 60% when John is 40, to 50% when he is 50, and finally to 40% when he reaches 60. This “glide path” follows the “invest 100 minus your age” rule of thumb mentioned above.

(g) An annual portfolio management cost of 0.5% is factored in the net return calculations. This reflects the typically low fees charged by passively managed, liquid exchange traded funds.

To address John’s shortfall concerns, I simulate the “wealth paths” of his portfolio by assuming that log returns follow the bell-shaped (normal) distribution. Remember the “hair diagram” I talked about in my previous blog? What I am doing here is to simulate a hair diagram for John. An Excel add-in that performs Monte Carlo simulations was used for this purpose.

I will spare you the details of the spreadsheet calculations which ain’t pretty. Instead, I will present snapshots of the simulation in progress (charts below).  The first plot shows the wealth paths after 5 trials. The last plot at the bottom right shows the end result based on a total of 1,000 draws from the bell curve.

Wealth Simulation in Progress.jpg

The main results of the simulation are shown below.

Wealth Outcomes

John will retire in 2051. The left panel shows the projected value of his portfolio at that point. Since there are too many values to show, I summarize results by reporting the mean, median (mid-point of the wealth distribution), and the 5th and 95th percentiles. You can view the 5th percentile as a worst-case scenario and the 95th percentile, a best-case scenario.

The last line shows the success probability (specifically, the percentage of simulation trials where no shortfall was encountered). The higher the success probability, the less one needs to tweak other parameters such as investment contributions or time to draw downs to keep shortfall risk comfortably low. As shown in the left panel, the success probability at John’s retirement is 100%.

The other results show wealth levels and success probabilities 10, 15 and 20 years into retirement. The 20-year success probability is still a relatively high 83%, suggesting that if all the other assumptions are realistic, John should feel assured that his asset allocation plan will enable him to enjoy a worry-free retirement.  In any case, since John is still at the planning stage now, there are parameters he can change to improve his chances of success.

I leave it as an exercise for you to think about what these tweaks could be.







Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s