Calculates the Internal Rate of Return on a cash flow that must contain at least one investment (negative) and one income (positive) value. Includes functionality to configure the initial guess and the number of iterations the algorithm can make.
@IRREX is an extension of @IRR, in which the initial guess of 0.07 cannot be changed.
Syntax
@IRREX (cashflowMbr, discountFlag[, [guess], [number_of_iteration], [STORECALCVALUE | STOREMISSING], [XrangeList])
Parameter | Description |
---|---|
cashflowMbr |
Single member specification. |
discountFlag |
Member specification, variable name, or numeric expression providing a constant value of either 1 or 0. Indicates whether the function should discount from the first period. 0 means discount from the first period, and 1 means do not discount from the first period. |
guess |
Optional. The starting guess for estimated IRR. If not specified, the default guess of 0.07 is used. |
number_of_iteration |
Optional. The number of iterations the Newton Raphson algorithm will loop through. (Newton Raphson is the mathematical method used for finding the IRR using the IRREX function.) The default value is 300. |
STORECALCVALUE | STOREMISSING |
Optional. STORECALCVALUE tells Essbase to always store the calculated value even when the IRR calculation returns ‘false’ results. This is the default. Optional. STOREMISSING tells Essbase to store #MISSING value when the IRR calculation returns false results after the specified number of iterations. |
XrangeList |
Optional parameter specifying the range over which the rate is calculated. If a range is not specified, Essbase uses the level 0 members from the dimension tagged as Time. Can be a valid member name, a comma-delimited list of member names, cross dimensional members, or a return value from a member set function or range function (including @XRANGE). For more information about rangeList and XrangeList, see Range List Parameters in the topic Range and Financial Functions. |
Notes
Financial functions never return a value; rather, they calculate a series of values internally based on the range specified.
Essbase returns #MISSING from calculator function @IRREX if all cash flows are zero.
@IRREX provides functionality to configure the initial guess and the number of iterations the algorithm can make. Even if both Essbase and similar functions in Excel start with the same initial guess, results may differ. This is because there may be more than one solution to an equation, and the algorithm stops looking when it finds a valid solution. Which solution is found first may differ based on the algorithm. Although leading or trailing zeros do not matter in a mathematical context, the algorithm may behave differently and find a different root because of the presence of leading or trailing zeros. If you need identical solutions regardless of the presence of leading or trailing zeros, you may wish to create a custom-defined function to handle these issues.
Example
@IRREX(IRROut1,0,0.02, 500,STOREMISSING,"2006":"2009");
The starting guess is 0.02 (2%). @IRREX iterates 500 times, and stores #MISSING if the solution does not converge.
@IRREX(IRROut1,0, , ,STOREMISSING,"2006":"2009");
The starting guess and iteration values are omitted (NULL). Note: The commas (,) are required even when passing null arguments.
The following example assumes a Year dimension is added to Sample Basic. The rate is calculated using a multidimensional range.
FIX ("100-10", "New York") Return = @IRREX(IRROut1,0, , ,STOREMISSING, @XRANGE("2011"->"Sep", "2012"->"Mar")); ENDFIX
The above calculation is performed across the following multidimensional range specified by XrangeList:
2011->Sep 2011->Oct 2011->Nov 2011->Dec 2012->Jan 2012->Feb 2012->Mar