You should be able to use the worksheet when not connected to the internet. Save the program file (ws_***.htm) and the accompanying instruction file (wi_***.htm) on your disk using the browser's command to File Save. At a later time you may retrieve the file using the browser's File Open file command; you may then use the page as you would if you were on the network. Note, however, that the file saved will have all the initial settings rather than those you have changed.
When using the worksheet, you may change any inputs. To do so, click inside the appropriate box, then make your changes or select the desired radio button. When finished, click any area outside the boxes on the form.
This worksheet is designed to find the range of likely post-retirement standards of living, based on specified savings and investment assumptions. It uses a very simple model of investment returns and hence should be considered only illustrative of more sophisticated methods that can be employed for this task.
The worker (principal) is assumed to purchase a real annuity at retirement. Such an annuity is guaranteed to provide a nominal amount that varies with inflation so as to provide a specified real amount, based on its terms. The three blocks in the first row of the input table provide the information on the nature of the desired annuity. If only the worker is to be covered in a single annuity, only the sex and age of the worker need be provided. If a joint annuity is to be purchased at retirement, the sex and age of the beneficiary must also be given. If a joint annuity is selected, the benefit will drop to 50% of its initial amount on the death of either the worker or the beneficiary.
The Investment Real Return block provides inputs for the expected annual real return and the standard deviation of the annual real return of the investments to be purchased with current and future savings up to the date of retirement. Note that the expected return is the expected value of the one-year return, not the long-term or geometric mean.
The next block provides the assumed Real Annuity discount rate. This is the rate that an annuity provider is assumed to use to discount expected future cash flows to determine the cost of an annuity. The higher this rate, the cheaper will be a given annuity and the greater will be the standard of living associated with a given investment account value at retirement. The expected cash flows are derived using an equally-weighted average of the mortality rates in the Commissioners 1980 Standard Ordinary (1970-1975) table, designed for estimating mortality for those who apply for individual life insurance policies, and the 1983 Individual Annuity Table (1971-1976, projected to 1983), designed for estimating mortality for those who apply for individual annuity policies.
The Worker Retirement Age is the age at which the worker wishes to retire. The worker is assumed to retire on the same date of the year that the analysis is performed. Thus if the Worker's Nearest Age is 60 and the retirement age is 65, the worker is assumed to retire at the end of 5 years.
The Savings block provides details concerning current and planned future savings. The Current Savings equals the total amount currently invested, divided by current salary. This expresses savings in terms of the number of years of current salary, putting it in a useful relative context. The Future Savings is stated as a percent of future salary. This percentage of salary will be saved each year up to retirement and added to the investment account.
The Real Salary Increase is stated as a percent of salary. The worker's real salary is assumed to increase each year up to retirement by this percentage.
The Analysis block provides information concerning the desired analysis. Two approaches are provided.
The first approach is deterministic -- the investment return is assumed to be the same every year, with the actual return equal to the geometric mean of the investment distribution. This is the estimated long-run (50/50) return for the specified expected return and standard deviation of return.
The second approach is stochastic. Monte Carlo simulation methods are used to estimate the range of likely outcomes. In this procedure, many cases are generated. Each such case involves a year-by-year projection of results up to the retirement age followed by determination of the resulting standard of living. Annual investment returns are drawn randomly from a probability distribution with the specified expected return and standard deviation. When a case has been completed, the result is recorded and added to a list of results -- one per case. When all the requested cases have been completed, this list of results is sorted from best to worst. Then the outcomes lying at specified percentiles are determined. For example, if 1,000 cases are simulated, the 10'th percentile outcome is the 100'th from the bottom, the 50'th percentile outcome is the median outcome, and the 90'th percentile outcome is the 900'th from the bottom (and 100'th from the top). The Analysis block allows the user to specify both the number of cases and the percentiles for which outcomes are to be shown.
In any given year the investment return is assumed to be earned on the initial account value plus half the annual contribution rate times the initial salary. This amount is added to the initial value of the account at year-end. Next, the salary is increased by the specified annual percentage. Finally, an amount equal to half the annual contribution rate times the ending salary is added to the account. The entire process is repeated for each year to retirement.
All results are stated in terms of the replacement ratio at retirement. This is the ratio of the value of the annuity purchased at retirement to the worker's salary just prior to retirement. The greater the ratio, the higher the associated standard of living.
For the simulation, annual returns are assumed to be drawn from a lognormal distribution where the return (not the logarithm of return) has the expected return (e) and standard deviation (sd) specified in the input section. This is accomplished by drawing a normal random deviate and using it to determine the logarithm of the value-relative (1 + return) for the year. Let:
zt = log ( 1 + rt )
rt = the (proportional) return in year t
The first and second moments (a and b) of the distribution of z are determined as follows:
b = sqrt ( log ( ( v / (u^2) ) + 1 ) )
a = 0.5 * log ( (u^2) / exp(b^2) )
u = 1 + e / 100
v = ( sd / 100 )2
Once a value for zt is drawn from a normal distribution with mean a and standard deviation b, the corresponding return is determined using:
1 + rt = exp (zt )
Random normal deviates are generated using the Box-Muller method (Box, G.E.P and M.E. Muller, A note on the generation of random normal deviates, Annals Math. Stat, V. 29, pp. 610-611).