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

来源：互联网发布：市场营销知乎编辑：程序博客网时间：2024/05/15 06:42

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

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

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

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

做生意，图回报，这是真理。投入多少，多长时间能收回，这就要计算IRR。用EXCEL的话，简单，用Oracle的话，麻烦。（这不是开玩笑，因为Excel装备有计算IRR的函数，而Oracle则是在价格不菲的OLAP软件中才提供了计算IRR的功能。）

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

$\mathrm{NPV} = \sum_{n=0}^{N} \frac{C_n}{(1+r)^{n}}$

当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;BEGINPDA := 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;BEGINPDA := 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/12002/1/12003/1/12004/1/12005/1/12006/1/1-6264695667885.4979329584.128329584.128329584.13527014

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计算结果的比较

　ExcelOracle差XIRR-0.04458783 -.044584477263-3.35245E-06IRR-4%-.0446097485299-3.67924E-11XNPV-2379917.807 -2380026.06389108.2568879NPV¥-2,214,823.89-2379385.302111164561.4141