Reference no: EM133068838

Please solve using excel and show excel formulas.

Carolyn just turned 52 years of age and works for the state of California. She started working there on her 23rd birthday, just after she graduated from college. This coming year will be her 30th year working for the state and she is considering retiring on her 53rd birthday as she will have reached 30 years of service. This entitles her to a defined benefit pension in which she receives 90% of her final year pay for the rest of her life, annually increased by the rate of inflation. For instance, if she made $50,000 in her final year working, she would receive $45,000 at the end of the first year of retirement (on her 54th birthday). Each year thereafter the payment will increase by the rate of inflation. Assume that she is expected to live to age 83, allowing for 30 years of retirement. Also assume that inflation is expected to be 3% each year during her retirement.

a. If Carolyn made $80,000 in her final year of working for the state, what is the present value of her pension plan if the appropriate opportunity cost of capital were 5.5% per year? Show all calculations.

b. The total pay used in the pension calculation is not limited to the base salary of the employee. It also includes any overtime pay that was earned in the final year, a practice known as "spiking". Carolyn's $80,000 is based on working 2000 hours (40 hours per week for 50 weeks) during the year at $40 per hour. If she works overtime, she is paid 1.5 of her normal hourly wage ($60 per hour) on any hours above 2000 hours (above 40 hours per week). If she instead worked 2200 hours in her final year, what would be the new present value of her pension plan? What is the true per hour cost of Carolyn's overtime?