Investment Studio > Expressions > Functions > Financial > IRR

float irr(float array cash_flows, float guess = 10%)

Returns the interest rate (IRR, Internal Rate of Return) for a series of cash flows occurring at regular intervals (e.g. monthly or annually).

cash_flows contains the series of inflows (positive values) and outflows (negative values). Cash flows must be listed in their order of occurrence. All elements in cash_flows are converted to floats (with exclusion if conversion fails) and interpreted as a single, one-dimensional sequence. If the array is two-dimensional, it's read in the usual order (i.e. row by row: left to right, top to bottom). Computation of the IRR requires at least one positive value (inflow) and one negative value (outflow) to be listed.

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 20 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%.

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

Example

You invest USD 10 000 to start a new business with annual returns of USD 2000, 5000, 7000, 8000 and 8500 during the first five years. After the first four years, your investment's IRR is

=irr({-10000, 2000, 5000, 7000, 8000})

» 32.3%. After five years, it's

=irr({-10000, 2000, 5000, 7000, 8000, 8500})

» 40.9% (not bad!).

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