80.2K

Verified Solution

Link Copied!

PLEASE HELP! Can someone provide the excel documentation as well or atleast show how to compute this in excel

Case: Retirement Plan

Suppose you work as a financial planner for Charles Schwab. Your supervisor brought you a new client named Tim who would like to establish a retirement plan. You met with Tim and gathered some information about him. Your next step is to develop a spreadsheet model that could be used to assist Tim with his retirement planning. Your model should include the following input parameters:

Tims current age = 37 years

Tims current total retirement savings = $259,000

Annual rate of return on retirement savings = 4%

Tims current annual salary = $145,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 = $90,000 (in nominal dollars)

Rate of return on retirement savings after retirement = 3%

Income tax rate postretirement = 15%

Additional information about the client: 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 $6,000 per year. Also, assume an inflation rate of 2% per year.

(1) Develop a retirement plan spreadsheet model in MS Excel,

Your spreadsheet model should provide the accumulated savings at the beginning of his retirement as well as the age at which the 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 his savings/funds will be depleted to the retirement age and additional pre-tax contributions. Similarly, consider other factors you think might be important (e.g., changes in inflation rate, interest rates, etc).

Answer & Explanation Solved by verified expert

See Answer