Pension fund performance calculation - need some formula help!

village_life

Hi

Can anyone point me to, or give me a formula for calculating my pension performance

For example I had a pension that I paid into for 5 years at employer 1, I paid in x per year (figure varied as contribution rate and salary varied) but I know annually what I paid in each year, and my statement shows the fund value at an equivalent point in time

Now that I have left a company and the pension is stale I presume it's not as easy as taking the fund value and the total contribution I made in payments, and working out a growth percentage? (Ie paid in 10k, fund value 15k = 50% growth?) Surely there is a more subtle compounding calculation I need to do in order to take growth each year?

Similarly I could do a % each year based on the contribution I made vs the fund value, which will give me a set of % growth/declines each year.... Would I just take an average across the years? Again doesn't seem right

Probably makes no sense- basically I want to validate for myself if my pension is growing at approximately the rate I'd like and expect (7% per Annum) but not sure how to calculate that - I have all the source data

Thanks

sandsy

Do you have Excel?
If so, in one column, input all the amounts paid in (including tax relief) each month. If you left a while ago, make sure you put zero for the months after you left and weren't contributing.
At the bottom of the column, put the current value of the fund but put a - (minus sign) in front of it.
So you should have something like all the cells a1:a61 filled with a number.
Then input the formula = IRR (a1:a61, 0.01)
This will give you the monthly rate of return, x.
To find the annual rate of return, do the formula (1+x)^12-1
And make it into a percentage to get the annualised rate.

atush

Now you are no longer contributing, it will be easy to track performace year on year with the annual statement. for a quck and dirty with ones I am contributing to currently, I take this years valuation, strip out any payments in this year, then calculate this years value compared to last

village_life

Cool thanks, good couple of options there.

So on option A, the IRR one I presume if I do the calc I will just have one figure at the end, and if the Annualised rate of return is 7% or what I am after then looking ok?

bigadaj

village_life wrote: »

Cool thanks, good couple of options there.

So on option A, the IRR one I presume if I do the calc I will just have one figure at the end, and if the Annualised rate of return is 7% or what I am after then looking ok?

Your returns are no real guide to the future, the one thing you should get out of this is the volatility of your investments, maybe up 20% one year and then down 10% the next.

If it's an old employer scheme then worth checking charges, discounts on these often only last as long a a you are employed by them, and charges increase when you leave, transfer out if you can find a cheaper deal with a good range of investment options. If you are in a default fund then this is normally a spread of investments, if you move into riskier investments then you have a better chance of higher returns, but it's got to be something you're comfortable with.

village_life

bigadaj wrote: »

Your returns are no real guide to the future, the one thing you should get out of this is the volatility of your investments, maybe up 20% one year and then down 10% the next.

If it's an old employer scheme then worth checking charges, discounts on these often only last as long a a you are employed by them, and charges increase when you leave, transfer out if you can find a cheaper deal with a good range of investment options. If you are in a default fund then this is normally a spread of investments, if you move into riskier investments then you have a better chance of higher returns, but it's got to be something you're comfortable with.

on your first point, while I accept they are no real guide to the future, what I want to do is pro actively check that they are "on track" - I do the Hargreave Lansdowne (and other) pension calculators regularly, and use the 7% annual growth figure (I do play with 5 and 9% too) - however I have no way of ascertaining if my investments are indeed growing at 7% per annum, and as such whether i am "on track" with the rate of growth required to achieve the figures in the projection.

I'm open to any other suggestions as to how I "manage" this on an annual basis to ensure my pensions are performing to my expectations (Set by the calculator tools!) - I have already changed some of my investment schemes to suit my risk/reward position - what I havent got a strong handle on is performance vs my baseline expectations

village_life

sandsy wrote: »

Do you have Excel?
If so, in one column, input all the amounts paid in (including tax relief) each month. If you left a while ago, make sure you put zero for the months after you left and weren't contributing.
At the bottom of the column, put the current value of the fund but put a - (minus sign) in front of it.
So you should have something like all the cells a1:a61 filled with a number.
Then input the formula = IRR (a1:a61, 0.01)
This will give you the monthly rate of return, x.
To find the annual rate of return, do the formula (1+x)^12-1
And make it into a percentage to get the annualised rate.

Hi

the figures I've got (particularly for now dormant schemes) are annual contribution figures. Can you advise how i recut the calculation for that scenario?

sandsy

Input figures as described before. Only there'll be less of them, maybe going down to a7.
Then the formula =IRR(a1:a7, 0.07)
Convert the answer to a percentage for your annual rate of return.

village_life

sandsy wrote: »

Input figures as described before. Only there'll be less of them, maybe going down to a7.
Then the formula =IRR(a1:a7, 0.07)
Convert the answer to a percentage for your annual rate of return.

thanks, so that looks like this (No harm in disclosing!)

2006 2000
2007 5500
2008 6960
2009 5115
2010 4185
2011 0
2012 0
2013 0
Current Value -34270.02

6%

the figures 2006 to 2010 are the sum total of the contributions I and my employer made, left in late 2010 so no further contributions, and current value is 34k as at last statement = IRR 6%

so that shows I am slightly off my 7% "target" as denoted in the calculators I follow? Could I extrapolate this out across each of my pension schemes to get a blended average across each scheme?

Thank you btw!

sandsy

Remember 6% is your net return, after charges.
The 7% of the calculators is normally before charges.
Also, the 7% is no more than an assumption. Given the new product projections which come in in April, most calculators will probably change the assumption to 5%.

No reason the methodology can't be extrapolated to other schemes but be careful to get the timing of contributions consistent for the IRR formula.