XIRR

Description

Use this function to calculate the internal rate of return for a schedule of cash flows that is not necessarily periodic.

XIRR returns the internal rate of return for a schedule of cash flows occurring at irregular intervals.

XIRR(values, dates, [guess])

This function accepts the following arguments:

Name	Required	Description	Valid input
`values`	Yes	A series of cash flows that corresponds to a schedule of payments in dates.	A number, a reference to a cell containing a number, a cell range that includes numbers, or a formula which results in any of these.
`dates`	Yes	A schedule of payment dates that corresponds to the cash flow payments.	A date, a reference to a cell containing a date, a cell range that includes dates, or a formula which results in any of these.
`guess`	No	A number that you guess is close to the result of XIRR.	A number. If omitted, guess is assumed to be 0.1 (10%).

Use case Formula Explanation and Result

Calculate the internal rate of return for the specified cash flow and date ranges, with no initial guess value specified.

=XIRR(B2:B6, A2:A6)

This formula works in the following manner:

XIRR(: This is the function itself, calculating the Internal Rate of Return for non-periodic cash flows.
B2:B6: The range containing the series of cash flow values corresponding to the schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it 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.
A2:A6: The range containing the schedule of payment dates that corresponds to the cash flow payments. Dates may occur in any order. Dates should be entered by using the DATE function, or as results of other formulas or functions.
The optional guess argument is omitted, so the function uses the default guess of 0.1 (10%).

For this data set, the formula returns the percentage value 39.4158870%.

Calculate the internal rate of return for the specified cash flow and date ranges, with a specified initial guess value.

=XIRR(B2:B6, A2:A6, 0.42)

This formula works in the following manner:

XIRR(: This is the function itself.
B2:B6: The range containing the cash flow values.
A2:A6: The range containing the corresponding payment dates.
0.42: The initial guess value (42%) for the internal rate of return. This helps the iterative calculation converge.

For this data set, the formula returns the percentage value 39.4158861%.

The XIRR function uses an iterative technique for calculating the internal rate of return.
The specified values and dates ranges must be the same size.
The dates range must only contain dates.
The values range must only contain numeric values.
If XIRR can't find a result that works after 100 tries, it returns the #NUM! error value.
The first value in the cash flow series must be negative (representing an initial investment) for the calculation to work properly.
Dates should be entered using a recognized date format, such as "1/1/2023" or "1-Jan-2023".
The calculation is sensitive to the order of cash flows. Ensure that dates (and associated values) are entered in chronological order.

Use XIRR for investments or projects with irregular cash flows, such as real estate investments or venture capital projects.
XIRR is particularly useful when cash flows occur at uneven intervals, unlike the IRR function which assumes regular periodic cash flows.
If you're analyzing multiple investment options, use XIRR to compare their potential returns.
You can combine XIRR with other financial functions such as NPV (net present value) for a more comprehensive investment analysis.