- 2 views
One may invest in mutual funds in two modes – lumpsum or staggered manner, i.e., through Systematic Investment Plans (SIPs)/Systematic Transfer Plans (STPs). When one invests in lumpsum, the investment is done in one go, while through SIP/ STP, one makes investments in instalments in a designated scheme.
It’s suggested to review one’s portfolio regularly and keep a watch on the funds; measuring the performance might be tricky when the investments are made in lumpsum mode but spread across various time points. Also, when the investments are made through SIP, the regular measurement of point-to-point returns may not reflect an accurate picture.
When evaluating the performance of a specific mutual fund scheme or the investment portfolio, there are several methods to measure investment performance, viz. Compounded Annual Growth Rate (CAGR), Internal Rate of Return (IRR), Extended Internal Rate of Return (XIRR), etc.
CAGR is used to calculate point-to-point returns for mutual fund investments made over a longer term. CAGR is calculated through the following formula for a specified time period
CAGR = (NAV at the end of the period/ NAV at the beginning of the period) ^ (1/ years) – 1
However, CAGR method of calculating returns may not be suitable for calculating mutual fund returns, as it is suitable for investments made in lumpsum.
Why does XIRR make sense for Mutual Fund investments?
The investments in mutual funds are not linear and may be made at different periods and in different amounts. One may have made lumpsum investments in mutual funds while also investing regularly through Systematic Investment Plans (SIP). This is where Extended Internal Rate of Return (XIRR) method may be more suitable to know the return value. This is because XIRR allows investors the flexibility to input cash flows with differing frequencies and amounts. This allows them to calculate the investment performance in one go for the period one has invested.
Example of XIRR in Mutual Funds
The below example illustrates the XIRR calculation in mutual funds: If an investor puts Rs. 10,000 on the 1 st of every month through SIP mode, the total cost of investments at the end of the year would Rs. 1,20,000 at different NAVs. If the NAV at the beginning of the year is Rs. 10 and the NAV at the end of the year is Rs. 11, the investor may think the investments have appreciated by 10%. However, it is not correct. XIRR will help the investors to calculate the time-weighted returns. The actual XIRR returns generated by the investor are only 6.56%.
| Date | Particulars | Cash Flows | NAV | No. of Units | Current Value |
| 01-01-2021 | Investment | 10,000.00 | 10.00 | 1,000.00 | 11,000.00 |
| 01-02-2021 | Investment | 10,000.00 | 10.20 | 980.39 | 10,784.29 |
| 01-03-2021 | Investment | 10,000.00 | 10.15 | 985.22 | 10,837.42 |
| 01-04-2021 | Investment | 10,000.00 | 10.00 | 1,000.00 | 11,000.00 |
| 01-05-2021 | Investment | 10,000.00 | 9.80 | 1,020.41 | 11,224.51 |
| 01-06-2021 | Investment | 10,000.00 | 9.50 | 1,052.63 | 11,578.93 |
| 01-07-2021 | Investment | 10,000.00 | 11.50 | 869.57 | 9,565.27 |
| 01-08-2021 | Investment | 10,000.00 | 12.00 | 833.33 | 9,166.63 |
| 01-09-2021 | Investment | 10,000.00 | 11.10 | 900.90 | 9,909.90 |
| 01-20-2021 | Investment | 10,000.00 | 10.80 | 925.93 | 10,185.23 |
| 01-11-2021 | Investment | 10,000.00 | 11.50 | 869.57 | 9,565.27 |
| 01-12-2021 | Investment | 10,000.00 | 11.70 | 854.70 | 9,401.70 |
| 31-12-2021 | Redemption | 1,24,219.15 | 11.00 | 11,292.65 | 1,24,219.15 |
| XIRR | 6.56% |
The above example, table and calculations are for illustration purpose only.
Calculation of XIRR
XIRR refers to that rate that can singly represent the investment returns over time. It can calculate the IRR for different cash flows happening at different periods.
XIRR can be calculated through Microsoft Excel's built-in function. One can calculate XIRR manually by considering each of the investments as separate investments and calculating the CAGR for each. However, CAGR methods may not be effective for calculating investment returns when there are large number of purchase/sale transactions. Therefore, the XIRR method may be well suited to measure portfolio performance in such cases.
Microsoft Excel also provides an in-built function for calculating XIRR. The investors need to plot the investment data with different dates, the amount of respective investments, and the latest portfolio valuation.
The formula used for XIRR calculation is as below:
=XIRR (values, dates, guess)
In this function, the variable ‘values’ represents the range of data for inflows & outflows, with inflows shown as positive cash flows and outflows as negative cash flows. The variable ‘dates’ represents the data range for dates for each cash flow, and the variable ‘guess’ is an optional parameter for the expected IRR estimate.
If the parameter is left blank, MS-Excel uses the default value of 0.10. Once the dates and cash flows have been plotted in a table in the excel worksheet, it is easier to calculate XIRR through the in-built function.
Step by step process to calculate XIRR in Microsoft Excel
Here is the step-by-step process to calculate XIRR in Microsoft Excel:
- Plot the data table with all the investment amounts entered as negative figure while the current investment value entered as a positive figure with current date.
- First column of the table should be the date values, while the second column of the data table should be the cash flows.
- Enter the XIRR formula referring the cash flows data range as values. One should further ensure that the date range should correspond to the values range. For example, if the values range has 10 rows, the date range should also have the same 10 rows. In case of any mismatch in the values range and data range, the formula will show an error.
- Once the formula has been entered, press enter to calculate the XIRR amount. While the default result is shown in number format, multiply the figure by 100 to calculate the XIRR value in percentage terms. Alternatively, one can format the cell as percentage formatting to display the result in percentage terms.
Example of How to use the XIRR function in Microsoft Excel
Going by the above example, here is the data range to be used for XIRR function:
| A | B | C | |
| 1 | Date | Particulars | Cash Flows |
| 2 | 01-01-2021 | Investment | -10,000.00 |
| 3 | 01-02-2021 | Investment | -10,000.00 |
| 4 | 01-03-2021 | Investment | -10,000.00 |
| 5 | 01-04-2021 | Investment | -10,000.00 |
| 6 | 01-05-2021 | Investment | -10,000.00 |
| 7 | 01-06-2021 | Investment | -10,000.00 |
| 8 | 01-07-2021 | Investment | -10,000.00 |
| 9 | 01-08-2021 | Investment | -10,000.00 |
| 10 | 01-09-2021 | Investment | -10,000.00 |
| 11 | 01-10-2021 | Investment | -10,000.00 |
| 12 | 01-11-2021 | Investment | -10,000.00 |
| 13 | 01-12-2021 | Investment | -10,000.00 |
| 14 | 31-12-2021 | Redemption | 1,24,219.15 |
| 15 | XIRR | 6.56% |
XIRR formula to be used in Microsoft Excel is =XIRR(C2:C14,A2:A14,) which will display the result in C15 as 6.56%.
Benefits of XIRR
XIRR method provides the following benefits for the evaluation of investment performance:
Useful for irregular cash flows
XIRR is useful for calculating the investment returns made on different dates. This is the most prominent utility of using the XIRR method of reviewing investment performance.
Universal performance evaluation
Since no specific time duration is embedded in the XIRR calculation, the investors can use such a method for lump sum and SIP investments. XIRR can replace CAGR and IRR calculations to calculate investment performance, even when the investments are made at regular intervals.
Suitability for different mutual fund transactions
XIRR method can consider both inflows and outflows within the data range. XIRR can help determine the investment returns for mutual fund transactions like SIP, STP, Systematic Withdrawal Plan (SWP), etc.
XIRR in account statements (AS)
Since the XIRR method is universally used for calculating investment performance, it is also shown in the Account Statement (AS) shared by the mutual fund company with the investors.
The summary of the mutual fund investments with an average investment cost per unit, total units, total investments, total valuation, and annualised returns are mentioned. Such annualised returns are computed using the XIRR method, considering the NAV appreciation and dividend received during the investment period.
With comparable investment performance duly considering the investment period, the investors can use the XIRR method to review the investment performance across different mutual fund schemes. This can help the investors make informed investment decisions accordingly.
Why can’t you use CAGR instead? With a solved example:
CAGR can only be used for lumpsum investments, since it can only calculate point-to-point returns for the investors. As such, it is not suitable for SIP investments. Further, even if CAGR is calculated for each investment separately and then average the CAGR returns of individual transactions, the resultant figure does not reflect the correct picture.
Continuing with the above example, the calculations are as below:
| Date | Particulars | Cash Flows | NAV | No. of Units | Current Value | CAGR |
| 01-01-2021 | Investment | 10,000.00 | 10.00 | 1,000.00 | 11,000.00 | 10.03% |
| 01-02-2021 | Investment | 10,000.00 | 10.20 | 980.39 | 10,784.29 | 8.63% |
| 01-03-2021 | Investment | 10,000.00 | 10.15 | 985.22 | 10,837.42 | 10.10% |
| 01-04-2021 | Investment | 10,000.00 | 10.00 | 1,000.00 | 11,000.00 | 13.54% |
| 01-05-2021 | Investment | 10,000.00 | 9.80 | 1,020.41 | 11,224.51 | 18.86% |
| 01-06-2021 | Investment | 10,000.00 | 9.50 | 1,052.63 | 11,578.93 | 28.56% |
| 01-07-2021 | Investment | 10,000.00 | 11.50 | 869.57 | 9,565.27 | -8.48% |
| 01-08-2021 | Investment | 10,000.00 | 12.00 | 833.33 | 9,166.63 | -18.86% |
| 01-09-2021 | Investment | 10,000.00 | 11.10 | 900.90 | 9,909.90 | -2.69% |
| 01-10-2021 | Investment | 10,000.00 | 10.80 | 925.93 | 10,185.23 | 7.64% |
| 01-11-2021 | Investment | 10,000.00 | 11.50 | 869.57 | 9,565.27 | -23.69% |
| 01-12-2021 | Investment | 10,000.00 | 11.70 | 854.70 | 9,401.70 | -52.79% |
| 31-12-2021 | Redemption | 1,24,219.15 | 11.00 | 11,292.65 | 1,24,219.15 | |
| XIRR | 6.56% | |||||
| Average of CAGR | -0.76% |
While the simple average of CAGR reflects returns of negative 0.76% for the investors, the actual returns generated by the investors have been 6.56%.
The above example, table and calculations are for illustration purpose only.
Disclaimer-
Mutual Fund investments are subject to market risks, read all scheme related documents carefully.
To know about the KYC documentary requirements and procedure for change of address, phone number, bank details, etc. please visit https://www.utimf.com/servicerequest/kyc. Please deal with only registered Mutual funds, details of which can be verified on the SEBI website under "Intermediaries/market Infrastructure Institutions". All complaints regarding UTI Mutual Fund can be directed towards service@uti.co.in and/or visit www.scores.gov.in (SEBI SCORES portal). This material is part of Investor Education and awareness initiative of UTI Mutual Fund.
