Investment Studio > Expressions > Functions > Financial > XIRR

float xirr(float array cash_flows, date array dates, float guess = 10%)

Returns the interest rate (IRR, Internal Rate of Return) for a series of cash flows which need not be periodic. For periodic cash flows, use the irr function.

	cash_flows contains the series of inflows (positive values) and outflows (negative values). Computation of the IRR requires at least one positive value (inflow) and one negative value (outflow) to be listed.
	dates contains the dates for each flow listed in the corresponding position of the cash_flows array. The two arrays must have the same number of elements. Dates may be listed in any order (as long as they match up with the corresponding cash flow), with one exception: no date may precede the date in the first position, which marks the beginning of the schedule of payments. All cash flows are discounted based on a 365-day year.
	guess is the starting point for the iterative procedure used to find the IRR. If no solution accurate to within 1E-7(1E-5 %) is found after 100 iterations, the result is an error (#N/A). In that case, a different guess may yield a better result. If guess is omitted, it defaults to 10%.

All array elements are converted to floats (with exclusion if conversion fails) and interpreted as a single, one-dimensional sequence. If an array is two-dimensional, it's read in the usual order (i.e. row by row: left to right, top to bottom).

The IRR of an investment is the interest rate corresponding to zero NPV (Net Present Value): ideally, xnpv(xirr(cash_flows, dates), cash_flows, dates) = 0.

Example

On July 8, 2001, you invest USD 10 000 to start a new business with irregular returns of USD 1400 (October 15, 2001), 3000 (December 3, 2001), 2500 (March 24, 2002), and 5000 (May 15, 2002).

Assuming standard US date format settings, the IRR is

=xirr({-10000, 1400, 3000, 2500, 5000}, {"7/8/2001", "10/15/2001", "12/3/2001", "3/24/2002", "5/15/2002"})

» 31.6%.

See also irr, mirr, npv, rate, xnpv.