Partner With Us NRI

Open Free Trading Account Online with ICICIDIRECT

Incur '0' Brokerage upto ₹500

XIRR in Mutual Funds: What is XIRR, Calculation & Benefits

2 Mins 25 Jul 2023 0 COMMENT

Mutual funds have become a popular means of investing for the masses because of ease of investing, low ticket size, and relatively lower costs. The investment tool has become ‘go to’ avenue for those that want to get decent returns on equities but do not have time or expertise to manage their own portfolio.

If you are invested in a mutual fund, you can know what return your fund has deliveredFor calculating the returns,  you should know three kinds of interest rate calculations while investing in mutual funds – Absolute returns, Compounded Annual Growth Rate (CAGR) and Extended Internal Rate of Return (XIRR).

What is XIRR?

XIRR or extended internal rate of returnis a variation of the Internal Rate of Return (IRR), a metric used in financial analysis to estimate the profitability of potential investments. Since it is a variation, there are slight differences. IRR aggregates the cash flow into annual periods without taking into account the timing of the actual cash flow. At the same time, it is also assumed that cash flow is periodic, meaning it is happening at a set interval. Whereas, XIRR is more flexible and allows a person to accommodate variations. It not just calculates the returns on SIP investments but also accommodates lump sum investment made in the same fund during the period you were invested in.

XIRR Formula

The formula of XIRR is as follows:

XIRR = (NPV (Cash Flow, r)/Initial Investment) *100

How to calculate XIRR?

XIRR is calculated using Microsoft Excel or any similar application. It is tedious to calculate XIRR using pen and paper. However, you do not need to be an expert at MS Excel to calculate the XIRR of a mutual fund. Neither you need to know the net asset value (NAV) of the mutual fund you had invested in. All you need to know is your SIP amount, SIP dates and maturity amount and maturity date. The software will take care of the rest.

The sequence to calculate XIRR is as follows:

Step 1: Open a blank workbook on MS Excel

Step 2: In one column, write a header ‘Transaction Date’ and start entering the transaction date right below it. The transaction date here is when your mutual fund house credits units in lieu of your investments.

Step 3: In the adjoining column on the right, write the header ‘Cash Flow’. Now start adding the SIP amount against the respective dates. Keep in mind to use negative values for them, not positive ones. This is just to denote that cash is flowing out of your account, not coming in.

Step 4: Now, in the first column, write the date of redemption at the bottom, that is, in the cell just below the last transaction date. The date of redemption refers to the date on which you sold your investment. Now add the redemption amount that you received in the column against it. This should be a positive value as the money is flowing into your account.

Note that if you have not sold the investment but want to calculate the returns, you can simply add today’s date. In such a case, you need to know the total value of your investment as of this date. The total value can be calculated very simply by multiplying the latest NAV by the number of units of mutual funds you hold.

You can see the entire process in the example below:


Step 5: Now, in any cell, type ‘=’. This will invoke the formula function. Following this, type XIRR. Select the formula that appears by either pressing Tab on the keyboard or left clicking on the mouse. When the formula asks you to enter values, select all values in the Cash Flow column from top to bottom. Now, type a comma and select all dates as shown below.


Step 6: Close the bracket, and press Enter. Keep the cell you entered the formula in selected and click on per cent (%) button in the Number section under the Home ribbon. This will convert the result into a percentage. This is the XIRR, you are seeking.


In our example, the fund returned 14.87 per cent in the indicated period.


The formula of XIRR is as follows:

XIRR = (NPV (Cash Flow, r)/Initial Investment) *100