Back to Basics.Understanding Internal Rate of Return (IRR)Continuing from a previous post on keeping track of our UT funds using Excel...
Aside from the ROI and the Annualised Returns (which is also known as CAGR – Compound Annual Growth Rate), we could add into the spreadsheet the IRR (Internal Rate of Return).
The IRR shows the ‘average’ returns of the total investment. I’m using the word ‘average’ loosely, as it is not a mathematic average – where the sum is divided by a denominator.
Why IRR is needed?In each purchase, we can easily calculate its returns or profit – which is the ROI, and also the CAGR. If there are 5 purchases (whether it is the same fund or 5 different funds, it does not matter), we will have 5 CAGRs. So what’s the CAGR of these 5 purchases?
In other words, we want to know the common CAGR of these 5 purchases, the annualised rate of return of the total investment in these 5 purchases – which is the Internal Rate of Return (IRR).
And we cannot simply total up the CAGRs and divide it by 5, as this will be completely inaccurate.
The calculation of the IRR is too complicated to fully explain in this post. You can read more of it here:
http://www.investopedia.com/terms/i/irr.aspBut in Excel, it has a very handy function (XIRR) to calculate the IRR. This XIRR function is a recursive function – meaning it goes around in a loop repeatedly until it comes to a figure that meets the 'set condition' before stopping & exiting the loop.
What is the ‘set condition’ in this case of finding the IRR?
This is the same CAGR, say X%, for all the 5 purchases; where if we plug in X% into the CAGR and calculating backwards to have a new ROI from each of the 5 purchases, the total of these 5 new ROI will be the same as the total of the original ROIs.
Get it?
Here’s an example to further clarify:
Purchased Date, Current Date, Purchase Cost, Current Value, ROI, CAGR
01/09/2014 21/01/2016 RM1,000.00 RM1,200.00 RM200.00
14.03%01/10/2014 21/01/2016 RM1,500.00 RM1,600.00 RM100.00
5.06%01/11/2014 21/01/2016 RM1,000.00 RM1,150.00 RM150.00
12.12%01/12/2014 21/01/2016 RM1,500.00 RM1,550.00 RM50.00
2.92%01/01/2015 21/01/2016 RM1,000.00 RM1,100.00 RM100.00
9.46%
Total ROI = RM600.(Please note the 5 different CAGR figures, which ranges from 2.92% to 14.03%.)
We can either manually calculate backwards on a trial & error basis to find the common CAGR or we can use the XIRR function... and we found out that the common CAGR or IRR is 8.10%.
Now, if we plug in 8.10% as the CAGR for all the 5 purchases , and calculate (backwards) to find their ROI, the figures would be as below:
Purchased Date, Current Date, Purchase Cost, Current Value, ROI, CAGR
01/09/2014 21/01/2016 RM1,000.00 RM1,114.32 RM114.32
8.10%01/10/2014 21/01/2016 RM1,500.00 RM1,660.80 RM160.80
8.10%01/11/2014 21/01/2016 RM1,000.00 RM1,099.90 RM99.90
8.10%01/12/2014 21/01/2016 RM1,500.00 RM1,639.31 RM139.31
8.10%01/01/2015 21/01/2016 RM1,000.00 RM1,085.67 RM85.67
8.10%Total ROI = RM600.Summary: The IRR is the effective rate of the total investment of several or more purchases. In the above example, when looking at the 5 different purchases as a whole investment, each ringgit invested is giving an effective rate of 8.10%.