# Excel function to calculate pension returns

Trying to compare performance of two funds

• ### Late filing Co House - Is Covid still an excuse

I am trying (for fun, not work) to calculate the investment percentage gain or loss on a pension fund. Two funds, actually.

Fund 1 - no investments in the year, so closing balance minus opening balance, divided by opening balance, works just fine.

Fund 2 - there is an opening and closing balance, and regular monthly payments in the year.

Is there an excel function that can calculate the percentage gain over the year.

Thanks,

### Replies (11)

By frankfx
17th Apr 2021 13:28

XIRR. Function

Thanks (0)
By tom123
17th Apr 2021 15:30

Thanks i will take a look!

Thanks (0)
By tom123
17th Apr 2021 15:30

Thanks i will take a look!

Thanks (0)
By carnmores
17th Apr 2021 16:29

You have a funy sense of fun. Last months performance is not necessarily a guide to next months.

Thanks (0)
By tom123
17th Apr 2021 17:05

Correct, of course, but I have two pensions running side by side.

A work one (with contributions) and another one, with no contributions currently.

That second one is via an IFA - and has higher charges than the 'tracker' type work one, but I am trying to see if it is worth it.

Of course I am not quite comparing apples with apples.

Thanks (0)
By Calculatorboy
17th Apr 2021 22:26

if you look at managed with charges and
trackers with minimal charges.. well you tell me if there's any difference in performance

Thanks (0)
By Calculatorboy
17th Apr 2021 22:29

Why bother,? many pension providers give you this info online,

Your time is better spent on things you enjoy, fishing, drugs ,...or fishing whilst on drugs ..etc

And besides any accountant with any sense of materiality can just glance at opening and closing balances ( and taking account of any injections of capital) will be able to come to a conclusion as to whether funds need to be moved.

Fun , ? ..I think you need to get a life before you are prematurely dead and your beneficiaries receive the fund tax free

Its called not seeing the wood for the trees.

Thanks (1)
By DJKL
17th Apr 2021 23:02

Checking pensions and savings once a year is a good habit to have, if you do it you may well have more to spend in retirement on all the enjoyable things in life and with all the time to enjoy them.

I monitor our pensions and investments at least annually (I have done since my 20s) , I have made key decisions based on those observations (Cheerio Standard Life) and as a result ,as we edge towards retirement, I am reasonably relaxed as at 61, if we stopped working now, shy five years of our state pensions, we could do so and still enjoy a reasonable lifestyle.

Putting data through excel, forecasting what these will likely/hopefully be worth at x date or y date in the future is valuable, charges are important (mine cap at £200 pa with Hargreaves Lansdown), even analysing exposure to geographic regions or sectors (and funds often need a little delving to get this ) helps calculate how exposed you are to particular markets or industries; accountants are very well placed to do all this analysis, it is in our blood.

Thanks (0)
By Calculatorboy
18th Apr 2021 00:08

Hargreaves landsdown...are they really independent, I don't know , maybe they could confirm ...Best of luck and keep your fingers crossed ...its a roulette wheel at the end of the day.. as you know you would perform just as well blindfold throwing darts into a dartboard ....

Only conmen try and convince the deluded with unsubstantiated nonsense

Thanks (0)
By DJKL
18th Apr 2021 12:04

Does it matter if they are or they are not independent, just buy what you want using their SIPP platform- I do not own any of the funds for which they charge commission just ITs which apart from dealing commission earn them nothing (and I do not deal that much, two or three purchases a year at best).

You say it is roulette, to a degree you may be right re particular individual investments but you are certainly not right re spreading risk by choosing market segment or geographic spread, you can readily bend performance by slanting your investments into different sectors that your macro economic senses suggest have some mileage. (In my case moves some time back into Asia/USA/Emerging and a far lesser reliance on UK and Europe seems to have paid and I see no reason yet to revert to UK centric investing, though maybe eventually it will be worth considering given higher dividend yields with UK listed stocks)

Whilst I accept I am not a very balanced investor, restricted mainly to equities and property, the fact is it works, it works consistently in the long term, and the only secrets are diverse investment sectors, think minimum ten years, buy and forget.

I am quite happy with £10k in twenties being £20k in thirties, £40k in forties, £80k in fifties and £160k in sixties and pretty much this is what I have received, 7% compound per annum(after costs) being a more than acceptable target.

Thanks (1)