You Asked
Hello Tom,
I would like to if there is any way to calculate IRR (internal rate of return) with the help of stored function/ procedure. IRR is normal function available in Excel which allow user to calculate the IRR on base of cashflows.
For Example
Principle Amount -34,178,795.54
Installments
1 4,516,270.00
2 4,516,270.00
3 3,793,666.00
4 3,793,666.00
5 3,793,666.00
6 3,793,666.00
7 3,186,680.00
8 3,186,680.00
9 3,186,680.00
10 3,186,680.00
11 2,676,811.00
12 2,676,811.00
13 2,676,811.00
14 2,676,811.00
and the IRR calculate by Excel is 21.3785% (Formula = =IRR(D5:D19)*4 For quarterly installments
Thanks for your help.
Regards,
and Connor said...
IRR is based around iterating over the data over and over. So I tried to keep this efficient by tackling it from a broad scale, and then narrowing the range (and hence improving the accuracy) over each iteration. I haven't tested it thoroughly for boundary conditions but it should get you started
SQL> @drop t
Y1 Y2
----------------------- -------------------------
TABLE cascade constraints purge
1 row selected.
Table dropped.
SQL> create table t ( id int, amt number );
Table created.
SQL> insert into t values (0,-34178795.54 );
1 row created.
SQL> insert into t values (1, 4516270.00 );
1 row created.
SQL> insert into t values (2, 4516270.00 );
1 row created.
SQL> insert into t values (3, 3793666.00 );
1 row created.
SQL> insert into t values (4, 3793666.00 );
1 row created.
SQL> insert into t values (5, 3793666.00 );
1 row created.
SQL> insert into t values (6, 3793666.00 );
1 row created.
SQL> insert into t values (7, 3186680.00 );
1 row created.
SQL> insert into t values (8, 3186680.00 );
1 row created.
SQL> insert into t values (9, 3186680.00 );
1 row created.
SQL> insert into t values (10, 3186680.00 );
1 row created.
SQL> insert into t values (11, 2676811.00 );
1 row created.
SQL> insert into t values (12, 2676811.00 );
1 row created.
SQL> insert into t values (13, 2676811.00 );
1 row created.
SQL> insert into t values (14, 2676811.00 );
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL> set serverout on
SQL> declare
2 type nlist is table of number index by pls_integer;
3 l_values nlist;
4 l_irr number;
5 l_principal number;
6 l_adjusted_amts number := 0;
7 divisor number;
8 begin
9
10 select amt bulk collect into l_values from t;
11
12 l_irr := 0;
13
14 for multiplier in 2 .. 8 loop
15 l_adjusted_amts := 0;
16 divisor := power(10,multiplier);
17 l_irr := l_irr - 1/divisor;
18
19 loop
20 exit
21 when l_adjusted_amts <> 0 And
22 l_adjusted_amts > l_values(l_values.count) * 5 / divisor And
23 l_adjusted_amts < l_values(l_values.count) * ( 1 + 5 / divisor );
24 l_principal := abs(l_values(1));
25 l_adjusted_amts := 0;
26 l_irr := l_irr + 1/divisor;
27
28 for idx in 2 .. l_values.count loop
29 l_adjusted_amts := (l_principal * (1 + l_irr)) - l_values(idx);
30 l_principal := l_adjusted_amts;
31 end loop;
32 l_principal := 0;
33 end Loop;
34 dbms_output.put_line('l_irr at scale '||multiplier||'='||l_irr);
35 l_irr := l_irr - 1/divisor;
36 end loop;
37 End;
38 /
l_irr at scale 2=.06
l_irr at scale 3=.054
l_irr at scale 4=.0535
l_irr at scale 5=.05345
l_irr at scale 6=.053447
l_irr at scale 7=.0534463
l_irr at scale 8=.05344622
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
For completeness, I also found this one in SQL
http://www.sql.ru/forum/434932/xirr-function-in-pl-sql?hl=irr#4567080 =======================
Addenda:
Here's perhaps a cleaner version
SQL> set serverout on
SQL> declare
2 type nlist is table of number index by pls_integer;
3 l_values nlist;
4
5 l_threshold number := 0.005;
6 l_guess number := l_threshold + 1;
7 l_next_guess number := 2;
8 l_irr number := 1;
9
10 begin
11 select cash_flow bulk collect into l_values from test_tbl order by id asc;
12
13 while abs(l_guess) > l_threshold
14 loop
15 l_guess := 0;
16 l_next_guess := 0;
17 for i in 1 .. l_values.count
18 loop
19 l_guess := l_guess + l_values(i)/power(1+l_irr/100, i-1);
20 l_next_guess := l_next_guess + -i*l_values(i)/power(1+l_irr/100, i-1);
21 end loop;
22 l_irr := l_irr - l_guess/l_next_guess;
23
24 --dbms_output.put_line('l_irr='||l_irr);
25 --dbms_output.put_line('l_guess='||l_guess);
26 --dbms_output.put_line('l_next_guess='||l_next_guess);
27 end loop;
28 dbms_output.put_line('Final l_irr='||l_irr);
29 end;
30 /
Final l_irr=.4904087585221242754073170297132463342012
PL/SQL procedure successfully completed.
Rating
(7 ratings)
Is this answer out of date? If it is, please let us know via a Comment