WILL GIVE THUMBS UP! please answer using excel

You have 2 sisters, Chrystia and Melanie. Your sisters are 40 years old. Your sisters wish to retire when they are 60 years old. Each of your sisters expect to live for 40 years after they retire. Each sister will receive a total of 40 annual payments from their pension.

Chrystia and Melanie belong to a defined benefit pension plan. Chrystias defined benefit pension plan will pay her 2% for each year she has worked at her company. Chrystia has worked for her current employer for 10 years. She expects to work for her current employer until she retires in 20 years. Her annual pension will be based on her average annual salary during her last 5 years of work. The calculation is the number of years worked * 2% * average annual salary during her last 5 years of work. She currently is being paid $70,000 per year. For simplicity you may assume that the current salary is being paid at the beginning of the year. She expects her salary to increase by 3% per year. There will be a total of 19 salary increases. During retirement she will receive an annual income from her pension plan. The annual pension payments will be made at the beginning of each year. She will receive a total of 40 pension payments. Her annual pension payments will increase by the rate of inflation. Chrystia expects inflation to be 3% per year during retirement.

Melanies defined benefit pension plan will pay her 2% for each year she has worked at her company. Melanie has worked for her current employer for 5 years. She expects to work for her current employer until she retires in 20 years. Her annual pension will be based on her average annual salary during her last 5 years of work. The calculation is the number of years worked * 2% * average annual salary during her last 5 years of work. She currently is being paid $60,000 per year. For simplicity you may assume that the current salary is being paid at the beginning of the year. She expects her salary to increase by 2% per year. There will be a total of 19 salary increases. During retirement she will receive an annual income from her pension plan. The annual payments will be made at the beginning of each year. She will receive a total of 40 pension payments. Melanies pension plan is not indexed, her annual payments will not change.

Chrystia and Melanies employers believe that after retirement the pension plans will earn an effective annual rate of return of 5%. Prior to retirement the pension plans will earn an effective annual rate of return of 6%.

Part A:

What was the amount of Chrystias final years annual salary?

What was the average salary during Chrystias final five years of working?

What is the amount of Chrystias first annual pension payment?

What is the amount of Chrystias final annual pension payment?

How much money will Chrystias employer need to have when Chrystia retires to make the anticipated annual pension payments to Chrystia?

Assuming the Chrystias employer decides to set up an annuity to fund Chrystias pension payments after she retires, how much must the employer contribute to the annuity every year? There will be a total of 20 annual contributions to the annuity, and the contributions will be made at the end of each year.

What was the amount of Melanies final years annual salary?

What was the average salary during Melanies final five years of working?

What is the amount of Melanies first annual pension payment?

What is the amount of Melanies final annual pension payment?

How much money will Melanies employer need to have when Melanie retires to make the anticipated annual pension payments to Melanie?

Assuming the Melanies employer decides to set up an annuity to fund Melanies pension payments after she retires, how much must the employer contribute to the annuity every year? There will be a total of 20 annual contributions to the annuity, and the contributions will be made at the end of each year.

On your excel sheet you should create an array (box) where you fill in the answers to the above questions.

	Chrystia	Melanie
Amount of final years salary?
Average salary during final five years of work?
Amount of first annual pension payment?
Amount of final annual pension payment?
Amount needed by employer to fund anticipated pension payments?
Amount of annual contribution (payment) the employer needs to make for the next 20 years to fund the pension?