XIRR is a more powerful function in Excel for calculating annualized yield for a schedule of cash flows occurring at irregular periods
In one of our earlier articles, we had discussed, ‘What is IRR & how to calculate it?’ As mentioned in the article, IRR or internal rate of return is the interest rate received for an investment—consisting of money invested at the beginning of the investment period and cash flows received at ‘regular’ periods such as half-yearly or annually.
But what happens if you make an investment in the beginning of the investment period and you receive cash flows that are ‘not’ necessarily periodic such as dividends offered on mutual fund units. In this case how would you calculate the return on your investment? Using IRR to calculate the internal rate of return for a series of inconsistent cash flows is not the right method.
Let’s try to understand the concept of XIRR to find out the internal rate of return for a series of cash flows that is not periodic.
Demystifying XIRR
XIRR is a more powerful function in Excel for calculating internal rate of return or annualized yield for a schedule of cash flows occurring at irregular intervals. To compute XIRR on an Excel sheet is easy. Assume we have a set of cash flows like those in the table below:
Date |
Cash flows (Rs.) |
01-Jan-2007 |
-50,000 |
10-Jan-2007 |
500 |
01-Jun-2007 |
500 |
25-Oct-2007 |
500 |
31-Dec-2007 |
500 |
01-Mar-2008 |
500 |
15-Jun-2008 |
51,000 |
XIRR |
4.89% |
In the above table, the cash flows are occurring at irregular intervals. Here, you can use XIRR function to compute the IRR for these cash flows. In an Excel sheet, first enter the original amount invested. The amount invested should be represented by a ‘minus’ sign. In each cell enter the cash flows which received each period. Remember to include the ‘minus’ sign whenever you invest money. Now find out XIRR by mentioning =XIRR(values,dates,guess)
Values refer to a series of cash flows that corresponds to a schedule of payments in dates. The first payment refers to the investment made at the beginning of the investment period and must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive and one negative value.
Dates stand for the date when the first investment was made and when the cash flows were received. Each date should correspond to its respective investment made or cash flow received as shown in the above table. Dates should be entered in DD-MM-YY (date-month-year) format. Problems can occur if dates are not entered in the right format. If any number in dates is not a valid date, XIRR returns the #VALUE! error value.
One of the disadvantages of the IRR function is that it assumes a periodic cash flow and most financial instruments these days are generally giving irregular cash flows. This is where the XIRR comes into play. XIRR allows for uneven cash flow intervals by taking into account the dates of which a cash flow occurs.
Aug 08, 2022
Aug 08, 2022
Aug 08, 2022
Aug 08, 2022
Aug 08, 2022
Aug 08, 2022
Aug 08, 2022
Aug 08, 2022
Aug 08, 2022
Aug 08, 2022
Aug 08, 2022
Aug 08, 2022
Aug 08, 2022
Aug 08, 2022
Aug 08, 2022
The laws of the financial world are different from the physical world. You can have prolonged periods of time, when sanity takes a back seat and excesses happen.
R. Venkataraman Aug 20, 2021
Retail trading or day trading has exploded because of falling brokerage rates, democratization of information, higher transparency and mobile platforms.
R. Venkataraman Jun 15, 2021
My simple message for dear readers is, if you don’t have any desperate need for funds, then don’t do anything.
R. Venkataraman May 12, 2021
The blow up of a US hedge fund has resulted in WhatsApp university offering many courses on what went wrong with Bill Hwang and Archegos.
R. Venkataraman Apr 09, 2021
The expensive valuations have been sustained by strong rebound in corporate earnings which led to ~8% upgrade in FY22 Nifty EPS since October 2020.
R. Venkataraman Mar 26, 2021
We believe the interest rates are likely to have bottomed due to inflationary pressure, large government borrowings and normalizing credit growth. Hence rate sensitive sectors should be avoided in our view.
R. Venkataraman Feb 17, 2021
As markets make new highs, one gets more emails and messages, which highlight the accomplishments of traders who have found a formula for making money.
R. Venkataraman Jan 27, 2021
Data does not seem to convincingly prove that short periods of high returns are always followed by meagre returns. Only in 4 instances, we had negative returns in the subsequent year.
R. Venkataraman Jan 01, 2021
Since September end, Bankex is up 16% with large banks like ICICI Bank, Bandhan up 20-27%, Housing Finance Companies like Repco, LICHF, PNB Housing are up 50%-100% from their six-month lows.
R. Venkataraman Oct 13, 2020
Morgan Housel’s 'The Psychology of Money' explains in detail the role of human biases in investment decisions.
R. Venkataraman Sep 26, 2020
Per Order for ETF & Mutual Funds Brokerage
Per Order for Delivery, Intraday, F&O, Currency & Commodity