ORACLE SQL 实现IRR的计算
一、IRR计算的原理：
内部收益率（Internal Rate of Return (IRR)），就是资金流入现值总额与资金流出现值总额相等、净现值等于零时的折现率。
用公式 标识：-200+[30/(1+IRR)+30/(1+IRR)^2+....+30/(1+IRR)^10]=0。多次方程求解。
在计算机界求解高次方程的做法通常是利用牛顿插值法（Newton-Raphson）来实现，也有翻译牛顿迭代的。
二、关于牛顿迭代：
设r是 
的根，选取
作为r的初始近似值，过点 
做曲线 
的切线L，L的方程为 
，求出L与x轴交点的横坐标
，称x1为r的一次近似值。过点 
做曲线 的切线，并求该切线与x轴交点的横坐标 
，称
为r的二次近似值。重复以上过程，得r的近似值序列，其中，
称为r的 
次近似值。三、Java的实现方式：
import java.math.BigDecimal; public class IRRUtilMath2 { public static double irr(double[] income) { return irr(income, 0.1D); } public static double irr(double[] values, double guess) { int maxIterationCount = 20; double absoluteAccuracy = 1.0E-007D; double x0 = guess; int i = 0; while (i < maxIterationCount) { double fValue = 0.0D; double fDerivative = 0.0D; for (int k = 0; k < values.length; k++) { fValue += values[k] / Math.pow(1.0D + x0, k); fDerivative += -k * values[k] / Math.pow(1.0D + x0, k + 1); } double x1 = x0 - fValue / fDerivative; if (Math.abs(x1 - x0) <= absoluteAccuracy) { return x1; } x0 = x1; i++; } return (0.0D / 0.0D); } public static void main(String[] args) { double[] income = {-359900,19413.67,19413.67,19413.67,19413.67,19413.67,19413.67,19413.67,19413.67,19413.67,19413.67,19413.67,19413.67,19413.67,19413.67,19413.67,19413.67,19413.67,19413.67,19413.67,19413.67,19413.67,18241.01,0,0}; double ret = irr(income,0.00001d)*12 ; System.out.println(new BigDecimal(ret)); } }
四、根据Java版实现的SQL版：
--定义type create or replace type typ_cashflow_array is varray(60) of number; --实现函数 function IRR_ZEN(p_amount_array in typ_cashflow_array, p_guess in number) RETURN NUMBER is rtn_err number := -9999999; maxIterationCount number := 20; absoluteAccuracy number := 0.0000001; x0 number := p_guess; x1 number := 0; i_num integer := 0; fValue number := 0.0; fDerivative number := 0.0; BEGIN --x0 :=p_guess; while (i_num < maxIterationCount) loop fValue := 0.0; fDerivative := 0.0; for k in 1..p_amount_array.count loop fValue :=fValue+p_amount_array(k)/power(1.0 + x0, k); fDerivative :=fDerivative+(-k *p_amount_array(k)/power(1.0 + x0, k + 1)); end loop; x1 := x0 - fValue / fDerivative; if (abs(x1 - x0) <= absoluteAccuracy) then return x1; end if; x0 := x1; i_num := i_num+1; end loop; return (0.0/0.0); EXCEPTION WHEN OTHERS THEN return rtn_err; END IRR_ZEN;
五、关于函数的调用：
方法一，将现金流组成字符串，然后用函数拆解字符串。具体实现方式是
function IRR(in_varray in varchar2) return number is v_irr number; v_amount_array typ_cashflow_array; begin v_amount_array :=typ_cashflow_array(); declare v_varray_str varchar2(1000); v_length number; v_split varchar2(2); v_cnt integer; begin v_varray_str:=ltrim(rtrim(in_varray)); v_length:=0; v_split :=','; v_cnt :=1; ---劈开字符串，为数据赋值 while instr(v_varray_str,v_split)<>0 loop v_length:=v_length+1; v_amount_array.extend; v_amount_array(v_cnt) :=to_number(substr(v_varray_str,1,instr(v_varray_str,v_split)-1)); v_varray_str:=substr(v_varray_str,instr(v_varray_str,v_split)+length(v_split),length(v_varray_str)); v_cnt :=v_cnt+1; end loop; --循环的末尾追加最后一个数字 v_amount_array.extend; v_amount_array(v_cnt) :=to_number(v_varray_str); end; v_irr :=IRR_ZEN(p_amount_array => v_amount_array, p_guess =>0.1 ); return v_irr; end ;
调用的时候：
select LES_FIN_TO_DW.IRR(listagg(t.fee_amt,',') within group( order by t.pay_pd))*12 from F_LES_PAY_SCH_IRR_CAL_CASHFLOW t where t.pay_sch_id=180605104121724 order by t.pay_pd;
方法二： 直接使用类型转换公式初始化数组
select les_fin_to_dw.IRR_ZEN(cast(collect(fee_amt) as typ_cashflow_array),0.1)*12 from( select t.fee_amt from F_LES_PAY_SCH_IRR_CAL_CASHFLOW t where t.pay_sch_id=171218104072346 order by t.pay_pd );
方法一的弊端：
listagg可连接的最大字节长度是4000byte.当金额比较大，现金流周期比较长的时候可能会有问题。
方法二的弊端：
现金流的输入是有顺序的，必须先排序。
参考资料：
https://baike.baidu.com/item/%E7%89%9B%E9%A1%BF%E8%BF%AD%E4%BB%A3%E6%B3%95/10887580?fr=aladdin
