Assume a employee has a salary of $100,000. The employee's salary is expected to grow at 3.25% per annum. The employee is expected work 22 years and then retire. The pension program provides the employee a defined benefit equal to 1.5% of his salary in year 22 for each year worked. The employee has a expected life expectancy of 33 years at retirement. The firm uses a 4% discount rate to determine pension obligations.

Show all work in Excel

The firm anticipates depositing $11,000 at the end of each year to fund their retirement obligation. Funds are expected to earn and do earn 6.5 per cent per annum.

1. What is the pension cost in years 2 and 3

2. What is the over(under) pension funding at the end of year 3.

3 Explain the logic for each of the three components of a firm's pension expense in any given year.