Share this content

Excel function to calculate pension returns

Trying to compare performance of two funds

Didn't find your answer?

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)

Please login or register to join the discussion.

avatar
By frankfx
17th Apr 2021 13:28

XIRR. Function

Thanks (0)
Replying to frankfx:
Routemaster image
By tom123
17th Apr 2021 15:30

Thanks i will take a look!

Thanks (0)
Replying to frankfx:
Routemaster image
By tom123
17th Apr 2021 15:30

Thanks i will take a look!

Thanks (0)
avatar
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)
Replying to carnmores:
Routemaster image
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)
Replying to tom123:
avatar
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)
avatar
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)
Replying to Calculatorboy:
Hallerud at Easter
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)
Replying to DJKL:
avatar
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)
Replying to Calculatorboy:
Hallerud at Easter
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)
Replying to Calculatorboy:
Routemaster image
By tom123
18th Apr 2021 08:31

But then, you are talking to someone who actually chose accountancy as a degree - so even did it while not being paid!

Real life will resume for us all soon..

Thanks (0)
Share this content