Question

You are planning to save for retirement. You would like to retire 22 years from today and you currently have $205,000 set aside. You anticipate saving $750 per month ($500 out of your pocket and $250 from a company match into your 401(k) plan. You anticipate earning an 8.7% rate of return over the next 9 years. After 9 years, you will up your monthly savings to $X per month (combined contribution from you and your employer into your 401(k) plan) over the last 13 years of your savings period. During this 13 year, you will lower your risk-return strategy so that the expected return will be 7.8%. Once you hit retirement, you want to take out $160,000 on the day you retire. After that you will take out money at the end of each year as follows: Years 1-7 $110,000 per year Years 8-15 $130,000 per year Years 16-21 $120,000 per year Finally, you want to have $300,000 remaining at the end of the 21-year retirement period and you anticipate earning 4.3% per year in retirement (Hint: Note that the $300,000 remaining is at year 21 of the retirement period so that your year 21 CF is $420,000 – the last $120,000 plus the $300,000). Figure out how much you need to save per month over the final 13 years leading to retirement in order to meet your plan.

Answer 1

		Savings required at the time of retirement
		Present Value(PV) of cash flow
		(Cash flow)/((1+i)^N)
		i=interest rate =4.3%=0.043
		N=Year of cash flow
		N	CF	PV=CF/(1.043^N)
		Years fromdate of retirement	Cash flow	Present Value
		0	$160,000	$160,000.00
		1	$110,000	$105,465.00
		2	$110,000	$101,116.97
		3	$110,000	$96,948.20
		4	$110,000	$92,951.30
		5	$110,000	$89,119.17
		6	$110,000	$85,445.04
		7	$110,000	$81,922.37
		8	$130,000	$92,825.84
		9	$130,000	$88,998.89
		10	$130,000	$85,329.71
		11	$130,000	$81,811.80
		12	$130,000	$78,438.93
		13	$130,000	$75,205.11
		14	$130,000	$72,104.61
		15	$130,000	$69,131.94
		16	$120,000	$61,183.22
		17	$120,000	$58,660.80
		18	$120,000	$56,242.38
		19	$120,000	$53,923.66
		20	$120,000	$51,700.54
		21	$420,000	$173,491.74
			SUM	$1,912,017.23
		Amount required at retirement	$1,912,017
		Amount Accumulated at the end of 9 years
	Rate	Annual Interest rate over 9 years	8.70%
	Nper	Number of years	9
	Pmt	Annual Savings=750*12	$9,000
	Pv	Savings available today	$205,000
	FV	Value of savings at end of 9 years	$550,055.71	(Using FV function of excelwith Rate=8.7%,Nper=9,Pmt=-9000, Pv=-205000)
		ExcelCommand :FV(8.7%,9,-9000,-205000)
	FV1	Value of Savings at end of 22 years
	Rate	Interest rate	7.80%
	Nper	Number of years	13
	Pv	Value of savingsat end of 9years	$550,055.71
	FV1	Value of Savings at end of 22 years	$1,460,329	(Using FV function of excelwith Rate=7.8%,Nper=13, Pv=-550055.71)
		Excel Command: FV(7.8%,13,,-550055.71)
		Savings required	$1,912,017
	FV2	Additional savings to be accumulated	$451,689	(1912017-1460329)
	Rate	Interest rate during 13 years of savings	7.80%
	Nper	Number of years	13
	FV2	Future Value required	$451,689
	PMT	Annual savings required	$21,289.68	(Using PMT function of excel with Rate=7.8%,Nper=13, FV=-451689)
		Excel Command: PMT(7.8%,13,,-451689)
	X=	Amount of savings required per month	$1,774.14	(21289.68/12)