[置顶] 从业N年初次用到高等数学：用PL/SQL算IRR

从业N年，一直做企业内部系统。

数年前，ITS部门来了一个A国（not USA）人，年龄比咱还小一轮。当然，自小喝洋墨水长大的，英语很溜。有次喝高了，对咱很不客气地说，你念那多书有啥用？我连大学的门都没进过，照样级别比你高一级。我听后很郁闷。但仔细想来，做公司内部系统吧，小学程度，会四则运算，逻辑上不糊涂，就可在世界500强企业的ITS部门混饭吃。

又过了几年，那A国人又高就别处，咱还照样做着小学生就能做的事情。这事情就一个结，从未了结。

最近，终于有个机会让咱用了一回高等数学。给读书无用论一记响亮的耳瓜。

做生意，图回报，这是真理。投入多少，多长时间能收回，这就要计算IRR。用EXCEL的话，简单，用Oracle的话，麻烦。（这不是开玩笑）

IRR如何计算，有兴趣的朋友可参考

WikiPedia：http://en.wikipedia.org/wiki/Internal_rate_of_return 

或 http://zainco.blogspot.jp/2008/08/internal-rate-of-return-using-newton.html

或 http://www.corality.com/tutorials/iterative-approach-calculating-internal-rate-return-irr

当npv=0时的r就是IRR。

展开后就是个多项式，

当多项式等于0时，r等于多少？

这个问题没有精确解（解析解），只有近似解（数值解）。

通常使用的方法是Newton-Raphson方法。

咱还是实用主义，先看编码。 

原理可参考以上几个URL。

PL/SQL编码

1. 计算XIRR的函数

这段代码是从Oracle Forum上抄来的， http://forums.oracle.com/forums/thread.jspa?threadID=549939

必须预先定义2个Type：

create or replace type p_date_array is varray(250) of date;

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

create or replace FUNCTION            "XIRR" (p_date_array in p_date_array,
                                  p_amount_array in t_amount_array,
                                  p_guess in number default 0
                                 )
  RETURN NUMBER
  IS
  ----- Reference: http://forums.oracle.com/forums/thread.jspa?threadID=549939 
  -- pre-defined types:
  -- create or replace type p_date_array is varray(250) of date;
  -- create or replace type t_amount_array is varray(250) of number;

  BEGIN
    declare
      z number := 0;
      step_limit number := 0;
      temp number;
      rtn_err number := -9999999;
      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;
        if (step_limit = 10000) then
          return rtn_err; -- a kind of error
          exit;
        end if;
        exit when(round(temp * 100000) = 0);
      end loop;
        return d;
    EXCEPTION
        WHEN OTHERS THEN
        return rtn_err;    
    end;
 END XIRR;
 

2. 计算IRR的函数

create or replace FUNCTION            "IRR" ( p_amount_array in t_amount_array,
                                p_guess in number default 0
                               )
  RETURN NUMBER
  IS
  PDA P_DATE_ARRAY;

BEGIN
PDA := P_DATE_ARRAY() ; -- initialize Varray with NULL, count=0
  for i in 1 .. p_amount_array.count
  loop
    PDA.EXTEND; -- Add 1 element to Varray
    PDA(i) := sysdate + 365*(i-1) ;
  end loop;
  Return XIRR(P_DATE_ARRAY => PDA,P_AMOUNT_ARRAY => p_amount_array, P_GUESS => P_GUESS);
END;
 

 3. 相关函数，XNPV，NPV

create or replace FUNCTION            "XNPV" (p_date_array in p_date_array,
                                  p_amount_array in t_amount_array,
                                  p_discount_rate in number
                                 )
  RETURN NUMBER
  IS
  ----- Reference: http://forums.oracle.com/forums/thread.jspa?threadID=549939 
  -- pre-defined types:
  -- create or replace type p_date_array is varray(250) of date;
  -- create or replace type t_amount_array is varray(250) of number;

      z number := 0;
      step_limit number := 0;
      temp number;
      step number := 0.1;
      xnpv number := 0.5;
      l_MaxDate date;
      l_MinDate date;
      srok number;
    begin
/*********** Formular *************
XNPV = I(1) + I(2)/(1+r)^(d(2)-d(1))/365 + ........ + I(n)/(1+r)^(d(n)-d(1))/365
***********************************/
  xnpv := p_amount_array(1);
  for i in 2 .. p_amount_array.count
    loop
       xnpv := xnpv + p_amount_array(i)/power((1 + p_discount_rate),(p_date_array(i) - p_date_array(1))/365);
    end loop;
 
  return xnpv;
end;
 

 

create or replace FUNCTION            "NPV" ( p_amount_array in t_amount_array,
                                p_discount_rate in number
                               )
  RETURN NUMBER
  IS
  PDA P_DATE_ARRAY;
BEGIN
PDA := P_DATE_ARRAY() ; -- initialize Varray with NULL, count=0
  for i in 1 .. p_amount_array.count
  loop
    PDA.EXTEND; -- Add 1 element to Varray
    PDA(i) := sysdate + 365*(i-1) ;
--htp.p(PDA(i));    
  end loop;
  Return XNPV(P_DATE_ARRAY => PDA,P_AMOUNT_ARRAY => p_amount_array, P_DISCOUNT_RATE => p_discount_rate);
END;
 

4. 用例

原始数据

2001/1/1	2002/1/1	2003/1/1	2004/1/1	2005/1/1	2006/1/1
-6264695	667885.4979	329584.128	329584.128	329584.1	3527014

DECLARE
    PDA P_DATE_ARRAY;
    PAA T_AMOUNT_ARRAY;
    P_GUESS NUMBER;
    P_DISCOUNT_RATE Number := 0.0743;
    v_Return NUMBER;
  BEGIN
    -- Modify the code to initialize the variable
    PDA := P_DATE_ARRAY(
                        to_date('2007/01/01','yyyy/mm/dd'),
                        to_date('2008/01/01','yyyy/mm/dd'),
                        to_date('2009/01/01','yyyy/mm/dd'),
                        to_date('2010/01/01','yyyy/mm/dd'), 
                        to_date('2011/01/01','yyyy/mm/dd'),
                        to_date('2012/01/01','yyyy/mm/dd'),
                        to_date('2013/01/01','yyyy/mm/dd'),
                        to_date('2014/01/01','yyyy/mm/dd')
);
    -- Modify the code to initialize the variable
    PAA := T_AMOUNT_ARRAY(-112651.395506849,274684.931506849); 

    P_GUESS := NULL;
    
    v_Return := XIRR(P_DATE_ARRAY => PDA,P_AMOUNT_ARRAY => PAA,P_GUESS => P_GUESS);
    DBMS_OUTPUT.PUT_LINE('XIRR = ' || v_Return);
    v_Return := IRR(P_AMOUNT_ARRAY => PAA,P_GUESS => P_GUESS);
    DBMS_OUTPUT.PUT_LINE('IRR = ' || v_Return);

    v_Return := XNPV(P_DATE_ARRAY => PDA,P_AMOUNT_ARRAY => PAA,P_DISCOUNT_RATE => P_DISCOUNT_RATE);
    DBMS_OUTPUT.PUT_LINE('XNPV = ' || v_Return);
    v_Return := NPV(P_AMOUNT_ARRAY => PAA,P_DISCOUNT_RATE => P_DISCOUNT_RATE);
    DBMS_OUTPUT.PUT_LINE('NPV = ' || v_Return);


  END;

结果：

XIRR = 1.4383624397218227386474609375
IRR = 1.4383624397218227386474609375
XNPV = 143035.9651064331372056222656613608861584
NPV = 143035.9651064331372056222656613608861584

Statement processed.

0.23 seconds

5. 与EXCEL计算结果的比较

	Excel	Oracle	差
XIRR	-0.04458783	-.044584477263	-3.35245E-06
IRR	-4%	-.0446097485299	-3.67924E-11
XNPV	-2379917.807	-2380026.06389	108.2568879
NPV	¥-2,214,823.89	-2379385.302111	164561.4141