# Documentation/Calc Functions/XIRR

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

## 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 ▸ 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}`

).

- For more background information about internal rate of return, visit Wikipedia's Internal rate of return page.

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

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:

## ODF standard:

## Equivalent Excel functions:

XIRR