[Stick to the top] I used advanced mathematics at the beginning of N: I used PL/SQL to calculate IRR.

I have been working as an internal enterprise system for N years.

A few years ago, its department came to a country (not USA) and was younger than ours. Of course, I grew up drinking foreign ink. English is very slippery. Once I had a good time, I was very rude to you. What do you use to read so many books? I have never been in college, and I am still at a higher level than you. I was very depressed. But if you want to think about it carefully, do it in the company's internal system. At the elementary level, there will be four arithmetic operations, and the logic is not confused. You can eat it in the its Department of the world's top 500 enterprises.

After a few years, the Chinese people are far away, and we are still doing what primary school students can do. This is just a knot and never ends.

Recently, we finally had a chance to use advanced mathematics. It's useless to read a book.

 

This is the truth about doing business and returning the image. How much is invested and how long can it be recovered? This requires calculating IRR. It is easy to use Excel. It is troublesome to use Oracle. (This is not a joke)

If you are interested in how to calculate IRR, refer

Wikipedia: http://en.wikipedia.org/wiki/Internal_rate_of_return

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

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

 

When NPV = 0, the R is IRR.

After expansion, it is a polynomial,

When polynomial is equal to 0, what is r equal?

This problem does not have a precise solution (resolution), but only an approximate solution (numerical solution ).

Generally, the Newton-Raphson method is used.

 

Let's look at the code first.

For the principle, refer to the above URLs.

 

PL/SQL Encoding

1. Calculate the xirr Function

This code was copied from Oracle Forum, http://forums.oracle.com/forums/thread.jspa? Threadid = 549939

Two types must be pre-defined:

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. Calculate IRR Functions

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. Related functions: xnpv and 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. Use Cases

Raw Data

 

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;

Result:

Xirr= 1.4383624397218227386474609375
Irr= 1.4383624397218227386474609375
Xnpv= 143035.9651064331372056222656613608861584
Npv= 143035.9651064331372056222656613608861584

Statement processed.

0.23 seconds

 

5. Comparison with Excel computing results

 

	Excel	Oracle	Difference
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