Oracle SQL或PLSQL中的IRR计算

你好，汤姆，

我想在存储功能/过程的帮助下，是否有任何方法可以计算IRR (内部收益率)。内部收益率是Excel中可用的正常函数，允许用户根据现金流量计算内部收益率。

例如


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

而Excel计算的IRR为21.3785% (公式 = = IRR(D5:D19)* 4为季度分期付款

谢谢你的帮助。

问候，

IRR基于一遍又一遍地迭代数据。因此，我试图通过从广泛的规模解决它来保持这种效率，然后在每次迭代中缩小范围 (从而提高准确性)。我还没有对边界条件进行彻底的测试，但是它应该可以帮助您开始

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>
复制

为了完整，我在SQL中也找到了这个

http://www.sql.ru/forum/434932/xirr-function-in-pl-sql?hl=irr#4567080

=

附录:

这可能是一个更干净的版本

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.
复制