70.2K

Verified Solution

Link Copied!

In the Modeling & Problem Solving module, the second problem dealt with a client who was planning for retirement. While meeting with him to discuss his needs further he asked for you to please build a model for him so he can see what his total retirement savings would likely be given the following:

His current age is 46 (He happens to have been born on January 1st).

His current annual salary is $126,000 and he gets a 2% salary increase annually.

He has decided that he wants to retire on his 65th birthday.

He started this year with retirement savings of $137,000

His annual contributions to his savings are 5.95% of his annual salary

His employers contributions to his savings are 7.95% of his annual salary

The annual rate of return on his savings account is 7%.

Assumptions:

His retirement contributions show up all at once on 12/31 each year.

His returns all appear at once on 12/31 each year.

Salary increases take effect as of 1/1 each year, always a nice birthday present!

Given the above, for this year we know (in rounded dollars):

His contributions will be ($126,000 * 5.95%) = $7,497

His employers contributions will be ($126,000 * 7.95%) = $10,017

Together, the total contributions will be ($7,497 + $10,017) = $17,514

His annual return on his savings will be ($137,000 * 7%) = $9,590

His account therefore will grow by ($17,514 + $9,590) = $27,104

At the end of this year his account will have ($137,000 + $27,104) = $164,104

In a this Excel file, do the following:

1. In a new sheet, create an influence diagram for this analysis.

2. In another new sheet, construct a model containing three modules: Parameters, Outputs, and Calculations. Name this sheet Model.

In the models where there have been only a few time periods in the analysis we have made one column per time period. When an analysis contains many years, like this one, I recommend flipping it so that the time periods go down the rows instead of across the columns.

Age	Salary	Contribution	Employer Cont.	Total Contribution	Annual Return	Annual Growth	Balance on 12/31
46	$126,000	$7,497	$10,017	$17,514	$9,590	$27,104	$164,104

When you're done, the Excel file (workbook) should contain three tabs (sheets): This one, the Influence Chart, and the Model.

Answer & Explanation Solved by verified expert

See Answer