[Bug 65988] Rate function giving incorrect results

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=65988

--- Comment #9 from Yegor Kozlov <ye...@gmail.com> ---
Its a nuance of the implementation. In some cases it solves the equation wrong.

Fixing the existing code is possible, but may take time as it requires fluency
in numerical recipes. 

The NumPy library provides a nice implementation of the RATE function, see
https://github.com/numpy/numpy/blob/v1.17.5/numpy/lib/financial.py#L580-L650

Replacing the existing implementation with the ported code below fixes the bug.
In my test POI returned 0.0009480267492294 which is within the 1E-8 accuracy
with what Excel returns ( 0.0009480170844060). 


    private static double _g_div_gp(double r, double n, double p, double x,
double y, double w) {
        double t1 = Math.pow(r+1, n);
        double t2 = Math.pow(r+1, n-1);
        return (y + t1*x + p*(t1 - 1)*(r*w + 1)/r) /
                (n*t2*x - p*(t1 - 1)*(r*w + 1)/(Math.pow(r, 2) + n*p*t2*(r*w +
1)/r +
                p*(t1 - 1)*w/r));
    }

    static double calculateRate(double nper, double pmt, double pv, double fv,
double type, double guess){
        double tol = 1e-8;
        double maxiter = 100;

        double rn = guess;
        int iter = 0;
        boolean close = false;
        while (iter < maxiter && !close){
            double rnp1 = rn - _g_div_gp(rn, nper, pmt, pv, fv, type);
            double diff = Math.abs(rnp1 - rn);
            close = diff < tol;
            iter += 1;
            rn = rnp1;

        }
        if(!close)
            return Double.NaN;
        else {
            return rn;
        }
    }



Yegor

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org