Answer Exercise 2 by both Spreadsheet and hand calculations (using appropriate formulae)

CASE STUDY COMPARING SOCIAL SECURITY BENEFITS Background When Sheryl graduated from Noetheastern University in 2000 and went to work for BAE Systems, she did not pay much attention to the monthly payroll deduction for social spousal benefits and start his own. In the meantime, hls ben- efits will have increased by 24%. Of course, this strategy could be swltched with Brad taking hls benefits and Sheryl recelving spousal benefits untl age 70 security. It was a necessary evil that may be helpful in re- tirement years. However, this was so far in the future that she fully expected this government retirement benefit system to All these opions led them to define four alternative plans. A: Each takes early benefits at age 62 with a 30% reduc tion to $1400 per month and recelves $2000 per month to $2480 per month be broke and gone by the time she could reap any benefits B Each takes ful benefits at full retdrement age of 67 from ber years of contributions This year, Sheryl and Brad, another engineer at BAE C: Each delays benefits until age 70 with a 24% Increase ot married. Recently, they both received notices from the Social Security Administration of their potential retirement amounts, were they to retire and start social security bene fits at preset ages. Since both of them hope to retire a few years early, they decided to pay closer attention to the pre- dicted amount of retirement benefits and to do some analy sis on the numbers D: One person takes full benefits of $2000 per month at age 67, and the other person recetves spousal benefits ($1000 per month at age 67) and switches to delayed benefits of $2480 at age 70 They realize, of course, that the numbers will change over time, based on thelr respective salaries and number of years of contribution to the soctal security system by them and by Information thelr employers They found that their projected benefits are substantially the same, which makes sense since their salbries are very close to each other. Although the numbers were slighty different in Case Study Exercises their two mailings, the similar messages to Brad anShery Brad and Sheryl are the same age. Brad determined that most can be summarized as follows If you stop working and start receiving benefits At age 62, your payment would At you full retirement age (67 of their investments make an average of 6% per year, with this as the interest rate, the analysis for the four alternatives is possible. Sheryl and Brad plan to answer the following ques tlons, but don't lave time this week. Can you please help them? (Do the analysis for one person at a tinme, not the cou- be about $1400 per month ple, and stop at the age of 85.) years), your payment would be about $2000 per month . How much in total (without the time value of money con- sldered) will each plan A throagh D pay through age 85? What Is the future worth at 6% per year of each plan at At age 70, your payment would be about $2480 per month 2, age 85 These numbers represent a reduction of 30% for early re- tirement (age 62) and an increase of 24% for delayed retire- 3. Plot the future worth values for all four plans on one spreadsheet graph. ment (age 70) Economically, what is the best comblnation of plans for Brad and Sheryl, assuming they both live to be 85 years old? Develop at least one additional question that you think Sheryl and Brad may have. Answer the questton 4. This couple also learned that t is possible for a spouse to take spousal benefits at the tdme that one of them is at ful retirement age. In other words, If Sheryl starts her $2000 ben- efit at age 67, Brad can receive a benefit equal to 50% of hers Then, when Brad reaches 70 years of age, he can discontinue