Documentation/Calc Functions/IRR

    From The Document Foundation Wiki
    Other languages:

    Function name:

    IRR

    Category:

    Financial Analysis

    Summary:

    Calculates the internal rate of return for a series of periodic cash flows. The internal rate of return is defined as the discount rate that results in a net present value (NPV) 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.

    IRR assumes that cash flows occur regularly, in equal length time periods. Cash flow values may vary between periods and represent either income (positive) or expenditure (negative). At least one value must be negative and at least one value must be positive.

    IRR 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:

    IRR(Values[; Guess])

    Returns:

    Returns a real number, which is the constant discount rate per period that will give a net present value of zero for the specified cash flows. The number returned is a percentage and, by default, is formatted as a percentage.

    Arguments:

    Values is a list of real numbers, which are the periodic 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:

    • Each cash flow should be expressed in the same currency unit.
    • Each cash flow may be positive (income), zero, or negative (expenditure).
    • One cash flow should be supplied for each period during the predicted life of the project or investment. The value 0 should be included for any period with a zero cash flow – if this is omitted, then later cash flows will be discounted incorrectly.
    • IRR expects cash flows to be supplied in chronological order. Range reference and array arguments are evaluated column-wise starting from the top-left.
    • The order of the cash flows passed to IRR is important. For example, the formula =IRR({-200, 200, 300}) returns 82.29%, while the formula =IRR({300, 200, -200}) returns -221.53%.

    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 periodic discount rate. This can be entered if you have some idea of the discount rate but, if omitted, 0.1 (10%) is assumed. It is common for discount rates to be quoted annually and, where necessary, care should be taken to convert such values to be compatible with the length of your period.

    • If the Guess argument is non-numeric, then IRR reports a #VALUE! error.
    • Cells within the Values argument that are empty or contain text are ignored.
    • Because of the iterative method used, it is possible for the calculation to fail to converge to a result. In such cases, IRR reports a calculation does not converge error (Err:523). It may be possible to obtain a result by specifying a different value for the Guess argument because a significant error in this initial guess can lead to non-convergence of the algorithm. It may also be worth rechecking the arguments. An example is included in the #Examples: section.
    • It is also possible for IRR to return one value from multiple available solutions for the given cash flows. Therefore, if IRR returns a value that is significantly different from that expected, a different value for the Guess argument may encourage IRR to return another solution that is closer to the expected value. An example is included in the #Examples: section.

    Additional details:

    • The IRR function calculates r in the following equation:
    [math]\displaystyle{ \sum_{i=1}^{n}\frac{FV_{i}}{(1+r)^{i}}~=~0 }[/math]
    where:
    n = total number of cash flows specified
    FVi = value of ith cash flow
    r = periodic discount rate
    • To calculate the internal rate of return for a series of non-periodic cash flows, use the XIRR function.

    Examples:

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

    Data for example of IRR function
    A B C D
    1 Year 1 Year 2 Year 3
    2 Q1 −2000 250 450
    3 Q2 100 300 500
    4 Q3 150 350 550
    5 Q4 200 400 600
    Formula Description Returns
    =IRR(B2:D5) where cells B2:D5 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 9.74%. Note that the formula =IRR({-2000, 100, 150, 200, 250, 300, 350, 400, 450, 500, 550, 600}) returns the same value. Note also that an inaccurate value for the Guess argument can adversely affect the performance of IRR – for example, the formulas =IRR(B2:D5, 50%) and =IRR(B2:D5, -80%) both return Err:523 (calculation does not converge). 9.74%
    =IRR(A1:A6; 40%) where cells A1:A6 contain the values -30000, 4000, 9000, 15000, 19000, and 26000. Here the function calculates the internal rate of return for the six cash flows defined in cells A1:A6, using an initial guess of 40%. The function returns the value 28.53%. To cross-check this result, use the value calculated by IRR (albeit with fuller precision than the default two decimal places) in the formula =NPV(28.5284546625%, A1:A6) and see that it returns 0.0 currency units as expected. 28.53%
    =IRR(A1:A5) where cells A1:A5 contain the values 10000, -2000, -2500, -2000, and -1500. Here the function calculates the internal rate of return for the five cash flows defined in cells A1:A5. The Guess argument is omitted and so the default value of 0.1 (10%) is applied. The function returns the value -8.79%. In this case, an inappropriate value for the Guess argument may cause non-convergence; for example, the formula=IRR(A1:A5; 80%) returns Err:523 (calculation does not converge). However, for certain values of the Guess argument a different value altogether is returned; for example, the formula=IRR(A1:A5; 55%) returns the value -153.78%. -8.79%

    Related LibreOffice functions:

    MIRR

    NPV

    RATE

    XIRR

    ODF standard:

    Section 6.12.24, part 2

    Related (or similar) Excel functions:

    IRR