Question

Your Company is restructuring its pension plan. You are 45 years old and have a base salary of $50,000 per annum, which is expected to increase at 4% per annum. Your pension benefit is based on 60% of the average of your final three years of salary. Post retirement, your pension benefit is expected to grow 2% per annum. The Company has two proposals for you to consider.

The first proposal is to give you 55% of the present value of your pension benefit. You are told the calculation is based on you receiving benefits until you are 88 years old. The distribution would be tax free and you could immediately invest the funds into a 401k account. The second proposal would be to reduce your future annual benefit by 25% and eliminate the 2% annual percentage increase in your pension benefit. Please answer the following questions.

1. What are the final three years of your salary and what is the average?

2. Show your annual pension benefit for the period you expect to receive a pension.

3. What is the present value, at age 45, of the cash flow stream associated with your pension benefit? The Company’s proposal suggested using a 10% annual discount rate. Calculate the present value using a 10% discount rate and a 6% discount rate, then calculate the value of the distribution.

Present Value @ 10% __________________ x 55% = _____________________

Present Value @ 6% ___________________ x 55% = _____________________

4. Assume you receive the distribution, based on the 10% discount rate, and immediately invest the proceeds into an account expected to earn a 6.5% annual return, compounded quarterly and allow it to accumulate until you turn 65. What would be the accumulated value of the account? Write out the excel formula you would employ to perform the calculation and fill in the relevant inputs.

Rate =

Nper =

Pmt =

[pv] =

[fv] =

[type] =

Accumulated Value ________________________

5. Assume at age 65 you reallocate the portfolio, changing the annual return expectation from 6.5% to 4% per annum, which will compound monthly. From age 65 onward, you want to receive equal monthly payments and have a $100,000 account balance when you turn 88. Write out the excel formula you would utilize to calculate the monthly annuity payment.

Rate =

Nper =

Pmt =

[pv] =

[fv] =

[type] =

Monthly payment ________________________

Answer 1

		Current Age	45
	A	Number of years to retirement	20	(65-45)
	B	Current Salary	$50,000
	C	Salary growth rate per year=4%	0.04
	D=B*((1+C)^A)	Salary at retirement	$109,556
	E	Salary one year before retirement	$105,342	(109556/1.04)
	F	Salary 2 years before retirement	$101,291	(105342/1.04)
	G	Salary 3 years before retirement	$97,395	(101291/1.04)
	H=(E+F+G)/3	Three years average salary	$101,343
	I=0.6*H	Pension benefit at retirement	$60,806
		PRESENT VALUE @10% at retirement	$699,548
		PRESENT VALUE @6% at retirement	$971,241
		Present value @10% today	$103,983	(699548/(1.1^20)
		Present value @6% today	$302,838	(971242/(1.06^20)
		Present Value @10%	$103,983	X55%	=	$57,191
		Present Value @6%	$302,838	X55%	=	$166,561
	D
	Pv	Distribution received @10%	$57,191
	Rate	Interest rate per quarter=(6,5/4)%	1.625%
	Nper	Number of quarters to retirement	80	(20*4)
	Type	End of quarter	0
	FV	Accumulated value in the account	$207,669	(Using excelFv function with Rate=1.625%, Nper=80, Pv=-57191,Type=0)
	E
	Rate	Monthly interest rate=(4/12)%=	0.33333%
	Pv	Accumulated value in the account	$207,669
	Nper	Number of months	276	(23*12)
	Fv	Amount in account at the end	$100,000
	Type	Payments at beginning of months	1
	PMT	Monthly Payment	$927.53	(Using excelPMT function with Rate=0.33333%, Nper=276, Pv=-207669,FV=100000Type=1)