Question

Marybeth and Anneal Yao are beginning to contemplate retirement. They are each forty-five years of age and have saved a total of $500,000 for retirement. Marybeth and Anneal realize that they have not saved sufficiently to be able to retire early, fully retire without some part-time employment, or replace 100 percent of current pre-retirement income. As such, they are willing to explore different approaches to reach retirement. Marybeth and Anneal have a combined annual income of $125,000. They believe their salaries will keep pace with inflation at 4 percent per year. They are also comfortable assuming that the effective annual rate of return on their retirement assets will be 9.0 percent before retirement and 6.5 percent after retirement. For now, Marybeth wants to keep the planning simple, projecting that they will both die in exactly forty years and that their retirement assets will be depleted with the exception of $100,000 to cover funeral and burial costs. Lastly, they do not want to continue saving after they retire (either partly or fully). As their financial planner, provide some assistance in calculating the amount of retirement assets needed on the first day of retirement, based on the two options listed below. Considering the information presented in the case, which outcome requires the lowest monthly (end-of-month) contribution if they also require that their retirement annuity grow by 4.0 percent per year to keep pace with inflation? (Ignore the effects of income taxes and Social Security on the answer.)

a.To retire at age fifty-five with an income replacement ratio of 60 percent.

b.To retire at age fifty-five with an income replacement ratio of 100 percent but work part-time for an additional ten years to offset half the projected annual need for those ten years. (In other words, Marybeth and Anneal will have a 50 percent replacement ratio for 10 years and a 100 percent replacement ratio thereafter.)

c.To retire at age sixty-five with an income replacement ratio of 100 percent.

Answer 1

A	B	C	D
2	Present Age	45
3	Present Income	125000
4	Present Fund	500000
5	Inflation	0.04
6	Rate of Return
7	pre Retirement	0.09
8	post Retirement	0.06
9	Death Age	85
10	Burial cost	100000
11
12	To retire at age fifty-five with an income replacement ratio of 60 percent	Calculations	Amount
13	Retirement Age	55	55
14	Income Replacement Ratio	0.6	0.6
15	Income @55 Years	=$C$3*(1+$C$5)^(C13-$C$2)	185030.535614793
16	Income Requied in 56th Year	=C15*(1+$C$5)*$C$14	115459.054223631
17	PV of Post retirement Fund required (55 to 85)	=(C16/($C$8-$C$5))*(1-((1+$C$5)/(1+$C$8))^($C$9-C13))+$C$10/(1+$C$8)^($C$9-C13)	2530329.03713725
18	Value of Present Fund @55th Year	=$C$4*(1+$C$7)^(C13-$C$2)	1183681.83729606
19	Further Fund requied to met PV of Post retirement Fund	=C17-C18	1346647.19984119
20	Monthly Contrbution Required	=C19/(((1+$C$7/12)^((C13-$C$2)*12)-1)/($C$7/12))	6958.90360203992
21
22	To retire at age sixty-five with an income replacement ratio of 100 percent	Calculations	Amount
23	Retirement Age	65	65
24	Income Replacement Ratio	1	1
25	Income @65 Years	=$C$3*(1+$C$5)^(C23-$C$2)	273890.392879178
26	Income Requied in 56th Year	=C25*(1+$C$5)*C24	284846.008594345
27	PV of Post retirement Fund required (65 to 85)	=(C26/($C$8-$C$5))*(1-((1+$C$5)/(1+$C$8))^($C$9-C23))+$C$10/(1+$C$8)^($C$9-C23)	4543104.88556225
28	Value of Present Fund @65th Year	=$C$4*(1+$C$7)^(C23-$C$2)	2802205.38388915
29	Further Fund requied to met PV of Post retirement Fund	=C27-C28	1740899.5016731
30	Monthly Contrbution Required	=C29/(((1+$C$7/12)^((C23-$C$2)*12)-1)/($C$7/12))	2606.57841927088
31
32	To retire at age fifty-five with an income replacement ratio of 100 percent but work part-time for an additional ten years to offset half the projected annual need for those ten years. (In other words, Marybeth and Anneal will have a 50 percent replacement ratio for 10 years and a 100 percent replacement ratio thereafter.)	Calculations	Amount
33	Part Time Joining Age	55	55
34	Part Time Retirement Age	65	65
35	Income Replacement Ratio	0.5	0.5
36	Income @55 Years	=$C$3*(1+$C$5)^(C33-$C$2)	185030.535614793
37	Income Requied in 56th Year	=C36*(1+$C$5)*C35	96215.8785196924
38	PV of Post retirement Fund required (55 to 65)	=(C37/($C$8-$C$5))*(1-((1+$C$5)/(1+$C$8))^($C$34-C33))	834380.840379598
39	Income Requied in 66th Year	=C36*(1+$C$5)*100%	192431.757039385
40	PV of Post retirement Fund required (65 to 85) @end of 65th Year	=(C39/($C$8-$C$5))*(1-((1+$C$5)/(1+$C$8))^($C$9-C34))+$C$10/(1+$C$8)^($C$9-C34)	3079274.93847642
41	PV of Post retirement Fund required (65 to 85) @end of 55th Year	=C40/(1+C8)^(C34-C33)	1719451.04233085
42	PV of Post retirement Fund required (55 to 85) @end of 55th Year	=C41+C38	2553831.88271045
43	Value of Present Fund @55th Year	=$C$4*(1+$C$7)^(C33-$C$2)	1183681.83729606
44	Further Fund requied to met PV of Post retirement Fund	=C42-C43	1370150.04541439
45	Monthly Contrbution Required	=C44/(((1+$C$7/12)^((C33-$C$2)*12)-1)/($C$7/12))	7080.3563750727

Please try to understand 3rd Part, it is very important and difficult.

Formula Used

Feel free to ask any Query in Comment Section

Please provide feedback.

Cheers