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. Your model should include the following input parameters:

Tims current age 5 37 years Tims current total retirement savings 5 $259,000 Annual rate of return on retirement savings 5 4 percent Tims current annual salary 5 $145,000 Tims expected annual percentage increase in salary 5 2 percent Tims percentage of annual salary contributed to retirement 5 6 percent Tims expected age of retirement 5 65 Tims expected annual expenses after retirement (current dollars) 5 $90,000 Rate of return on retirement savings after retirement 5 3 percent Income tax rate postretirement 5 15 percent

Assume that Tims employer contributes 6% of Tims salary to his retirement fund. Tim can make an additional annual contribution to his retirement fund before taxes (tax free) up to a contribution of $16,000. Assume he contributes $6,000 per year. Also, assume an inflation rate of 2%.

Your spreadsheet model should provide the accumulated savings at the onset of retire- ment as well as the age at which funds will be depleted (given assumptions on the input parameters).

As a feature of your spreadsheet model, build a data table to demonstrate the sensitiv- ity of the age at which funds will be depleted to the retirement age and additional pre-tax contributions. Similarly, consider other factors you think might be important.

Develop a report for Tim outlining the factors that will have the greatest impact on his retirement.