Czech
translation by Barbora Lebdová; Swedish
translation by Andrijana Savicević; Russian
translation by Sandi Wolfe

These Excel 5.0 spreadsheet simulations were developed for instructional purposes, to demonstrate in a graphic and interactive manner the potential benifits of long-term investing. They are not intended as tools for detailed personal financial planning.

The Investment Simulation Spreadsheet 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) over a 35-year period by saving a certain amount each year and investing it in a combination of fixed-interest or variable (equity) instruments.

The Income Simulation Spreadsheet shows how much income you can withdraw from a retirement account (e.g. an IRA or 401k account) that is invested it in a combination of fixed-interest or variable (equity) instruments.

Both of the simulations use a random-number
generator to simulate the fluctuation (volatility) in the
investment returns of equities (stocks and stock mutual funds).
Most spreadsheets have only a *uniformly-distributed*
random number function (RAND) and not a *normally-distributed*
random number function like a haystack curve, but it's much more
realistic to simulate deviations that are normally
distributed, because normal distributions have more small
deviations that are close to the mean and few deviations that are far from the mean. In
terms of investments, small losses and gains are much more
common that large ones. So these spreadsheets make use of the Central
Limit Theorem to create approximately normally distributed
random numbers by combining several RAND functions.

The RAND() function produces random numbers with the range
of zero to 1.0; it can never produce a number greater than 1.00.
As a result, rand()-rand() produces numbers with a mean of zero
and with a maximum range of values from -1.00 to +1.00 and never
greater, no matter how many times you try.

However, the actual observed range of random variables in the
real world is described by a "Gaussian" or "Haystack" curve,
which has a progressively lower probability of producing a
number far from the average. So, for that reason, the
formulations `rand()-rand()+rand()-rand()`, or even `rand()-rand()+rand()-rand()+rand()-rand()`,
are more realistic, allowing for a small but non-zero
probability of generating occasional results that are very far
from the norm.

The standard deviation of` rand(1,1000)` is about
0.28

The standard deviation of `rand(1,1000)-rand(1,1000)` is
about 0.41.

The standard deviation of `std(rand(1,1000)-rand(1,1000)+rand(1,1000)-rand(1,1000))`
is about 0.57.

The last one, which has 4 "RAND"s, is the best because it allows
for the occasional large deviation (market crash or bubble).

In order to add a known amount of variation to a simulation (of
stock market gains and losses, for example) you need to consider
how variation is measured. Most commonly. this is by
calculating the standard deviation. So, to add a known standard
deviation of variation to a number, such as a stock price or
portfolio balance. you can write:

RealPrice = AveragePrice + DesiredStandardDeviation *
rand(1,1000)-rand(1,1000)+rand(1,1000)-rand(1,1000))

But this won't work out quantitatively, because the standard
deviation of the RAND expression is not 1.000. To fix
that. you just divide by 0.57, and 1/0.57 is 1.7544, which
I rounded up to 0.8 in my spreadsheet.

(c) 1997, 2021 T. C. O'Haver, The University of Maryland at College Park

toh@umd.edu Number of unique visits since May 17, 2008: \