Bug 65988 - Rate function giving incorrect results
Summary: Rate function giving incorrect results
Alias: None
Product: POI
Classification: Unclassified
Component: POI Overall (show other bugs)
Version: unspecified
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on: 54349
  Show dependency tree
Reported: 2022-03-31 13:44 UTC by Pranay
Modified: 2022-08-03 08:29 UTC (History)
0 users

RATE function fixes (9.12 KB, text/plain)
2022-04-05 13:39 UTC, Yegor Kozlov

Note You need to log in before you can comment on or make changes to this bug.
Description Pranay 2022-03-31 13:44:28 UTC
Hi Team,

Please find the code below

double nper = 360.0;
double pmt = 6.56;
double pv = -2000.0;

XSSFWorkbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();

XSSFRow row = (XSSFRow) sheet.createRow(1);
XSSFCell cell = row.createCell(1);

cell.setCellFormula("RATE(" + nper + ", " + pmt + ", " + pv + ")");
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

double rate = cell.getNumericCellValue();

Result : 0.0

whereas in excel I get: 0.0009480170844060

This is currently affecting one of our critical flows in production. Could you please suggest how to fix this?
Comment 1 PJ Fanning 2022-03-31 14:03:39 UTC
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.
Comment 2 PJ Fanning 2022-03-31 14:05:33 UTC
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.
Comment 3 Pranay 2022-03-31 14:27:11 UTC
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
Comment 4 PJ Fanning 2022-03-31 14:34:29 UTC
poi-ooxml-schemas: 4.1.2 is not meant to be used with POI 5 - use poi-ooxml-lite 5.2.2 instead
Comment 5 Pranay 2022-03-31 15:03:00 UTC
tried changing it, getting the same results.
Comment 6 PJ Fanning 2022-03-31 19:58:12 UTC
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)
Comment 7 PJ Fanning 2022-03-31 20:11:21 UTC
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.
Comment 8 PJ Fanning 2022-03-31 20:33:45 UTC
according the stackoverflow answer on https://bz.apache.org/bugzilla/show_bug.cgi?id=54349 - the convergence criteria in the existing code is wrong
Comment 9 Yegor Kozlov 2022-04-04 15:52:01 UTC
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;

            return Double.NaN;
        else {
            return rn;

Comment 10 PJ Fanning 2022-04-04 15:56:57 UTC
Thanks Yegor - do you think we should just make your change then?
Comment 11 Yegor Kozlov 2022-04-04 16:02:31 UTC
Yes, I think we should borrow this code. 
NumPy is BSD 3 which is permissive and compatible with the Apache licence.
Comment 12 Yegor Kozlov 2022-04-05 13:39:19 UTC
Created attachment 38240 [details]
RATE function fixes

Use the implementation of RATE ported from the NumPy library: 

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.
Comment 13 Yegor Kozlov 2022-04-08 15:10:31 UTC
Patch applied in r1899668
Comment 14 Pranay 2022-08-03 08:19:41 UTC
Thanks Yegor Kozlov, in which version of POI these changes will be available? Any release date you have?
Comment 15 PJ Fanning 2022-08-03 08:24:49 UTC
See https://poi.apache.org/changes.html -- no date agreed for the POI 5.2.3. Possibly September but could be later.
Comment 16 Pranay 2022-08-03 08:29:19 UTC
Thanks PJ Fanning