Excel RATE() function in Java behaving a bit differently

• March 23rd, 2014, 04:16 AM
capricorn86
Excel RATE() function in Java behaving a bit differently
Hi guys,

Just wanted to check something. I have the following function below which is the equivalent of the RATE function in Excel

In Excel, if I do the following in any cell: =RATE(68, -118161.59, 4146470.27) - it gives me: 2.2014989%

However, the below code gives me: 0.0220150136..

I am expecting it to give me 0.0022014989

It is only marginally out, but can anyone point me why it is marginally out?

Any help would be much appreciated

Code :

``` public static void main(String[] args) { double rate1 = Financials.rate(68, 118161.59, 4146470.27); System.out.println(rate1); System.out.println("finished");   }```
Code :

``` public static double rate(double nper, double pmt, double 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 too high, lower the guess high = rate; rate = (high + low) / 2; } else if (calc < -error) { // guess too low, higher the guess low = rate; rate = (high + low) / 2; } else { // acceptable guess break; } }   //System.out.println("Rate : "+rate); return rate; }```
• March 23rd, 2014, 05:08 AM
GregBrannon
Re: Excel RATE() function in Java behaving a bit differently
This - the second answer - is one of the best general answers to your question I found without spending too much time at it. As is mentioned there and several other places, the precision you seek may require the use of BigDecimal.
• March 23rd, 2014, 05:44 AM
capricorn86
Re: Excel RATE() function in Java behaving a bit differently
Hi Greg, thank you very much for the reply. But I am still a bit confused as to BigDecimal, for example:

Code :

```double rate1 = Financials.rate(68, 118161.59, 4146470.27); System.out.println(new BigDecimal(rate1)); // prints 0.02201501368381107714977673595058149658143520355224609375```

In Excel, I see 0.022014989..

But java gives 0.022015013..

Do you think there is some miscalculation in the java rate() method above for the number being slightly out?

I even tried changing the input parameters to the rate function to BigDecimal and it still gives me the same results.

Hope you can help, appreciate any feedback.
• March 23rd, 2014, 06:18 AM
Norm
Re: Excel RATE() function in Java behaving a bit differently
Can you post the code showing the new rate() method that was rewritten to use the BigDecimal class?

--- Update ---

Also posted at: Excel RATE() function in Java behaving slightly differently