Retirement Distribution Planning Shannon Wilson Bell is preparing for retirement He turned age 63 in June of 2017, and has a Social Security Statement that indicates his benefit, if he continues to work until full retirement age would be 2,500/month at age 66. He currently earns 7,25o/month, and he is saving 1,000/month for retirement in his employer's 401(k). He does not anticipate any salary increases, other than cost of living between now and his retirement. The balance in his employer's 401(k) at the beginning of 2017 was $425,000. It is invested 100% in Vanguard Total Stock Market ETF (VTI) He also has $25,000 cash (about 5 months spending) in a Roth account (Emergency Fund) earning 1.5%. He has a $150/day, 60-month Long-Term Care policy with an annual premium of 2,75o At age 65, he will purchase Medicare B, and high-deductible option on Medicare Supplement Plan F Shannon is planning to retire at the end of June 2019 (age 65), but is unsure whether to begin drawing Social Security at 65, 66, or 7o. ASSUME: Shannon has no debts. Shannon was born June 1954 - He will retire July 1, 2020. (You will need to consider half year income for 2020, and for the year he begins Social Security) Inflation will average 3%. o.ukA Lud? (d.ma Social Security COLA will also average 3%.Atreinhedl Shannon will invest any RMD not used for personal spending in VTI outside the 4010k). Tax rates and rules will not change over his life, other than inflation adjustments. Consider: a. What is a reasonable estimate of his Wage Replacement Ratio? b. What would his social security benefit be if he retires at age 65 and begins drawing at 65? c. What would his social security benefit be if he retires at age 65 and begins drawing at 66? d. What would his social security benefit be if he retires at age 65 and begins drawing at 70? e. What will his accumulation (401(k) balance) be at age 65 (midway through 2019)? f, what will his accumulation (401(k) balance) be at age 70 (end of 2024)? Note that this will differ, depending on when he begins drawing Social Security) Estimate his investment balances at the end of 2025, 2030, 2035, 2040, 2045, 2050, & 2055 under the three options. REQUIRED: Create a spreadsheet to analyze Shannon's options; the spreadsheet should: Provide a backup of the details for the computations the memo is based on. 1. 2. Have a clear identifying header with Who, What, and When 3. Use columns and rows appropriately to identify items 4. Be formatted to print on pages in a readable manner Write a Memo to Shannon explaining the consequences of choosing to defer Social Security claiming. The memo should: i. State the question that the client was asking, and provide a response. ii. Re-state the assumptions on which the analysis was based. ii. Provide an explanation of the analysis in terms that the client will understand iv. Provide all necessary information to answer the question without referring to the preadsheet State the conclusion again. Be concise, and not give lengthy details about the computations v. vi