XIRR Function in Google Sheets

Calculate the internal rate of return for a series of cash flows using the XIRR function.

XIRR Function

The XIRR function in Google Sheets calculates the internal rate of return (IRR) for a series of cash flows that may not be periodic.

Syntax

XIRR(cashflow_amounts, cashflow_dates, [rate_guess])
  • cashflow_amounts: An array or range of cash flows corresponding to payment dates. This is a required parameter.
  • cashflow_dates: An array or range of dates corresponding to the cash flows. This is a required parameter.
  • rate_guess: (Optional) An estimate for the IRR. Default is 0.1 (10%).

Examples

  1. Basic Calculation

Calculate the internal rate of return for a series of cash flows:

=XIRR({-1000, 300, 400, 500}, {DATE(2023,1,1), DATE(2023,6,1), DATE(2023,12,1), DATE(2024,6,1)})

This will output the internal rate of return.

Notes

  • The cash flow sequence must include at least one negative and one positive value.
  • XIRR is useful for irregular cash flow intervals.
  • IRR: Calculate the internal rate of return for periodic cash flows.
  • XNPV: Calculate the net present value for a series of cash flows.