if possible show formulas in excel

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 C75 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 necessarv 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 rcent to borrow monev, 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 pe answer the following questions If each individual retires at age 65, how much will his or her estimated pension be? (hint: find the 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 a. ast year Barber: $89433.01.) 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? (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) c. If the firm can earn 8 percent on the money it must invest annually to fund the pension, how muc will the firm have to invest annually to have the funds necessary to purchase the annuities? (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)