Reference no: EM131091300

Part I. The Social Security Statement

Charles Smith is a senior production engineer who has worked for 18 years since he graduated from college. Yesterday in the mail, he received a report from the U.S. Social Security Administration. In short, it stated that if he continues to earn at the same rate, social security will provide him with the following estimates monthly retirement benefits:

Normal retirement at age 66; full benefit of $1,800 per month starting at age 66.

Early retirement at age 62; benefit reduced by 25% starting at age 62.

Extended retirement at age 70; benefit increased by 30% starting at age 70.

Charles never thought much about social security; he usually thought of it as a monthly deduction from his paycheck that helped pay for his parent’s retirement Benefits from social security. But this time, he decided an analysis should be performed. Charles decided to neglect the effects of the following over time: income taxes, cost-of living increases, and inflation. Also, he assumed the retirement benefits are all received at the end of each year; that is, no compounding effect occurs during the year. Using an expected rate of return on investment of 8% per year and anticipated death just after his 85th birthday use a spreadsheet to do the following for Charles.

Questions:

1. Calculate the total future worth of each benefit scenario through the age of 85. Plot the annual accumulated future worth for each benefit scenario through the age of 85. (2pts)

The Social Security Report from in part I. also mentioned that if Charles dies this (2016) year, his spouse, Susan is eligible at full retirement age for a benefit of $2,000 per month for the remainder of her life. If Charles and his wife are both 40 years old today, determine the following about his wife’s survivor benefits, if she starts receiving them at age 66 and lives through her 85^th birthday.

2. Present worth now and future worth for his wife after her 85^th birthday.