XIRR - What is XIRR and how to calculate it?

Many of us invest in mutual funds or similar financial schemes in installments (e.g. using SIPs) and want to evaluate the performance of our investments. For example, we want to know our overall rate of return so as to compare it to with other possibilities. The standard mechanism in such cases is to calculate either IRR or XIRR.

IRR means 'Internal rate of return' and XIRR means 'Internal rate of return for irregular cash flows'. Both IRR and XIRR are used to calculate rate of return where we buy/sell in installments. IRR is used for fixed interval investment - e.g., Rs 5000 invested in a mutual fund every month. XIRR allows for irregularity as well - e.g., Rs 10000 invested on Jan 3, Rs 5000 on Jan 27, Rs 3000 on Feb 15 and then sold on Nov 5 for 21000. Given more general nature of XIRR, we are going to use XIRR in this article.

Understanding XIRR (what is XIRR)

XIRR is an annualized rate of return for a series of buy and sell transactions (or cash in-flow and out-flows). To understand it, let's start with a simple example:

Date Action Amount
Jan 1, 2015 Buy 6000
Jan 1, 2016 Sell 6600
XIRR = 10%

In this simple case, XIRR is equivalent to simple rate of return (i.e., (6600 - 6000) / 6000))

Date Action Amount
Jan 1, 2015 Buy 6000
July 1, 2015 Sell 6600
XIRR = 20%

Here simple rate of return is 10% which converted to annualized return amount to 20% which is same as XIRR.

Date Action Amount
Jan 1, 2016 Buy 2000
Feb 1, 2016 Buy 2000
Aug 1, 2016 Buy 4000
Oct 4, 2016 Buy 3000
Nov 11, 2016 Sell 12232
XIRR = 27.30%

How to calculate XIRR

The easiest way to calculate XIRR is to use either Microsoft Excel or Google sheets. Both of these have in-built support to calculate XIRR.

Let's learn how to calculate it using Google sheets. Below is an example of sample Google sheet containing 4 buy transactions and 1 sell transaction.

index A B
1 2000 1/1/2016
2 2000 2/1/2016
3 4000 8/1/2016
4 3000 10/4/2016
5 -12232 11/11/2016
x return rate =XIRR(A1:A5, B1:B5)

XIRR is a built-in function which takes two arguments:

  • 1st argument: Cashflow amounts supplied as range of columns (A1:A5 in the example). Please note that buy is represented as positive value and sell as negative value
  • 2nd argument: Cashflow dates corresponding to 1st argument (B1:B5 in the example)

That's it and you have your XIRR (i.e., annualized rate of return for irregular cash flows).

Category: finance