Erin O'Reilly was recently employed by the human resources department of a moderate-sized engineering firm. Management is considering the adoption of a defined-benefit pension plan in which the firm will pay 75 percent of an individual's last annual salary if the employee has worked for the firm for 25 years.

The amount of the pension is to be reduced by 3 percent for every year less than 25, so that an individual who has been employed for 15 years will receive a pension of 45 percent of the last year's salary [75 percent - (10 x 3%)]. Pension payments will start at age 65, provided the individual has retired. There is no provision for early retirement. Continuing to work after age 65 may increase the individual's pension if the person has worked for less than 25 years or if the salary were to increase.

One of the first tasks given O'Reilly is to estimate the amount that the firm must set aside today to fund pensions. While management plans to hire actuaries to make the final determination, the managers believe the exercise may highlight some problems that they will want to be able to discuss with the actuaries. O'Reilly was instructed to select two representative employees and estimate their annual pensions and the annual contributions necessary to fund the pensions.

O'Reilly decided to select Arnold Berg and Vanessa Barber. Berg is 58 years old, has been with the firm for 27 years, and is earning $34,000. Barber is 47, has been with the firm for 3 years, and earns $42,000 annually.

O'Reilly believes that Berg will be with the firm until he retires; he is a competent worker whose salary will not increase by more than 4 percent annually, and it is anticipated he will retire at age 65. Barber is a more valuable employee, and O'Reilly expects Barber's salary to rise at least 7 percent annually in order to retain her until retirement at age 65.

To determine the amount that must be invested annually to fund each pension, O'Reilly needs (in addition to an estimate of the amount of the pension) an estimate of how long the pension will be distributed (i.e., life expectancy) and how much the invested funds will earn.

Since the firm must pay an interest rate of 8 percent to borrow money, she decides that the invested funds should be able to earn at least that amount. While O'Reilly believes she is able to perform the assignment, she has come to you for assistance to help answer the following questions.

a. If each individual retires at age 65, how much will his or her estimated pension be? (SHOW ALL YOUR WORK ON EXCEL)

b. Life expectancy for both employees is 15 years at age 65. If the firm buys an annuity from an insurance company to fund each pension and the insurance company asserts it is able to earn 9 percent on the funds invested in the annuity, what is the cost or the amount required to purchase the annuity contracts? (SHOW ALL YOUR WORK ON EXCEL)

c. If the firm can earn 8 percent on the money it must invest annually to fund the pension, how much will the firm have to invest annually to have the funds necessary to purchase the annuities? (SHOW ALL YOUR WORK ON EXCEL)