You are the CFO of Termination, Inc. Your company has 40 employees, each earning a salary of $40,000/year. Employee salaries grow at 4% per year. Starting from next year, and every second year thereafter, 8 employees retire and no new employees are recruited. Your company has in place a pension plan that entitles retired workers to an annual pension which is equal to their annual salary at the moment of retirement. Life expectancy is 20 years after retirement, and the annual pension is paid at year-end. The return on investment is 10% per year. What is the total value of your pension liabilities as of now?

Hint: The total value of your pension liabilities will be the sum of the PV of pension liability of each retirement cohort. Please Foll out the following Excel worksheet, please show all the formula and method!

1 Workforce 2 Life expectancy 20 40,000 Annual initial salary 4 Salary yearly growth 5 Annual return 10% Retirees Pension Liability at Retirement PV of Pension Liabil 7 Year Sal 41,600 44,995 12 15 16 17 Total 40 Total Value of Pension Liabilities 20 21 23 25 26 1 Workforce 2 Life expectancy 20 40,000 Annual initial salary 4 Salary yearly growth 5 Annual return 10% Retirees Pension Liability at Retirement PV of Pension Liabil 7 Year Sal 41,600 44,995 12 15 16 17 Total 40 Total Value of Pension Liabilities 20 21 23 25 26