Tom O’Haver, University of Maryland, March 1997. Revised Nov. 1999.
This is a simulation of saving and investing for retirement. It shows how much you can accumulate in a tax-deferred retirement account (e.g. an IRA or 401k account) by saving a certain amount each year and investing it in a combination of fixed-interest and variable (equity) instruments. You can control the amount invested, the rate at which that amount is increased with time, the return on the fixed-interest and equity portions of your investment, and the volatility (uncertainty) of the returns of the equity portion. Graphs show the amount invested per month. the growth of your principal with time, and the return on equiities vs time for a 35-year period (for example, from age 30 to the normal retirement age of 65). (A companion simulation, the Income Simulation Spreadsheet. can be used to estimate the income that you can obtain from your investments in retirement).
Note: This simulation was developed for instructional purposes and is not intended a tool for detailed personal financial planning. It does not take into account certain personal and legal factors such as: annual contribution limitations; income and capital gains taxes; IRS minimum required withdrawls from tax-deferred accounts after age 70 1/2.
This simulation is available in three different spreadsheet formats:
The Microsoft Excel version will work on both the Windows and the Macintosh version of Excel 5.0. You must own Excel 5.0 or Excel Office 97 in order to run this version.
The Works version was developed in Microsoft Works 3.0. You must own a copy of Microsoft Works 3.0 or higher to run this version. You may download the Works version of the simulation in binary or HQX format.
The original WingZ version of this spreadsheet is still available. This version has mouse-controlled sliders for input control and was developed using WingZ 1.1, an object-oriented spreadsheet that is available for Windows, Macintosh, and UNIX from Investment Intelligence Systems Corp. You must own a copy of WingZ 1.1 to run this version. You may download this version of the simulation in binary or HQX format.
The Inputs:
First month investment. Amount invested (saved) in the first month. The yearly investment is automatically calculated and displayed under Outputs.
Yearly Increase. This is the percent increase in investment each year. If you set this to zero, it means that you invest the same amount each year. If you set this to 5, it means that each year you invest 5% more than the previous year. Because your income is likely to increase with time as you obtain raises, promotions, and cost-of-living adjustments, you should be able to afford to increase the amount that you invest by a few percent per year.
Initial assets at start of year 1. The number of dollars (if any) that you initally transfer into this investment program from previous investments, gifts, or other sources. This will be zero if you are starting from scratch.
Expected Return on Fixed. The average annualized return on the fixed-interest portion of your investment portfolio (such as bonds, certificates of deposit, or money market accounts). Typical fixed account returns are 3 — 6%.
Expected Return on Equities. The average long-term annualized return on the equity (stock and stock mutual fund) portion of your investment portfolio. Returns on equity investments are typically greater than on fixed investments. Typical long-term equity returns average 10 — 20%.
Fraction in equities. The fraction of your portfolio’s value that is invested in equities (stocks and stock funds). If you set this to zero, it means that all your portfolio is in fixed investments (an ultra-conservative stance); if it is set to 100%, all your investments are in equities (a more aggressive stance).
Volatility (Sigma). This simulates the volatility of the equity portion of your portfolio, by controlling the year-to-year fluctuation of the equity returns. If you set this to zero, it means that there is no fluctuation in the returns (an unrealistic supposition). Volatility is measured in sigma (standard deviation). Typical sigmas for individual equity mutual funds are 10 to 20%, but a well-balanced portfolio of diverse fund types may have a volitility towards the lower end of this range.
The Outputs:
Yearly Investment: Total investment in the first year.
Principal in Year 35: The total value of your investments in Year 35, assuming that all interest is re-invested and not taxed. Of course, not everyone will have a full 35-year investment period. If you are starting late or retiring early, then the total principal you will have can be read off the Principal graph that is displayed on the spreadsheet.
Out-of-pocket expense: The total amount that you have actually paid into your retirement accounts over the 35-year period of the simulation.
Annualized return: The average annual return on your entire portfolio (fixed and equity portions combined) over the 35-year period of the simulation. This will typically differ somewhat from the Expected return set in the Inputs because of the volatility of equity investments.
The Graphs:
Principal: The total value of your investments in each year, assuming that all interest is re-invested and not taxed. The horizontal axis is years from the beginning of your investment program. If you are planning to retire, say, 20 years from the beginning of your investment program, then you would read off your expected principal at year 20 from this graph.
$ invested per month: The amount you invest per month. This will be a flat line if Yearly increase is zero.
Return on equities: The simulates year-to-year variation in annualized return on the equity (stock and stock fund) portion of your investment portfolio. The average is controlled by the Expected Return on Equities and the fluctuation (variation) is controlled by the Volatility. Every time you recalculate the spreadsheet (by pressing F9), another random set of returns is calculated.
Experiments.
Start with all the inputs set to zero. Obviously in this case you never accumulate anything and all the graphs stay at zero.
Set the First month investment to $170. This means you are investing roughly $2000 per year, but since there is no annual increase in savings and since the return on your investments is zero, the money simply accumulates. The principal graph in this case is just a straight line. By retirement at age 65, you would have accumulated a little over $70,000. You might call this the stick the money under the mattress scenario. You can do much better than this.
Now let us assume that you invest your savings in a fixed-return account earning 5% yearly, such as a certificate of deposit or money market account. Set the Expected Return on Fixed to 5. Now the principal graph shows an upward curve as the interest from your investment compounds from year to year. By retirement at age 65, you would have accumulated roughly $184,000. And note that this does not increase your out-of-pocket expense. Not bad, but you can do better than this.
Because your earned income is likely to increase with time, as you get raises or cost-of-living adjustments, you should be able to afford to increase the amount that you invest each year. For example, suppose you increase your savings 5% per year (set the Yearly increase to 5). In this case by retirement at age 65, you would have accumulated nearly $400,000! In fact, if you feel you will have trouble investing in the early years (when your salary is low), you can always reach the same goal by reducing the First month investment and increasing the Yearly increase to compensate. This means that you invest less in the beginning but more in later years, when you can presumably afford it. (However, doing this does increase your total out-of-pocket expense). But you can do even better than this by increasing the return on your investments.
Typically, returns on equity (stock and stock mutual fund) investments are greater than for fixed investments. The long-term historical average return of the stock market as a whole is 10% including the Great Depression and 12% excluding the Depression. To simulate investment in equities, set the Fraction in equities to 100% and the Expected Return on Equities to 10% — 12%. In this case by retirement at age 65, you would have accumulated close to one million dollars, at no further increase in out-of-pocket expense! Of course, there is really no way to predict the future; past returns are no guarantee of future results. The future may be better or worse than the past, but most likely it will be about the same. The best we can do is to use historical trends to predict the most likely future results.
It is actually possible to do even better than the above by carefully selecting your equity investments in order to maximize returns. A good way to do this is to invest in high-quality equity mutual funds. The long-term average return of the high-quality equity mutual funds with the longest track records is in the range of 13 to 14% over a 30 — 50 year period. (For example, the American Fund’s Investment Company of America has had an annual return of 13.7% since it was founded in 1934, during the Great Depression). Your employer’s 401k plan will probably allow you to choose from an assortment of mutual funds (or variable annuities, which are similar) which achieve similar long-term returns. Try putting these returns into the Expected Return on Equities and observe the result. Clearly, even small (1%) increses in investment return can result in huge increases in wealth over a long investment period.
Note that the Principal graph is now a very curved line, starting out almost flat and sweeping up sharply in the later years. This is an important and natural characteristic of investing. Why is this important? It means that it is very important to begin your investment program as early as possible and not to keep putting it off because you can’t afford it. If you delay starting by one year, it has the same effect as retiring one year early — in either case your investment period is reduced by one year. One year can make a lot of difference. Just look at the Principal graph. If you have accumulated $1,000,000 by year 34, and you are making a 10% return on your investments, then in the last year you make $100,000 in interest (10% of $1,000,000). So reducing your investment period by one year (by starting one year later or by retiring one year early) would cost you $100,000. Are you willing to throw away $100,000 just to delay biting the bullet for one year?
The down side of investing in equities is the risk of fluctuating returns (called volatility). In some years the stock market does better than in other years. In some years it even looses money (has a negative return). Nevertheless, the long-term average return is still better for equities than for bonds or other fixed investments. Saving for retirement is a long-term investment, so you are generally better of investing heavily in equities.
You can simulate the effect of market fluctuations by setting the Volatility to some non-zero value. Typical volatility values for equity mutual funds are 10 to 20%. Every time you recaculate the spreadsheet (by pressing F9), another random set of returns is calculated. This is like simulating various alternative possible futures. Every time you try out a different set of input variables, you should press F9 several times to observe how much the total value of your principal varies. As you can discover, small amounts of volatility pose little real risk — there is some bumpiness in the rising principal curve, but it ultimately rises nonetheless. If the volatility is high enough (relative to the average return), you will see that in some years the returns are negative ; that is, your principal actually looses money. But even so, over the long term, the principal gradually grows. Volatility is unavoidable when investing in equities. What it really means is that you can not predict exactly how rich you will be at the end of your investment period. You may end up with $1,000,000, or maybe only $800,000, or maybe $1,200,000, or maybe even more or less. You can never be exactly sure how rich you will be. But, like the man said, don’t you wish you had that problem!
Can the volatility ever be too great, or is total return the only factor that is ultimately important? It is often said that for the long term investor, total returns are more important than volatility. Nevertheless, if the volatility is too great, there is a chance that your principal may be wiped out or reduced to a small fraction of its former glory. Try increasing the volatility and see if you can observe such a go broke scenario. (Fortunately, even if if this does happen, it is possible to recover to some extent, assuming that you continue to make your regular contributions. You can always hope that, after a big market crash, there will be a period of market recovery). Neverthelss, I think you can prove to yourself that it is possible to have too much volatility.
One way of reducing the risk of investing in stocks is to buy equity mutual funds. Individual stocks may have long- term standard deviations or 20% or more. Mutual funds reduce risk by spreading your investment over many stocks. What are the typical returns and variations in returns (volatility) of equity mutual funds? The table below lists the performance of sixteen mutual funds and variable annuities over the last 10 years, listing the average annualized return and the standard deviation of the annual returns over that period.
