從業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.

?

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

?

?

[置頂] 從業N年初次用到高等數學：用PL/SQL算IRR