Rob is thinking about retirement. He is 38 years old and working as a sales associate at Dillard's. His current annual income is $56,000 and this income will increase at a 5% rate annually. Dillard's offers a defined contribution plan and Rob contributes $2240 out of his gross income every year and now his current retirement account balance is $55,000. He wants to retire at his normal retirement age which is 67, and his life expectancy is 90. The interest rate before and after retirement is assumed to be 7.5%, and inflation is 3.5%. Use a 75% wage replacement ratio. His estimated monthly social security benefit is $5,649.00. Please prepare a retirement plan sheet including your calculations in excel file for the client. And answer these two questions 1) Does he have a retirement shortfall or surplus? How much? 2) How much more he needs to save on top of the $2240 annual contribution in order to fill the shortfall? PLEASE SHOW ALL WORK AND EQUATIONS USED IN EXCEL.

edit:

He wants to retire at his normal retirement age which is 67, and his life expectancy is 90. The interest rate before and after retirement is assumed to be 7.5%, and inflation is 3.5%. Use a 75% wage replacement ratio. You will need to check his estimated social security benefit online using the calculator.