Apache POI rate formula inconsistency with long pe

Question

In order to emulate Excel's rate function, I'm using the Apache POI rate function I grabbed from the svn:

private double calculateRate(double nper, double pmt, double pv, double fv, double type, double guess) {

  //FROM MS http://office.microsoft.com/en-us/excel-help/rate-HP005209232.aspx
  int FINANCIAL_MAX_ITERATIONS = 20; //Bet accuracy with 128
  double FINANCIAL_PRECISION = 0.0000001; //1.0e-8

  double y, y0, y1, x0, x1 = 0, f = 0, i = 0;
  double rate = guess;
  if (Math.abs(rate) < FINANCIAL_PRECISION) {
     y = pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv;
  }
  else {
     f = Math.exp(nper * Math.log(1 + rate));
     y = pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
  }
  y0 = pv + pmt * nper + fv;
  y1 = pv * f + pmt * (1 / rate + type) * (f - 1) + fv;

  // Find root by the Newton secant method
  i = x0 = 0.0;
  x1 = rate;
  while ((Math.abs(y0 - y1) > FINANCIAL_PRECISION) && (i < FINANCIAL_MAX_ITERATIONS)) {
     rate = (y1 * x0 - y0 * x1) / (y1 - y0);
     x0 = x1;
     x1 = rate;

     if (Math.abs(rate) < FINANCIAL_PRECISION) {
        y = pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv;
     }
     else {
        f = Math.exp(nper * Math.log(1 + rate));
        y = pv * f + pmt * (1 / rate + type) * (f - 1) + fv;
     }

     y0 = y1;
     y1 = y;
     ++i;
  }
  return rate;

}

For calculateRate(120, 28.1, -2400, 0, 0, 0.1)), the output is the same as Excel: 0.599

But if I try the same calculation, this time with the values:

calculateRate(360, 15.9, -2400, 0, 0, 0.1))

In Excel I get 0.580, and the program returns -1.1500428517726355. Any hints?

Answer 1

There are a bunch of things that are wrong with this code that you have pasted in your question.

It assumes that a rate is always found (not true) and makes no provision for instances when a rate is not found.

Some of the statements will throw an error which could have been avoided by using a more appropriate programming statement. For instance, take the following statement from your code:

f = Math.exp(nper * Math.log(1 + rate));

This will throw an error when attempting to find Log of a negative or zero value. It could have been rewritten as

f = Math.pow(1 + rate, nper);

The comment in iterative calculations states that it is programming the secant method, yet the iterative calculations are checked for convergence of the wrong variable. It is testing for convergence of a future value when it should be testing for convergence of the interest rate.

I copy pasted your code in Notepad and removed the variable declaration of Java and replaced these with JavaScript variable declarations to test the code with your sample data. And just as I said, the code stops at the second iteration since the difference of future values goes out of error bound and since there is no test in place to see whether a rate is found, the code returns the interest rate as is and one which is wrong.

I am not sure why this code works in instances where it does report a correct rate as is the case with first data set. I would suggest re-coding of the function in a correct manner.

Answer 2

public double rate(double nper, double pmt, double pv)
{
    //System.out.println("function rate : " + nper + " " + pmt + " pv " + pv);

    double error = 0.0000001;
    double high =  1.00;
    double low = 0.00;

    double rate = (2.0 * (nper * pmt - pv)) / (pv * nper);

    while(true) {
        // Check for error margin
        double calc = Math.pow(1 + rate, nper);
        calc = (rate * calc) / (calc - 1.0);
        calc -= pmt / pv;

        if (calc > error) {
            // Guess is too high, lower the guess
            high = rate;
            rate = (high + low) / 2;
        } 
        else if (calc < -error) {
            // Guess is too low, higher the guess.
            low = rate;
            rate = (high + low) / 2;
        } 
        else {
            // Acceptable guess
            break;
        }
    }

    //System.out.println("Rate : " + rate);
    return rate;
}

Example: =RATE(60, 2112500, 65000000) returns 0.025198; the same with Excel (correct).