What is XIRR in Mutual Funds

What Is XIRR in Mutual Funds

Investments 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 time periods, you can estimate the returns using XIRR in mutual fund

In this article, you will get the complete detail 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 calculate by absolute return for per yield calculation or CAGR. The SIP paid in the preceding month has to reduce 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 use MS Excel to Calculate XIRR?

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 inbuilt 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.

SIPDateInvestments
500001/01/2022-5000
500002/01/2022-5000
500003/01/2022-5000
500004/01/2022-5000
500005/01/2022-5000
500006/01/2022-5000
500007/01/2022-5000
500008/01/2022-5000
500009/01/2022-5000
500010/01/2022-5000
500011/01/2022-5000
500012/01/2022-5000
 01/01/202364032

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. And Click Enter, and you will get the XIRR (Accurate returns) of the investment over the period.

XIRR in Mutual funds using Spread Sheet

Calculation of XIRR in Sheet

Things to consider while using a spreadsheet to calculate XIRR,

  1. Use only the XIRR function. Never use related functions, it will end with incorrect results.
  2. Use proper format for the date.
  3. Also, mention investments with a minus (-) symbol.
  4. 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 used 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 between 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 has 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 calculate 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 or 30, or 31 days. Besides this, government holidays, and weekends can push the investment to the 8th or 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 few difference between CAGR and 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 them. But, the returns should be calculated with XIRR (if it has multiple transactions and multiple timelines).
  3. CAGR calculates the returns of the Lumpsum, and on other hand, XIRR calculates the performance of the cash flow.