XIRR stands of Extended Internal Rate of Return. Similar to CAGR, it is another powerful way to gauge the performance of your investments. It is used to calculate the aggregated return when you invest (or redeem) your funds on multiple occasions over a period of time.
For example – in systematic investment plans (SIP) you do multiple transactions throughout the year, some of these may be equally spaced (like 1st of every month), while some of these may be random (like investing your bonus payment in lumpsum).
It makes sense to calculate returns for such periodic investments through XIRR. In this post, we’ll try to understand XIRR in detail, compare it with CAGR and finally learn how to calculate XIRR in an Excel sheet.
Also Read: How to calculate CAGR in an Excel Sheet?
How XIRR differ from CAGR?
Suppose you invest an amount of 1000 every month for a period of 12 months, which grows to 50000 in 5 years. That’s mean your investment of 12000 gets appreciated to 50000. Hence CAGR can be calculated as:
CAGR= (50000/12000)1/5 -1 = 33.03%
However, there is a catch here.
In the CAGR formula, all your investments and considered for a period of 60 months (5 years) which is not true. Your first investment of 1000 was definitely made for 60 months, but the second investment holds true only for 59 months (since it was made on 2nd month), third investment is for 58 months and so on.
So, in this case of periodic investments, it makes sense to calculate individual CAGRs for each investment and aggregate it into one overall CAGR. This aggregated CAGR is termed as XIRR.
For the example above the value of XIRR is calculated as 36.8%
XIRR Calculation
XIRR can be easily calculated through an excel sheet. In fact, Microsoft Excel has an inbuilt function to calculate XIRR. It uses an adjustment technique known as the Newton Raphson method to calculate aggregated CAGR (read XIRR) for each individual investment.
In order to calculate XIRR, you need to records each investment value with its respective date in an excel. The investment value should be marked as negative, and the redemption should be marked as positive.
See below:
In the above example, an investment of 1000 is made every month throughout 2015, and it’s redeemed after 5 years in 2020. The final redemption amount is 50000.
The final amount may also represent the current value of your investment, it may not be redemption always.
Next, in order to calculate XIRR you need to use below formula:
=XIRR (values, dates, [guess])
values – cells containing investment or redemption amount
dates – Dates that correspond to investment or redemption
guess – [optional] An estimate for expected IRR. Default is .1 (10%).
The XIRR gets calculated as 36.8% using the above formula. See below:
Download Excel Sheet
Download the excel sheet to calculate XIRR from the below link:
XIRR Calculation- Trading Tuitions
The excel sheet can be very useful to calculate accurate returns for your systematic investments in mutual funds or stocks.
