John is 37 years old and would like to establish a retirementplan. Develop a spreadsheet model that could be used to assist Johnwith retirement planning. Your model should include the followinginput parameters:
John’s current age = 37 years,
Johns current total retirement savings = $259,000,
Annual rate of return on retirement savings = 4 percent,
Johns current annual salary = $145,000,
Johns expected annual percentage increase in salary = 2percent,
Johns percentage of annual salary contributed to retirement = 6percent,
Johns expected age of retirement = 65,
Johns expected annual expenses after retirement (currentdollars) = $90,000,
Rate of return on retirement savings after retirement = 3percent,
Income tax rate postretirement = 15 percent
Assume that John’s employer contributes 6% John’s salary to hisretirement fund. John can make an additional annual contribution tohis retirement fund before taxes (tax free) up to a contribution of$16,000. Assume he contributes $6,000 per year. Also, assume aninflation rate of 2%.
Managerial Report Your spreadsheet model should provide theaccumulated savings at the onset of retirement as well as the ageat which funds will be depleted (given assumptions on the inputparameters).
Outline the factors that will have the greatest impact on hisretirement.
Show all work in excel.
