Tim is 37 years old and would like to establish a retirement plan. Develop a spreadsheet model that could be used to assist Tim with retirement planning. The following information characterizes the economic conditions that Tim is facing. All these parameters are deterministic, and we think their values will not change in the future.

Current age: 37 years

Current annual salary: $145,000

Current total retirement savings: $259,000

Personal contribution to retirement based on annual salary: 5%

Employers contribution to retirement based on annual salary: 6%

Additional annual contribution cap: $12,000

Income tax rate post retirement: 15%

There are also some other important factors that impact on the retirement fund. But their values may or may not be changed in the future. Hence, we will view these factors as uncertain factors and list the estimated values of these factors.

Annual expenses after retirement (current dollars): $90,000

Return rate on retirement savings: 4%

Annual percentage increase in salary: 2%

Inflation rate: 2%

Tims decisions focus on two issues: how many to contribute additionally every year and at what age to retire. His current plan is as follows:

Make an additional $6,000 contribution every year

Retire at the age of 65

All the above data are presented in the attached Excel template file.

You can also find the two model templates. One is for calculating the saving accumulation over the years. Since Tim may change his decision on at what age to retire, the template allows to calculate the saving accumulation up to that Tim is 100 years old. The other is for calculating the saving depletion over the years after his retirement.

Set up the spreadsheet models to analyze the performance of Tims retirement plan. Try to answer the following questions:

1. Set up a spreadsheet model to predict the accumulated savings at the onset of retirement given to the current estimation on the uncertain factors and Tims current plan. Make your own assumption on when, at the beginning of the year or at the end of the year, the contributions will be added to the fund. If added at the beginning of the year, the contributions will also gain investment growth over the year. Note that Tim may change his retirement age. Your model must include as many as possible years to cover all the possibilities, such as till Tim is 100 years old when it is sure that he will retire.

2. Set up a spreadsheet model to predict the age at which funds will be depleted, given the current estimation on the uncertain factors and Tims current plan. Suppose Tim retires at the beginning of the year and he always withdraws the money at the end of the year. Note that it is impossible to tell at what age Tim will die. To deal with these two issues, we can let the model include as many as possible years to cover all the possibilities, such as till Tim is 128 years old when he is nearly impossible to be alive.

3. Based on the models you have set up in questions 1 and 2, analyze how Tims two decision focuses (the retirement age and the additional yearly contribution) impact the age at which funds will be depleted. Come up with a range of retirement age for testing as well as a range of additional yearly contributions. Build up a two-way data table to show the age at which funds will deplete based on every combination of retirement age and additional yearly contributions. Graphically demonstrate the impacts and make the comments. Calculate how many years Tim will earn by delaying retirement (e.g. from 65 to 70) or by increasing the additional yearly contribution (e.g. from $6,000 to $12,000).

4. Build a two-way data table to demonstrate the sensitivity of the age at which funds will be depleted to the inflation rate and the return rate of retirement savings. Suppose the lowest possible inflation rate is 1% and the highest possible is 10%. The return rate can be as low as 1% and as high as 5%. Graphically show the impacts and make comments on your findings.