loader2
Partner With Us NRI

Open Free Trading Account Online with ICICIDIRECT

Incur '0' Brokerage upto ₹500

XIRR in Mutual Funds: What it is & How to Calculate it

10 Mins 25 Jul 2023 0 COMMENT

Mutual funds have become a popular means of investing for the masses, thanks largely to the 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.

Usually, it is reasonable to expect 12 to 15% returns per annum from equity mutual funds and 5 to 7% returns from debt mutual funds, unless the yields are very low in an interest rate cycle.

If you are invested in a mutual fund, you can know what return your fund has delivered. The calculation is extremely simple. One 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).

Absolute returns, which are also called point to point returns, help in calculating simple returns on investment. This is suitable only for lump sum investments that are yet to complete one year.

CAGR makes more sense for investments over one year period. While simple returns will not give you how much a fund has returned every year, CAGR will. Crudely speaking, it is an average annualised return taking into account the impact of compounding. CAGR basically smoothens the return generated over a period.

What is XIRR?

Both CAGR and absolute returns are not suitable for calculating returns on a systematic investment plan (SIP) where you invest a sum at a periodic interval. In such cases the investment period and mutual fund units you hold vary significantly over time.

In such cases, you need to calculate XIRR. Mathematically, XIRR is 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 for example annually. 

XIRR is relatively much more flexible and allows a person to accommodate variations. It not just calculates the returns on SIP investments but can also take care of any lump sum investment that you made in the same fund during the period you were invested in.

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.

 

Disclaimer: ICICI Securities Ltd. (I-Sec). Registered office of I-Sec is at ICICI Securities Ltd. - ICICI Venture House, Appasaheb Marathe Marg, Prabhadevi, Mumbai - 400 025, India, Tel No : 022 - 6807 7100. I-Sec is a Member of National Stock Exchange of India Ltd (Member Code :07730), BSE Ltd (Member Code :103) and Member of Multi Commodity Exchange of India Ltd. (Member Code: 56250) and having SEBI registration no. INZ000183631. AMFI Regn. No.: ARN-0845. We are distributors for Mutual funds. Mutual Fund Investments are subject to market risks, read all scheme related documents carefully. Name of the Compliance officer (broking): Ms. Mamta Shetty, Contact number: 022-40701022, E-mail address: complianceofficer@icicisecurities.com. Investments in securities markets are subject to market risks, read all the related documents carefully before investing. The contents herein above shall not be considered as an invitation or persuasion to trade or invest.  I-Sec and affiliates accept no liabilities for any loss or damage of any kind arising out of any actions taken in reliance thereon. The contents herein above are solely for informational purpose and may not be used or considered as an offer document or solicitation of offer to buy or sell or subscribe for securities or other financial instruments or any other product. Investors should consult their financial advisers whether the product is suitable for them before taking any decision. The contents herein mentioned are solely for informational and educational purpose.