nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle AI and Analytics

Child Item

How to calculate the IRR function in OBIEE?

862823

Hi people,

How to calculate the Internal Rate of Return (IRR) in OBIEE?, since there is no function.

Thank you all

Luciana

Find more posts tagged with

Accepted answers

All comments

Gbenga Ajakaye

This might help. https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1105.htm#OLADM566

cesar.advincula.o

Just to comment, OLAP option is a option that you pay for.

862823

Thank you for the sugestion :-)

I had found this link but could not understand how I would use it in the report.

The IRR needs to be calculated at run time because it will depend on three user information: time period, rate, and value

:-(

Luciana

862823

Hum. Thank you for this information Cesar

cesar.advincula.o

Check this link could help you, the idea is that you could create an database function to calculate IRR and call this function in your DB using EVALUATE('db_function(%1...%N)' [AS datatype] [, column1, columnN]).

PL/SQL to calculate XIRR in Oracle

862823

Good idea Cesar.

I will check this information ;-)

Thank you

Luciana

862823

Hi Cesar,

I tried to execute the steps of the PL/SQL to calculate XIRR in Oracle link and the function returned the same data found by Satyaki_De (v_Return = .248719024658203125)

I simulated the calculation of the IRR in excel for the same values reported and the result was different

This function is correct?

Att,

Luciana

cesar.advincula.o

Hello Luciana,

In a real life the dates of the cash flow are important because of the time takes a important part in the profitability , if you see the XIRR is a more exactly IRR because it considere the time to compute the rate, in the sample of the code:

the xirr is about 24.87%, because you have a clash flow like this:

01.01.2017 -100

01.07.2007 58

01.01.2008 60

check the result in excel

If you use IRR you say that cash flow has the same periods, and you for example could do something to change to make the time evenly:

Hope it helps!

the code:

create or replace type t_date_array is varray(200000) of date;

create or replace type t_amount_array is varray(200000) of number;

create or replace FUNCTION XIRR(p_date_array in t_date_array,

p_amount_array in t_amount_array,

p_guess in number default 0

) RETURN NUMBER AS

BEGIN

declare

z number := 0;

step_limit number := 0;

temp number;

step number := 0.1;

d number := 0.5;

l_MaxDate date;

l_MinDate date;

srok number;

begin

l_MaxDate := p_date_array(1);

l_MinDate := p_date_array(1);

-- 5@2K9 ?@>E>4: ?>8A: <0:A. 40BK 8 =0;8G8O E>BO 1K >4=>3> <8=CA0 8 ?;NA0 2 ?>B>:0E

for i in 1 .. p_date_array.count loop

if p_date_array(i) > l_MaxDate then

l_MaxDate := p_date_array(i);

end if;

if p_date_array(i) < l_MinDate then

l_MinDate := p_date_array(i);

end if;

end loop;

select months_between(l_MaxDate, l_MinDate) into srok from dual;

loop

temp := p_amount_array(1);

for i in 2 .. p_amount_array.count loop

temp := temp +

p_amount_array(i) /

power((1 + d), (p_date_array(i) - p_date_array(1)) / 365);

end loop;

if (temp > 0) and (z = 0) then

step := step / 2;

z := 1;

end if;

if (temp < 0) and (z = 1) then

step := step / 2;

z := 0;

end if;

if (z = 0) then

d := d - step;

else

d := d + step;

end if;

step_limit := step_limit + 1;

exit when((round(temp * 100000) = 0) or (step_limit = 10000));

end loop;

return d;

end;

END XIRR;

DECLARE

P_DATE_ARRAY T_DATE_ARRAY;

P_AMOUNT_ARRAY T_AMOUNT_ARRAY;

P_GUESS NUMBER;

v_Return NUMBER;

BEGIN

-- Modify the code to initialize the variable

P_DATE_ARRAY := T_DATE_ARRAY(to_date('01.01.2007','dd.mm.yyyy'),to_date('01.07.2007','dd.mm.yyyy'),to_date('01.01.2008','dd.mm.yyyy'));

-- Modify the code to initialize the variable

P_AMOUNT_ARRAY := T_AMOUNT_ARRAY(-100,58,60);

P_GUESS := NULL;

v_Return := XIRR(

P_DATE_ARRAY => P_DATE_ARRAY,

P_AMOUNT_ARRAY => P_AMOUNT_ARRAY,

P_GUESS => P_GUESS

);

DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);

END;

862823

Hello Cesar,

Thank you for your attention :-)

I realized that the period of all examples found on the web, are from year to year.

The XIRR function shows different values when periods change from year to year to month to month.
The TIR function does not change the values when these periods are changed.

I need the XIRR function to show the same IRR result for a month-to-month period.
Is this possible?

Luciana

cesar.advincula.o

Hello Luciana,

Sorry by the delay, you have only change the logic about of the function, don't considere the change between dates:

IRR

Screen Shot 2017-01-07 at 12.03.18 PM.png

Screen Shot 2017-01-07 at 12.09.20 PM.png

Screen Shot 2017-01-07 at 12.05.43 PM.png

- The code:

create or replace FUNCTION IRR( p_amount_array in t_amount_array)

RETURN NUMBER AS

BEGIN

DECLARE

v_Return NUMBER;

z number := 0;

step_limit number := 0;

vpn number;

step number := 0.1;

d number := 0.99;

BEGIN

loop

vpn := p_amount_array(1);

for i in 2 .. p_amount_array.count loop

vpn := vpn + p_amount_array(i) /

power( (1 + d), i - 1 );

end loop;

if (vpn > 0) and (z = 0) then

step := step / 2;

z := 1;

end if;

if (vpn < 0) and (z = 1) then

step := step / 2;

z := 0;

end if;

if (z = 0) then

d := d - step;

else

d := d + step;

end if;

step_limit := step_limit + 1;

--DBMS_OUTPUT.PUT_LINE(' NPV: '|| round(vpn * 100000)||' STEP: '|| step ||' RATE : ' || d );

exit when((round(vpn * 100000) = 0) or (step_limit = 10000));

end loop;

return d;

END;

END IRR;

a test:

DECLARE

P_AMOUNT_ARRAY T_AMOUNT_ARRAY;

v_Return NUMBER;

BEGIN

-- Modify the code to initialize the variable

P_AMOUNT_ARRAY := T_AMOUNT_ARRAY(-100,58,60);

v_Return := IRR(

P_AMOUNT_ARRAY => P_AMOUNT_ARRAY

);

DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);

END;

Top Leaders

Gianni Ceresa 364

Malik Sikandar Hayat 155

Brendan T 105

bae15 105

User_TD15Q 90