A friend of yours is starting a new job January 2022. Your friend can choose between one of two retirement plans offered by the employer.

• A defined benefit plan that will pay your friend $35,000 a year, starting with retirement and for the rest of her life. (Assume payments are made once annually at the end of the year, a simplification.) • A defined contribution plan, in which the employer will match 40% of the employee’s contribution. Assume the contributions (employee and employer) are made at the end of each year, so the first contribution would be made at the end of 2022.

• Your friend is planning to work for 20 years, then retire (December 2041). Retirement benefits will begin December of 2042 (again, a simplification, don’t worry about what your friend will do during that year with no benefits and no income).

• Assume a reasonable discount rate and return on investments is 5%. Also assume that your friend will live 35 years after retirement.

Your friend asks you these questions:

1. How much will I need to contribute annually to ensure I have enough invested to withdraw $35,000 a year for the rest of my life?

2. Which plan should I choose? The defined benefit or the defined contribution plan? Why? Create a spreadsheet that answers question #1.

Design the spreadsheet so that you could change the 5% discount rate to see the effect on the contribution required.

Answer rating: 100% (QA)

1 Amount she needs to contribute annually 1237995 2 Decision She should choose defined benefit plan Reason Because the Value of defined benefit plan o... View the full answer