XIRR in Mutual Funds

What is XIRR?

XIRR or extended internal rate of return is a measure of return which is used when multiple investments have been made at different points of time in a financial instrument like mutual funds. It is a single rate of return when applied to all transactions (investments and redemptions) would give the current rate of return. It can be calculated only in excel.

XIRR is a very useful tool for calculating returns. There are series of investments in a SIP and sometimes one can redeem a little from their investments and sometimes several months’ investments are skipped as one has the option to pause their SIPs. Calculating the returns, in this case, gets easy.

Why XIRR is used for Mutual Funds?

Investment cash flows, be it in or out, they are never evenly spaced out. Sometimes, there are late deposits or early withdrawals. A couple of months are skipped in a row, and in such cases calculating the return from investment becomes difficult. While calculating returns, amount invested and time determine the output differently.

Similarly, for Mutual Fund investments be it SIP, lump sum or withdrawals through SWP or lump sum XIRR will help in calculating the returns upon considering all the irregularities.

How to calculate XIRR Using Excel?

XIRR is calculated in Excel. All one needs is the date on which the transaction happened and the amount of transaction.

The excel function for XIRR is as follows:

XIRR = XIRR (value, dates, guess)

Value is the transaction amounts – Investment and redemption.

The Date is the date on which the transaction happened.

Guess is the approximate returns.

In an excel make two columns one with the dates and the other with the transactions. The investments are usually written in negative as you made a payment. The redemption amounts should be in positive as you received the amount. The last row should have the current value of the portfolio and the current date. Now insert the formula in a new cell and select values first then the dates. One can always skip the guess. Multiply the answer by 100 to the actual return. The table below shows the cash flows of an investor in a mutual fund from 2017 to 2019.

Dates	Values	Dates	Values
January 5, 2017	-5000	March 5, 2018	-5000
February 5, 2017	-5000	April 5, 2018	-5000
March 5, 2017	-5000	May 5, 2018	-5000
April 5, 2017	-5000	June 5, 2018	-5000
May 5, 2017	-5000	July 5, 2018	-5000
June 5, 2017	-5000	August 5, 2018	-5000
July 5, 2017	-5000	September 5, 2018	-5000
August 5, 2017	-5000	October 5, 2018	-5000
August 6, 2017	10000	November 5, 2018	-5000
September 5, 2017	-5000	December 5, 2018	-5000
October 5, 2017	-5000	January 5, 2019	-5000
November 5, 2017	-5000	February 5, 2019	-5000
December 5, 2017	-5000	March 5, 2019	-5000
January 5, 2018	-5000	April 5, 2019	140000
February 5, 2018	-5000	XIRR	10.39%

Why can’t you use CAGR instead?

The most common misconception is that CAGR and XIRR are the same. However, there are quite a few properties that make both of them different. CAGR usually calculates point to point returns and ignores the cash inflows and outflows in an investment period. XIRR considered every cash inflow and outflow while calculating the return.

• XIRR is the average rate earned by every cash flow during the period. While CAGR is the compounded annual growth rate.
• XIRR considers irregular cash flows. While CAGR, considers only the initial value, end value and investment duration. CAGR calculates point to point returns.
• XIRR calculates the annualized return. While CAGR calculates the absolute and annualized return.
• XIRR is measured using multiple cash flows. While CAGR is usually used for lump sum investments.

Explore: CAGR Calculator

Similarities and differences between XIRR and CAGR

• For lump sum amounted invested only once for a year gives the same return using both XIRR and CAGR.
• The same annual return throughout the investment period, return using both XIRR and CAGR will be same (Like Bank FDs).
• For multiple cash inflows and outflows, both XIRR and CAGR will be different.
• For varying returns, such as returns from mutual funds, XIRR and CAGR are different.

Both XIRR and CAGR are used to calculate returns from mutual funds. CAGR is usually used for lump sum investments and XIRR is used for SIP investments. As an investor, it is always better to have knowledge about return calculations so that you don’t have to depend on others for the same.