Description
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
In this assignment, you produce a spreadsheet that models the problem. Then, in Assignment 2, you will use the spreadsheet to gather data and write a memorandum that explains your findings. In Assignment 3, you may be asked to prepare an oral presentation of your analysis.
A spreadsheet has been started and is available for you to use; it will save you time. If you want to use the spreadsheet skeleton, locate Case 10 in your data files and then select TeachersPensionFund.xlsx. Your worksheet should contain the following sections:
Constants Inputs Summary of Key Results Calculations Fund Balance Statement Fund Liability
A discussion of each section follows.
Constants Section
Your spreadsheet should include the constants shown in Figure 10-1. An explanation of the line items follows the figure.
FIGURE 10-1 Constants section
Retiree Years of Service—On average, teachers work for 25 years before retiring. Average Increase in Teacher Salary—Teacher salaries are expected to increase by an average of 1 percent each year for the next 30 years. Retiree Rate—On average, 4 percent of teachers are expected to retire each year in the next 30 years. Mortality Rate—On average, a pensioner receives payouts for 20 years. On average, 5 percent of pensioners are expected to die each year. Expected Average Final Salary—The average final salary for teachers retiring in 2016 was $82,000. The average final salary is expected to increase somewhat each year, as shown.Expected Administrative Expense—The pension fund has employees, rents office space, consults with experts in securities markets about investments, and has other expenses. The plan’s administrative cost is expected to be $25 million in 2017 and to increase each year, as shown.
Inputs Section
Your spreadsheet should include the inputs shown in Figure 10-2. Possible values are shown in the figure. Each of the inputs applies to each of the 30 years modeled. An explanation of the line items follows the figure.
FIGURE 10-2 Inputs section
Cost of Living Adjustment—By union contract, this adjustment is 3 percent. Ideally, plan administrators would like to negotiate this percentage lower. Long Term Rate of Return—A 7.5 percent return on investments is assumed. Plan administrators want to see the effects of changing this variable. Productivity Factor—The total number of teachers has been declining by 0.5 percent each year in recent years. State officials hope for greater productivity in the future. Employee Contribution Rate—Working teachers contribute 9.5 percent of their salary to the pension fund. Some state officials think this rate must increase in the future. Final Salary Give Back—State officials want a reduction in the final salary for pension purposes. The reduction would be called the “give-back.” State Contribution Factor—By contract, the state contributes 2.5 times what the teachers contribute. This factor may need to be increased to ensure there is enough money to pay pensions.
Summary of Key Results Section
Your worksheet should include the key results shown in Figure 10-3. An explanation of the line items follows the figure.
FIGURE 10-3 Summary of Key Results section
NPV of Unfunded Liability—The NPV of the pension fund’s unfunded obligation is computed elsewhere in the spreadsheet and can be echoed here. Ratio of Assets to Liability NPV—The ratio of the value of fund assets to fund liabilities is computed elsewhere in the spreadsheet and can be echoed here.
Calculations Section
The Calculations section is shown in Figure 10-4. Some 2016 values are provided. Values for 2017 through 2046 are calculated by formula. Use cell addresses when referring to constants in formulas unless otherwise directed. Use absolute addressing properly. An explanation of the line items follows the figure.IGURE 10-4 Calculations section
Average Teacher Salary—The average in a year is a function of the prior year’s value and the expected rate of increase in the year. The latter value is from the Constants section. Number of Active Teachers—This amount is a function of the prior year’s value and the expected “productivity factor.” The latter value is from the Inputs section.
Number of New Retirees—This amount is a function of the number of active teachers in the prior year (from the previous row) and the retiree rate for the year (from the Constants section). Number of Retirees—The number of retirees in a year is the number of retirees in the prior year plus the number of new retirees in the year, minus the number of retirees who die in the year. The number of retirees who die is a function of the number of retirees in the prior year and the year’s mortality rate. The latter value is from the Constants section.
Total Teacher Compensation—This amount is a function of the average teacher salary in the year and the number of active teachers. Both values are from the Calculations section. Employee Contribution to Fund—This value is a function of total teacher compensation (from the previous row) and the contribution rate (from the Inputs section).
State Contribution to Fund—This value is a function of the employee contribution (from the previous row) and the state contribution factor (from the Inputs section). Average Retiree Benefit—The average retiree payout in a year is a function of the expected final salary in the year (from the Constants section), the .022 payout rate (a factor you can hard- code), and the expected years of service (from the Constants section). This amount should be increased by the expected cost of living factor and then reduced by any give-back amount; both values are from the Inputs section.
Expected Benefits Payout—The total benefits to be paid in a year is a function of the average retiree benefit and the number of retirees in a year. Both values are from the Calculations section.
Fund Balance Statement Section
This section shows a calculation of the pension fund balance at the end of each year, as illustrated in Figure 10-5. The pension fund’s balance is increased by employee contributions, state contributions, and earnings on fund assets. The pension fund’s balance is decreased by benefits paid and administrative expenses. An explanation of the line items follows the figure.Beginning Balance—The balance at the beginning of a year equals the balance at the end of the prior year. Add: Employee Contribution—This amount has been calculated elsewhere in the spreadsheet and can be echoed here.
Add: State Contribution—This amount has been calculated elsewhere and can be echoed here. Add: Income on Investments—This amount equals the fund balance at the beginning of the year multiplied by the expected earnings rate. The latter value is from the Inputs section. Less: Benefits Payout—This amount has been calculated elsewhere and can be echoed here. Less: Administrative Expenses—This amount is taken from the Constants section and can be echoed here.
Ending Balance—This amount equals the beginning balance plus the employee contribution, the state contribution, and income on investments, minus the benefits paid and administrative expenses.
Fund Liability Section
This section shows a calculation of the NPV of the pension fund’s unfunded liability and the ratio of fund assets to this NPV, as illustrated in Figure 10-6. An explanation of the line items follows the figure.
FIGURE 10-6 Fund Liability section
Expected Benefits Payout—The fund’s payout in each year has been calculated elsewhere in the spreadsheet and can be echoed here. The series of values will be used in the NPV calculation. Net Present Value of Payouts—The NPV of a series of values is calculated using a discount rate applied to those values. Apply the NPV function to the series of expected benefit payouts using .075 as the discount rate. You can hard-code the discount rate.
NPV of Unfunded Liability—This value is the NPV of payouts minus the fund balance at the end of 2017. Ratio of Assets to Liability NPV—This value is the ratio of the fund balance at the end of 2017 to the NPV of payouts.
ASSIGNMENT 2: USING THE SPREADSHEET FOR DECISION SUPPORT
You will now complete the case by (1) using the spreadsheet model to gather data needed to answer the budget director’s questions about the plan, (2) documenting your findings in a memo, and (3) giving an oral presentation if your instructor requires it.
Assignment 2A: Using the Spreadsheet to Gather Data
You have built the spreadsheet to create “what-if” scenarios for the model’s input values. The inputs represent the logic of a question and the outputs provide information needed to answer the question. The budget director’s questions are discussed next.
Question 1 (Base Case)
The budget director asks, “What are the net present value of the unfunded liability and the ratio of assets to the net present value of the unfunded liability, given the current situation? This is the ‘base case.’ How bad are things right now?” The inputs for the base case are shown in Figure 10-7
Enter the inputs and then observe the outputs in the Summary of Key Results section. Next, manually record the results in a summary area. You could use a second worksheet for this purpose, as shown in Figure 10-8 (values shown are for illustration only).
FIGURE 10-8 Question 1 (base case) inputs and results recorded in summary area Question 2 (Worst Case)
The budget director says, “In the worst case, we cannot do anything about the cost of living adjustment, the stock market tanks, and we earn very little—say 3 percent. Productivity goes to zero and other factors remain the same. That is the ‘worst case.’ How bad would that be?” The inputs for the worst case are shown in Figure 10-9.
FIGURE 10-9 Question 2 input data Enter the inputs and then observe the outputs in the Summary of Key Results section. Next, manually
record the results in the summary area.
Question 3 (Aggressive Case)
The budget director says, “In my dreams, I take an aggressive line with the union and I win the battles. The cost of living adjustment is reduced to 1 percent. The productivity factor doubles to 1 percent. The employee contribution rate is increased to 10 percent. The salary give-back is $4,000, and the stock market comes back, so we earn 10 percent on our money. That is the ‘aggressive case.’ How good would things be? Surely the ratio gets to 80 percent then!” The inputs for the aggressive case are shown in Figure 10-10.
FIGURE 10-10 Question 3 input data Enter the inputs and then observe the outputs in the Summary of Key Results section. Next, manually
record the results in the summary area.
Question 4 (Rescue Case)
The budget director says, “I know the governor is going to ask what the state would have to do to bail out the current system. So, assume the conditions of the base case, except for the state contribution factor.” Run a “what-if” scenario with that factor until you reach a ratio of 80 percent. How big a factor is needed? Call this question the “rescue case.” How much extra money would the state have to contribute versus the base case contribution by the state? The inputs for the rescue case are shown in Figure 10-11.
FIGURE 10-11 Question 4 input data
Enter the inputs and then observe the outputs in the Summary of Key Results section. The extra dollar amount that the state would contribute can be calculated by comparing state contribution amounts in the Calculations section in the two scenarios. Next, manually record the results in the summary area.
When you finish gathering data for the four questions, print the model’s worksheet with any set of inputs. Print the summary sheet data as well, and then save the spreadsheet for the final time.
Assignment 2B: Documenting your Findings and Recommendation in a Memo
Document your findings in a memo that answers the budget director’s four questions. The memo should also state your more general assessment of the fund’s financial position: How bad or good is the situation? Use the following guidelines to prepare your memo in Microsoft Word:
Your memo should have proper and standard headings, such as Date, To, From, and Subject. You can address the memo to the administrators of the state pension fund. Set up your memo as described in Tutorial E. Briefly outline the situation. However, you need not provide much background—you can assume that readers are familiar with the situation.Answer the four questions in the body of the memo. Include tables and charts to support your claims, as your instructor specifies. Tutorial E explains how to create a table in Microsoft Word. Tutorial F explains how to create charts in Excel.
ASSIGNMENT 3: GIVING AN ORAL PRESENTATION
Assume that the budget director asks you to be ready to present your analysis and results in an oral presentation to some key legislators. “These guys are always looking for the silver bullet—you know, trying to fix the problem by changing only one thing. So, they want to see sensitivity data and they want it in chart format,” she tells you. “For example, someone will want to know how much the asset-to-liability ratio would change if there was a change in the market rate of return. So get those kinds of charts ready.”
Prepare to talk to the group for 10 minutes or less. Tutorial F explains how to prepare and give an oral presentation.
Your instructor will tell you what sensitivity analyses to prepare or may tell you to choose these analyses yourself. The example chart to which the budget director referred should look like the one shown in Figure 10-12.
FIGURE 10-12 Chart for presentation
DELIVERABLES
Assemble the following deliverables for your instructor:
A printout of your memo Spreadsheet printouts A flash drive or CD that contains your Word file and Excel file
Staple the printouts together with the memo on top. If there is more than one .xlsx file on your flash drive or CD, write your instructor a note that identifies the correct file.
Unformatted Attachment Preview
Purchase answer to see full attachment
Explanation & Answer
Please view explanation and answer below.
TEACHERS PENSION FUND
CONSTANTS
Retiree Years of Service
Average Increase in Teacher Salary
Retiree Rate
Mortality Rate
Expected Average Final Salary
Expected Administrative Expense
2016
2017
25
0.01
0.04
0.05
82,000 $
$
NA
25
0.01
0.04
0.05
82,820
$25,000,000
INPUTS
Cost of Living Adjustment
Long Term Rate of Return
Productivity Factor
Employee Contribution Rate
Final Salary Give Back
State Contribution Factor
0.03
0.075
0.005
0.095
2.5
$
SUMMARY OF KEY RESULTS
NPV of Unfunded Liability
Ratio of Assets to Liability NPV
($42,488,028,485)
1380%
CALCULATIONS
Average Teacher Salary
Number of Active Teachers
Number of New Retirees
Number of Retirees
Total Teacher Compensation
Employee Contribution to Fund
State Contribution to Fund
Average Retiree Benefit
Expected Benefits Payout
2016
$
NA
101,500
NA
NA
NA
NA
NA
FUND BALANCE STATEMENT
Beginning Balance
Add: Employee Contribution
Add: State Contribution
Add: Income on Investments
Less: Benefits Payout
Less: Administrative Expenses
Ending Balance
FUND LIABILITY
Expected Benefits Payout
Net Present Value of Payouts
2017
68,500 $
133,000
$
2016
NA
NA
NA
NA
NA
NA
40,000,000,000
2016
NA
NA
$
$
$
$
$
$
$
$
$
$
$
$
69,185
133,665
5,320
101,745
9,247,613,025
878,523,237
2,196,308,093
45,551
242,331,320
2017
40,000,000,000
878,523,237
2,196,308,093
3,000,000,000
(242,331,320)
($25,000,000)
45,807,500,011
2017
242,331,320
$3,319,471,525
NPV of Unfunded Liability
Ratio of Assets to Liability NPV
NA
NA
($42,488,028,485)
1380%
$
$
$
$
$
$
$
2018
2019
2020
2021
25
0.01
0.04
0.05
83,648 $
$25,250,000
25
0.01
0.04
0.05
84,485 $
$25,502,500
25
0.01
0.04
0.05
85,330 $
$25,757,525
25
0.01
0.04
0.05
86,183
$26,015,100
2018
2019
2020
2021
69,877
134,333
5,347
102,004
9,386,789,601
891,745,012
2,229,362,530
46,007
245,978,406
$
$
$
$
$
$
70,576
135,005
5,373
102,277
9,528,060,785
905,165,775
2,262,914,436
46,467
249,680,381
$
$
$
$
$
$
71,281
135,680
5,400
102,564
9,671,458,099
918,788,519
2,296,971,299
46,931
253,438,071
$
$
$
$
$
$
71,994
136,358
5,427
102,863
9,817,013,544
932,616,287
2,331,540,717
47,401
257,252,314
$
2018
45,807,500,011 $
891,745,012 $
2,229,362,530 $
3,435,562,501 $
(245,978,406) $
($25,250,000)
52,092,941,648 $
2019
52,092,941,648 $
905,165,775 $
2,262,914,436 $
3,906,970,624 $
(249,680,381) $
($25,502,500)
58,892,809,601 $
2020
58,892,809,601 $
918,788,519 $
2,296,971,299 $
4,416,960,720 $
(253,438,071) $
($25,757,525)
66,246,334,543 $
2021
66,246,334,543
932,616,287
2,331,540,717
4,968,475,091
(257,252,314)
($26,015,100)
74,195,699,222
$
2018
245,978,406 $
2019
249,680,381 $
2020
253,438,071 $
2021
257,252,314
$
$
$
$
$
$
$
$
$
$
$
$
2022
2023
2024
2025
25
0.01
0.04
0.05
87,045 $
$26,275,251
25
0.01
0.04
0.05
87,915 $
$26,538,004
25
0.01
0.04
0.05
88,794 $
$26,803,384
25
0.01
0.04
0.05
89,682
$27,071,418
2022
2023
2024
2025
72,714
137,040
5,454
103,174
9,964,759,598
946,652,162
2,366,630,404
47,875
261,123,961
$
$
$
$
$
$
73,441
137,725
5,482
103,497
10,114,729,229
960,899,277
2,402,248,192
48,353
265,053,877
$
$
$
$
$
$
74,176
138,414
5,509
103,831
10,266,955,904
975,360,811
2,438,402,027
48,837
269,042,938
$
$
$
$
$
$
74,917
139,106
5,537
104,176
10,421,473,591
990,039,991
2,475,099,978
49,325
273,092,034
$
2022
74,195,699,222 $
946,652,162 $
2,366,630,404 $
5,564,677,442 $
(261,123,961) $
($26,275,251)
82,786,260,017 $
2023
82,786,260,017 $
960,899,277 $
2,402,248,192 $
6,208,969,501 $
(265,053,877) $
($26,538,004)
92,066,785,107 $
2024
92,066,785,107 $
975,360,811 $
2,438,402,027 $
6,905,008,883 $
(269,042,938) $
($26,803,384)
102,089,710,506 $
2025
102,089,710,506
990,039,991
2,475,099,978
7,656,728,288
(273,092,034)
($27,071,418)
112,911,415,311
$
2022
261,123,961 $
2023
265,053,877 $
2024
269,042,938 $
2025
273,092,034
$
$
$
$
$
$
$
$
$
$
$
$
2026
2027
2028
2029
25
0.01
0.04
0.05
90,579 $
$27,342,132
25
0.01
0.04
0.05
91,485 $
$27,615,553
25
0.01
0.04
0.05
92,400 $
$27,891,709
25
0.01
0.04
0.05
93,324
$28,170,626
2026
2027
2028
2029
75,667
139,802
5,564
104,532
10,578,316,768
1,004,940,093
2,512,350,232
49,818
277,202,069
$
$
$
$
$
$
76,423
140,501
5,592
104,897
10,737,520,436
1,020,064,441
2,550,161,103
50,317
281,373,960
$
$
$
$
$
$
77,188
141,203
5,620
105,272
10,899,120,118
1,035,416,411
2,588,541,028
50,820
285,608,638
$
$
$
$
$
$
77,959
141,909
5,648
105,657
11,063,151,876
1,050,999,428
2,627,498,571
51,328
289,907,048
$
2026
112,911,415,311 $
1,004,940,093 $
2,512,350,232 $
8,468,356,148 $
(277,202,069) $
($27,342,132)
124,592,517,584 $
2027
124,592,517,584 $
1,020,064,441 $
2,550,161,103 $
9,344,438,819 $
(281,373,960) $
($27,615,553)
137,198,192,434 $
2028
137,198,192,434 $
1,035,416,411 $
2,588,541,028 $
10,289,864,433 $
(285,608,638) $
($27,891,709)
150,798,513,959 $
2029
150,798,513,959
1,050,999,428
2,627,498,571
11,309,888,547
(289,907,048)
($28,170,626)
165,468,822,831
$
2026
277,202,069 $
2027
281,373,960 $
2028
285,608,638 $
2029
289,907,048
$
$
$
$
$
$
$
$
$
$
$
$
2030
2031
2032
2033
25
0.01
0.04
0.05
94,257 $
$28,452,332
25
0.01
0.04
0.05
95,199 $
$28,736,855
25
0.01
0.04
0.05
96,151 $
$29,024,224
25
0.01
0.04
0.05
97,113
$29,314,466
2030
2031
2032
2033
78,739
142,619
5,676
106,050
11,229,652,312
1,066,816,970
2,667,042,424
51,841
294,270,150
$
$
$
$
$
$
79,526
143,332
5,705
106,452
11,398,658,579
1,082,872,565
2,707,181,413
52,360
298,698,915
$
$
$
$
$
$
80,322
144,048
5,733
106,863
11,570,208,391
1,099,169,797
2,747,924,493
52,883
303,194,334
$
$
$
$
$
$
81,125
144,769
5,762
107,282
11,744,340,027
1,115,712,303
2,789,280,756
53,412
307,757,409
$
2030
165,468,822,831 $
1,066,816,970 $
2,667,042,424 $
12,410,161,712 $
(294,270,150) $
($28,452,332)
181,290,121,455 $
2031
181,290,121,455 $
1,082,872,565 $
2,707,181,413 $
13,596,759,109 $
(298,698,915) $
($28,736,855)
198,349,498,771 $
2032
198,349,498,771 $
1,099,169,797 $
2,747,924,493 $
14,876,212,408 $
(303,194,334) $
($29,024,224)
216,740,586,911 $
2033
216,740,586,911
1,115,712,303
2,789,280,756
16,255,544,018
(307,757,409)
($29,314,466)
236,564,052,113
$
2030
294,270,150 $
2031
298,698,915 $
2032
303,194,334 $
2033
307,757,409
$
$
$
$
$
$
$
$
$
$
$
$
2034
2035
25
0.01
0.04
0.05
98,084 $
$29,607,611
25
0.01
0.04
0.05
99,065 $
$29,903,687
2034
2035
81,936
145,493
5,791
107,709
11,921,092,344
1,132,503,773
2,831,259,432
53,946
312,389,158
$
$
$
$
$
$
82,755
146,220
5,820
108,143
12,100,504,784
1,149,547,954
2,873,869,886
54,486
317,090,615
2036
2037
25
0.01
0.04
0.05
100,056 $
$30,202,724
2036
$
$
$
$
$
$
83,583
146,951
5,849
108,584
12,282,617,381
1,166,848,651
2,917,121,628
55,031
321,862,828
25
0.01
0.04
0.05
101,056
$30,504,751
2037
$
$
$
$
$
$
84,419
147,686
5,878
109,033
12,467,470,773
1,184,409,723
2,961,024,309
55,581
326,706,864
$
2034
236,564,052,113 $
1,132,503,773 $
2,831,259,432 $
17,742,303,909 $
(312,389,158) $
($29,607,611)
257,928,122,458 $
2035
257,928,122,458 $
1,149,547,954 $
2,873,869,886 $
19,344,609,184 $
(317,090,615) $
($29,903,687)
280,949,155,181 $
2036
280,949,155,181 $
1,166,848,651 $
2,917,121,628 $
21,071,186,639 $
(321,862,828) $
($30,202,724)
305,752,246,547 $
2037
305,752,246,547
1,184,409,723
2,961,024,309
22,931,418,491
(326,706,864)
($30,504,751)
332,471,887,455
$
2034
312,389,158 $
2035
317,090,615 $
2036
321,862,828 $
2037
326,706,864
$
$
$
$
$
2038
$
2039
25
0.01
0.04
0.05
102,067 $
$30,809,799
2038
$
$
$
$
$
$
85,263
148,424
5,907
109,489
12,655,106,208
1,202,235,090
3,005,587,724
56,137
331,623,802
2040
25
0.01
0.04
0.05
103,087 $
$31,117,896
2039
$
$
$
$
$
$
86,116
149,166
5,937
109,952
12,845,565,556
1,220,328,728
3,050,821,820
56,698
336,614,740
2041
25
0.01
0.04
0.05
104,118 $
$31,429,075
2040
$
$
$
$
$
$
86,977
149,912
5,967
110,421
13,038,891,318
1,238,694,675
3,096,736,688
57,265
341,680,792
25
0.01
0.04
0.05
105,159
$31,743,366
2041
$
$
$
$
$
$
87,847
150,662
5,996
110,896
13,235,126,632
1,257,337,030
3,143,342,575
57,838
346,823,088
$
2038
332,471,887,455 $
1,202,235,090 $
3,005,587,724 $
24,935,391,559 $
(331,623,802) $
($30,809,799)
361,252,668,228 $
2039
361,252,668,228 $
1,220,328,728 $
3,050,821,820 $
27,093,950,117 $
(336,614,740) $
($31,117,896)
392,250,036,256 $
2040
392,250,036,256 $
1,238,694,675 $
3,096,736,688 $
29,418,752,719 $
(341,680,792) $
($31,429,075)
425,631,110,470 $
2041
425,631,110,470
1,257,337,030
3,143,342,575
31,922,333,285
(346,823,088)
($31,743,366)
461,575,556,907
$
2038
331,623,802 $
2039
336,614,740 $
2040
341,680,792 $
2041
346,823,088
$
$
$
$
$
2042
$
2043
25
0.01
0.04
0.05
106,211 $
$32,060,800
2042
$
$
$
$
$
$
88,725
151,415
6,026
111,378
13,434,315,288
1,276,259,952
3,190,649,881
58,416
352,042,776
2044
25
0.01
0.04
0.05
107,273 $
$32,381,408
2043
$
$
$
$
$
$
89,612
152,172
6,057
111,866
13,636,501,733
1,295,467,665
3,238,669,162
59,000
357,341,019
2045
25
0.01
0.04
0.05
108,346 $
$32,705,222
2044
$
$
$
$
$
$
90,508
152,933
6,087
112,359
13,841,731,084
1,314,964,453
3,287,411,132
59,590
362,719,002
25
0.01
0.04
0.05
109,429
$33,032,274
2045
$
$
$
$
$
$
91,414
153,698
6,117
112,858
14,050,049,137
1,334,754,668
3,336,886,670
60,186
368,177,923
$
2042
461,575,556,907 $
1,276,259,952 $
3,190,649,881 $
34,618,166,768 $
(352,042,776) $
($32,060,800)
500,276,529,932 $
2043
500,276,529,932 $
1,295,467,665 $
3,238,669,162 $
37,520,739,745 $
(357,341,019) $
($32,381,408)
541,941,684,076 $
2044
541,941,684,076 $
1,314,964,453 $
3,287,411,132 $
40,645,626,306 $
(362,719,002) $
($32,705,222)
586,794,261,744 $
2045
586,794,261,744
1,334,754,668
3,336,886,670
44,009,569,631
(368,177,923)
($33,032,274)
635,074,262,516
$
2042
352,042,776 $
2043
357,341,019 $
2044
362,719,002 $
2045
368,177,923
$
$
$
$
$
2046
$
25
0.01
0.04
0.05
110,524
$33,362,597
2046
$
$
$
$
$
$
92,328
154,466
6,148
113,363
14,261,502,377
1,354,842,726
3,387,106,814
60,788
373,719,000
$
2046
635,074,262,516
1,354,842,726
3,387,106,814
47,630,569,689
(373,719,000)
($33,362,597)
687,039,700,147
$
2046
373,719,000
$
$
$
$
$
TEACHERS PENSION FUND
CONSTANTS
Retiree Years of Service
Average Increase in Teacher Salary
Retiree Rate
Mortality Rate
Expected Average Final Salary
Expected Administrative Expense
2016
2017
25
0.01
0.04
0.05
82,000 $
$
NA
25
0.01
0.04
0.05
82,820
$25,000,000
INPUTS
Cost of Living Adjustment
Long Term Rate of Return
Productivity Factor
Employee Contribution Rate
Final Salary Give Back
State Contribution Factor
0.03
0.075
0.005
0.095
2.5
$
SUMMARY OF KEY RESULTS
NPV of Unfunded Liability
Ratio of Assets to Liability NPV
($42,488,028,485)
1380%
CALCULATIONS
Average Teacher Salary
Number of Active Teachers
Number of New Retirees
Number of Retirees
Total Teacher Compensation
Employee Contribution to Fund
State Contribution to Fund
Average Retiree Benefit
Expected Benefits Payout
2016
$
NA
101,500
NA
NA
NA
NA
NA
FUND BALANCE STATEMENT
Beginning Balance
Add: Employee Contribution
Add: State Contribution
Add: Income on Investments
Less: Benefits Payout
Less: Administrative Expenses
Ending Balance
FUND LIABILITY
Expected Benefits Payout
Net Present Value of Payouts
2017
68,500 $
133,000
$
2016
NA
NA
NA
NA
NA
NA
40,000,000,000
2016
NA
NA
$
$
$
$
$
$
$
$
$
$
$
$
69,185
133,665
5,320
101,745
9,247,613,025
878,523,237
2,196,308,093
45,551
242,331,320
2017
40,000,000,000
878,523,237
2,196,308,093
3,000,000,000
(242,331,320)
($25,000,000)
45,807,500,011
2017
242,331,320
$3,319,471,525
NPV of Unfunded Liability
Ratio of Assets to Liability NPV
NA
NA
($42,488,028,485)
1380%
$
$
$
$
$
$
$
2018
2019
2020
2021
25
0.01
0.04
0.05
83,648 $
$25,250,000
25
0.01
0.04
0.05
84,485 $
$25,502,500
25
0.01
0.04
0.05
85,330 $
$25,757,525
25
0.01
0.04
0.05
86,183
$26,015,100
2018
2019
2020
2021
69,877
134,333
5,347
102,004
9,386,789,601
891,745,012
2,229,362,530
46,007
245,978,406
$
$
$
$
$
$
70,576
135,005
5,373
102,277
9,528,060,785
905,165,775
2,262,914,436
46,467
249,680,381
$
$
$
$
$
$
71,281
135,680
5,400
102,564
9,671,458,099
918,788,519
2,296,971,299
46,931
253,438,071
$
$
$
$
$
$
71,994
136,358
5,427
102,863
9,817,013,544
932,616,287
2,331,540,717
47,401
257,252,314
$
2018
45,807,500,011 $
891,745,012 $
2,229,362,530 $
3,435,562,501 $
(245,978,406) $
($25,250,000)
52,092,941,648 $
2019
52,092,941,648 $
905,165,775 $
2,262,914,436 $
3,906,970,624 $
(249,680,381) $
($25,502,500)
58,892,809,601 $
2020
58,892,809,601 $
918,788,519 $
2,296,971,299 $
4,416,960,720 $
(253,438,071) $
($25,757,525)
66,246,334,543 $
2021
66,246,334,543
932,616,287
2,331,540,717
4,968,475,091
(257,252,314)
($26,015,100)
74,195,699,222
$
2018
245,978,406 $
2019
249,680,381 $
2020
253,438,071 $
2021
257,252,314
$
$
$
$
$
$
$
$
$
$
$
$
2022
2023
2024
2025
25
0.01
0.04
0.05
87,045 $
$26,275,251
25
0.01
0.04
0.05
87,915 $
$26,538,004
25
0.01
0.04
0.05
88,794 $
$26,803,384
25
0.01
0.04
0.05
89,682
$27,071,418
2022
2023
2024
2025
72,714
137,040
5,454
103,174
9,964,759,598
946,652,162
2,366,630,404
47,875
261,123,961
$
$
$
$
$
$
73,441
137,725
5,482
103,497
10,114,729,229
960,899,277
2,402,248,192
48,353
265,053,877
$
$
$
$
$
$
74,176
138,414
5,509
103,831
10,266,955,904
975,360,811
2,438,402,027
48,837
269,042,938
$
$
$
$
$
$
74,917
139,106
5,537
104,176
10,421,473,591
990,039,991
2,475,099,978
49,325
273,092,034
$
2022
74,195,699,222 $
946,652,162 $
2,366,630,404 $
5,564,677,442 $
(261,123,961) $
($26,275,251)
82,786,260,017 $
2023
82,786,260,017 $
960,899,277 $
2,402,248,192 $
6,208,969,501 $
(265,053,877) $
($26,538,004)
92,066,785,107 $
2024
92,066,785,107 $
975,360,811 $
2,438,402,027 $
6,905,008,883 $
(269,042,938) $
($26,803,384)
102,089,710,506 $
2025
102,089,710,506
990,039,991
2,475,099,978
7,656,728,288
(273,092,034)
($27,071,418)
112,911,415,311
$
2022
261,123,961 $
2023
265,053,877 $
2024
269,042,938 $
2025
273,092,034
$
$
$
$
$
$
$
$
$
$
$
$
2026
2027
2028
2029
25
0.01
0.04
0.05
90,579 $
$27,342,132
25
0.01
0.04
0.05
91,485 $
$27,615,553
25
0.01
0.04
0.05
92,400 $
$27,891,709
25
0.01
0.04
0.05
93,324
$28,170,626
2026
2027
2028
2029
75,667
139,802
5,564
104,532
10,578,316,768
1,004,940,093
2,512,350,232
49,818
277,202,069
$
$
$
$
$
$
76,423
140,501
5,592
104,897
10,737,520,436
1,020,064,441
2,550,161,103
50,317
281,373,960
$
$
$
$
$
$
77,188
141,203
5,620
105,272
10,899,120,118
1,035,416,411
2,588,541,028
50,820
285,608,638
$
$
$
$
$
$
77,959
141,909
5,648
105,657
11,063,151,876
1,050,999,428
2,627,498,571
51,328
289,907,048
$
2026
112,911,415,311 $
1,004,940,093 $
2,512,350,232 $
8,468,356,148 $
(277,202,069) $
($27,342,132)
124,592,517,584 $
2027
124,592,517,584 $
1,020,064,441 $
2,550,161,103 $
9,344,438,819 $
(281,373,960) $
($27,615,553)
137,198,192,434 $
2028
137,198,192,434 $
1,035,416,411 $
2,588,541,028 $
10,289,864,433 $
(285,608,638) $
($27,891,709)
150,798,513,959 $
2029
150,798,513,959
1,050,999,428
2,627,498,571
11,309,888,547
(289,907,048)
($28,170,626)
165,468,822,831
$
2026
277,202,069 $
2027
281,373,960 $
2028
285,608,638 $
2029
289,907,048
$
$
$
$
$
$
$
$
$
$
$
$
2030
2031
2032
2033
25
0.01
0.04
0.05
94,257 $
$28,452,332
25
0.01
0.04
0.05
95,199 $
$28,736,855
25
0.01
0.04
0.05
96,151 $
$29,024,224
25
0.01
0.04
0.05
97,113
$29,314,466
2030
2031
2032
2033
78,739
142,619
5,676
106,050
11,229,652,312
1,066,816,970
2,667,042,424
51,841
294,270,150
$
$
$
$
$
$
79,526
143,332
5,705
106,452
11,398,658,579
1,082,872,565
2,707,181,413
52,360
298,698,915
$
$
$
$
$
$
80,322
144,048
5,733
106,863
11,570,208,391
1,099,169,797
2,747,924,493
52,883
303,194,334
$
$
$
$
$
$
81,125
144,769
5,762
107,282
11,744,340,027
1,115,712,303
2,789,280,756
53,412
307,757,409
$
2030
165,468,822,831 $
1,066,816,970 $
2,667,042,424 $
12,410,161,712 $
(294,270,150) $
($28,452,332)
181,290,121,455 $
2031
181,290,121,455 $
1,082,872,565 $
2,707,181,413 $
13,596,759,109 $
(298,698,915) $
($28,736,855)
198,349,498,771 $
2032
198,349,498,771 $
1,099,169,797 $
2,747,924,493 $
14,876,212,408 $
(303,194,334) $
($29,024,224)
216,740,586,911 $
2033
216,740,586,911
1,115,712,303
2,789,280,756
16,255,544,018
(307,757,409)
($29,314,466)
236,564,052,113
$
2030
294,270,150 $
2031
298,698,915 $
2032
303,194,334 $
2033
307,757,409
$
$
$
$
$
$
$
$
$
$
$
$
2034
2035
25
0.01
0.04
0.05
98,084 $
$29,607,611
25
0.01
0.04
0.05
99,065 $
$29,903,687
2034
2035
81,936
145,493
5,791
107,709
11,921,092,344
1,132,503,773
2,831,259,432
53,946
312,389,158
$
$
$
$
$
$
82,755
146,220
5,820
108,143
12,100,504,784
1,149,547,954
2,873,869,886
54,486
317,090,615
2036
2037
25
0.01
0.04
0.05
100,056 $
$30,202,724
2036
$
$
$
$
$
$
83,583
146,951
5,849
108,584
12,282,617,381
1,166,848,651
2,917,121,628
55,031
321,862,828
25
0.01
0.04
0.05
101,056
$30,504,751
2037
$
$
$
$
$
$
84,419
147,686
5,878
109,033
12,467,470,773
1,184,409,723
2,961,024,309
55,581
326,706,864
$
2034
236,564,052,113 $
1,132,503,773 $
2,831,259,432 $
17,742,303,909 $
(312,389,158) $
($29,607,611)
257,928,122,458 $
2035
257,928,122,458 $
1,149,547,954 $
2,873,869,886 $
19,344,609,184 $
(317,090,615) $
($29,903,687)
280,949,155,181 $
2036
280,949,155,181 $
1,166,848,651 $
2,917,121,628 $
21,071,186,639 $
(321,862,828) $
($30,202,724)
305,752,246,547 $
2037
305,752,246,547
1,184,409,723
2,961,024,309
22,931,418,491
(326,706,864)
($30,504,751)
332,471,887,455
$
2034
312,389,158 $
2035
317,090,615 $
2036
321,862,828 $
2037
326,706,864
$
$
$
$
$
2038
$
2039
25
0.01
0.04
0.05
102,067 $
$30,809,799
2038
$
$
$
$
$
$
85,263
148,424
5,907
109,489
12,655,106,208
1,202,235,090
3,005,587,724
56,137
331,623,802
2040
25
0.01
0.04
0.05
103,087 $
$31,117,896
2039
$
$
$
$
$
$
86,116
149,166
5,937
109,952
12,845,565,556
1,220,328,728
3,050,821,820
56,698
336,614,740
2041
25
0.01
0.04
0.05
104,118 $
$31,429,075
2040
$
$
$
$
$
$
86,977
149,912
5,967
110,421
13,038,891,318
1,238,694,675
3,096,736,688
57,265
341,680,792
25
0.01
0.04
0.05
105,159
$31,743,366
2041
$
$
$
$
$
$
87,847
150,662
5,996
110,896
13,235,126,632
1,257,337,030
3,143,342,575
57,838
346,823,088
$
2038
332,471,887,455 $
1,202,235,090 $
3,005,587,724 $
24,935,391,559 $
(331,623,802) $
($30,809,799)
361,252,668,228 $
2039
361,252,668,228 $
1,220,328,728 $
3,050,821,820 $
27,093,950,117 $
(336,614,740) $
($31,117,896)
392,250,036,256 $
2040
392,250,036,256 $
1,238,694,675 $
3,096,736,688 $
29,418,752,719 $
(341,680,792) $
($31,429,075)
425,631,110,470 $
2041
425,631,110,470
1,257,337,030
3,143,342,575
31,922,333,285
(346,823,088)
($31,743,366)
461,575,556,907
$
2038
331,623,802 $
2039
336,614,740 $
2040
341,680,792 $
2041
346,823,088
$
$
$
$
$
2042
$
2043
25
0.01
0.04
0.05
106,211 $
$32,060,800
2042
$
$
$
$
$
$
88,725
151,415
6,026
111,378
13,434,315,288
1,276,259,952
3,190,649,881
58,416
352,042,776
2044
25
0.01
0.04
0.05
107,273 $
$32,381,408
2043
$
$
$
$
$
$
89,612
152,172
6,057
111,866
13,636,501,733
1,295,467,665
3,238,669,162
59,000
357,341,019
2045
25
0.01
0.04
0.05
108,346 $
$32,705,222
2044
$
$
$
$
$
$
90,508
152,933
6,087
112,359
13,841,731,084
1,314,964,453
3,287,411,132
59,590
362,719,002
25
0.01
0.04
0.05
109,429
$33,032,274
2045
$
$
$
$
$
$
91,414
153,698
6,117
112,858
14,050,049,137
1,334,754,668
3,336,886,670
60,186
368,177,923
$
2042
461,575,556,907 $
1,276,259,952 $
3,190,649,881 $
34,618,166,768 $
(352,042,776) $
($32,060,800)
500,276,529,932 $
2043
500,276,529,932 $
1,295,467,665 $
3,238,669,162 $
37,520,739,745 $
(357,341,019) $
($32,381,408)
541,941,684,076 $
2044
541,941,684,076 $
1,314,964,453 $
3,287,411,132 $
40,645,626,306 $
(362,719,002) $
($32,705,222)
586,794,261,744 $
2045
586,794,261,744
1,334,754,668
3,336,886,670
44,009,569,631
(368,177,923)
($33,032,274)
635,074,262,516
$
2042
352,042,776 $
2043
357,341,019 $
2044
362,719,002 $
2045
368,177,923
$
$
$
$
$
2046
$
25
0.01
0.04
0.05
110,524
$33,362,597
2046
$
$
$
$
$
$
92,328
154,466
6,148
113,363
14,261,502,377
1,354,842,726
3,387,106,814
60,788
373,719,000
$
2046
635,074,262,516
1,354,842,726
3,387,106,814
47,630,569,689
(373,719,000)
($33,362,597)
687,039,700,147
$
2046
373,719,000
$
$
$
$
$
TEACHERS PENSION FUND
CONSTANTS
Retiree Years of Service
Average Increase in Teacher Salary
Retiree Rate
Mortality Rate
Expected Average Final Salary
Expected Administrative Expense
2016
2017
25
0.01
0.04
0.05
82,000 $
$
NA
25
0.01
0.04
0.05
82,820
$25,000,000
INPUTS
Cost of Living Adjustment
Long Term Rate of Return
Productivity Factor
Emplo...
Review
Review
