Apache POI speed formula inconsistency with long periods

Question

To emulate the Excel speed function, I use the Apache POI speed function, which I captured from 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; 

}

To calculate the Rat (120, 28.1, -2400, 0, 0, 0.1)), the result 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 clues?

Answer 1

There are a few things that do not match this code that you inserted into your question.

It is assumed that the bet is always found (not true) and does not provide any conditions when the speed is not found.

Some of the statements will cause an error that could have been avoided by using a more suitable programming instruction. For example, make the following code from your code:

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

This will cause an error when trying to find a log of negative or zero value. It could be rewritten as

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

The comments in the iterative calculations indicate that he is programming the secant method, but the iterative calculations are checked for the convergence of the wrong variable. He tests the convergence of future value when he needs to test the convergence of interest rates.

I copied your code to Notepad and deleted the Java variable declaration and replaced it with JavaScript variable declarations to check the code with your sample data. And just like I said, the code stops at the second iteration, since the difference in future values ​​goes beyond the error limits, and since there is no test to check if the rate is found, the code returns the interest rate as is and one is wrong.

I’m not sure why this code works when it reports the correct speed, as is the case with the first data set. I would suggest the correct function encoding.

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; same with excel (correct).