Retirement Plan to take probabilities into account (Finance)

Chapter 7

Spreadsheet Models

Case Problem: Tim’s Retirement Planning

We take as the base case for Tim’s retirement planning problem the parameter settings below:

While there are many ways to model this problem, any sound approach will have the following characteristics:

There spreadsheet model will have a parameters section and a model section.

There will be essentially two modules for calculating the age when funds run out and the balance at the beginning of retirement. These are a pre-retirement module and a post-retirement module. During the pre-retirement time, money is accumulated through salary-based contributions from Tim, additional pre-tax contributions from Tim, the school’s contribution to Tim’s account and returns on the investment. During the post-retirement time, spending is taken out of the account, taxes must be paid on the amount withdrawn, and returns on investment accrue into the account.

We have made a number of assumptions including the following:

All input parameters will be constant over time.

All contributed funds come in at the end of the year, so that those funds do not earn a return during that year.

After retirement, a return is made on the beginning balance for that year and all expenses are deducted at the end of the year.

Below we show each portions of each individual module. Note that we inflate the salary by the appropriate amount each year (column C) and we include the return on investment along with the other cash flows into the fund (Column H).

Note that spending is inflated in (Column L). We use the INDEX function in cell N27 to find the beginning balance based on the year of retirement. We use an IF statement to determine if funds are still available or not. In cell Q27, we use an INDEX function with the MATCH function to find the first occurrence of a one in column P and return the age of Tim when this occurs.

There are many factors at work here and student responses may vary. However the impact of retirement age and additional pre-tax contributions is specifically requested. A Data table as shown below indicates how age when funds run out varies with these inputs.

Graphically, using just the even ages we have:

Obviously as retirement age and pre-tax contributions increase, so too will the age when funds run out. For a given age, contributing the maximum pre-tax additional contributions will earn Tim an additional 4 to 6 years. Delaying retirement by 5 years (from 65 to 70) will earn Tim an additional two to three years.

While retirement age and pre-tax additional contributions are choices Tim can make, many other factors will have an impact on his retirement account. For example, the rate of inflation and the return on the pre-retirement funds are variables that Tim cannot directly control. The following table and chart below shows the impact of these factors on the age when funds run out.

As the chart shows, even moderate inflation can have a major impact on how long the fund lasts after retirement.

Contributing more or strong returns can help mitigate the impact of inflation.

Age When Funds Run Out

70 0 2000 4000 6000 8000 10000 12000 16000 83 84 85 85 86 87 88 89 68 0 2000 4000 6000 8000 10000 12000 16000 80 81 81 82 83 84 84 86 66 0 2000 4000 6000 8000 10000 12000 16000 77 78 78 79 80 81 81 83 64 0 2000 4000 6000 8000 10000 12000 16000 74 75 76 76 77 77 78 79 62 0 2000 4000 6000 8000 10000 12000 16000 72 72 73 73 74 74 75 76 60 0 2000 4000 6000 8000 10000 12000 16000 69 69 70 70 71 71 72 73

Additional Pre-tax Contributions

Age When Funds Run Out

Age When Funds Run Out

0 0 0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 77 73 71 69 68 67 67 66 66 65 65 0.01 0 0.01 0.02 0.03 0.0 4 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 80 75 72 70 69 68 67 66 66 66 65 0.02 0 0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.0 9 0.1 84 77 73 71 69 68 67 67 66 66 66 0.03 0 0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 89 80 75 72 70 69 68 67 67 66 66 0.04 0 0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 98 84 78 74 71 70 69 68 67 66 66 0.05 0 0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 113 89 81 76 73 71 69 68 67 67 66

Inflation

Age whenFunds Run Out