Calculating performance % increase

David_66

Hi I'm trying to work out the performance of my partners pension over the last year.
On the 4th February 2019 the pension had £29088 in it
Today 4th February 2020 it has £37920

She made the following payments in over the last year and took no money out
14th February 2019 £625 payment (in incl tax relief)
14th March 2019 £625 payment (in incl tax relief)
15th April 2019 £625 payment (in incl tax relief)
14th May 2019 £625 payment (in incl tax relief)
14th June 2019 £625 payment (in incl tax relief)
15th July 2019 £625 payment (in incl tax relief)
14th August 2019 £625 payment (in incl tax relief)
Total paid in £4375

I was wanting to work out how well her pension has performed over the last year but am not sure how to calculate that as a percentage. Just taking off the amount paid in £4375 from the final figure and then calculating the percentage increase from the starting amount wouldn't give an accurate percentage increase presumably.
Are there any online calculators to do this?

Thanks

bowlhead99

Do you have Microsoft Excel?

The way I would get an internal rate of return from those figures is to create a column of dates and a column of cashflows and use the XIRR formula to give the return. About 14%.

To do it a really simplified way I would say that the new cash dripping in monthly over the first half of the year looks like it was in the investment for on average about three quarters of a year, while the old money (opening balance) cash was of course in for a full year.

In terms of earning a return, having all the new cash (£4375) for three quarters of a year is basically the same as having three quarters of that new cash in (£3281) for all of the year.

So you could pretend that you'd had £29088 in it for a year and £3281 in it for a year, in other words treat it like you had a total of £32369 in it for a year; and now it's worth £37920.

If putting £32369 in it for a year has given you £37920, that's like getting a 17% return (because 37920/32369 = 117%).

However, that's only a rough guesstimate of course and does not really deal with the nature of compounding returns vs straight lines, and will be a little overstated

silvermum

Well, what you suggest is the quickest, bluntest way and at first glance works out at about +15% growth not including contributions.

What is the pension invested in? Are there any annual fees/ costs you need to offset?

David_66

Thanks Bowlhead I'll try figuring out how to do what you suggest in excel.

The pension is invested in various funds and has the usual platform and fund fees but I'm not bothered about separating those off as I would consider them part and parcel of how well the pension as a whole performs.

bowlhead99

David_66 wrote: »

Thanks Bowlhead I'll try figuring out how to do what you suggest in excel.

The pension is invested in various funds and has the usual platform and fund fees but I'm not bothered about separating those off as I would consider them part and parcel of how well the pension as a whole performs.

Yes I would agree to ignore all the fees as what matters to you as an investor is money in vs money out (or value at the end).

One thing to note is, you mentioned putting £625 in, including tax relief. So presumably £500 net and the platform collects £125 from HMRC some time later.

Most DIY investment platforms don't pre-fund the tax relief for you, they wait until it arrives from HMRC before you can spend it, which can be sometimes a couple of months later depending on the timing of cut-offs. Although if the same £500 is contributed each month you still have £625 of new money to spend each month because there is always tax relief arriving.

But for your calculations (for example) the £625 was not all invested on 14 August. In practice it might be there was £500 invested in August and £125 in October. So if you assume your money was invested in August you might think you had a particular rate of return... but actually a bit of it was not invested for quite that long, so the return you must have really earned is higher than what you would think you'd got if you had been assuming the money was invested August and had a full six months to grow before your next statement date.

TBC15

If the OP punched the information into Trusrnet would this not answere his question.

Prism

TBC15 wrote: »

If the OP punched the information into Trusrnet would this not answere his question.

Trouble is they would need to remember every payment and which fund it went into. Much easier to open up Excel or Sheets and use XIRR I reckon. Its then also easy to keep a running total.

Notepad_Phil

TBC15 wrote: »

If the OP punched the information into Trusrnet would this not answere his question.

I think the consensus from this thread https://forums.moneysavingexpert.com/discussion/6081149/how-does-trustnet-portfolio-calculate-yearly-profit would suggest it wouldn't.

Thrugelmir

Short term performance will be impacted by when the investment was made. Comparisons would need to be made on a like for like basis to have any meaning.

David_66

Thanks Bowlhead and others

Yes I had presumed the tax relief might come in later but on the Aviva website it always shows the tax relief being added on the same day as the deposit.

I have tried downloading a ready made exel spreadsheet and slotting my numbers in which does give an annualised return of 13.8%, however the XIRR to date column gives some odd numbers such as 131% after the first payment in on the 14th February. See link to file https://drive.google.com/file/d/1l_srRzvFX-Y19n7IUSMmrYWXnRZUMksy/view?usp=sharing

Example spreadsheet downloaded from here https://www.vertex42.com/ExcelTemplates/investment-tracker.html

AnotherJoe

David_66 said:

Hi I'm trying to work out the performance of my partners pension over the last year.
<snip>

I was wanting to work out how well her pension has performed over the last year 

Here's the issue with that - compared to what ..
You can work out what it did, but whether it did well depends compared to what, and for her level of risk. Eg if she's got VLS40 because she's very cautious no point thinking it did badly because a S&P500 outperformed  it massively.