Definition
Extended Internal Rate of Return or XIRR is a method for calculating the return on an investment when cash flows are distributed in a non-uniform manner.
Uses
- To calculate returns for irregular cash flows.
- To compare various investment option with non-uniform cash flow patterns.
- To calculate returns for investment which may include both positive and negative cashflows.
Calculation
We need to provide a series of cash flows that occurred at non-uniform intervals, along with the corresponding dates on which the cash flows occurred to calculate XIRR.
Formula for XIRR :
The formula uses the following arguments:
- Values (required) – This is the list of values that represent the series of cash flows. Instead of a list, it can be a reference/pointer to a range of cells containing values.
- Dates (required) – This is a list of dates that correspond to the provided values. Subsequent dates should be later than the first date.
- [guess] (optional) – This is an initial guess – or estimate – of what the XIRR will be. Default value is generally taken as 10%.
Excel uses an iterative technique for calculating XIRR. Using a changing rate (starting with [rate (guess)]), XIRR cycles through the calculation until the result is within purview of 0.000001%.
Key points
- XIRR assumes that we are reinvesting cash flows at the unqiue XIRR rate. This means that all investments and withdrawals are transacted at XIRR rate.
- It is not the same as the annual percentage rate (APR), which is a measure of the cost of borrowing money.
XIRR vs IRR
- XIRR generally works with irregular cash flows while IRR is used with regular or uniform cashflows.
- XIRR and IRR assumes that the cash flows are reinvested at the XIRR and IRR rate respectively.
- This tends XIRR rate to be generally higher than the IRR when the cash flows are irregular.
XIRR vs CAGR
We would recommend going through our article related to CAGR before going through following comparison.
Suppose John invested ₹10,000 annually for 8 years and he wants to know about the average rate at which his investments have compounded in those years.
The rate that would give him this information is known as Extended Internal Rate of Return or XIRR.
We will now learn what XIRR represents and how it is related to CAGR.
Following is an annual SIP investment schedule according to the example description. The investment is made annually at the start of each year. A weekly or monthly SIP follows the same logic but could be a bit more difficult to comprehend.
| SIP installment no. | Invested value at start of year | Value at year end |
|---|---|---|
| 1 | 10,000 | 21,478 |
| 2 | 10,000 | 38,078 |
| 3 | 10,000 | 74,853 |
| 4 | 10,000 | 1,28,333 |
| 5 | 10,000 | 2,01,610 |
| 6 | 10,000 | 1,22,223 |
| 7 | 10,000 | 1,87,767 |
| 8 | 10,000 | 2,25,810 |
The total value after 8 years is 2,25,810
The same schedule can be viewed in a more elaborative way.
| SIP installment no. | Invested value at start of year | Years (investment kept) | Final value after years | CAGR |
|---|---|---|---|---|
| 1 | 10,000 | 8 | 35,214 | 17.04% |
| 2 | 10,000 | 7 | 23,800 | 13.19% |
| 3 | 10,000 | 6 | 39,187 | 25.56% |
| 4 | 10,000 | 5 | 37,500 | 30.26% |
| 5 | 10,000 | 4 | 24,560 | 25.19% |
| 6 | 10,000 | 3 | 29,091 | 42.75% |
| 7 | 10,000 | 2 | 19,293 | 38.90% |
| 8 | 10,000 | 1 | 17,165 | 71.65% |
The first investment has 8 years to grow. The second investment has 7 years to grow, and so on and so forth.
In fourth column, we have calculated final value of each investment based on number of years (it gets) to grow.
The first investment after 8 years grows to 35,214 at a CAGR of 17.04% .The second instalment after 7 years grows to 23,800 at a CAGR of 13.19% and so on and so forth.
Each investment has its own CAGR as the investment period varies. The sum of all the investments (including returns) must be equal to 2,25,810.
Instead of assigning each investment unqiue CAGR, what if we assigned a single (common) CAGR?
That means each investment is perceived to grow at that common CAGR. The aim is to adjust this single (common) CAGR until the sum of all the investments (including returns) becomes equal to 2,25,810.
This adjusted CAGR is called XIRR.
| SIP installment no. | Invested value at start of year | Years (investment kept) | Final value after years | XIRR (or adjusted CAGR) |
|---|---|---|---|---|
| 1 | 10,000 | 8 | 52,099 | 22.9% |
| 2 | 10,000 | 7 | 42,387 | 22.9% |
| 3 | 10,000 | 6 | 34,484 | 22.9% |
| 4 | 10,000 | 5 | 28,055 | 22.9% |
| 5 | 10,000 | 4 | 22,825 | 22.9% |
| 6 | 10,000 | 3 | 18,565 | 22.9% |
| 7 | 10,000 | 2 | 15,105 | 22.9% |
| 8 | 10,000 | 1 | 12,290 | 22.9% |
The total value after 8 years comes out again as 2,25,810
The last column is the adjusted CAGR or simply XIRR.
So our process was to adjust the CAGR until the sum of all the cash flows (including returns) equals to the final value (when CAGR is calulated for each investment separately).