Documentation/Calc Functions/XIRR

    From The Document Foundation Wiki
    Other languages:

    Function name:

    XIRR

    Category:

    Financial Analysis

    Summary:

    Calculates the internal rate of return for a series of future cash flows that do not need to be periodic. The internal rate of return is defined as the discount rate that results in a net present value (XNPV) of zero. Internal rate of return analysis is a financial technique that is often used to assess the likely profitability of a project or investment.

    XIRR allows cash flows to occur on any date, with values that may vary and represent either income (positive) or expenditure (negative). At least one value must be negative and at least one value must be positive.

    XIRR assumes that all years (including leap years) comprise 365 days.

    XIRR uses an iterative algorithm to solve the relevant equation, with the option of providing an initial starting value as one of the function's arguments.

    Syntax:

    XIRR(Values; Dates[; Guess])

    Returns:

    Returns a real number, which is the constant annual discount rate that will give a net present value of zero for the specified cash flows. The number returned is a percentage expressed as a decimal fraction (for example, a returned value of 0.025 represents 2.5%).

    Arguments:

    Values is a list of real numbers, which are the values of the cash flows. Values may be a specific cell range (for example, "$SourceData.A3:B12"), the name of a named or database range, or an inline array. The following points should be noted with regards to the supplied cash flows:

    • Values should be the same length as the Dates argument and each should comprise at least two cells.
    • For each value in Values, there should be a corresponding date in Dates.
    • Each cash flow should be expressed in the same currency unit.
    • Each cash flow may be positive (income) or negative (expenditure). There must be at least one negative value and at least one positive value.

    Dates is a list of dates, which are the dates when the cash flows occur. Dates may be a specific cell range (for example, "$SourceData.A3:B12"), the name of a named or database range, or an inline array. The following points should be noted with regards to the supplied dates:

    • Dates should be the same length as Values and each should comprise at least two cells.
    • For each date in Dates, there should be a corresponding value in Values.
    • The first entry in Dates is taken as the start of the cash flows, with subsequent entries discounted to that date.
    • The second and subsequent cash flows defined in the Dates and Values lists need not be in chronological order.

    Guess is a real number (expressed as a percentage, such as 2.5%, or a decimal fraction, such as 0.025), or a reference to a cell containing that number, which is an initial guess for the annual discount rate. This can be entered if you have some idea of the discount rate but, if omitted, 0.1 (10%) is assumed. Guess must be greater than -1 (-100%).

    The following conditions (including errors) may be encountered:

    • If the number of cells in Values is not equal to the number of cells in Dates, then XIRR reports an invalid argument error (Err:502).
    • If the number of cells in Values is less than two, then XIRR reports an invalid argument error (Err:502).
    • If the Guess argument is non-numeric, then XIRR reports an invalid argument error (Err:502).
    • If the Guess argument is less than or equal to -1 (-100%), then XIRR reports an invalid argument error (Err:502).
    • Because of the iterative method used, it is possible for the calculation to fail to converge to a result. In such cases, XIRR reports an invalid argument error (Err:502).

    Additional details:

    • The XIRR function calculates r in the following equation:
    [math]\displaystyle{ \Large \sum_{i=1}^{N}~\frac{Values_{i}}{(1+r)^{\frac{Dates_{i}-Dates_{1}}{365}}}~=~0 }[/math]
    where N is the number of cash flows defined by the Values and Dates arguments.
    • To calculate the internal rate of returns for a series of periodic cash flows, use the IRR function.
    • If you suspect incorrect results for XIRR, look in the data ranges for empty cells or cells containing text. To highlight text contents in a data range, use Calc’s View ▸ Value Highlighting facility.
    • Difficulties have been experienced when attempting to define the dates of cash flows in an inline array, such as {"2022-01-01", "2022-07-01", "2023-01-01"}, when XIRR does not give the results expected. Either place the dates in cells or use equivalent date-time serial numbers within the inline array (for example, {44562, 44743, 44927}).

    Examples:

    Dates in the following examples are formatted in accordance with the ISO 8601 standard (YYYY-MM-DD). Other locale-dependent date formats will be available for your Calc installation. Note that it is often good practice to utilize the DATE function instead of typing formatted date values.

    The following table shows the data used in the first example below.

    Data for example of XIRR function
    A B
    1 Value Date
    2 −3500 2021-03-31
    3 100 2021-06-30
    4 150 2021-09-30
    5 200 2021-12-31
    6 250 2022-03-31
    7 300 2022-06-30
    8 350 2022-09-30
    9 400 2022-12-31
    10 450 2023-03-31
    11 500 2023-06-30
    12 550 2023-09-30
    13 600 2023-12-31
    Formula Description Returns
    =XIRR(A2:A13; B2:B13) where cells A2:B13 contain the values shown in the table above. Here the function calculates the internal rate of return for twelve quarterly cash flows. The Guess argument is omitted and so the default value of 0.1 (10%) is applied. The function returns the value 0.0530 (to four decimal places), equivalent to 5.30%. Note that the formula =XNPV(XIRR(A2:A13; B2:B13); A2:A13; B2:B13) returns a very small number that is approximately equal to 0.0. 0.0530
    =XIRR(A1:A6; B1:B6; C1) where cells A1:A6 contain the values -30000, 4000, 9000, 15000, 19000, and 26000 respectively; cells B1:B6 contain the dates 2020-01-01, 2021-01-01, 2022-01-01, 2023-01-01, 2024-01-01, and 2025-01-01 respectively; cell C1 contains the value 35%. Here the function calculates the internal rate of return for the six cash flows defined in cells A1:B6, using an initial guess defined in cell C1. The function returns the value 0.2850 (to four decimal places), equivalent to 28.50%. Note that the formula =XNPV(XIRR(A1:A6; B1:B6); A1:A6; B1:B6) returns a very small number that is approximately equal to 0.0. 0.2850
    =XIRR({-2750, 1000, 2000}; {44597, 44747, 44931}) Here the function calculates the internal rate of return for three cash flows. The values 44597, 44747, and 44931 are the date-time serial numbers for 2022-02-05, 2022-07-05, and 2023-01-05 respectively. The function returns the value 0.1241 (to four decimal places), equivalent to 12.41%. 0.1241

    Related LibreOffice functions:

    IRR

    XNPV

    ODF standard:

    Section 6.12.51, part 2

    Related (or similar) Excel functions:

    XIRR