I got an email from the Public Plans Database that they had updated their data, and remembered I had updated the projection tool about this time last year.
So here it is!
Spreadsheet Update: with FY2025 Data
Data and Model Info:
Basic information: Public pension plan data are sourced from the Public Plans Database, which is compiled by the Center for Retirement Research at Boston College.
The August 2026 update featured:
The most current data for plans that have released financial reports by May 30, 2026, allowing for 75 percent completion of FY 2025 data.
To keep it simple, I’m focusing only on those plans with data with FY2024 and FY2025 data:
FY2025: 186 plans
FY2024: 61 plans
So there’s a total of 247 plans covered.
The model used for projections is extremely simple: a constant annually compounding investment return, and a constant growth rate for various cash flows:
Contributions
Benefits
Expenses (usually insignificant)
I project only to FY2040, as even a decade’s worth of projections with such simplistic dynamics is questionable.
I will demonstrate how the tool works using two plans so you can see the types of inputs and outputs. I will choose one that has FY2025 data, and one that stops with FY2024 data.
Example 1: New York City Teachers, from FY2025
Once again, I will be using one NYC plan and one Chicago plan to demonstrate the projections.
The input/output interface is on the tab named “Projection Results”.
Make sure, when you open the spreadsheet, you have the calculation mode in Excel set on “Automatic”, or that you recalculate the spreadsheet each time you change an input.
All the items in the yellow can be changed — the plan and the projection assumptions. All the remaining items are outputs.
Choose the pension plan from the dropdown list
The list is in alphabetical order.
Check out the plan’s historical stats.
This is on the upper-right-hand side of the sheet. I give the 5- and the 10-year results, which may inform your inputs for the next steps.
Choose inputs for the projections… and project!
These are the four parameters that go into the projection. Notice the yellow background — that means they’re input items.
I often start with a 0-projection — what if there are no changes to the dollar amount of benefits or contributions, and no investment gains/losses… what then?
Look at the asset projection
This is from the lower-left-hand side of the sheet.
The graph shows the assets — dark blue for “actual” market value from the historical financial reports, light blue for the projections.
Notice the numbers above… it checks if the assets run out before 2040. With this set of assumptions, it does. Note that second number, because here’s the next step:
Look at the cash flow projection
This is the lower-right-hand side of the sheet, and the most complicated of the displays. There are five bits, and I don’t distinguish, in colors, between actual and projected. You can tease that apart for yourself.
Cash flows out — to benefits and expenses (or investment losses) — are below the horizontal axis. Cash flows in — contributions, investment gains — are above the line.
Notice in 2037 and after the black shaded portions — those are the additional contributions needed to be made due to the assets running out and the pension plan becoming pay-as-it-goes.
Something odd is going on with those “non-benefit expenses” for NYC Teachers… I need to check that data. Maybe it’s another type of benefit (post-retirement health?) that wasn’t captured adequately.
Try different assumption sets!
This is the fun bit.
Hmmm, that didn’t seem to fix much.
But you can start to see dynamics — even with a steady investment income, if the benefits are growing steadily, one needs to have a higher contribution rate to have a sustainable situation.
Cash flow here:
I will need to return to the NYC Teachers pension, because it’s supposedly been at a 85% funded ratio, but had a very high percentage of assets flowing out… doesn’t sound like a highly-funded plan to me.
Example 2: Chicago Municipal, from FY2024
Let’s do the first few steps:
So the plan has been chosen, we see the plan stats, and we’ll use the 0-everything assumptions as our first projection assumptions.
Check out that contribution growth rate… that’s REALLY high.
But notice the labels have changed so that it holds up to FY2024, instead of FY2025 as with the FY2025
So let’s assume Chicago can hold to the last contribution absolute amount made, and continues indefinitely.
Not too bad, but that assumes the pension benefits also don’t increase.
Notice the light blue portion of the graph starts with 2025, instead of 2026. That’s becuase I have historical info only through FY2024 for Chicago Municipal.
Let’s try a different set of assumptions:
Ah, we see here the assets peak and then start coming down — the benefit payments are getting to be too much.
Let’s try something else — the contribution amounts are a bit too much for Chicago. What if they started cutting the contributions?
Ooops, that wouldn’t be good.
Purpose of the Model
Mind you, most of the plans I have in here will be sustainable past 2040 for all sorts of assumptions, but it doesn’t hurt to test the projections out.
These are simplistic models, intended to try out a few levers in public pensions — particularly, contribution growth rates, because that is the ONE THING pension plan sponsors can actually control.
Theoretically, they could control benefit growth rate, but usually in a bad way (aka pension sweeteners).
Enjoy!
Great tool!