[置顶] 从业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 |
