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:

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

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

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.

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).