RETIREMENT PLANNER CASE STUDY

This assignment involves developing a spreadsheet to determine how much will need to be deposited into an account to meet retirement income objectives.

The following information is to be placed into the cells shown:

Current salaryA4

Money desired per year if retirement took place nowA5

Rate of salary growth as a decimal (i.e. .025 for 2.5%/yr)A6

Rate of inflation per year as a decimalA7

Interest rate as a decimal during first half of working yearsA8

Interest rate as a decimal during last half of working yearsA9

Number of years workingA10

Number of years retiredA11

Interest rate as a decimal during first half of retirement yearsA12

Interest rate as a decimal during second half of retirement yearsA13

This assignment will assume that you will work for A10 years, and are retired for A11 years.The first retirement withdrawal will be at the end of year A10+1.Deposits will be made at the end of each year from years 1-A10.Withdrawals need to increase at the rate of inflation.The range for A10 is 5-50 and for A11 is 10-40, with only even numbers allowed.

Part A.Determine the annual deposit required for years 1-A10 if each deposit is the same.Complete the following table for years 1 through A10 + A11.

YrBeg BalanceInterestDepositWithdrawalSalary % SalaryEnd Balance

Part B.Determine the annual deposit required for years 1 - A10 if the amount deposited each year will increase at the same rate as the increase in salary.Complete a second table with the same columns as in part a.

Part C.Assume that the number of working years is 40 and the number of years retired is 20.Assume that the salary annual salary growth rate, the annual inflation rate, and the annual investment rate are now normally distributed random variables, with a mean as given and a standard deviation equal to 15% of the mean.These variables will now change each year.Redo part A.Determine how much extra will be needed to deposit each year to be 90% sure you will meet your retirement objective.Complete a new table for this part.

Part D.Assuming a 40/20 year scenario, and using the same random variables generated in part C, determine the % of salary necessary to be 90% sure you will meet your retirement objective.Complete a new table for this part.

Part E.Assume your employer also contributes to your retirement plan with a given percent, X%, where X is from 1-10% of your salary each year.Assuming a 40/20 year scenario, and using the same random variables in part C, complete a table showing for values of X in 1% increments the required annual deposit in $ and as a % of your salary that will be necessary to be 90% sure you will meet your retirement objective.

Part F.For part C assume the deposit could be adjusted each year and the withdrawals can be adjusted each year to make the balance come out to zero.Construct a sheet that does this as the simulation trials proceed.Create forecast cell that keeps track of the total amount that will need to be deposited and another forecast cell for the total amount of money withdrawn for each cycle.