Question: how do I calculate this in excel with formulas? Case: Retirement Plan Suppose you work as a financial plannct for Charles Schwab. Your supervisor brought

 how do I calculate this in excel with formulas? Case: Retirement

how do I calculate this in excel with formulas?

Plan Suppose you work as a financial plannct for Charles Schwab. Your

Case: Retirement Plan Suppose you work as a financial plannct for Charles Schwab. Your supervisor brought you a new elient named Tim who would like to cstablish a rotircment plan. You met with Tim and gathered some information about him. Your ncxt step is to develop a sprcadshcet model that could be used to assist Tim with his retirement planning. Your modcl should include the following input parameters: - Tim's current age =37 ycars - Tim's current total retirement savings =$259,000 - Annual rate of return on retirement savings =4% - Tim's current annual salary =$145,000 - Tim's expectod annual percentage increase in salary =2% - Tim's percentage of annual salary contributcd to retirement =6% - Tim's expected age of retirement =65 - Tim's 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: Tim's employer contributes 6% of Tim's 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. - Your spreadshect 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 paramctcrs). - As a feature of your spreadshect 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-tar contributions. Similarly, consider other factors you think might be important (e.g., changes in inflation rate, interest rates, ctc). Develop a report for Tim outlining the factors that will have the greatest impact on his retirement. What recommendations can you give him to help him enhance his retirement savings, beyond the factors discussed above

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Finance Questions!