Bug 65988

One workaround is to call setForceFormulaRecalculation(true) on the XSSFWorkbook before writing it out. Excel will then recalculate the value when it loads the xlsx.

The functions support in POI is of variable quality. The project is maintained by volunteers and some of the function implementations are incomplete.

Can you fill in the POI version that you are seeing the issue in? It's possible that newer versions of POI have better implementations of some functions.

Edit: output library giving is 4.6918646695350337E-17
Whereas output excel giving is 0.0009480170844060

Please find the demo project: https://github.com/pranayhere/poi-bug

Dependencies : 

poi: 5.2.2
poi-ooxml: 5.2.2
poi-ooxml-schemas: 4.1.2 
openjdk version: 1.8.0_302

poi-ooxml-schemas: 4.1.2 is not meant to be used with POI 5 - use poi-ooxml-lite 5.2.2 instead

tried changing it, getting the same results.

this function was added in 2011 based on https://bz.apache.org/bugzilla/show_bug.cgi?id=52050 - hasn't been touched since (except for general code tidy up)

I added r1899452 with a test case that shows https://support.microsoft.com/en-us/office/rate-function-9f665657-4a7e-4bb7-a030-83fc59e748ce example works but that Pranay's example does not.

I have not attempted to rewrite the rate function to fix the issue. It's possible that the signs of the pmt and pv are confusing the existing POI impl. The MS example has negative pmt and positive pv.

according the stackoverflow answer on https://bz.apache.org/bugzilla/show_bug.cgi?id=54349 - the convergence criteria in the existing code is wrong

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

Thanks Yegor - do you think we should just make your change then?

Yes, I think we should borrow this code. 
NumPy is BSD 3 which is permissive and compatible with the Apache licence.

Created attachment 38240 [details]
RATE function fixes

Use the implementation of RATE ported from the NumPy library: 
https://github.com/numpy/numpy/blob/648fe68f79dfbad21947db4b55873703db0675c4/numpy/lib/financial.py#L580-L650

I added a few more unit tests from the LibreOffice documentation (https://wiki.documentfoundation.org/Documentation/Calc_Functions/RATE) and NumPy tests (https://github.com/numpy/numpy-financial/blob/master/numpy_financial/tests/test_financial.py) 

One test case is still disabled: RATE(3,10,900,1,0,0.5) does not converge in Excel and LibreOffice , but POI and NumPy return a result. Other examples of infeasible solutions pass, only this one fails.

Patch applied in r1899668

Thanks Yegor Kozlov, in which version of POI these changes will be available? Any release date you have?

See https://poi.apache.org/changes.html -- no date agreed for the POI 5.2.3. Possibly September but could be later.

Thanks PJ Fanning