In the Modeling & Problem Solving module, the second problem dealt with a client who was planning
Fantastic news! We've Found the answer you've been seeking!
Question:
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 employer’s 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 employer’s 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. |
Related Book For
Posted Date: