You are hired as a junior manager at a state-owned institution at the beginning of 2021 with a salary of $100,000. You must choose between two retirement plans in the first week of your employment. This choice cannot be reversed. The two alternatives are:

• the states defined benefit plan, which will provide annual retirement benefits determined by the following formula: 1.5% * years of service * salary at retirement.
• a defined contribution plan under which the institution will contribute each year an amount equal to 8% of your salary.

You assume that salaries will rise by 3% a year, interest rates and return of retirement assets will stay constant at 6%, you will retire after 40 years (end of 2060), and draw retirement pay for 20 years (between the end of 2060 and the end of 2080).

Prepare an Excel spreadsheet to answer the questions below (Suggested columns: Year (end), Years to end of 2060, Salary, DBP Payments, Value of DBP Payments at end of 2060, DCP Contribution, Value of DCP Contribution at end of 2060). Enter present value, discount formulas yourself. Show and use constant values (e.g., 3% and 6%) in the formulas. Do not use present value multipliers:

1) What will your annual retirement pay if you choose the states defined benefit plan? What is the present value of DBP at the expected retirement date (end of 2060)?

2) What will your annual retirement pay if you choose the defined contribution plan? What is the present value of DCP as of the expected retirement date (end of 2060)?

3) Based on the values of your retirement plans as of the expected retirement date, which plan would you choose? What other factors do you consider in making the choice?

4) How would the state record pension expense under DCP and DBP in year 2021?