PLEASE ANSWER IN EXCEL WITH FORMULAS INCLUDED

Erin OReilly 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 individuals 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 years 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 individuals pension if the person has worked for less than 25 years or if the salary were to increase.

One of the first tasks given OReilly 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. OReilly was instructed to select two representative employees and estimate their annual pensions and the annual contributions necessary to fund the pensions.

OReilly 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. OReilly 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 OReilly expects Barbers 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, OReilly 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 OReilly believes she is able to perform the assignment, she has come to you for assistance to help answer the following questions.

1. If each individual retires at age 65, how much will his or her estimated pension be? (8 points) (hint: find the last year annual salaries for both employees. And decide the pension benefit formula for each employee. Apply the pension benefit formula to calculate the annual pension.) (Answer: For Berg: 33556.26; For Barber: $89433.01.)

2. 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? (8 points) (hint: this cost is incurred when the firm pays for the insurance contract. That is, when the two employees retire at 65. Please calculate the cost or amount required for each employee separately.) (Answer: For Berg: 270486.56; For Barber: 720891.61)

3. 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? (9 points) (hint: the firm needs to set aside equal amounts every year until both the employee retires at their 65 years old. Again, please separate the two employees.) (Answer: For Berg: 30314.08; For Barber: 19249.32)