Eclipse Community Forums - RDF feed https://www.eclipse.org/forums/ Eclipse Community Forums IRR function https://www.eclipse.org/forums/index.php/mv/msg/219172/694378/#msg_694378
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]]>
Tomas Greif 2011-07-08T13:05:39-00:00
Re: IRR function https://www.eclipse.org/forums/index.php/mv/msg/219172/694523/#msg_694523
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 )
{
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;

final Object cashFlowStart = cashFlows;
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++ )
{
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:
http://en.wikipedia.org/wiki/Internal_rate_of_return

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]]>
Jason Weathersby 2011-07-08T18:47:11-00:00
Re: IRR function https://www.eclipse.org/forums/index.php/mv/msg/219172/695259/#msg_695259
Now I understand that I can't use IRR aggregation function to calculate IRR from monthly cash flow data. The problem is the following line:
```MathUtil.divide( cashFlows[j], Math.pow( 1.0 + irr, j ) ));
```

Even there is monthly cash flow, it will be discounted by factor "j" which is obviously number of the cash flow row. In case of 5 years project, the last row will be discounted by factor 60 instead of 5. Even there is a way how you can translate this result to monthly IRR, you can do this only in the case when NPV is higher than 0 - this will never happen with my data (if NPV<0, there is no IRR defined and adjustment is not possible).

Is there a way how to change the behaviour of IRR function? I guess the only change needed is to add number of CF rows per year, something like:

```MathUtil.divide( cashFlows[j], Math.pow( 1.0 + irr, j/12 ) ));
```

Tomas]]>
Tomas Greif 2011-07-11T11:44:47-00:00
Re: IRR function https://www.eclipse.org/forums/index.php/mv/msg/219172/695596/#msg_695596
You could always write your own aggregation extension. It is very easy.
Take a look at this post:
http://birtworld.blogspot.com/2006/09/optimistic-sums.html

There are also some examples on birt exchange

Jason

On 7/11/2011 7:44 AM, TomasGreif wrote:
> Thank you Jason.
>
> Now I understand that I can't use IRR aggregation function to calculate
> IRR from monthly cash flow data. The problem is the following line:
>
> MathUtil.divide( cashFlows[j], Math.pow( 1.0 + irr, j ) ));
>
>
> Even there is monthly cash flow, it will be discounted by factor "j"
> which is obviously number of the cash flow row. In case of 5 years
> project, the last row will be discounted by factor 60 instead of 5. Even
> there is a way how you can translate this result to monthly IRR, you can
> do this only in the case when NPV is higher than 0 - this will never
> happen with my data (if NPV<0, there is no IRR defined and adjustment is
> not possible).
>
> Is there a way how to change the behaviour of IRR function? I guess the
> only change needed is to add number of CF rows per year, something like:
>
>
> MathUtil.divide( cashFlows[j], Math.pow( 1.0 + irr, j/12 ) ));
>
>
> Tomas]]>
Jason Weathersby 2011-07-12T05:21:30-00:00
Re: IRR function https://www.eclipse.org/forums/index.php/mv/msg/219172/695647/#msg_695647 , I wrote my own IRR function in postgres:
```drop function bizdata.mirr(Double Precision[], double precision);

CREATE OR REPLACE FUNCTION bizdata.mirr (
input_values Double Precision[],
irrguess double precision
) RETURNS double precision
AS \$\$
DECLARE

CashFlowSum	double precision;  -- CashFlowSum
n     integer;
PositiveCFcount	integer;
NegativeCFcount	integer;
npv		double precision;
npv2		double precision;
irr		double precision;
irr_lower	double precision;
irr_upper	double precision;
irr_step	double precision;
prev_irr	double precision;
BEGIN
CashFlowSum := 0.0;
npv := 0.0;
npv2 := 0.0;
prev_irr := 0.0;
irr := irrguess;
irr_lower := 0.1;
irr_upper := 0.1;
irr_step := 0.01;
PositiveCFcount := 0;
NegativeCFcount := 0;
n := array_upper(input_values,1) - array_lower(input_values,1) + 1;

for i in 1..n loop
CashFlowSum :=  CashFlowSum + input_values[i] ;
if input_values[i] > 0 then
PositiveCFcount := PositiveCFcount + 1;
else
NegativeCFcount := NegativeCFcount + 1;
end if;
end loop;

-- Return -999 if IRR cannot be calculated
if n<= 2 or input_values = 0 or PositiveCFcount = 0 or NegativeCFcount = 0 or CashFlowSum < 0 then
return -999;
end if;

-- GET INITIAL BOUNDS

-- Get NPV using irr guess
for i in 1..n loop
npv :=  npv + input_values[i]/(1.0+irr)^((i-1)/12.0) ;
end loop;

-- Which way will be used to get initial bounds
if npv > 0 then
irr_step:= 0.05;
else
irr_step:= -0.05;
end if;

-- Get initial lower and upper bound
loop
prev_irr := irr;
irr := irr+irr_step;
npv := 0.0;
for i in 1..n loop
npv :=  npv + input_values[i]/(1.0+irr)^((i-1)/12.0) ;
end loop;
exit when (irr_step < 0 and npv > 0) or (irr_step > 0 and npv <0);
end loop;

if prev_irr > irr then
irr_lower := irr;
irr_upper := prev_irr;
else
irr_lower := prev_irr;
irr_upper := irr;
end if;

-- Loop to find final IRR
loop
npv := 0.0;
npv2 := 0.0;
for i in 1..n loop
npv :=  npv + input_values[i]/(1.0+irr_lower)^((i-1)/12.0) ;
npv2 :=  npv2 + input_values[i]/(1.0+(irr_lower+(irr_upper-irr_lower)/2.0))^((i-1)/12.0) ;
end loop;

if (npv > 0 and npv2 > 0) then
irr_lower := irr_lower + (irr_upper-irr_lower)/2.0;
elseif (npv > 0 and npv2 < 0) then
irr_upper  :=  irr_lower + (irr_upper-irr_lower)/2.0;
end if;
exit when abs(irr_upper - irr_lower) < 0.00000001;
end loop;

return (irr_upper+irr_lower)/2.0;
END;
\$\$ LANGUAGE 'plpgsql';
```

]]>
Tomas Greif 2011-07-12T08:33:07-00:00
Re: IRR function https://www.eclipse.org/forums/index.php/mv/msg/219172/696285/#msg_696285
Jason

On 7/12/2011 4:33 AM, TomasGreif wrote:
> I'm much better in SQL than java stuff :), I wrote my own IRR function
> in postgres:
>
> drop function bizdata.mirr(Double Precision[], double precision);
>
> CREATE OR REPLACE FUNCTION bizdata.mirr (
> input_values Double Precision[],
> irrguess double precision
> ) RETURNS double precision
> AS \$\$
> DECLARE
>
> CashFlowSum double precision; -- CashFlowSum
> n integer;
> PositiveCFcount integer;
> NegativeCFcount integer;
> npv double precision;
> npv2 double precision;
> irr double precision;
> irr_lower double precision;
> irr_upper double precision;
> irr_step double precision;
> prev_irr double precision;
> BEGIN
> CashFlowSum := 0.0;
> npv := 0.0;
> npv2 := 0.0;
> prev_irr := 0.0;
> irr := irrguess;
> irr_lower := 0.1;
> irr_upper := 0.1;
> irr_step := 0.01;
> PositiveCFcount := 0;
> NegativeCFcount := 0;
> n := array_upper(input_values,1) - array_lower(input_values,1) + 1;
>
>
> for i in 1..n loop
> CashFlowSum := CashFlowSum + input_values[i] ;
> if input_values[i] > 0 then
> PositiveCFcount := PositiveCFcount + 1;
> else
> NegativeCFcount := NegativeCFcount + 1;
> end if;
> end loop;
>
> -- Return -999 if IRR cannot be calculated
> if n<= 2 or input_values = 0 or PositiveCFcount = 0 or
> NegativeCFcount = 0 or CashFlowSum < 0 then
> return -999;
> end if;
>
>
> -- GET INITIAL BOUNDS
>
> -- Get NPV using irr guess
> for i in 1..n loop
> npv := npv + input_values[i]/(1.0+irr)^((i-1)/12.0) ;
> end loop;
>
> -- Which way will be used to get initial bounds
> if npv > 0 then
> irr_step:= 0.05;
> else
> irr_step:= -0.05;
> end if;
>
> -- Get initial lower and upper bound
> loop
> prev_irr := irr;
> irr := irr+irr_step;
> npv := 0.0;
> for i in 1..n loop
> npv := npv + input_values[i]/(1.0+irr)^((i-1)/12.0) ;
> end loop;
> exit when (irr_step < 0 and npv > 0) or (irr_step > 0 and npv <0); end
> loop;
>
> if prev_irr > irr then
> irr_lower := irr;
> irr_upper := prev_irr;
> else
> irr_lower := prev_irr;
> irr_upper := irr;
> end if;
>
>
> -- Loop to find final IRR
> loop
> npv := 0.0;
> npv2 := 0.0;
> for i in 1..n loop
> npv := npv + input_values[i]/(1.0+irr_lower)^((i-1)/12.0) ;
> npv2 := npv2 +
> input_values[i]/(1.0+(irr_lower+(irr_upper-irr_lower)/2.0))^((i-1)/12.0) ;
> end loop;
>
> if (npv > 0 and npv2 > 0) then
> irr_lower := irr_lower + (irr_upper-irr_lower)/2.0;
> elseif (npv > 0 and npv2 < 0) then
> irr_upper := irr_lower + (irr_upper-irr_lower)/2.0;
> end if;
> exit when abs(irr_upper - irr_lower) < 0.00000001; end loop;
>
> return (irr_upper+irr_lower)/2.0;
> END;
> \$\$ LANGUAGE 'plpgsql';
>
>
>]]>
Jason Weathersby 2011-07-13T15:58:16-00:00
Re: IRR function https://www.eclipse.org/forums/index.php/mv/msg/219172/720279/#msg_720279 Jason Weathersby wrote on Fri, 08 July 2011 14:47
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 )
{
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;

final Object cashFlowStart = cashFlows;
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++ )
{
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

This is not quite the Newton Raphson Method to find IRR. This may be an alternative algorithm to find IRR but it surely does not look like Newton Raphson Method

IRR Calculation using Newton Raphson method requires setting up the cash flows as a function of x say f(x) which is the sum of discounted net cash flows in other terms it is the NPV Equation

Once we have the function f(x), the next step is to find its derivative or its differential f'(x)

Now we are ready to use the IRR Formula using the Newton Raphson method which uses an iterative process to find IRR. If the values from successive calculations converge we assume the rate as the IRR. If the values from successive calculations do not converge we discard the calculations are restart the algorithm by selecting a different seed value for the algorithm

The IRR Calculation using Newton Raphson method are shown below for a sample series of cash flows. The rate of return you get is a periodic IRR value, if your cash flows are monthly then you will get a monthly IRR rate. You can annualize the rate if you wish to with this formula {1+IRR)^12}-1 to get an annualized IRR from monthly IRR

We will select unusual cash flows of

-800 5000 -5000

Using 10% as seed value we get an IRR of 25% as shown below

f(x) = -800(1+i)^0 +5000(1+i)^-1 -5000(1+i)^-2

f'(x) = -5000(1+i)^-2 +10000(1+i)^-3

x0 = 0.1
f(x0) = -386.7769
f'(x0) = 3380.9161
x1 = 0.1 - -386.7769/3380.9161 = 0.214400017026
Error Bound = 0.214400017026 - 0.1 = 0.1144 > 0.000001

x1 = 0.214400017026
f(x1) = -73.1057
f'(x1) = 2193.2398
x2 = 0.214400017026 - -73.1057/2193.2398 = 0.24773228325
Error Bound = 0.24773228325 - 0.214400017026 = 0.033332 > 0.000001

x2 = 0.24773228325
f(x2) = -4.3725
f'(x2) = 1936.3245
x3 = 0.24773228325 - -4.3725/1936.3245 = 0.249990427286
Error Bound = 0.249990427286 - 0.24773228325 = 0.002258 > 0.000001

x3 = 0.249990427286
f(x3) = -0.0184
f'(x3) = 1920.0684
x4 = 0.249990427286 - -0.0184/1920.0684 = 0.24999999983
Error Bound = 0.24999999983 - 0.249990427286 = 1.0E-5 > 0.000001

x4 = 0.24999999983
f(x4) = -0
f'(x4) = 1919.9998
x5 = 0.24999999983 - -0/1919.9998 = 0.25
Error Bound = 0.25 - 0.24999999983 = 0 < 0.000001
IRR = x5 = 0.25 or 25%

We could now select a seed value of say 200% to find a new IRR value of 400% as shown below

f(x) = -800(1+i)^0 +5000(1+i)^-1 -5000(1+i)^-2

f'(x) = -5000(1+i)^-2 +10000(1+i)^-3

x0 = 2
f(x0) = 311.1111
f'(x0) = -185.1852
x1 = 2 - 311.1111/-185.1852 = 3.67999999751
Error Bound = 3.67999999751 - 2 = 1.68 > 0.000001

x1 = 3.67999999751
f(x1) = 40.0906
f'(x1) = -130.7276
x2 = 3.67999999751 - 40.0906/-130.7276 = 3.9866727162
Error Bound = 3.9866727162 - 3.67999999751 = 0.306673 > 0.000001

x2 = 3.9866727162
f(x2) = 1.6021
f'(x2) = -120.4273
x3 = 3.9866727162 - 1.6021/-120.4273 = 3.99997633894
Error Bound = 3.99997633894 - 3.9866727162 = 0.013304 > 0.000001

x3 = 3.99997633894
f(x3) = 0.0028
f'(x3) = -120.0008
x4 = 3.99997633894 - 0.0028/-120.0008 = 3.99999999993
Error Bound = 3.99999999993 - 3.99997633894 = 2.4E-5 > 0.000001

x4 = 3.99999999993
f(x4) = 0
f'(x4) = -120
x5 = 3.99999999993 - 0/-120 = 4
Error Bound = 4 - 3.99999999993 = 0 < 0.000001
IRR = x5 = 4 or 400%

IRR values may not be unique or in some cases an IRR may not exist at all