What is XIRR


This article discusses the formula syntax and usage of theXINTZINSFUSS described in Microsoft Excel.


Returns the internal rate of return for a series of non-recurring payments. Use the IKV function to calculate the internal rate of return for a series of recurring payments.


XINTZINSFUSS (values; time pts; [estimate])

The XINTZINSFUSS function syntax has the following arguments:

  • values Required. A series of non-recurring payments that relate to the dates of the payment schedule. The first payment is optional and corresponds to a payout that is made at the beginning of each investment. If the first value is a cost or a payment, this value must be negative. All subsequent payments are discounted (discounted) based on a 365-day year. The series of values ​​must contain at least one positive value and one negative value.

  • Time points Required. The times in the payment schedule of the non-periodic payments. Dates can appear in any order. Dates should be entered using the DATE function or as the result of other formulas or functions. For example, use DATE (2008; 5; 23) for May 23, 2008. Problems can arise when entering dates as text. .

  • Estimate Optional. A number that you think will be close to the result of the XINTZINSFUSS function


  • Microsoft Excel stores dates as serial numbers for use in calculations. By default, January 1st, 1900 is the sequential number 1 and January 1st, 2008 is the sequential number 39448, since this year is 39448 days after January 1st, 1900.

  • All numbers given in time points are shortened to whole numbers by cutting off their decimal places.

  • XINTZINSFUSS expects at least one positive and at least one negative payment. Otherwise, XINTZINSFUSS returns the #NUM! back.

  • If one of the numbers valid in time information 2010 is not a valid date, XIRR returns the #VALUE! Error value.

  • If one of the characters in the time information is before the starting date, XIRR returns the #NUM! Error value.

  • If values ​​and dates contain different numbers of values, XIRR returns the value #NUM! Error value.

  • In most cases it is not necessary to provide an estimated value for the calculation of XINTZINSFUSS. If guess is omitted, it is assumed to be 0.1 (10 percent).

  • XINTZINSFUSS is closely related to the XCAPITALVALUE function, which calculates the net present value of an investment. An interest rate calculated by XINTZINSFUSS corresponds to the interest rate for which XCAPITAL VALUE = 0 applies.

  • Excel XIRR is calculated iteratively. When using a changing rate (starting with Guess), XIRR goes through the calculation until the result is accurate to 0.000001 percent. If XIRR can't find a result after 100 storms, #NUM! returned. The sentence is changed until:

    in which:

    • di = the i-th or last payment date.

    • d1 = the 0th payment date.

    • Pi = the amount of the i-th or last payment.


Copy and paste the sample data in the following table into cell A1 of a new Excel worksheet. To see the results of the formulas, highlight them, press F2, and then press ENTER. If necessary, you can adjust the width of the columns so that all data is displayed.















Description (result)


= XINTZINSFUSS (A3: A7, B3: B7, 0.1)

The internal rate of return (0.373362535 or 37.34%)