1. Calculation and Query Reference for Oracle Essbase
  2. Calculation Functions
  3. Calculation Function List
  4. @IRR

@IRR

The @IRR calculation function for Essbase calculates the Internal Rate of Return on a cash flow that must contain at least one investment (negative) and one income (positive) value.

Also see @IRREX.

Syntax

@IRR (cashflowMbr, discountFlag[, XrangeList])

Parameters

cashflowMbr

Single member specification.

discountFlag

Member specification, variable name, or numeric expression providing a constant value of either 1 or 0. discountFlag indicates whether the function should discount from the first period. 1 means do not discount from the first period.

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.

  • This function returns #MISSING if all cash flows are zero.

  • This function provides an initial guess of 0.07. This value cannot be changed, in contrast to similar functions in Excel. Because results depend in part on the initial guess, any difference in the initial guess may result in a different result. Even if both Excel and Essbase 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

This example calculates the Internal Rate of Return (Return) on a cash flow (Cash).

Return = @IRR(Cash,0,FY1998:FY2000,FY2001:FY2003);

This example produces the following report:

         FY1998  FY1999  FY2000  FY2001  FY2002    FY2003
         ======  ======  ======  ======  ======    ======                
Cash    (1,000)    500    600    500   #MISSING   #MISSING
Rate         0       0      0      0   #MISSING   #MISSING
Return       0       0      0      0          0          0

The following example assumes a Year dimension is added to Sample Basic. It calculates the return using a multidimensional range.

FIX ("100-10", "New York")
"Return" = @IRR(Cash,0,@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