Part 1

As a base case, you have been assigned to help Tim, another employee at your company with his retirement planning. You have been tasked to 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 = 37 years

Tims current total retirement savings = 50,000

Annual rate of return on retirement savings = 4%

Tims current annual salary = 45,000

Tims expected annual percentage increase in salary = 2%

Tims percentage of annual salary contributed to retirement = 6%

Tims expected age of retirement = 65

Tims expected annual expenses after retirement (current pounds) = 25,000

Rate of return on retirement savings after retirement = 3%

Income tax rate post-retirement = 15%

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 that he contributes an additional 1,200 per year. Also, assume an inflation rate of 2%. Finally, the state pension should also be taken into consideration when helping Tim in planning his retirement. You will need to research how much state pension Tim can expect at retirement age and factor this into your model.

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

As a feature of your spreadsheet model, build a data table to demonstrate the sensitivity of the age at which funds will be depleted relative to the retirement age and additional pre-tax contributions should Tim wish to make additional contribution more than 1200 per year. Similarly, consider other factors you think might be important.

Part 2

Tim believes if he invested the additional 1200 himself rather than into the traditional employer pension scheme (as in Part 1), he should be able to achieve an annual portfolio growth rate of 10%. He also believes that he should be able to achieve an annual salary growth rate of 5% instead of 2%. Modify the spreadsheet accordingly and prepare a short report to demonstrate the models developed in parts 1 and 2 and discuss the key factors of the model.