你的位置:首页 > Java教程

[Java教程]Java实现Excel中的NORMSDIST函数和NORMSINV函数


由于工作中需要将Excel中的此两种函数转换成java函数,从而计算内部评级的资本占用率和资本占用金额。经过多方查阅资料和整理,总结出如下两个转换方法

标准正态分布累计函数NORMSDIST:

public static double NormSDist(double z) {    // this guards against overflow    if (z > 6)      return 1;    if (z < -6)      return 0;    double gamma = 0.231641900,         a1 = 0.319381530,         a2 = -0.356563782,         a3 = 1.781477973,         a4 = -1.821255978,         a5 = 1.330274429;    double x = Math.abs(z);    double t = 1 / (1 + gamma * x);    double n = 1        - (1 / (Math.sqrt(2 * Math.PI)) * Math.exp(-z * z / 2))        * (a1 * t + a2 * Math.pow(t, 2) + a3 * Math.pow(t, 3) + a4            * Math.pow(t, 4) + a5 * Math.pow(t, 5));    if (z < 0)      return 1.0 - n;    return n;  }

标准正态分布累计反函数NORMSINV:

public static double normsinv(double p) {    double LOW = 0.02425;    double HIGH = 0.97575;    double a[] = { -3.969683028665376e+01, 2.209460984245205e+02,        -2.759285104469687e+02, 1.383577518672690e+02,        -3.066479806614716e+01, 2.506628277459239e+00 };    double b[] = { -5.447609879822406e+01, 1.615858368580409e+02,        -1.556989798598866e+02, 6.680131188771972e+01,        -1.328068155288572e+01 };    double c[] = { -7.784894002430293e-03, -3.223964580411365e-01,        -2.400758277161838e+00, -2.549732539343734e+00,        4.374664141464968e+00, 2.938163982698783e+00 };    double d[] = { 7.784695709041462e-03, 3.224671290700398e-01,        2.445134137142996e+00, 3.754408661907416e+00 };    double q, r;    if (p < LOW) {      q = Math.sqrt(-2 * Math.log(p));      return (((((c[0] * q + c[1]) * q + c[2]) * q + c[3]) * q + c[4])          * q + c[5])          / ((((d[0] * q + d[1]) * q + d[2]) * q + d[3]) * q + 1);    } else if (p > HIGH) {      q = Math.sqrt(-2 * Math.log(1 - p));      return -(((((c[0] * q + c[1]) * q + c[2]) * q + c[3]) * q + c[4])          * q + c[5])          / ((((d[0] * q + d[1]) * q + d[2]) * q + d[3]) * q + 1);    } else {      q = p - 0.5;      r = q * q;      return (((((a[0] * r + a[1]) * r + a[2]) * r + a[3]) * r + a[4])          * r + a[5])          * q          / (((((b[0] * r + b[1]) * r + b[2]) * r + b[3]) * r + b[4])              * r + 1);    }  }

不足之处,敬请指出