Tomas,

Here is the function from the source with comments:

/**
* Calculate internal rate of return (IRR) using cash flows that occur at
* regular intervals, such as monthly or annually. The internal rate of
* return is the interest rate received for an investment consisting of
* payments and receipts that occur at regular intervals.
*
* Method: Newton-Raphson technique. Formula: sum(cashFlow(i) / (1 + IRR)^i)
*
* @param cashFlows
* Cash flow values. Must contain at least one negative value
* (cash paid) and one positive value (cash received).
* @param estimatedResult
* Optional guess as start value (default: 0.1 = 10%;
* if value is negative: 0.5). As the
* formula to calculate IRRs can have multiple solutions, an
* estimated result (guess) can help find the result we are
* looking for.
* @return Internal rate of return (0.25 = 25%) or Double.NaN if IRR not
computable.
* @throws BirtException
*
*/
static public double irr( final Object[] cashFlows,
final double estimatedResult ) throws BirtException
{

int cashFlowsCount = cashFlows.length;

if ( cashFlows == null || cashFlowsCount < 2 )
{
throw DataException.wrap( new AggrException(
ResourceConstants.ILLEGAL_PARAMETER_FUN,
"irr" ) ); //$NON-NLS-1$
}
// check if business startup costs is not zero:
if ( MathUtil.compareTo0( cashFlows[0] ) != 0 )
{
Number sumCashFlows = 0.0;
// check if at least 1 positive and 1 negative cash flow exists:
int numOfNegativeCashFlows = 0;
int numOfPositiveCashFlows = 0;
for ( int i = 0; i < cashFlowsCount; i++ )
{
sumCashFlows = MathUtil.add( sumCashFlows, cashFlows[i] );
if ( MathUtil.compareTo0( cashFlows[i] ) > 0 )
{
numOfPositiveCashFlows++;
}
else if ( MathUtil.compareTo0( cashFlows[i] ) < 0 )
{
numOfNegativeCashFlows++;
}
}

// at least 1 negative and 1 positive cash flow available?
if ( numOfNegativeCashFlows > 0 && numOfPositiveCashFlows > 0 )
{
// set estimated result:
double irrGuess = 0.1; // default: 10%
if ( !Double.isNaN( estimatedResult ) )
{
if ( estimatedResult >= 0 )
{
irrGuess = estimatedResult;
}
else
{
irrGuess = 0.5;
}
}
else
{
throw DataException.wrap( new AggrException(
ResourceConstants.ILLEGAL_PARAMETER_FUN,
"irr" ) ); //$NON-NLS-1$
}
// initialize first IRR with estimated result:
double irr;
if ( MathUtil.compareTo0( sumCashFlows ) < 0 )
{ // sum of cash flows negative?
irr = -irrGuess;
}
else
{ // sum of cash flows not negative
irr = irrGuess;
}

// iteration:
// the smaller the distance, the smaller the interpolation
// error
final double minDistance = 1E-15;

// business startup costs
final Object cashFlowStart = cashFlows[0];
final int maxIteration = 50;
boolean highValueGap = false;
Number cashValue = 0.0;
for ( int i = 0; i <= maxIteration; i++ )
{
// calculate cash value with current irr
cashValue = MathUtil.toNumber( cashFlowStart ); // initialized with
startup
// costs

// for each cash flow
for ( int j = 1; j < cashFlowsCount; j++ )
{
cashValue = MathUtil.add( cashValue,
MathUtil.divide( cashFlows[j], Math.pow( 1.0 + irr, j ) ));
}

// cash value is close to zero
if (MathUtil.compare( MathUtil.abs( cashValue ), 1E-7 ) <= 0 )
{
return irr;
}

// adjust irr for next iteration:
// cash value > 0 => next irr > current irr
if ( MathUtil.compare(cashValue, 0.0) > 0 )
{
if ( highValueGap )
{
irrGuess /= 2;
}

irr += irrGuess;

if ( highValueGap )
{
irrGuess -= minDistance;
highValueGap = false;
}

}
else
{// cash value < 0 => next irr < current irr
irrGuess /= 2;
irr -= irrGuess;
highValueGap = true;
}

// estimated result too small to continue => end
// calculation
if ( irrGuess <= minDistance
&& MathUtil.compare( MathUtil.abs( cashValue ), 1E-7 ) <= 0 )
{
return irr;
}
}
}
}
return Double.NaN; //$NON-NLS-1$
}

I have also attached an example that uses the values show on this wiki page:

Jason

On 7/8/2011 9:05 AM, TomasGreif wrote:
> Hi,
>
> how does IRR function work in BIRT?
> I need to calculate IRR for approx. 40 projects, each with own monthly
> cash flow data. I can see there is IRR function available when creating
> table groups, so I've tried to:
> - sort table according to project id and time
> - add aggregation, hide details and use IRR function with cash flow
> column and some interest rate guess
>
> This approach returns NaN most of the time and some strange numbers for
> the rest. I guess I need something like XIRR function from excel,
> however I don't see how to make it work in BIRT. I have also tried NPV
> function - this time I get some positive numbers, but results are quite
> different from calculations in excel (XNPV function).
>
> Thanks for any suggestion.
>
> Regards,
>
> Tomas