How to calculate your annualised rate of return (Statsgeek Thread)



  • I’ve seen a few people to ask how to calculate an actual, annualised rate of return if they have made multiple deposits and/or withdrawals over a period of time.

    It’s actually really simple using the XIRR function on Excel. An IRR (Internal Rate of Return) calculates an annual return using a series of dates and values so is a great indicator of what your portfolio is really generating.

    Hopefully the picture below is fairly self explanatory, but the key things to remember are:

    1. Show all deposits as a negative and all withdrawals as a positive
    2. Make sure you have today’s portfolio value and cash amounts as the final positive in the list
    3. You can see the formula in the top right of this picture - make sure your cell range captures all of the dates and all of the values

    In this example you can see the investor has deposited £4,000 and his portfolio is currently valued at £5700 plus £30 in cash. A simple ROI calculation would suggest a profit of 43% however that doesn’t take into account the fact that
    A) The money has been drip fed in over a period of time
    B) He’s been investing for a less than a year

    The IRR calculation works out an annualised return of 156% - which is the more accurate figure to use if you’re going to start comparing returns with other savings and investments!

    I’ve no idea if I’ve explained that well - shout if you have any questions.
    0_1559126360990_68B74BCE-ED56-491D-A610-C3589A90A225.jpeg



  • @Coleyscrooge its the calculation behind the function I was trying to do the other day... couldn't work it out! I'll see if Google explains whats going on behind the scenes :) thanks.



  • @Coleyscrooge said in How to calculate your annualised rate of return (Statsgeek Thread):

    I’ve seen a few people to ask how to calculate an actual, annualised rate of return if they have made multiple deposits and/or withdrawals over a period of time.

    It’s actually really simple using the XIRR function on Excel. An IRR (Internal Rate of Return) calculates an annual return using a series of dates and values so is a great indicator of what your portfolio is really generating.

    Hopefully the picture below is fairly self explanatory, but the key things to remember are:

    1. Show all deposits as a negative and all withdrawals as a positive
    2. Make sure you have today’s portfolio value and cash amounts as the final positive in the list
    3. You can see the formula in the top right of this picture - make sure your cell range captures all of the dates and all of the values

    In this example you can see the investor has deposited £4,000 and his portfolio is currently valued at £5700 plus £30 in cash. A simple ROI calculation would suggest a profit of 43% however that doesn’t take into account the fact that
    A) The money has been drip fed in over a period of time
    B) He’s been investing for a less than a year

    The IRR calculation works out an annualised return of 156% - which is the more accurate figure to use if you’re going to start comparing returns with other savings and investments!

    I’ve no idea if I’ve explained that well - shout if you have any questions.
    0_1559126360990_68B74BCE-ED56-491D-A610-C3589A90A225.jpeg

    Cheers @Coleyscrooge, I'd often wondered about what my ROI actually was so it's good to know, I'd have had no idea how to get to this on my own!



  • @Vespasian32 said in How to calculate your annualised rate of return (Statsgeek Thread):

    @Coleyscrooge its the calculation behind the function I was trying to do the other day... couldn't work it out! I'll see if Google explains whats going on behind the scenes :) thanks.

    If you look at the top of the picture the actual calculation is shown! =XIRR (C1:C10, A1:A10)?

    Not sure if that helps but thought I would highlight in case you missed it :-)



  • @betting2win no that's a function not the formula... XIRR calculation (as I just read online) is;

    0_1559130676656_upload-b43d2b7f-758d-406b-8bb9-831ba48cb666

    this is what I was trying to do previously (not knowing a function existed)…



  • @Vespasian32 said in How to calculate your annualised rate of return (Statsgeek Thread):

    @betting2win no that's a function not the formula... XIRR calculation (as I just read online) is;

    0_1559130676656_upload-b43d2b7f-758d-406b-8bb9-831ba48cb666

    this is what I was trying to do previously (not knowing a function existed)…

    Lol...way above my pay grade :-)



  • @betting2win said in How to calculate your annualised rate of return (Statsgeek Thread):

    Very useful thank you. I seem to be at 186% in just under 10 months which I am very happy with.



  • @Zidave Very impressive! Has anyone else calculated theirs, out of interest?



  • @Coleyscrooge said in How to calculate your annualised rate of return (Statsgeek Thread):

    @Zidave Very impressive! Has anyone else calculated theirs, out of interest?

    Yeah I'm hovering around 225%, been on just over a year now. How is yours?

    I don't think it will be able to continue at this level, I think I was just lucky to join at a time when there was incredible growth, I don't delude myself that I'm some sort of master trader! Realistically even if it were to halve from that then the returns are still incredible and it's good fun.



  • Great thread thanks,
    I'm on 160% since I started properly logging my balance against deposits in the middle of April. Gone full excel mode with graphs etc, becoming a bit of an obsession!



  • @Joev said in How to calculate your annualised rate of return (Statsgeek Thread):

    @Coleyscrooge said in How to calculate your annualised rate of return (Statsgeek Thread):

    @Zidave Very impressive! Has anyone else calculated theirs, out of interest?

    Yeah I'm hovering around 225%, been on just over a year now. How is yours?

    I don't think it will be able to continue at this level, I think I was just lucky to join at a time when there was incredible growth, I don't delude myself that I'm some sort of master trader! Realistically even if it were to halve from that then the returns are still incredible and it's good fun.

    I’m at 218% IRR from the 10 months I’ve been trading. Likewise I’ll be amazed if I can keep that up but am very happy so far!



  • @Neil2265 said in How to calculate your annualised rate of return (Statsgeek Thread):

    Great thread thanks,
    I'm on 160% since I started properly logging my balance against deposits in the middle of April. Gone full excel mode with graphs etc, becoming a bit of an obsession!

    This site is ideal for both football obsessives and stats obsessives... if you’re both then happy bloody days!



  • Does this include if players are in the sell queue? (Doesn't it take it out of your overall balance?)



  • No, they stay in your portfolio value until they are sold, at which point the value is added to your cash balance (so either will be included in the calculation above)

    @Abaalan said in How to calculate your annualised rate of return (Statsgeek Thread):

    Does this include if players are in the sell queue? (Doesn't it take it out of your overall balance?)



  • Finally got round to doing this today, made more relevant as I've put the vast majority of my net deposits in the last 3 months. IRR is currently 296%.



  • @Yellow Great effort! After the trading bonus was paid mine jumped up to 230%



  • 190% for me, having made my first deposit on 4th December 2018. That is insane.

    In terms of cash ROI, I'm on around 58%.



  • @Coleyscrooge

    Thank you! This is exactly what I have been looking for. I am very happy with my returns but I wanted to know exactly how I am getting on, especially as I have been making fairly large deposits in a short space of time and these deposits haven't had time to build up CA or dividends, plus bonuses have distorted things.

    I will do this tomorrow on my laptop, rather than my tablet. One question though. Is there a formula that would show exact returns if you placed the money in your example in a 1.4% ISA instead of FI? Especially when deposits are mid month rather than the first. At the moment I work out it in a very longhand manner! Please don't spend much time on this, I am happy with my way unless you have an obvious answer!

    Thanks again, football, numbers, spread sheets and money, love it 🙂



  • @Martyn-B depends on a few things, for example whether interest on the ISA is paid regularly (so compounds) or once a year. But the simplest way is just to compare the quoted interest rate (so 1.4%) with the IRR you have calculated as per the above. I imagine it compares very favourably!



  • @Coleyscrooge Thanks. The interest is paid yearly. I am sure my IRR will be very favourable to my ISA. Might be easiest to just adapt my thinking (and spreadsheet) to % returns rather than monetary values! Thanks again.


Log in to reply