The Investment Simulation Spreadsheet

http://www.wam.umd.edu/~toh/investment/
Tom O'Haver, University of Maryland, March 1997. Revised April 2008.


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 Inputs:

The Outputs:
The Graphs:
Experiments.
  1. Start with all the inputs set to zero. Obviously in this case you never accumulate anything and all the graphs stay at zero.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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?

  8. 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.

  9. 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.

    Name of fund 10-year average
    annual return
    Standard
    Deviation
    Fidelity Growth & Income 20 % 15
    Fidelity Puritan 15 % 10
    Washington Mutual Investors 17.6 % 14
    Income Fund of America 15 % 11
    Fundamental Investors 17.9 % 13
    New Perspectives 14 % 10
    Investment Company of America 16.8 % 12
    Invesco Dynamics 18.5 % 21
    MAS Equity 16.7 % 13
    VALIC Growth fund 15.5 % 13
    VALIC Science and Technology 22.7 % 22
    Lincoln Global Asset Allocation 8.3 % 11
    Lincoln Growth and Income 13.4 % 13
    Lincoln Managed Fund 10.4 % 10
    Lincoln Social Awareness 14.2 % 17
    Lincoln Special Opportunities 13.3 % 16

    Obviously, both high average return and low standard deviation are desirable. In general, funds that use more aggressive investment strategies (such as Invesco Dynamics) yield greater average returns and greater standard deviations than funds that use more conservative strategies (such as the Lincoln Managed Fund). Your employer's 401k plan will not have these particular funds available, but they will hopefully have a range of different equity funds, some conservative and some more agressive, for you to choose from. Most people like to spread out their contributions between several funds. You can simulate the effect of investing in these types of funds by using these values to set the "Expected Return on Equities" and "Volatility" inputs. However, keep in mind that these numbers are only for the 10-year period 1988-1998. This period has been a better-than-average period for the US economy and it includes the longest-running bull market ever. The long-term average returns of the equity mutual funds with the longest track records (such as Investment Company of America) hve been only 13 to 14% over a longer 30 - 50 year period. It is likely that the equity funds in your employers 401k plan selection will have long-term returns somewhere in the range of 10% to 14%. If possible, you should try to select the funds with the best long-term returns.

  10. Another way to reduce risk is to invest in a mix of fixed investments and equities. Most employer-sponsored 401k plans have both types of funds available. You can simulate this by setting the "Fraction in equities" somewhere between 0 and 100%. You will find, however, that diluting your equity investments with fixed-return investments will reduce your average annualized returns. For example, if you have a portfolio of 50% equities (returning 12%) and 50% fixed investments (returning 6%), then the overall return of this mixed portfolio would be 9% (half-way between 12% and 6%). Most financial investors recommend that long-term investors should have 80% to 100% of their principal invested in equity funds.

  11. Perhaps the best way to reduce volatility, without reducing your investment returns, is to construct a portfolio that distributes its assets between different fund types and sectors, for example, a mix of domestic and foreign funds, large-company, small-company, and mid-size company funds, industry sectors such as technology, pharmaceuticals, and financial funds, and funds utilizing different investment strategies such as "growth", "value", and "income" funds. The idea is that if some types of funds are doing poorly one year, other types of funds may be doing better in that year, which will help to smooth out returns from year to year. If each of the funds achieves good long-term returns on its own, then this strategy can reduce volatility without reducing the overall long-tern returns of the portfolio. You can learn about the holdings, historical rates of return and volatility, and investment strategies of mutual funds by researching the funds on Morningstar (http://www.morningstar.com) or in Value Line (http://www.valueline.com) or by looking on the funds' own Web sites.

  12. How to get started. See your employer's payroll department or officer to learn if they have a 401k plan. Many employers have a automatic payroll deduction plan that can be set up to withdraw a specified amount from your paycheck before taxes and invest it in one or more mutual funds or other investment instruments in your 401k account. This is usually the most painless and the most reliable way of insuring your continued contributions. Some employers will even "match" a portion of your contributions with their own contributions, up to some limit. If you don't take advantage of that, you are in effect throwing away perfectly good money. A 401k plan also has the beneficial effect of reducing your income tax rate during you working years. Contributions to your 401k are made with untaxed dollars and accumulate tax-free until you begin to make withdrawals after you retire, at which time you play regular income taxes on the amount that you withdraw. (If you leave your current employer, it is always possible to "roll over" your 401k account into another tax-deferred retirement plan). Or you can set up your own IRA (Individual Retirement Account) to which you can contribute up to $2000 per year. A regular IRA, like a 401K plan, is funded with pre-tax dollars, and you pay regular income taxes on the amount you withdraw in retirement. A newly available option is the "Roth IRA"; contributions to a Roth IRA are made with after-tax dollars, but there are no taxes on investment gains, and withdrawals are tax-free when you retire. See a personal financial planner for detailed advice on these and other retirement investment options.

References

  1. Personal and Family Finance (http://www.nnfr.org/econ/famfin.htm)

  2. Mutual Fund Investors Center (http://www.mfea.com/)

  3. TIAA-CREF Library Series (Detailed, customer-friendly information on retirement, investing, Social Security and health care) (http://www.tiaa-cref.org:80/libra/index.html)

  4. Fidelity Investments, Retirement section (http://personal300.fidelity.com/retirement/)

  5. VanKampen Investor Library (http://www.vankampen.com/knowledge/educ/)

  6. Stock Market Indices (http://www.efmoody.com/investments/)

  7. A Primer of Asset Allocation and Portfolio Theory for Small Investors (http://www.efficientfrontier.com/BOOK/title.shtml)

  8. "The Mutual Fund Wealth Builder", Michael D. Hirsh, HarperBusiness, 1992.

  9. "The Quick and Easy Guide to Investing for Retirement", G. Liberman, A. Lavine, C. Janik, and R. Rejnis, Alpha Books, 1996.

  10. "How to Retire Young and Rich", J. S. Coyle, Warner Books, 1996.

  11. "How to Pick the Best No-load Mutual Funds for Solid Growth and Safety", Sheldon Jacobs, Irwin Professional Publishers, 1992.

  12. "How Mutual Funds Work", Albert J. Fredman and Russ Wiles, New Your Institute of Finance, 1993.

  13. "Die Broke: A Radical Four-Part Financial Plan", Stephen M. Pollan and Mark Levine, HarperCollins, New York, 1997.

(c) 1997, 1999 T. C. O'Haver, The University of Maryland at College Park
toh@umd.edu
Unique visitors since May 17, 2008: