Skip to content

Calculate your own personal loss

October 24, 2011

In October 2010, our Vice-Chancellor Michael Arthur made a statement of recommendation about the benefits to all USS members at Leeds in a covering letter to a series of consultation papers concerning the proposed changes to their pensions.

We offer here for download an excel spreadsheet that USS members can use to calculate the loss they will shoulder as a result of these now implemented changes to your pension scheme. This spreadsheet is for ‘final salary’ members, not the new entrants into the scheme, who face the even worse ‘career average’ method of calculating pension. If you were employed before 1 October 2011 and paying into the USS scheme, this is for you. Here is an example of some losses calculated by the spreadsheet:

University of Leeds UCU USS CPI spreadsheet

The spreadsheet you can download here will give you the amount you stand to lose depending on whether you live for 10, 20 or 30 years in retirement, and each of these is matched against four different scenarios of inflation (CPI). You can even input your own prediction of inflation to get a scenario based upon your own optimistic/pessimistic projection of where CPI might go. If CPI stays below 5% for the duration of your retirement, you will make no loss against the current scheme (other than thousands of pounds of additional contributions). Inflation has never been below 5% for more than 18 years in the last two hundred years.

Last week we explained how the annual increases to your pension in payment will be based on a cap on the rate of inflation (CPI). Read that here. Now, you can calculate the loss that you, personally, stand to pay as a result of that cap.

In order to calculate this, you will need some figures from the USS modeller (available here). You’ll need your member number (on your last service statement) and your NI number. Then, once you’ve got your statement online, take a note of these two numbers, which you can then input into the spreadsheet we provide: The existing benefits and the future benefits (the figure given in the ‘future benefits’ in the modeller include the existing benefits – you will see different colours strata in their graphic – so subtract existing from future to have a figure for all benefits to accrue). You can finesse the figures in the modeller by using the slides at the bottom to adjust the retirement age and the final salary that you might expect to be earning.

existing benefitsfuture benefits

In our spreadsheet, put the existing benefits in box A and the future benefits in box B. Put your estimated year of retirement in box C and then in the yellow boxes the kinds of losses you face in the given scenario are calculated.

As with the USS modeller, this spreadsheet should not be used as a basis for financial planning. Do talk to a registered financial advisor if you choose to make adjustments to your financial plans for retirement.

If you find that you don’t believe that you can afford such losses, and recognise that they are unnecessarily high, do continue in the action to re-open dialogue on the changes:

Assumptions, caveats and formulae

Turn away now if the maths bores you

Of course, the figures from our spreadsheet are subject to the caveats that USS provide with the modeller. Figures given represent calculations as of your last statement, 31 March, but are the best available for this calculation. The figures you put in include any benefits from AVCs if you included them in the modeller.

The spreadsheet calculations are based on the assumptions you input and extract from the USS modeller for your pension in payment.

Inflation is given as averaged in four scenarios, and a fifth scenario allows you to put in your own inflation figures over 30 years.

The pension loss is calculated thus:

x-y=loss, where x is the pension you would have got in the scheme as it was, and y is the pension you will get under the new scheme.

x and y are calculated for each year over 10, 20 and 30 years for different scenario.

Each scenario creates x by taking a+b (where a is the part of your pension from benefits to date and b is the part of your pension from future benefits) and increasing their sum each year, up to thirty years, by the inflation given in each scenario.

Each scenario creates y by taking a (your pension from benefits to date) and increasing  it each year, up to thirty years, by the inflation given in each scenario. Then it takes b (your estimated pension from future benefits) and increases it by the given inflation in each scenario, subject to the cap after 5%. It then adds a+b for each year’s pension amount.

So, for Excel users, the calculations for any year (where Z represents CPI) are:

To calculate increases to a in any one year the formula is simply –


To calculate increase to b in any one year the formula is less simply –

b + If[Z>5%,(b*5%)+(b*((Z-5%)/2),b*Z]

These then give increased figures for the next year’s calculations of a and b, and so on up to thirty years of retirement.

x-y then gives the loss for each year. These then give the cumulative losses over 10, 20 and 30 years in the spreadsheet.

All of this is based on the declaration of the calculation of pension increases by USS:

“Pension increases for service built up after 30 September 2011 are matched up to 5% a year, however, if official pensions increase by more than 5% then USS will match only half of the difference up to a maximum increase of 10%. So, if official pensions increased by 15%, USS increases would be 10% in that year. Benefits built up before 1 October 2011 will increase fully in line with official pensions.”

We are happy to be corrected on any of these formulae or assumptions, by USS or any individual. We will happily adjust the spreadsheet in accord with any error pointed out to us. If you wish to see the embedded equations in the hidden sheet, you may unprotect the workbook, and ‘unhide’ the calculations sheet.

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: