Question

a.Laura Smith is planning for her and her husband Luke’s retirement. Both Luke and Laura expect to retire in 35 years (when they turn 65). The life expectancy of men is 75 years and the life expectancy of women is 85 years (i.e., assume that they die the day before their 75^th or 85^th birthday). During retirement (while they are living), the couple wants to withdraw $10,000 at the beginning of each year from their savings account- $5,000 for each of them. Assume that the interest rate during their retirement is 9 percent compounded annually; the interest rate after Luke dies is 10% compounded semi-annually; and, the interest rate prior to retirement is 10 percent compounded annually. How much will they have to deposit in their joint savings account each month (beginning one month from now and ending on their retirement date)?

b.How would your answer change if Luke and Laura Smith expect to inherit $50,000, 5 years from now? Assume that, at that time, they will spend $10,000 for a two-week luxury all-inclusive cruise, spend $25,000 to buy top-of-the-line stainless steel appliances for their kitchen, and save the rest for their retirement. Assume equal monthly payments throughout the 35 years to retirement- i.e., the same monthly payments beginning one month from now and ending on their retirement date.

Answer 1

	Inputs:
		Years to retirement	35
		Months to retirement =35*12=	420
		Length of retirement (in years) for Luke	10	(75-65)
		Length of retirement (in years) for Laura Smith	20	(85-65)
		Annual interest rate for first 10 years of retirement	9%				1.00797414
		Semi annual interest for last 10 years of retirement	5%	(10/2)
		Annual interest for last 10 years of retirement=(1.05^2)-1	10.25%
		Annual interest rate prior to retirement	10%
		Monthly interest rate prior to retirement =r
		(1+r)^12=1+0.1=1.1, 1+r=1.1^(1/12)=1.007974
		Monthly interest rate prior to retirement =r	0.7974%
		Withdrawal for first 10 years after retirement(beginning of year)	$10,000
		Withdrawal for last 10 years after retirement(beginning of year)	$5,000
		Type for beginning of perid withdrawal	1
	Ouputs:
	A	Present Value of first 10 years withdrawal at the time of retirement	$69,952	(Using PV Function of excel with Rate=9%,Nper=10,Pmt=-10000,Type=1)
		Present Value of Last 10 years withdrawal at the time death of Luke	$33,511	(Using PV Function of excel with Rate=10.25%,Nper=10,Pmt=-5000, Type=1)
	B	Present Value of Last 10 years withdrawal at the time of retirement	$14,155	(33511/(1.09^10)
	C=A+B	Total amount needed at retirement	$84,108
	D	Required Savings Per MONTH for 35 Years	$24.75	(Using PMT Function of excel with Rate=0.7974%,Nper=420,Fv=-84108)
	b	Additional inputs
		Inheritance after 5 years	$50,000
		Amount spent from inheritance=$10000+$25000	$35,000
		Net Saving after 5 years	$15,000	(50000-35000)
		Number of years the savings earns 10% annual interest	30	(35-5)
	E	Future Value of savings of $15000 at the time of retirement	$261,741	(Using FV Function of excel with Rate=10%,Nper=30,Pv=-15000))
		Hence No monthly saving is required
											H21 H J K M N O ο L 1.00797414 I 9% 5% (10/2) 10% - X Fax =PV(H10,H7,-H16,,H18) EF Annual interest rate for first 10 years of retirement Semi annual interest for last 10 years of retirement Annual interest rate prior to retirement Monthly interest rate prior to retirement =r (1+r)^12=1+0.1=1.1, 1+r=1.1^(1/12)=1.007974 Monthly interest rate prior to retirement=r Withdrawal for first 10 years after retirement(beginning of year) Withdrawal for last 10 years after retirement(beginning of year) Type for beginning of perid withdrawal 0.7974% $10,000 $5,000 Ouputs: Present Value of first 10 years withdrawal at the time of retirement $69,952 | (Using PV Function of excel with Rate=9%, Nper=10, Pmt=-10000, Type=1 hid H22 - x fc =PV(H11,10,-H17,,H18) E F Inputs: H I J K L M N 351 420 10 (75-65) 20 (85-65) 9% 1.00797414 Years to retirement Months to retirement =35*12= Length of retirement in years) for Luke Length of retirement in years) for Laura Smith Annual interest rate for first 10 years of retirement Semi annual interest for last 10 years of retirement Annual interest for last 10 years of retirement=(1.05^2)-1 Annual interest rate prior to retirement 1 erest rate prior to retirement Er (1+r)^12=1+0.1=1.1, 1+r=1.1^(1/12)=1.007974 Monthly interest rate prior to retirement=r Withdrawal for first 10 years after retirement(beginning of year) | Withdrawal for last 10 years after retirement(beginning of year) | Type for beginning of perid withdrawal 5% (10/2) 10.25% 10% 0.7974% $10,000 $5,000 Ouputs: Present Value of first 10 years withdrawal at the time of retirement Present Value of Last 10 years withdrawal at the time death of Luke $69,952 (Using PV Function of excel with Rate=9%,Nper=10, Pmt=-10000, Typ $33,511 (Using PV Function of excel with Rate=10.25%,Nper=10, Pmt=-5000, T Clipboard S Font Alignment Number Styles 1 Cells Editing H25 - x fc =PMT(H15, H6,-H24) F J K L M N O H I 5% (10/2) 10.25% 10% Semi annual interest for last 10 years of retirement Annual interest for last 10 years of retirement=(1.05^2)-1 Annual interest rate prior to retirement Monthly interest rate prior to retirement =r (1+r)^12=1+0.1=1.1, 1+r=1.1^(1/12)=1.007974 Monthly interest rate prior to retirement Er Withdrawal for first 10 years after retirement(beginning of year) Withdrawal for last 10 years after retirement(beginning of year) | Type for beginning of perid withdrawal 0.7974% $10,000 $5,000 $69,952 (Using PV Function of excel with Rate=9%,Nper=10, Pmt=-10000, Type=1) 20 Ouputs: Present Value of first 10 years withdrawal at the time of 21 A retirement Present Value of Last 10 years withdrawal at the time death of Luke Present Value of Last 10 years withdrawal at the time of 23 B retirement 24 C=A+B Total amount needed at retirement 25D Required Savings Per MONTH for 35 Years $33,511 (Using PV Function of excel with Rate=10.25%,Nper=10, Pmt=-5000, Type=1) (33511/(1.09-10) $14,155 $84,108 $24.75 | (Using PMT Function of excel with Rate=0.7974%,Nper=420,Fv=-84108) H32 X fx =FV(H12, H31,,-H30) H J K L M N O 10% Annual interest rate prior to retirement Monthly interest rate prior to retirement =r (1+r)^12=1+0.1=1.1, 1+r=1.1^(1/12)=1.007974 Monthly interest rate prior to retirement =r Withdrawal for first 10 years after retirement(beginning of year) | Withdrawal for last 10 years after retirement beginning of year) | Type for beginning of perid withdrawal 0.7974% $10,000 $5,000 1 $69,952 (Using PV Function of excel with Rate=9%,Nper=10, Pmt=-10000, Type- Ouputs: Future Value of first 10 years withdrawal at the time of retirement Future Value of Last 10 years withdrawal at the time of retirement C=A+B Total amount needed at retirement Required Savings Per MONTH for 35 Years (Using PV Function of excel with Rate=5%, Nper=20, Pmt=-5000, Type=1 $65,427 $135,379 $39.83 (Using PMT Function of excel with Rate=0.7974%,Nper=420,Fv=-135379 b Additional inputs Inheritance after 5 years Amount spent from inheritance $10000+$25000 Net Saving after 5 years Number of years the savings earns 10% annual interest Future Value of savings of $15000 at the time of retirement $50,000 $35,000 $15,000 (50000-35000) 30 (35-5) $261,741 (Using FV Function of excel with Rate=10%,Nper=30,Pv=-15000)) |