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;
}

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.

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.

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