XIRR Calculator

1. Using the XIRR calculator
2. What is XIRR?
3. XIRR formula
4. Extended Internal Rate of Return calculation example
5. XIRR vs IRR
6. Financial caution

    Using the XIRR calculator

Using the Extended Internal Rate of Return calculation tool is a bit more complicated than a typical IRR calculator:

1. Enter the initial investment (it can be in any currency like US dollars, EUR, Swiss francs, etc.) as well as the exact date when it has been made or is expected to be made.
2. Select the number of cash flow events there are to be analyzed with a maximum of 25.
3. Input the value of each cash inflow/outflow event as well as the date when it happened or is expected to happen. Use negative numbers for negative flows.

Once ready, press "Calculate" and our XIRR calculator will output the Extended Internal Rate of Return per annum, a.k.a. the annualized discounted cash flow rate of return, as well as the Gross Return rate (in percent) and the Net Cash Flow (Profit - Loss)

    What is XIRR?

The Extended Internal Rate of Return, abbreviated as XIRR, is defined as the discount rate that makes the net present value (NPV) of all cash flows from a particular project equal to zero, where the cash flows are not necessarily periodic. This last part differentiates the discounted cash flow rate of return calculation called XIRR from the classic IRR so "Extended" refers to the fact that unlike IRR, XIRR can accommodate irregular cash flows. This means it can be used in scenarios involving non-periodic investments and/or returns. "Internal" refers to the omission of external factors like capital cost, currency inflation, etc.

XIRR is used to estimate the profitability of potential investments when the investments or returns do not happen at regular time intervals. In such cases one experiences irregular cash flows, meaning they do not follow a monthly, quarterly or yearly schedule. Such non-periodic cash flows are not amenable to simpler IRR models and require the calculation of the extended IRR. A higher the rate of return makes a project more desirable while the lower it is, the more risky and overall undesirable it would be to get involved. An investor can use it to rank prospective investments on even grounds even when facing irregularity in the timing and amounts of cash flows, as well as a variable return rate, a.k.a. floating return rate.

Compared to CAGR, XIRR gives greater weight to earlier cash flows compared to later ones. This corresponds to the time preference of investors who by definition prefer $100 now versus $100 a year from now. See time value of money for more on this topic.

    XIRR formula

If you wonder how to calculate the Extended Internal Rate of Return by yourself or using an Excel spreadsheet, you would realize that there is no analytical solution to the issue and the only way to calculate it is programmatically or by using tools such as our calculator above. The algorithm uses to find a solution to the NPV formula after replacing the left hand side with zero:

where r is the discount rate / interest rate and T is the number of cash flow periods with t denoting a specific cash flow timing, C₀ is the initial investment while C_t is the return during cash flow event t. d_t is the date of a specific cash flow event expressed as the number of days since a given epoch and d₀ is the date of the initial investment. To solve for r after NPV is replaced with zero an iterative algorithm is required as there is no analytical solution. For this reason, our XIRR calculator performs a recursive search until it finds a value of r which results in an NPV very close to zero which is actually XIRR.

    Extended Internal Rate of Return calculation example

Consider the following scenario: an investment project requires an initial cash injection of $10,000 and is expected to return $25,000 in five years time with a positive cash flow of $3,000 at the end of the second year, then $3,000 on the third year, then $4,000 on the fourth year, then $5,000 in the beginning of the third quarter of the fifth year and another $10,000 at its end. What is the internal rate of return?

To find the discount rate at which the net present value becomes zero an XIRR calculation is required. One way to solve this problem is to graphically examine the relationship between the net present value and the discount rate to see at which rate the NPV turns from positive to negative. The process is cumbersome as NPV needs to be calculated at many different rates.

Instead, a computer algorithm can approximate the extended internal rate of return by trying different discount rates until it converges on an NPV very close to zero. In this case the answer is 30.52%.

    XIRR vs IRR

IRR is the simpler form of internal rate of return calculations, which can be applied with variable amounts of inflows and outflows, but these must be happening at fixed periods of time such as years, quarters, months, and so on. XIRR, on the other hand, can accommodate cash flows with variable amounts and variable time intervals between them. The ability to handle non-periodic cash flows is what differentiates XIRR from IRR. See CAGR vs IRR vs XIRR for a full discussion and comparison of these measures of the rate of return of investments.

    Financial caution

This is a simple online calculator which is a good starting point in estimating the extended internal rate of return from an investment with non-period cash flows, it is by no means the end of such a process. You should always consult a qualified professional when making important financial decisions and long-term agreements, such as long-term bank deposits. Use the information provided by the software critically and at your own risk.