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 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
Get step-by-step solutions from verified subject matter experts
