Question

Show your numerical answer(s) and the Excel function(s) and inputs you used to get the answer.  You may use up to 25 words (50 for #4) to supplement your numbers, tables and Excel functions.

1. State Retirement Funding (5 points – 1 page with table, functions and 25 words)

A state retirement plan has been frozen.  It is considered fully-funded, with $635,244,352.26 of assets on hand and makes payouts to 1,000 recipients.  It assumes it will earn 7.5% per year on these assets.  The most recent total payout was $50,000,000. Next year it will be $51,000,000, which includes a 2% COLA increase in benefits.  This payout amount is scheduled to increase by 2% per year for inflation.  All interest earned and payments occur at the end of the year.  For this cohort of retirees the final payment will be made in exactly22 years from today.  The fund balance at that time will be zero.

The effective rate for annuities like this is RATE = .

The PV was calculated as =PV(RATE,22,-50000000,0,0)

1. Create an amortization table that shows the pension is fully-funded.
2. Suppose that instead of 7.5% the assets earn 5% per year.  By how much is the pension under-funded assuming the 2% COLA adjustment continues.
3. At a 5% growth rate what total annual payments can the original asset balance support for 22 years with no inflation adjustment?  I.e., the same amount each year.
4. Given the initial balance of $635,244,352.26 and assuming a 2% COLA increase ever year, what initial payment can be made to beneficiaries?

Answer 1

i). Amortization table:

Formula	F1n = F2n-1	Pn-1*(1+2%)	F1-P	7.5%*F1	A+I
Year (n)	Fund value at the beginning (F1)	Payment (P)	Amount remaining (A)	Interest earned (I)	Fund value at the end of the year (F2)
0					635244352.26
1	635244352.26	51000000.00	584244352.26	47643326.42	631887678.68
2	631887678.68	52020000.00	579867678.68	47391575.90	627259254.58
3	627259254.58	53060400.00	574198854.58	47044444.09	621243298.67
4	621243298.67	54121608.00	567121690.67	46593247.40	613714938.07
5	613714938.07	55204040.16	558510897.91	46028620.36	604539518.27
6	604539518.27	56308120.96	548231397.31	45340463.87	593571861.18
7	593571861.18	57434283.38	536137577.79	44517889.59	580655467.38
8	580655467.38	58582969.05	522072498.33	43549160.05	565621658.39
9	565621658.39	59754628.43	505867029.96	42421624.38	548288654.33
10	548288654.33	60949721.00	487338933.33	41121649.08	528460582.41
11	528460582.41	62168715.42	466291866.99	39634543.68	505926410.67
12	505926410.67	63412089.73	442514320.94	37944480.80	480458801.74
13	480458801.74	64680331.52	415778470.22	36034410.13	451812880.35
14	451812880.35	65973938.15	385838942.20	33885966.03	419724908.22
15	419724908.22	67293416.92	352431491.31	31479368.12	383910859.42
16	383910859.42	68639285.25	315271574.17	28793314.46	344064888.63
17	344064888.63	70012070.96	274052817.67	25804866.65	299857684.31
18	299857684.31	71412312.38	228445371.94	22489326.32	250934698.26
19	250934698.26	72840558.63	178094139.63	18820102.37	196914242.00
20	196914242.00	74297369.80	122616872.20	14768568.15	137385440.35
21	137385440.35	75783317.19	61602123.16	10303908.03	71906031.19
22	71906031.19	77298983.54	-5392952.35	5392952.34	-0.01

As can be seen, the pension is fully funded.

ii). Adding together the underfunded amounts from Year 17 to Year 22, the total underfunding is 432,136,960.13

Formula	F1n = F2n-1	Pn-1*(1+2%)	F1-P	5%*F1	A+I
Year (n)	Fund value at the beginning (F1)	Payment (P)	Amount remaining (A)	Interest earned (I)	Fund value at the end of the year (F2)
0					635244352.26
1	635244352.26	51000000.00	584244352.26	31762217.61	616006569.87
2	616006569.87	52020000.00	563986569.87	30800328.49	594786898.37
3	594786898.37	53060400.00	541726498.37	29739344.92	571465843.28
4	571465843.28	54121608.00	517344235.28	28573292.16	545917527.45
5	545917527.45	55204040.16	490713487.29	27295876.37	518009363.66
6	518009363.66	56308120.96	461701242.70	25900468.18	487601710.88
7	487601710.88	57434283.38	430167427.50	24380085.54	454547513.04
8	454547513.04	58582969.05	395964543.99	22727375.65	418691919.65
9	418691919.65	59754628.43	358937291.21	20934595.98	379871887.20
10	379871887.20	60949721.00	318922166.20	18993594.36	337915760.56
11	337915760.56	62168715.42	275747045.14	16895788.03	292642833.16
12	292642833.16	63412089.73	229230743.44	14632141.66	243862885.09
13	243862885.09	64680331.52	179182553.57	12193144.25	191375697.83
14	191375697.83	65973938.15	125401759.67	9568784.89	134970544.56
15	134970544.56	67293416.92	67677127.65	6748527.23	74425654.88
16	74425654.88	68639285.25	5786369.62	3721282.74	9507652.37
17	9507652.37	70012070.96	-60504418.59
18	0.00	71412312.38	-71412312.38
19	0.00	72840558.63	-72840558.63
20	0.00	74297369.80	-74297369.80
21	0.00	75783317.19	-75783317.19
22	0.00	77298983.54	-77298983.54
Underfunding			-432136960.13

iii). PV = 635,244,352.26; FV = 0; rate = 5%; N = 22, solve for PMT.

Annual payment = 48,259,836.50

iv). Total initial payment is already given as 51 million. Payment per recipient will be 51,000.

Note: The solution assumes that the fund is fully funded at 635,244,352.26 after the last payment of 50 million has been made, since the question does not explicitly mention this.