TABLE ON EXCEL AND GRAPH ON EXCEL (PLEASE DO NO GIVE WHATS ON CHEGG SOLUTIONS) B
ID: 3594868 • Letter: T
Question
TABLE ON EXCEL AND GRAPH ON EXCEL (PLEASE DO NO GIVE WHATS ON CHEGG SOLUTIONS)
Background
When Sheryl graduated from Northeastern University in 2000 and went to work for BAE Systems, she did not pay much attention to the monthly payroll deduction for social security. It was a “necessary evil” that may be helpful in retirement years. However, this was so far in the future that she fully expected this government retirement bene t system to be broke and gone by the time she could reap any bene ts from her years of contributions. T his year, Sheryl and Brad, another engineer at BAE, got 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 ts at preset ages. Since both of them hope to retire a few years early, they decided to pay closer attention to the predicted amount of retirement bene ts and to do some analysis on the numbers.
Information
They found that their projected bene ts are substantially the same, which makes sense since their salaries are very close to each other. Although the numbers were slightly different in their two mailings, the similar messages to Brad and Sheryl can be summarized as follows: If you stop working and start receiving bene ts . . .
At age 62, your payment would be about $1400 per month
At you full retirement age (67 years), your payment would be about $2000 per month
At age 70, your payment would be about $2480 per month These numbers represent a reduction of 30% for early retirement (age 62) and an increase of 24% for delayed retirement (age 70).
This couple also learned that it is possible for a spouse to take spousal bene ts at the time that one of them is at full retirement age. In other words, if Sheryl starts her $2000 bene t at age 67, Brad can receive a bene t equal to 50% of hers. Then, when Brad reaches 70 years of age, he can discontinue
spousal bene ts and start his own. In the meantime, his bene ts will have increased by 24%. Of course, this strategy could be switched with Brad taking his bene ts and Sheryl receiving spousal bene ts until age 70. A ll these options led them to de ne four alternative plans.
A: Each takes early bene ts at age 62 with a 30% reduction to $1400 per month.
B: Each takes full bene ts at full retirement age of 67 and receives $2000 per month.
C: Each delays bene ts until age 70 with a 24% increase to $2480 per month.
D: One person takes full bene ts of $2000 per month at age 67, and the other person receives spousal bene ts ($1000 per month at age 67) and switches to delayed bene ts of $2480 at age 70.
They realize, of course, that the numbers will change over time, based on their respective salaries and number of years of contribution to the social security system by them and by their employers.
Case Study Exercises Brad and Sheryl are the same age. Brad determined that most 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 questions, but don’t have time this week. Can you please help them? (Do the analysis for one person at a time, not the couple, and stop at the age of 85.)
QUESTION:
What is the future worth at 6% per year of each plan at age 85? .
Plot the future worth values for all four plans on one spreadsheet graph.
Explanation / Answer
Plan A
Plan B
Plan C
Plan D
Sheryl
386,400
(1400*12*23)
432,000
(2000*12*18)
446,400
(2480*12*15)
432,000
(2000*12*18)
Bred
386,400
(1400*12*23)
432,000
(2000*12*18)
446,400
(2480*12*15)
482,400
(1000*12*3) + (2480*12*15)
Total
772,800
864,000
892,800
914,400
Plan A
FVIAF @6%
Future value
Sheryl
1,400
592.25
829,150
Bred
1,400
592.25
829,150
Total
2,800
1,658,300
Plan B
FVIAF @6%
Future value
Sheryl
2,000
387.35
774,700
Bred
2,000
387.35
774,700
Total
4,000
1,549,400
Plan C
FVIAF @6%
Future value
Sheryl
2,480
290.82
721,234
Bred
2,480
290.82
721,234
Total
4,960
1,442,467
Plan D
FVIAF @6%
Future value
Sheryl
2,000
387.35
774,700
Bred (Up to 70 years)
1,000
39.34
39,340
Bred (After70 years)
2,480
290.82
721,234
Total
3,000
814,040
Plan A is economically better for both, because it gives maximum benefit.
Plan A
Plan B
Plan C
Plan D
Sheryl
386,400
(1400*12*23)
432,000
(2000*12*18)
446,400
(2480*12*15)
432,000
(2000*12*18)
Bred
386,400
(1400*12*23)
432,000
(2000*12*18)
446,400
(2480*12*15)
482,400
(1000*12*3) + (2480*12*15)
Total
772,800
864,000
892,800
914,400
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.