Is anyone a statistics wizz?

[Benbay001]

Id like to get some kind of meaningful annualised return rate from my ISA.

The issue is that i put in different amounts in different months.

Im fairly poor at maths and cannot work out a way to make it work in excel.

I have figures for how much i put in and on which dates and obviously have the value of the account today.

I was thinking i could do something in excel for each ISA subscription along the lines of

=DAYS(Todays date, Date the money went in)

Which would give the number of days that have passed and then multiply the number of days passed by the £ value added on that date.

This would provide a weighting value for each payment in, but then i cant work out what to do with that data (probably because its meaningless)



Sorry if the above is confusing.. its exactly how i feel...

Thanks

[Mr Pointy]

Have a look at the spreadsheets on this site:
https://www.vertex42.com/Calculators/financial-cal...

This one in particular may help - it uses Excel's XIRR function:
https://www.vertex42.com/ExcelTemplates/investment...

[dodsi2000]

The question I would ask is how your isa pays its interest? Is it monthly and if so when in the month?

Not sure exactly what you want to do with the information but you need to know month 1 payment plus interest then month 1 with interest plus month 2 payment plus interest and so on.

[anonymous-user]

Benbay001 said:

Id like to get some kind of meaningful annualised return rate from my ISA.

Why? It's a lot of work and doesn't really tell you anything. Yes, you'll eventually be able to work out where you've been but it will never tell you where you are going.

What things have you got in the ISA? It's often more useful IMO to look at those individual things and see how they are performing. If they are funds you can look them up on Morningstar or Trustnet to see detailed past performance and an assessment of how they are rated today.

One "quick and dirty" way to look at a portfolio that's been running for a number of years with income reinvested and no withdrawals is to,
1. Look at the value on 1 Jan 2020
2. Look at the value on 1 Jan 2021
3. Deduct from the 2021 figure any new contributions added during 2020
4. The % difference between 1 and 3 is approximately your return.

[LeoSayer]

XIRR is the excel function to use.

You provide it with a list of dates and the amounts invested followed by today's date and the current value. It then works out the annualised return.

[Benbay001]

Mr Pointy said:

Have a look at the spreadsheets on this site:
https://www.vertex42.com/Calculators/financial-cal...

This one in particular may help - it uses Excel's XIRR function:
https://www.vertex42.com/ExcelTemplates/investment...

Thank you. I actually found this site shortly after posting this thread.

dodsi2000 said:

The question I would ask is how your isa pays its interest? Is it monthly and if so when in the month?

Not sure exactly what you want to do with the information but you need to know month 1 payment plus interest then month 1 with interest plus month 2 payment plus interest and so on.

Its a S&S ISA, so it goes up, it goes down.

rockin said:

Why? It's a lot of work and doesn't really tell you anything.

Not much else to do during lockdown

I also think it does tell me something. Im picking my own companies and the alternative to that for me would be a global index. If i consistently under perform the index id give up and just buy an index etf.

Thanks all.

Edited by Benbay001 on Tuesday 5th January 11:32

[anonymous-user]

Benbay001 said:

Not much else to do during lockdown

I also think it does tell me something. I'm picking my own companies...

Yup, that all adds up.

[Benbay001]

rockin said:

Yup, that all adds up.

Since 2018.