Skip to content
Google Sheets

XIRR function in Google Sheets

Calculates the internal rate of return of an investment based on a specified series of potentially irregularly spaced cash flows.

=XIRR(cashflow_amounts, cashflow_dates, [rate_guess])

XIRR syntax and parameters

Three arguments, two required.

  • cashflow_amountsarrayRequired

    An array or range containing the income or payments associated with the investment. cashflow_amounts must contain at least one negative and one positive cash flow to calculate rate of return.

  • cashflow_datesarrayRequired

    An array or range with dates corresponding to the cash flows in cashflow_amounts.

  • rate_guessnumberOptional

    [ OPTIONAL - 0.1 by default ] - An estimate for what the internal rate of return will be.

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 Excel

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 Google Sheets gets this done.