The Investment Simulation Spreadsheet

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

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 OpenOffice Calc version will work on both the Windows and the Macintosh version of OpenOffice, which is available for free download from openoffice.org.

The Microsoft Excel version is in Excel 97/2000/XP format. You must own Excel or Microsoft Office in order to run this version.

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.
  

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

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

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.