What is XIRR in Mutual Funds: Calculation & Formula in Excel
XIRR or Extended Internal Rate of Return helps calculate the return on mutual fund investments made through SIP (Systematic Investment Plan). CEO of Navi Mutual Fund, Hari Shyamsunder says, “Your investments should always be compared against a possible alternative. But to make that comparison, you must understand the basic concept of XIRR.”
This article is a beginner’s guide to understanding the XIRR meaning in mutual funds, its importance and the calculation of your SIP returns. Read on!
What is XIRR in Mutual Funds?
XIRR Meaning – XIRR (Extended Internal Rate of Return) can be defined as a computation method for calculating returns of mutual fund investments at irregular intervals. It can also be defined as a single rate of return which can be applicable for every instalment and redemption. You can use it to have an understanding of the current value of all your investments.
When you invest in a scheme via SIP (Systematic Investment Plan), you need to pay multiple instalments. You can easily calculate the returns of these investments with the help of IRR (Internal Rate of Return), but, you need to make sure that the time frame between the consecutive cash flows remains the same.The XIRR method simplifies the entire process of calculating returns even in the case of irregular investments. You can simply use an excel sheet to calculate your investment returns. Here, the XIRR formula will modify the IRR and enable you to allocate separate dates to individual cash flows.
XIRR Formula and Calculation
To calculate XIRR in mutual funds, you have to insert the transactions (additional purchases, SIP/SWP instalments, redemption) and the corresponding dates in the designated place in an MS Excel sheet. These transaction details will be available in the statement of account sent by the AMC (Asset Management Company).
To calculate a mutual fund’s XIRR, you can use the XIRR formula in MS Excel:
“= XIRR (values, dates, guess)”
Here, you have to insert cash outflows (lump-sum purchases and SIP instalments) as negative values (i.e. put a minus sign before the amount) and cash inflows (dividends, SWP, redemptions) as positive values. ‘Guess’ is an optional input. It is considered to be 0.1 by default.
If you have not redeemed your mutual fund units, then, to calculate XIRR, you have to enter the current investment value with the NAV (Net Asset Value) of your mutual fund investment.
Do not include transactions like dividend reinvestment because they do not involve actual cash flows. Moreover, if you are calculating the XIRR at the level of schemes, then switches should be treated as redemption. But, if you are calculating XIRR in mutual funds as portfolio level, then switches are not relevant in the calculation.
Also Read
Example of XIRR Calculation in Excel
Here is an example of a six-month SIP to calculate XIRR in mutual funds:
- SIP amount = Rs.5,000
- SIP investment dates: Start on 01-01-2017, End on 01-06-2017
- Date of redemption: 01-07-2017
- Maturity sum = Rs.31,000
Let’s assume cash flows as mentioned in the table below:
| 01/01/2017 | -5000 |
| 03/02/2017 | -5000 |
| 01/03/2017 | -5000 |
| 11/04/2017 | -5000 |
| 01/05/2017 | -5000 |
| 25/06/2017 | -5000 |
| 01-07-2017 | 31000 |
| XIRR | 11.92429 |
Let’s take a look at the calculation part below:
- In the left column, all the dates are mentioned.
- The right column includes your SIP instalments of Rs.5,000. It is marked as negative owing to the outflow of cash.
- In the left column, you can find the redemption date. Just beside that, there is a redemption amount of Rs.31,000.
- Use the XIRR formula: Insert the range of cells that represent the transaction values. After that, enter the series of payment dates with respect to the value of the cash flows.
You will have your XIRR value of 11.92 %, as mentioned in the table.
Why is XIRR Accurate for Mutual Funds?
Let us assume that you had started investing in a scheme via a monthly SIP of Rs.15,000 and then continued the investment for 5 years. Supposed, after 5 years, the value of the investment grew to Rs.12 lakh.
Here, the annual return of the first month’s instalment will be different from the rest as it had remained invested for the maximum number of months. In simpler words, the CAGR of each instalment will differ as their investment period will be different.
Analysing the performance of a mutual fund will become complicated when you look at the CAGRs of these instalments. To make the task easier, all the CAGRs are adjusted together to a common CAGR which is then considered as a mutual fund’s XIRR.
XIRR vs CAGR – Can we use CAGR Instead of XIRR?
This is a common misconception that CAGR and XIRR are the same. CAGR is the accurate calculation of returns on every instalment, which doesn’t take inflows and outflows into consideration. In contrast, XIRR means the overall average of the CAGRs and takes into account every inflow & outflow of cash.
Here are some simplified pointers to make their differences obvious:
| XIRR | CAGR |
| XIRR meaning is an average annualised return. | CAGR is the absolute annualised return. |
| XIRR takes into account every cash inflow and outflow. | CAGR is calculated only considering the initial value, absolute value and investment tenure. |
| XIRR is calculated when there are multiple cash flows; hence, it can be good for SIP investments. | CAGR is ideal for lump-sum investments. |
| XIRR means an average of every return earned by each cash flow during tenure. | CAGR is the compounded growth rate. |
Final Word
XIRR is the most comprehensive way of determining your returns in case of multiple transactions, especially when your investment time is irregular. In other words, allocating your money to mutual funds via an SIP will lead to cash outflows and cash inflows. In such cases, the time of investment also plays a significant role in the calculation of the returns. To that end, the role of Extended Internal Rate of Return (XIRR) in SIP becomes essential.
If you’re looking to explore diverse low-cost funds, check out Navi Mutual Fund. Download the Navi app to start SIP at just Rs.10 per month.
Disclaimer: Mutual Fund investments are subject to market risks, read all scheme related documents carefully.
FAQs
If you wish to calculate XIRR in mutual funds, you need to enter all transactions and dates in corresponding columns in an MS Excel sheet. Then, you have to enter the following formula: “= XIRR (values, dates, guess). Cash outflows have to be entered as negative outflows and cash inflows as positive.
The formula for CAGR is as follows:
CAGR ={(Ending balance/beginning balance)^1/n} – 1
Where, beginning balance = Investment value at the beginning of the investment period
Ending balance = Investment value at the end of your investment
N = Total number of years you have invested in a mutual fund
IRR (Internal Rate of Return) is described as a discounting cash flow technique which provides the rate of return that a mutual fund has earned. Here, both the discounted cash inflow and the total initial cash outlay are equal to zero.
IRR is used to determine returns against investments made at fixed intervals. But investments and redemptions are never regular. In that case, XIRR is the right choice.
A SIP or Systematic Invest Plan is a tool to invest in mutual funds. It is a systematic way to invest a fixed sum of money in a periodical manner. It is also a very safe way to invest.
Return on Investments for National Pension Scheme gives the annualised effective compounded return rate in the PRAN account and is calculated using the formula of XIRR. The calculation is done considering all the contribution/redemptions processed in the PRAN account since inception and the latest valuation of the investments.
XIRR Return is the returns on your mutual fund investments made at irregular intervals, like SIP investments. XIRR calculates the CAGR of every SIP investment made. These are then added together to give you the overall CAGR.
CAGR gives you the value of your annual return over a period of time longer than one year. But in the case of calculating returns on frequent investments or cash flows during different time periods, such a method is not apt as it does not consider multiple cash flows. In such cases, XIRR is an ideal option.
The XIRR function in MS Excel always computes an annualised IRR even if an individual calculates returns on weekly/monthly cash flows.
XIRR stands for Extended Internal Rate of Return.
Want to put your savings into action and kick-start your investment journey 💸 But don’t have time to do research? Invest now with Navi Nifty 50 Index Fund, sit back, and earn from the top 50 companies.
Disclaimer: Mutual Fund investments are subject to market risks, read all scheme-related documents carefully.
This article has been prepared on the basis of internal data, publicly available information and other sources believed to be reliable. The information contained in this article is for general purposes only and not a complete disclosure of every material fact. It should not be construed as investment advice to any party. The article does not warrant the completeness or accuracy of the information and disclaims all liabilities, losses and damages arising out of the use of this information. Readers shall be fully liable/responsible for any decision taken on the basis of this article.
