Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
How to calculate the IRR function in OBIEE?
Hi people,
How to calculate the Internal Rate of Return (IRR) in OBIEE?, since there is no function.
Thank you all
Luciana
Answers
-
-
Just to comment, OLAP option is a option that you pay for.
0 -
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
0 -
Hum. Thank you for this information Cesar
0 -
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]).
0 -
Good idea Cesar.
I will check this information ;-)
Thank you
Luciana
0 -
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
0 -
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;
0 -
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
0 -
Hello Luciana,
Sorry by the delay, you have only change the logic about of the function, don't considere the change between dates:
IRR
- 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;
0