A Pension Analysis Tool for Everyone

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.

4 replies
  1. Avatar
    Jim McKee says:

    I think your effort to translate the actuarial science and language into basic soundbytes for broader consumption is commendable. I also think that anyone with an understanding of pension science will recognize that the discount rate lever is arguably the most powerful and dangerous assumption to play with among public funds. Fortunately, this assumption is more strictly regulated among corporate pension funds, so they don’t have as much discretion to understate their pension liabities and funding needs.

    The link to the pension model doesn’t seem to have the complete set of spreadsheet files in the zipped file for the reader to use. Any help to fix or clarify the downloading process would be appreciated.


  2. Avatar
    Robert Mitchell says:

    Great to have a tool for this.
    It would also be helpful to show the extra challenges that spiking causes.
    With a little more effort, you could put the assumed pay increase rate in the final year on the spreadsheet.
    This would then show how much the level cost grows with spiked pay.

  3. Avatar
    Ken Churchill says:

    I have been talking with a lot of public officials and they see their contributions increasing every year but don’t really understand the true impact of the retroactive increases. This is a great tool to help them see the HUGE problem that has been created with retroactive increases combined with lower than assumed investment returns.

    Up until early 2000, most employees and employers at public agencies each contributed 5-7% of salary to their pensions. If that was still the case, we would not be here. But a 60% benefit, often times combined with Social Security was not good enough for them, so here we are. Drastic cuts in services and on the brink of bankrupcy. They simply blew up their pension system and this tool demonstates it.

    As the data shows, retroactive increases to 90% for employees halfway through their careers increases the required contribution to from 19% to 54.5% of salary. Use this tool to determine the contribution requied if a person retires 1 to 5 years after the increase as so many thousands have done and use a 6% assumed rate of return and you will be truly shocked at the result.

    I hope those of you who are deeply concerned about these costs will forward this link onto your local politicians so that they will have a better understanding of the true costs of the retroactive benefit increases and will negotiate future employee contributions accordingly so once again they pay their fair share or are willing to go back to the affordable levels before the insane and unaffordable increase. That is what we really need to have happen.

  4. Avatar
    Tough Love says:

    Hi Ed,

    Nice follow-up to your similar spreadsheet of a few months back. I like this one better (leaving inflation in).

    First, I validated all of your #s with my own spreadsheet. As I pointed out last time, it’s really easy to hit exactly zero in your green cell if you use EXCEL’s GOALSEEK function in the TOOL dropdown (try it, you’ll LOVE it). It’s also very easy to make assumption changes and then get the green cell back to exactly zero w/o a lot of trial-and-error.

    I was a bit surprised your required level annual percentages weren’t higher …. because if you had to purchase the promised annuity (i. e., the future cashflows) from an annuity writer, the lump sum the annuity writer would require (to take on this obligation) would be quite a bit higher than your ending fund balance at retirement (the highest # in your rightmost column).

    This is likely due to the annuity writer assuming a more conservative investment return on the invested funds … as well as including margins for profit, investment “risk”, and mortality “risk”.

    I believe it would be enlightening for you to followup with what a typical annuity writer WOULD CHARGE (i.e., what THEY would require as payment to guarantee the spreadsheet post-retirement payouts), and then back into the investment return (using your spreadsheet) to match THEIR purchase-amount requirement.

    I’m guessing you’ll likely find it to be in the 4-5% range, as that’s all THEY’RE comfortable with … since THEY don’t have the Taxpayers as a backstop if things go wrong.

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.