Reference no: EM133056783

GIVEN INFORMATION:

Company X provides a noncontributory Defined Benefit (DB) retirement plan that uses a formula of:

(0.5% * # years in the plan * final year salary)

The plan provides a 3 year cliff vesting schedule.

Minimum service requirement to participate is 3 years.

All employees will retire in 2041.

Employee	Age	Salary	Years of service	Currently in DB plan
FB	35	340,000	5	yes
DD	27	280,000	4	yes
JS	32	110,000	4	yes
AS	30	50,000	5	yes
KD	28	75,000	3	yes
BD	25	65,000	4	yes
YG	27	60,000	3	yes
MV	32	40,000	5	yes
TB	33	35,000	3	yes
MM	24	100,000	2	no
JH	59	40,000	0	no
JB	62	42,000	1	no

QUESTION:

What is the Present Value (@ a 3% discount rate) of the retirement benefits from the DB plan, assuming all employees will receive the DB pension income for exactly 15 years?

Include your spreadsheet.