Table of Contents
ToggleInvestments are made to grow wealth with better returns. But how do calculate the returns? Be it shares, mutual funds, or any investment, SIP or Lumpsum are the two modes of payment. We usually calculate the returns by CAGR, IRR, and XIRR.
Most of the investments made in mutual funds are SIP (systematic investment plan) or dollar cost average. SIP is the concept of investing regularly, at different timelines.
Hence, to calculate the returns of the investment at different periods, you can estimate the returns using XIRR in a mutual fund
In this article, you will get the complete details on What is XIRR in mutual funds return calculation. How does it differ from CAGR and IRR? When you should calculate a return using XIRR?
What is XIRR?
XIRR (Extended Internal Rate of Return) is one of the terminologies used to calculate the performance of any investment when multiple transactions are made with different timelines.
XIRR helps in calculating the accurate rate of returns of the investment. In contrast, absolute returns (CAGR) calculate the returns using initial investment, current value, and the number of years.
Absolute returns are the way to calculate returns only if you invest Rs. 1 lakhs 10 years before and calculate the returns for the last 10 years.
As in SIP, only the amount invested in the 1st month of the year is eligible to be calculated by absolute return for per yield calculation or CAGR. The SIP paid in the preceding month has to be reduced by one month from the full term (assuming 1 year).
For example,
1st SIP – covers 12 months in a year
2nd SIP – covers 11 months in a year
3rd SIP – covers 10 months in a year, and so on.
In this case, there is no possibility of calculating the absolute return on the total amount invested in a year.
Here comes XIRR to calculate the accurate returns of any investment when there is a different amount or timeline used in an investment.
XIRR Formula in Mutual Funds
Mutual fund investments are subjected to multiple transactions at different transactions. So, XIRR is the metric to calculate the fund performance.
Calculating XIRR is quite easy with Excel or Google Sheets.
Formula: =XIRR(values, date, [guess])
Values (Mandatory field) – It holds the series of values invested in any fund for a provided duration.
Date (Mandatory field) – This field holds the dates of investments. The first field will be the first payment, and as follows.
Guess (Optional field) – An initial guess or estimate of IRR. In default, excel takes 10% as the guess.
How to Calculate XIRR in Excel?
Here are a few steps to calculate the XIRR with the help of Microsoft Excel to validate the returns of SIP investment for a particular duration.
Microsoft Excel or Google Sheets calculate XIRR in mutual funds and SIP investments using the same steps. These two spreadsheets are built with XIRR functions.
Step 1 – Open MS Excel or Google Sheet.
Step 2 – Create two columns. One if for entering the dates of SIP investments. Another column should contain the number of investments done on respective dates.
Step 3 – Provide a negative sign (-) before the invested amount. Make a positive sign for the returns.
For example, the SIP investments are -5000, and the return is 65000.
SIP | Date | Investments |
5000 | 01/01/2022 | -5000 |
5000 | 02/01/2022 | -5000 |
5000 | 03/01/2022 | -5000 |
5000 | 04/01/2022 | -5000 |
5000 | 05/01/2022 | -5000 |
5000 | 06/01/2022 | -5000 |
5000 | 07/01/2022 | -5000 |
5000 | 08/01/2022 | -5000 |
5000 | 09/01/2022 | -5000 |
5000 | 10/01/2022 | -5000 |
5000 | 11/01/2022 | -5000 |
5000 | 12/01/2022 | -5000 |
01/01/2023 | 64032 |
Step 4 – Use the formula of XIRR, as mentioned in the previous section. Under the open bracket, select all the columns of the invested amount. Followed by a space and comma, select all the dates. Click Enter, and you will get the XIRR (Accurate returns) of the investment over the period.
Things to consider while using a spreadsheet to calculate XIRR,
- Use only the XIRR function. Never use related functions, it will end with incorrect results.
- Use proper format for the date.
- Also, mention investments with a minus (-) symbol.
- While selecting data for the XIRR formula, use the value (investments) first, followed by dates. It will end with an error if used in reversal.
Difference between CAGR, IRR, and XIRR:
CAGR, IRR, and XIRR are mathematical derivatives used to calculate the return on investment for a certain period.
These formulas are generally used while calculating the returns of stocks, mutual funds, bonds, and cryptos investments.
But, how it differs? It is easy to calculate a return if it has only one transaction of investments and redemption.
Simple as ((Final Amount-Initial Amount)/Initial Amount)*100
It is how businesses calculate growth over two years. This way of calculating is known as CAGR (Compounded Annual Growth Rate).
CAGR = ((Final Value/Initial Value)^(1/n))*100
^ – Power of
n- Duration of investment
This way of calculation provides you with the compounded growth of the investment.
What to do, if the investments are made through SIP? It has multi-transaction in different intervals. What if, CAGR is limited to calculating multi-transactions?
Here comes IRR and XIRR.
Both the terminologies do the same in calculating the returns of multiple-transaction but IRR has a limitation, and hence XIRR is the optimal derivate to calculate returns of SIP transactions.
IRR can only be calculated if the SIP is made on the same day. If you invest Rs. 5000 on the 7th of January, you should invest on the same 7th of every month.
But, it is not possible, as some months have 28, 30, or 31 days. Besides this, government holidays, and weekends can push the investment to the 8th 9th, or 10th of the month.
So, IRR (Internal Rate of Return) can’t be used to calculate the return. But, XIRR can calculate the return. Hence it is called Extended IRR.
Here are a few differences between CAGR and XIRR:
CAGR | XIRR | |
1 | CAGR helps in calculating the compounding (absolute) growth. | XIRR helps in calculating accurate returns of multiple transactions. |
2 | CAGR is useful in assessing a mutual fund’s performance, before choosing it. | The returns should be calculated with XIRR (if it has multiple transactions and multiple timelines). |
3 | CAGR calculates the returns of the Lumpsum, | on the other hand, XIRR calculates the performance of the cash flow. |