Question

1. You have recently been hired as a consultant for a personal financial planning firm. One of your first projects is creating a retirement plan for a couple, Xavier and Elizabeth Martell. They have just celebrated their 40^th birthdays and after paying for their children’s education, they have decided to get serious about saving for retirement.

Xavier and Elizabeth hope to retire 25 years from now (on their 65^th birthdays), and they expect to live until age 90. Their hope is to be able to withdraw $150,000 a year from their retirement account – the first withdrawal will occur on their 65^th birthdays, and the 25^th and final withdrawal will occur on their 89^th birthdays. On their 90^th birthdays, the account is expected to have a zero value (i.e., they don’t expect to have any remaining funds left for their children’s inheritance).

Xavier and Elizabeth currently have $75,000 saved in a retirement account, which consists of a portfolio of mutual funds that is expected to produce an annual return of 8%. To accomplish their goals, they would like to deposit an equal annual amount into their account starting one year from today (on their 41^st birthdays) and continue to make those deposits through age 65. (Again, the account has an expected annual return of 8%.) Thus, they will make 25 annual end-of-year deposits to this account.

1. How much do Xavier and Elizabeth need to contribute to the account at the end of each of the next 25 years in order to accomplish their goals? (6 points)

2. If they wanted to have $1 million available for inheritance at age 90, how much would they need to contribute to the account at the end of each of the next 25 years? (Assume everything else stays the same.) (6 points)

2. If they instead expected to earn only 5% a year from their mutual funds, how much would they need to contribute to the account at the end of each of the next 25 years? (Continue to assume that they want to have $1 million available for inheritance at age 90.) (6 points)

2. Xavier and Elizabeth realize that there are a lot of variables in their retirement plan. The two variables that they are particularly interested in are the expected return of their mutual funds and the amount they have available for inheritance at age 90. Create in Excel a two-input Data Table that tests the sensitivity of their annual deposit amount by varying the expected returns from 2% to 10% in 1% increments and varying the inheritance level from 0 to $4 million in $0.5 million increments. The data table should be constructed with the expected returns shown on the side of the table and the amount available for inheritance shown across the table. [Hint: An Excel file (in xlsx format) explaining how to create a Data Table is contained in the Calculator Tutorials & Excel Tools folder on the class web page. In addition, there is a Data Table video explaining how to set up data tables in this folder.] You MUST set up and submit a functioning Excel data table with correct values within the data table using Excel’s Data Table feature with Data Table cell formulas to receive credit. (6 points)
3. Xavier and Elizabeth have one last concern. They recognize that the value of their $150,000 annual withdrawals during retirement will steadily decline because of expected inflation. Assume that they want to have the value of these withdrawals increase by 2% a year during retirement to account for expected inflation. In other words, they want to withdraw $150,000 at age 65, $153,000 at age 66, and 153,000 × 1.02 at age 67, etc. Going back to the other original assumptions (8% return and no expected inheritance), how much would they need to contribute to the account at the end of each of the next 25 years in order to meet this revised goal which protects them against rising inflation? Set up this problem using Excel, refer to the Growing Annuity Example spreadsheet in the Files Page in the Background Information folder on the class web page. (6 points)

ALSO IF YOU CAN SHOW ME WHAT FORMULA TO PUT IN FOR EXCEL THAT'D BE GREAT!!!
FOR EXAMPLE, =PV(RATE,NPER,PMT,FV,TYPE)

Answer 1

	a
		Years to retirement	25	(65-40)
		Length of retirement (in years)	25	(90-65)
		Amount already invested	$75,000
		Annual interest rate	0.080
		At retirement:
		Annual Retirement income needed	$150,000
		Return on investment	0.0800
		Fund required at the time of retirement	$1,729,314	(Using PV Function of excel with Rate=0.08,Nper=25,Pmt=-150000,Type=1
		Total needed at retirement	$1,729,314
		Funds available at retirement	$513,636	(Using FV Function of excel with Rate=0.08,Nper=25,Pv=-75000)
		Additional Funds needed	$1,215,678	(1729314-513636)
	1	THERE IS SHORTFALL OF	$1,215,678
	2	Required annual contribution	$16,629	(Using PMT Function of excel with Rate=0.08,Nper=25,Fv=-1215678)
	b
		Years to retirement	25
		Length of retirement (in years)	25
		Amount already invested	$75,000
		Annual interest rate	0.080
		At retirement:
		Annual Retirement income needed	$150,000
		Inheritance Required	$1,000,000
		Return on investment	0.0800
		Fund required at the time of retirement	$1,875,332	(Using PV Function of excel with Rate=0.08,Nper=25,Pmt=-150000,Fv=-1000000,Type=1)
		Total needed at retirement	$1,875,332
		Funds available at retirement	$513,636
		Additional Funds needed	$1,361,696
	1	THERE IS SHORTFALL OF	$1,361,696
	2	Required annual contribution	$18,626
												If they earn 5% from mutual fund: 25 25 $75,000 0.050 Years to retirement Length of retirement (in years) Amount already invested Annual interest rate At retirement: Annual Retirement income needed Inheritance Required Return on investment Fund required at the time of retirement Total needed at retirement Funds available at retirement 1 THERE IS SHORTFALL OF 2 Required annual contribution $150,000 $1,000,000 0.0500 $2,515,099 $2,515,099 $253,977 $2,261,122 $47,376 H20 =PV(H19, H6,-H18,,1) HI J K L M N O 25 (65-40) 25 (90-65) $75,000 $2,240 $56,000 0.080 5 Years to retirement 6 Length of retirement in years) 7 Amount already invested 8 Current Annual Savings 9 Current Annual Income 10 Annual interest rate 11 Annual Inflation 12 Salary increase per year 13 Wage Replacement Ratio 14 Annual Social Security Benefit 15 16 17 At retirement: 18 Annual Retirement income needed 19 Return on investment 20 Fund required at the time of retirement 0.05 0.75 $67,788 (5649*12) $150,000 0.0800 | $1,729,314 Using PV Function of excel with Rate=0.08,Nper=25, Pmt=-150000, Type=1 -: fax =FV(H8, H5,,-17) | н т т к с м 25 (65-40) 25 (90-65) $75,000 0.080 5 Years to retirement 6 Length of retirement (in years) 7 Amount already invested 8 Annual interest rate 9 10 11 At retirement: 12 Annual Retirement income needed 13 Return on investment 14 Fund required at the time of retirement $150,000 0.0800 $1,729,314 (Using PV Function of excel with Rate=0.08,Nper=2 - x fc =PV(H33,H25,-H31,-H32,1) E н TJ K 25 25 Years to retirement Length of retirement (in years) Amount already invested Annual interest rate $75,000 0.080 At retirement: Annual Retirement income needed Inheritance Required Return on investment Fund required at the time of retirement $ 150,000 $1,000,000 0.0800 $1,875,332 (Using PV Function of ex H19 x fc =PMT(H8, H5,-H18) H I J K L ME 4 5 Years to retirement 6 Length of retirement (in years) 7 Amount already invested 8 Annual interest rate 25 (65-40) 25 (90-65) $75,000 0.080 9 10 11 At retirement: 12 Annual Retirement income needed 13 Return on investment 14 Fund required at the time of retirement 15 Total needed at retirement 16 Funds available at retirement 17 Additional Funds needed 18 THERE IS SHORTFALL OF 19 Required additional annual contribution $150,000 0.0800 $1,729,314 (Using PV Function of excel with Rate=0.08, Nper=2 $1,729,314 $513,636 (Using FV Function of excel with Ra $1,215,678 |(1729314-513636) $1,215,678 $16,629 (Using PMT Function of excel with Rate=0.08, H58 : * f ID E =PMT(H46,H43,-457) F Length of retirement (in years) Amount already invested Annual interest rate 25 $75,000 0.050 At retirement: Annual Retirement income needed Inheritance Required 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 $150,000 $1,000,000 0.0500 $2,515,099 $2,515,099 $253,977 $2,261,122 $2,261,122 $47,376 58 ro