What is XIRR in Mutual Funds? And How to Calculate it?
May 4, 2022 Mutual Fund
The primary objective of mutual fund investment is to earn returns. Returns on mutual fund are calculated by various ways such as CAGR, XIRR, absolute, etc.
Compounded Annualised Growth Rate (CAGR) is the most commonly used method to calculate the growth of your investment per annum over a specified period. However, CAGR is not relevant if there are multiple investments in a scheme over a period, such as in the case of investment via a Systematic Investment Plan (SIP). This is where XIRR comes in handy.
The full form of XIRR is Extended Internal Rate of Return. It is a method used to calculate returns on investments where there are multiple transactions taking place at different points in time. While CAGR is a useful tool for comparing and selecting mutual fund schemes, XIRR helps you to evaluate the actual returns on your investment.
CAGR is usually used to calculate returns on your lump sum investment if the holding period is more than one year and simple absolute return for less than one year.
On the other hand, XIRR is generally used to calculate mutual fund returns via SIP because it usually involves multiple cash inflows and outflows. XIRR can also be used to calculate returns on lump sum investment with additional purchases. In addition, if you are redeeming mutual fund units via a Systematic Withdrawal Plan (SWP), XIRR can be useful for calculating the returns.
The XIRR formula calculates the annual average return of each SIP instalment to give you the total average annual return on investments.
To calculate returns, XIRR discounts the cash flows based on the timing of the flows to arrive at the present value of the investment. Cash flows that occur earlier during the investment tenure are discounted less, while the ones that occur later are discounted more. This is because the value of money diminishes over time.
How to calculate the XIRR of your investment?
XIRR in mutual funds can be calculated by using a simple function on Microsoft Excel. All you have to do is enter the cash inflows/outflows along with the date of transaction. You can get details of transaction from the statement of account the mutual funds provide their investors on a monthly basis.
Enter all cash outflows (SIP instalment, lump sum purchases) as negative values and all cash inflows (SWP, redemptions) as positive values. Then enter the current value of the investment along with the date to calculate the XIRR of your mutual fund investment.
Let’s take an example of a monthly SIP of Rs 5,000 on the 1st of every month. If the SIP date falls on a public holiday or a weekend, the SIP purchase will take place on the next business day. To calculate XIRR, enter details of all SIP purchases and the current value or the redemption value along with the date in the Excel columns. In the cell below, type in the formula =XIRR(B2:B14,A2:A14)*100 and hit Enter. The XIRR in the below example is 15.77%.
XIRR calculation in Excel
| A | B | |
| 1 | Transaction Date | Cash Flows |
| 2 | 1-Jan-21 | -5000 |
| 3 | 1-Feb-21 | -5000 |
| 4 | 1-Mar-21 | -5000 |
| 5 | 1-Apr-21 | -5000 |
| 6 | 3-May-21 | -5000 |
| 7 | 1-Jun-21 | -5000 |
| 8 | 1-Jul-21 | -5000 |
| 9 | 2-Aug-21 | -5000 |
| 10 | 1-Sep-21 | -5000 |
| 11 | 1-Oct-21 | -5000 |
| 12 | 1-Nov-21 | -5000 |
| 13 | 1-Dec-21 | -5000 |
| 14 | 31-Dec-21 | 65,000 |
| 15 | SIP returns (XIRR) | 15.77% |
(For illustration purpose only)
To conclude
As we can see in the illustration above, XIRR is an easy and useful tool for calculating SIP returns. If you have multiple mutual fund transactions then you should use XIRR to calculate the return on your investment instead of CAGR. As an investor, it is important to use the right method to calculate the returns, which will give you an accurate picture of the growth of your investments.
This article first appeared on PersonalFN here
