Function RATE() returning NAN

Topics: Developer Forum, Project Management Forum, User Forum
Apr 7, 2012 at 1:50 AM
Edited Apr 7, 2012 at 1:50 AM

Hi All,   

I have this code:

When i call something like rate(60, -6000, 120000) it returns me a NAN result, but the same function on MS Excel returns me 0,04678.... I have the same problem trying -5000, -4000, -3000 and -2000.

When i debug the code, i see that about the 8/9 iteration, the line number 29 begins to return a NAN result, making all of other results to turn NAN too.

BUT, when i call something like rate(60, -1000, 120000) it returns me a float -0.02044..., exactly the same result of MS Excel.

I have already tryed to convert all of math calculations into BCMath functions, but this way the results of -6000 is wrong (-1.0427... instead of 0,04678...) but using -1000 the result is correct, matching excel's result.

Is there a way to make it work correctly? Is it a bug?

Thanks in advance for any useful sight about that.

Apr 7, 2012 at 9:36 AM

I'm guessing it's a bug... but I'll need to do some work to identify where the algorithm is causing the error.

Switching to use bcmath probably isn't a good idea because it has an enormous performance overhead, and will only be beneficial if you want accuracy to a level way beyond 12 decimal places.


Apr 7, 2012 at 1:56 PM
Edited Apr 7, 2012 at 1:56 PM

Hi MarkBaker,

Thank you for your reply. I tryed bcmath just as a 'last try' to make a workaround.

I have a great interest to see that (apparently) bug to be fixed. I even would like to help but I do not understand the Newton's method in a way that I could 'handle' it.

Please, let me know when it have some advance.

Thank you, Sidney.