Calculating the Internal Rate of Return
If you, like most of the personal financial community, prefer to invest passively each month it can be difficult to calculate the true percentage gain throughout the year.
Take for example someone who starts a brand new fund and invests £100 each month for 12 months, giving a total investment pot of £1200. If the pot was now worth £1260, the platform website would show a percentage gain of 5% over the total amount invested. However, the actual percentage gain is much higher. The reason for this is that the 5% gain is calculated from the entire current investment pot whereas in reality it’s only after the 12th month that the full £1200 has been invested. Before that, the pot was at £100 in month 1, £200 + gains in month 2. £300 + gains in month 3, etc.
To calculate the true rate of growth averaged out over the term, we need to calculate the Internal Rate of Return.
Excel comes with a built-in function (XIRR) to help us calculate this and defines each monthly investment amount as a ‘payment’. Syntax for the XIRR function is (Values, Dates, Guess).
The values will be a column of payments (in negative form) which represent each monthly investment and finished with a positive figure which represents the current fund value. Dates is the corresponding date that each payment was made. Guess is an optional figure used to help calculations and is your estimate of what the percentage is likely to be (1%, 10%, 10000% etc).
Taking our initial example:
Gives an Internal Rate of Return equal to 0.093 or 9.3%. Quite a difference!
Eventually as a portfolio grows and the yearly contribution begins to contribute less as a percentage of the total pot, the Internal Rate of Return is going to drop closer to the platform’s reported total annual percentage change. However this calculation is still useful, particularly for beginner investors such as myself who are beginning with a small initial fund.
6 thoughts on “Calculating the Internal Rate of Return”
This is good information. Now if the function could only make the 1,260 be 12,600.
I am curious if all gain percentages are using the simple calculations. Thanks ERG!
No worries Wade. I thought something might have been up when my fund was showing a relatively low % gain for the year despite looking much stronger on the tracker graphs. Now I’ll recalculate it myself each year to get a more accurate reflection.
Hey Guy,
I love XIRR, and run it on our portfolio across accounts as well as each account at the end of each year. Not only does it give you a more accurate number, but it helps with benchmarking against what your portfolio should be doing.
Exactly! My find platform only shows the final % increase for the entire period i’ve had the fund and calculated using the entire contributions as if they had been in from day 1. Not a very accurate benchmarking number.
I never knew about the XIRR function – thanks! As someone who makes small monthly investments, this is very interesting and now, I’ll be spending hours on my spreadsheets, haha!
I’ve only recently found it, I certainly hadn’t seen it mentioned on any other blogs before and yet seems like something all of us should be using.