Internal rate of return

Returns the internal rate of return on a set of payments. The internal rate of return is the interest rate received for an investment consisting of payments that occur periodically. These payments do not need to be equal in value, but they must occur at regular intervals, such as monthly, quarterly, or annually.

To calculate the internal rate of return for payments that do not occur at regular intervals, see XIRR (internal rate of return for payments at irregular intervals).

Syntax

IRR <FactID, Guess, METHOD, Precision, SortBy> (Argument)

Where:

FactID is a parameter that forces a calculation to take place on a fact table that contains the selected fact.

Guess is a parameter that indicates an estimate assumed to be close to the result sought. The default value is 0.1, which can be thought of as representing an expected internal rate of return of 10 percent.

Method is a parameter that determines the algorithm used to calculate the IRR function. You can use the following values:

0 (default): The IRR function uses the secant method to determine the internal rate of return. This is the recommended method as it can adjust the initial guess as required to return a result.
1: The IRR function uses Newton’s method to determine the internal rate of return. This method can return results similar to those returned for internal rate of return functions that are supplied with Microsoft Excel.

Precision is a parameter that determines how close the evaluation of a potential solution must be to zero to be considered valid.

Argument is a fact or metric representing a list of numbers for which the internal rate of return is sought.

SortBy is a parameter that defines the order of calculation. For more information see BreakBy and SortBy parameters.

Usage Notes

The list of values must contain at least one positive entry and one negative entry.

Payments, whether positive or negative, must be entered in the desired sequence, as this is the order in which entries are interpreted for calculation.