30 May 2020

7 minutes

We invest a certain amount every year in some financial instruments like mutual funds. After 5 years, 10 years or 15 years, we may want to redeem our investments.

At that time, we usually calculate the money which we originally invested with the maturity amount that we received to understand how much did we gain. But do you think this is the right method to check how much did we gain?

Returns have always been the basic benchmarks for investors, while going for any investments. These indicate how much the fund has lost or gained during particular investment duration.

You may come across returns expressed in a variety of ways and nomenclatures.

Mathematically**,**

*XIRR is that single rate of return, which when applied to every installment (and redemptions if any) would give the current value of the total investment.*

XIRR is your personal rate of return. It is your actual return on investments.

XIRR stands for Extended Internal Rate of Return is a method used to calculate returns on investments where there are multiple transactions happening at different times.

In case of SIP, since there are multiple investments (therefore multiple purchase prices) and different time periods for each installment, calculating returns is a bit tricky. Returns on mutual fund SIP is commonly done in terms of XIRR.

It is called internal because the calculation does not take into consideration external factors like financial risks, risk-free rate of return, the cost of capital, etc.

Suppose you invest SIPs of ₹ 4000, ₹ 9000, ₹ 5000, ₹ 4000 and ₹ 6500 in 5 years and receive ₹ 53,000 at the end of 5 years then your return on investment is 22%. Since the investments are made after equal intervals, this return is the Internal Rate of Return or IRR.

However, investments in mutual funds are not usually made at equally spaced intervals. Most investors tend to invest when they have surplus funds available (except in the case of a SIP).

Irregularly spaced investments lead to cash inflows and outflows at different times. Therefore, in addition to the invested amount, the time of the investment also assumes significance to yield a certain outcome.

Here the concept of Extended Internal Rate of Return (XIRR) is applied.

In other words,, XIRR is a function to calculate returns when your cash flows ( investments or redemption) are spread at irregular intervals over time.

In the case of mutual funds, if you are investing using SIP or lump-sum or redeeming through SWP or lump sum, XIRR can take care of all those scenarios and helps you calculate a consolidated return considering timings of your investment and withdrawals.

XIRR can be easily calculated using Microsoft Excel. Excel provides an inbuilt function to calculate XIRR.

XIRR is a powerful function in excel for calculating annualized yield for a schedule of cash flows occurring at irregular periods.

XIRR formula in excel is: *= XIRR (value, dates, guess)*

1.Enter all your transactions in one column. Mark all outflows like investments, purchases negative and all inflows like redemption’s positive.

2.In the next column add the corresponding dates of the transaction.

3.In the last row mention the current value of your holding and the current date

4.Now Use XIRR function in excel which is something like this =XIRR (values, date, Guess),

5.Select values to a series of cash flows that corresponds to a schedule of payments in dates and date columns stand for the date when the first investment was made and when the cash flows were received, guess parameter is optional ( if you do not put any value Excel use a value of 0.1)

For this calculation you need is with an example of six-month SIP. Let

SIP amount = ₹ 5000

SIP investment dates = start-01/01/2017, end-01/06/2017

Redemption date = 01/07/2017

Maturity amount = ₹ 31000

Assume we have a set of cash flows like those in the table below :

01-01-2017 |
-5000 |

03-02-2017 |
-5000 |

01-03-2017 |
-5000 |

11-04-2017 |
-5000 |

01-05-2017 |
-5000 |

25-06-2017 |
-5000 |

01-07-2017 |
31000 |

11.92429 |

In the above table, the cash flows are occurring at irregular intervals. HenceHere, you can use the XIRR function to compute the return for these cash flows. Remember to include the ‘minus’ sign whenever you invest money.

- In column A, enter the transaction dates on the left side.
- In column B, enter SIP figure of 5000 as a negative figure as it’s an outflow cashflow.
- Against the redemption date (Column A), enter the redemption amount (Column B) (31000) .
- In the box below 31000, type in: “ =XIRR (B1: B7, A1: A7)*100 ” and hit enter

XIRR value of 11.92 % will be display as a result.

When we want to invest in a mutual fund, we first check its returns of the past 3 years, 5 years, etc. These returns are point-to-point returns and are called Compounded Annual Growth Rate or CAGR.

Read whether you should invest based on past returns

For example, 3-year returns of 12 % for a mutual fund X will mean that ₹10,000 invested exactly 3 years ago have now become ₹14,049.28.

CAGR can be simply calculated by using the formula :

*CAGR = ((Ending Amount/Beginning Amount)^(1/No. of years)) – 1*

This typical metric is used in calculating returns of investment in a lot of mutual funds. While it is easy to calculate CAGR for a mutual fund but for personal investments it becomes a little tricky.

Let’s say you make a monthly SIP of ₹ 10,000 for 3 years. So, you have invested in 36 installments and at the end of 3 years, your portfolio value is ₹ 400000.

At the end of the 36 months if you want to calculate your portfolio return you will have to calculate CAGR for 35 months, 34 months, 33 months for different investments you made and hence quite complicated.

XIRR makes this simpler by calculating one return for your investments. So, if you are looking to calculate returns on your mutual fund investments XIRR might be the right way to go.

XIRR calculates the internal rate of return for a series of cash flows in a non-periodic manner. An investor usually uses this function to calculate the actual rate of return on his investments. XIRR calcuates the return by using a trial and error method to satisfy the equation:

Where:

- P is the cash flow
- d is the date
- i is the period number
- n is the total number of periods

Hence, when you use this function, if the returns are negative, then XIRR will be negative and vice versa. You will never get positive XIRR for negative overall returns.

As you can see from the above examples, XIRR is the right way to find out your investment returns in real life. CAGR is important to check for selection of a mutual fund but XIRR is critical to evaluate the returns you got from your investments.

And IRR is used for investments in case of cash flows that are equally spaced in time, but usually, investments are not as evenly spaced as you saw above in case of mutual funds.

So, when there is a series of investments being made over time, including transactions such as withdrawals, dividends, switch, etc. the better way to calculate the return is with XIRR. XIRR works much better for calculating returns from your mutual fund as compared to IRR and CAGR.

On Groww dashboard, XIRR is showed for each investment ( SIP or Lump Sum).

*Happy investing!*

*Disclaimer: views expressed here are of the author and do not reflect those of Groww. *

Do you like this edition?

LEAVE A FEEDBACK

Want to invest?

Open a Groww account & start investing. It's fast & 100% free.

GET STARTED