Updated Public Pension Projection Tool -- Includes Fiscal Year 2024 Data
With a few examples to test it out!
I decided not to wait until Christmas again to release the next version of this tool, mainly because of the boost to Chicago Police and Fire pensions.
I wanted to try out a few things!
Projection Spreadsheet: with FY 2024 Data
Data 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. This had been run by Alicia Munnell, but she retired recently.
For the data in this release:
The Public Plans Database (PPD) currently contains plan-level data from 2001 through 2023 for about 230 major state and local government pension plans — with about half of plans administered at a state level and half administered locally. This sample covers 95 percent of public pension membership and assets nationwide. The sample of plans is a carry-over from the Public Fund Survey (PFS), which was constructed with an eye toward the largest state-administered plans in each state, but also includes some large local plans such as New York City ERS and Chicago Teachers.
The PPD is updated quarterly from data available in the most recent financial reports. Intermediate updates may occur when new variables are added or data errors are corrected.
This specific description isn’t exactly up to date.
Here’s what’s going on: I am using data for 244 plans, and the last fiscal year I have data for the plans break out as:
FY2024: 164 plans
FY2023: 76 plans
FY2022: 4 plans
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 with two plans, so you can see the sorts of inputs and outputs.
First example: New York City Fire
Yes, I’m going to compare this against Chicago Fire in a moment.
The input/output interface is on the tab named “Projection Results”.
Make sure, when you open the spreadsheet, you have the calculation mode 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.
First, there’s a drop-down list for which plan you want:
So I will select New York City Fire, after which a bunch of plan historical statistics will be calculated, which may inform your choice of projection parameters:
I put in the worst 1-year historical return, just as a reminder of how bad an economic shock can be.
The growth rates and investment returns, over each period, are quoted on a geometric/compounded growth rate.
To keep things simple, let me choose a simple set of projection parameters:
I chose 4% as a benefit growth rate, to get close the the 10-year benefit growth rate for the plan.
For the other three assumptions, I picked something “conservative”: no increase in contributions, no change in expenses, and a 6% per year investment return.
Here are the results:
Hmm, steady growth.
Let’s look at cash flows.
What you’re seeing is investments meeting the needs of the benefit flows in this projection, mostly.
It will probably not surprise you that not only is the NYC Fire plan 72% funded currently, showing a steadily growing funded ratio….
But that NYC Fire has been making contributions of about 100% payroll for some years now.
Yeah, I’m not expecting them to hike contributions much more than their payroll is growing.
Second Example: Chicago Fire
You knew it was coming.
Yeah, I don’t see Chicago Fire being able to keep up such a high contribution growth rate.
But I don’t feel like putting in realistic projection parameters — I want to do a stress test. Let’s say, due to the recent pension sweeteners, instead of a 5% per year increase in benefit growth, there were an 8% per year benefit growth rate.
And, let’s say, given all the strains on the Chicago budgets, the contributions were held at current levels. Let’s just keep expense growth at 0 and then investments at 0 (but no losses!), too.
Remember, this is a stress scenario.
Oh, I forgot to tell you about the other feature of the spreadsheet — it’s possible for the assets to run out. It will tell you what year that will occur, given your projection assumption set.
And how much more money you would have to pay if the money ran out.
Isn’t that a nice feature?
Enjoy!
I will read this is depth later, but I wonder if having data on the same plans for, say, 10 or more years, especially if there are other fields of data one can get about the plans, can yield some interesting analyses about, say, which plans are more likely to be better funded in the future.