Question

Problem 3: Using a Spreadsheet to Calculate Pension Benefit Payments

Your employer uses a final pay formula to determine retirement payments to its employees. You have 20 years of service at the company and are considering retirement sometime in the next 10 years. Your employer uses a final pay formula by which you receive an annual benefit payment of 4 percent of your employer uses a final pay formula by which you receive an annual benefit payment of 4 percent of your average salary over the last three years of service times the number of years employed. Calculate the annual benefit if you retire now, in 2 years, 5 years, 8 years, and 10 years using the estimated salary during the last three years of service listed below.

		Average	Years of	Benefit		=>	Annual
Retire		Salary	Service	Percent			Payment
now		$50,000	ENTER	VALUE		=	FORMULA
in 2 years		$51,005				=
in 5 years		$52,551				=
in 8 years		$54,143				=
in 10 years		$55,231				=

Answer 1

The only tricky part here is to calculate the last 3 year average salary. This is clear for 5 years and 8 years because for the last three years, we can consider the given average salary as the salary for 3 years. However, for 2 years and 10 years, we will ahve to calculate the average salary for last three years from the given information. The details of the calculation is copied in the screenshot below:

If this understanding of using last 3 year salary is incorrect then we will have to take the given average salary as given.

A В с Е F G Н 1 Comment Average Years of Annual 2 Benefit => 3 Retire Salary Service Percent Payment Avg for last 3 years considered as $40,000 $50,000 $50,000 4 20 0.8 now Avg for last 3 years calculated as ($50,000 2*$51005)/3 because we $44,589.60 need to consider average of last 3 years Avg for last 3 years considered as 5 in 2 years $51,005 22 0.88 in 5 years $52,551 $52,551 $52,551 6 1 Avg for last 3 years considered as 7 in 8 years $60,640 $54,143 $54,143 28 1.12 Avg for last 3 years calculated as ($54,143 2*$55,231)/3 because we $65,842.00 need to consider average of last 3 years 8 in 10 years $55,231 30 1.2 25

The excel with formula copied below:

		Average	Years of	Benefit		=>	Annual	Comment
Retire		Salary	Service	Percent			Payment
now		50000	20	=0.04*D4		=	=E4*C4	Avg for last 3 years considered as $50,000
in 2 years		51005	22	=0.04*D5		=	=(C5*2+C4)/3*E5	Avg for last 3 years calculated as ($50,000 + 2*$51005)/3 because we need to consider average of last 3 years
in 5 years		52551	25	=0.04*D6		=	=C6*E6	Avg for last 3 years considered as $52,551
in 8 years		54143	28	=0.04*D7		=	=C7*E7	Avg for last 3 years considered as $54,143
in 10 years		55231	30	=0.04*D8		=	=(C8*2+C7)/3*E8	Avg for last 3 years calculated as ($54,143 + 2*$55,231)/3 because we need to consider average of last 3 years