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