# A Pension Analysis Tool for Everyone

*April 2, 2012*

A concern often voiced by pension reform activists and politicians interested in better understanding pension finance is that they have to depend solely on the information delivered by actuaries. This information, in turn, is typically delivered in a report so voluminous and so technical that the activists and politicians have to hire their own experts to explain it all to them. The mass of data and assumptions are usually so intimidating that ultimately many people who need to understand pension finance give up. Additionally, it is difficult to eradicate bias from expert analyses of pension solvency. The result is that many people, including paid professional spokespersons and other opinion makers, offer assertions that do not necessarily reflect the reality of pension finance, while voters and policymakers alike remain uncertain regarding the the nature and severity of the problem.

This post is to provide anyone who wishes to understand some of the fundamentals of pension finance a tool that allows them to do their own “what-ifs” on pensions. Because this model has distilled the mechanics of a pension fund to a single page of data and calculations, it offers a glimpse of how pensions operate that is relatively understandable and extremely transparent. This model is not intended in any way to replace the far more complex models used by actuaries, but it can be quite useful to illustrate, for example, how very sensitive the required annual contribution to a pension is to any change in other assumptions – especially the rate of return.

To download this Excel model, simply click on “pension_analysis_model” and you will have a spreadsheet to save and experiment with. Start with the first tab “constant inputs,” the 2nd tab will be explained later. The graphic images below show the upper section of this spreadsheet; all of the cells that accept inputs are at the top of the spreadsheet and are highlighted in yellow. While this model is only designed to show the pension fund performance by year for one person, it is important to understand that pension funds that aggregate pension contributions and allocate pension benefits for thousands of people follow the same rules.

To use this model, simply enter the assumptions you would like to use into the yellow cells. Don’t enter anything in a cell that is not highlighted in yellow or you will overwrite a formula. The result that matters is displayed in the one cell highlighted in green. If this number is positive, it indicates a pension would be adequately funded under the assumptions input by the user. If this number is negative, it shows by how much a pension would be underfunded. The goal is to enter a combination of assumptions in the yellow cells that yields the smallest amount in the green cell possible without being a negative number. That is a financially sustainable pension.

The three examples provided here are chosen because they clearly illustrate some of the key financial issues that challenge the solvency of pensions today. In all three examples, the pensioner is assumed to work 30 years and enjoy 25 years of retirement. They are assumed to earn a 1.0% increase in their salary each of those 30 years for merit (promotions and raises), and a 3.0% increase in their salary each year for cost of living adjustments (COLAs). Once retired, they are assumed to get a 2.0% COLA increase in their pension each year. These assumptions can all be changed, since they are all driven by inputs in the yellow highlighted cells, but to show the impact of two key variables – the pension benefit formula, and the rate of return – they are held constant on all three examples to follow.

The first example, on the table immediately below this paragraph, shows what public safety pensions were historically – up until somewhere between 5 and 15 years ago, when virtually every city and county in California adopted more generous pension formulas. In the “pension formula/yr” cell, 2.0% is entered, which means that for every year worked, the pensioner will receive 2.0% of their final salary in retirement. This means a person who works 30 years, as in this example, will receive 60% of their final salary per year as a retirement pension. In the “fund return %” cell, the typical long-term rate of return for the pension funds is entered, 7.75% per year. Once you enter all these numbers, go to the “% of salary to pension” cell and enter various amounts until you arrive at one that provides the smallest positive number possible in the green cell. Doing this indicates that under these assumptions, an employee would require an amount equivalent to 13.1% of their salary to be set aside each year to fund a pension benefit equal to 60% of their final salary.

In the next example, shown below, one can view the impact of a change in the benefit formula from 2.0% to 3.0%. That is, the only change that has been made to the assumptions is the change in the “pension formula/yr” cell from 2.0% to 3.0%. This is to model the current typical pension formula for safety employees, 3.0% times years worked, times final salary. As shown, in order to still have a positive fund ending balance in the green cell, the amount to be contributed each year into the pension fund, “% of salary to pension,” now has to increase from 13.1% to 19.6%.

It is important to digress here to point out that because the change in the pension benefit formula from 2.0% to 3.0% (or from 1.25% to 2.0% for non-safety employees) was done *retroactively*, pension funds would have been required to increase their rate of contributions far beyond 19.6% going forward. This is because, for example, a mid-career employee, suddenly receiving this retroactive benefit enhancement, would have only been putting 13.1% into their pension fund for the entire first half of their career, a critical period since money invested that early has more time for earnings to compound. The impact of making the benefit enhancement retroactive will be explored at the end of this post.

The third and final example, below, shows the impact of a lowering of the fund’s rate of return. In this case, not only is the benefit formula enhanced from 2.0% per year to 3.0% per year, but the rate of return for the fund is lowered from 7.75% per year to 6.00% per year. At this rate of return, pension solvency would not require an annual contribution equivalent to 13.1% of payroll, or 19.6% of payroll, but 31.4% of payroll. This is a huge adjustment. In the concluding section of this post, a more in-depth analysis is presented explaining why even this may not be enough.

The model presented thus far is not designed to allow the user to input differing values in each year under analysis, but in the same Excel file “pension_analysis_model,” there is a 2nd tab, “flexible inputs,” that does provide this ability to the user. To delve into the details of how to use this model would go beyond the scope of this post. In short, any cell highlighted in yellow is an input cell, including entire columns where each row corresponds to a different year. The user will still iterate to achieve a near-zero result in the lone green cell which represents the final ending balance of the fund. The model on the 2nd tab uses exactly the same formulas and logic as the model illustrated above, except the user can assume and input differing values per year on this version. Here is a summary of the default case that is already entered on the downloadable spreadsheet, tab two, entitled “variable inputs:”

This analysis assumes that the change to the benefit formula from 2.0% per year to 3.0% per year was done in late 2000, in mid-career for the employee (year 15 of a 30 year career). This means that through the year 2000, holding all other assumptions constant, the annual pension contribution was only 13.1% of salary (because at through that point, that was all it needed to be – see example #1 above). What also happened starting around the year 2001 was the rate of return earned by pension funds fell – they have actually fallen to around 4.0% during the past decade, but in this analysis, the rate is lowered to 6.0% per year and held there through the rest of the timeline. Prior to 2001, from 1985 through 2000, the rate of return is assumed to be 7.75% per year.

Based on these assumptions, which reflect a fairly realistic assessment of history to-date, starting in 2001 it is necessary for an employee with these rate-of-return and benefit changes to make an annual contribution to their pension fund equaling 54.5% of their salary. And for every year they have not done this, that percentage must rise. Nowhere in this analysis, moreover, is the all-too-frequent practice of “spiking” accounted for, which raises necessary annual contributions still further.

By using in this final example a person for whom the pension fund adjustment was made in mid-career, it is reasonably accurate to say that whatever unfunded liability may exist in reality in this individual case, could be used as a basis for calculating the total unfunded liability of the fund in aggregate. To get a global estimate, of course, one must input a blended benefit rate that takes into account the lower formulas that apply to non-safety employees, or run them as separate studies.

Again, this model is not meant to replace actuarial models that take into account specific fund demographics and deliver results precisely aggregated for all participants in the fund. But actuarial models, for all their precision and complexity, must nonetheless rely on the same set of assumptions this model does, and how those assumptions are made delivers vastly differing outcomes. For anyone who uses it, this model may serve as a useful tool to better understand and communicate the dynamics of pensions, and to sanity check whatever does come out of the black boxes reserved for qualified actuaries.