Question

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.

Answer 1

		Inputs:
			Years to retirement	29	(67-38)
			Length of retirement (in years)	23	(90-67)
			Amount already invested	$55,000
			Current Annual Savings	$2,240
			Current Annual Income	$56,000
			Annual interest rate	0.075
			Annual Inflation	0.035
			Salary increase per year	0.05
			Wage Replacement Ratio	0.75
			Annual Social Security Benefit	$67,788	(5649*12)
		Ouputs:
			At retirement:
			Annual Income just before Retirement	$230,504	(56000*((1+0.05)^29))
			Annual Retirement income needed	$172,878	(230504*0.75)
			Annual Retirement income required from investment	$105,090	(172878-67788) Social security benefits
			Inflation Adjusted Return on investment	0.0386	((1+0.075)/(1+0.035))-1
			Fund required at the time of retirement	$1,582,423	(Using PV Function of excel with Rate=0.0386,Nper=23,Pmt=-105090
			Total needed at retirement	$1,582,423
			Funds available at retirement	$661,300	(Using FV Function of excel with Rate=0.075,Nper=29,Pmt=-2240Pv=-55000)
			Additional Funds needed	$921,123	(1582423-661300)
		1	THERE IS SHORTFALL OF	$921,123
		2	Required additional annual contribution	$9,670	(Using PMT Function of excel with Rate=0.075,Nper=29,Fv=-921123)
					LMDudlu Allgrimeri Number Styles H22 =PV(H21, H6,-H20) 4 A B C D E F J K L M.NO Years to retirement Length of retirement (in years) Amount already invested Current Annual Savings Current Annual Income Annual interest rate Annual Inflation Salary increase ner vear Salary increase per year Wage Replacement Ratio Annual Social Security Benefit H I 29 (67-38) 23 (90-67) $55,000 $2,240 $56,000 0.075 0.035 0.05 0.75 $67,788 (5649*12) Ouputs: At retirement: Annual Income just before Retirement Annual Retirement income needed Annual Retirement income required from investment Inflation Adjusted Return on investment Fund required at the time of retirement $ 230,504 (56000*((1+0.05)^29)) $172,878 (230504*0.75) $105,090 (172878-67788) Social security benefits 0.0386 ((1+0.075)/(1+0.035))-1 $1,582,423 (Using PV Function of excel with Rate=0.0386,Nper=23,Pmt=-105090 H24 : X fix =FV(H10,45,-H8,-H7) A B C D E J K L M N O P Q Years to retirement Length of retirement (in years) Amount already invested Current Annual Savings Current Annual Income Annual interest rate Annual Inflation Salary increase per year Wage Replacement Ratio Annual Social Security Benefit I 29 (67-38) 23 (90-67) $55,000 $2,240 $56,000 0.075 0.035 0.05 0.75 $67,788 (5649*12) Ouputs: At retirement: Annual Income just before Retirement Annual Retirement income needed Annual Retirement income required from investment Inflation Adjusted Return on investment Fund required at the time of retirement Total needed at retirement Funds available at retirement Additional Funds needed $230,504 (56000*((1+0.05)^29)) $172,878 (230504*0.75) $105,090 (172878-67788) Social security benefits I ((1+0.075)/(1+0.035))-1 $1,582,423 (Using PV Function of excel with Rate=0.0386,Nper=23, Pmt=-105090 $1,582,423 $661,300 (Using FV Function of excel with Rate=0.075,Nper=29,Pmt=-2240Pv=-55000) $921 123 1/1582423.661300) H27 =PMT(H10, H5,-H26) A B C D E F Ι Ι Ι Κ Ι ι Ι Μ Ι Ν Ι Ο Ι Ρ Years to retirement Length of retirement (in years) Amount already invested Current Annual Savings Current Annual Income Annual interest rate Annual Inflation Salary increase per year Wage Replacement Ratio Annual Social Security Benefit Η Ι Ι 29 (67-38) 23 (90-67) $55,000 $2,240 $56,000 0.075 0.035 0.05 0.75 $67,788 (5649*12) Ouputs: At retirement: Annual Income just before Retirement Annual Retirement income needed Annual Retirement income required from investment Inflation Adjusted Return on investment Fund required at the time of retirement Total needed at retirement Funds available at retirement Additional Funds needed 1 THERE IS SHORTFALL OF 2 Required additional annual contribution $230,504 (56000*((1+0.05)^29)) $172,878 (230504*0.75) $105,090 (172878-67788) Social security benefits 0.0386 ((1+0.075)/(1+0.035))-1 $1,582,423 (Using PV Function of excel with Rate=0.0386,Nper=23,Pmt-105090 $1,582,423 $661,300 (Using FV Function of excel with Rate=0.075,Nper=29,Pmt=-2240Pv=-55000) $921,123 (1582423-661300) $921,123 $9,670 (Using PMT Function of excel with Rate=0.075,Nper=29,Fv=-921123)