Skip to content
Excel

XIRR function in Excel

Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic

=XIRR(values, dates, [guess])

XIRR syntax and parameters

Three arguments, two required.

  • valuesarrayRequired

    A series of cash flows that corresponds to a 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.

  • datesarrayRequired

    A 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. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text. .

  • guessnumberOptional

    A number that you guess is close to the result of XIRR.

XIRR examples

Formulas you'll actually reuse.

  1. Annualised return on irregularly dated cash flows — dates in A, amounts in B:

    =XIRR(B2:B8, A2:A8)

    Result0.1136

  2. Compare the return against the hurdle rate:

    =IF(XIRR(B2:B8, A2:A8) > 8%, "Beats hurdle", "Below hurdle")

    Result"Beats hurdle"

XIRR in Google Sheets

Same name — your formula ports as-is.

Try XIRR in the playground

Edit the example — nothing to install.

Preloaded with the XIRR formula from Example 1 — change anything and watch it respond.

Loading the editor…

XIRR errors

What they mean — and the fixes.

  • #NUM!

    The flows never change sign, or the iteration didn't converge — pass a guess as the third argument.

Related functions

More ways Excel gets this done.