View Single Post
Old 24th November 2019, 07:09 PM   #3
Penultimate Amazing
Brainster's Avatar
Join Date: May 2006
Posts: 17,431
Originally Posted by DevilsAdvocate View Post
I don't think it is a basic error. The RATE function solves through iteration so that it can solve in all cases. Excel recognizes that won't always work, which is why it has the "guess" parameter. From the help file:

If you get a #NUM! error, the resolution is to enter a value for "guess". This will solve without error:

=RATE(50,0,-1000,1000000000, 0, 0.3)

The person who wrote the function chose to use a single algorithm and obviously realized that there would have to be limitation on convergence and iterations, and therefore added the guess parameter to compensate. The advantage of a single algorithm is simplicity and consistency. If it solved the formula different ways in different circumstances, it could result in different levels of precision that could leave users scratching their heads.

Of course it would be possible to create a more robust function that chooses different methods of solving based on different variables. Perhaps you could have the class assignment to write their own "better" formula that could solve the formula without a guess parameter.
At least in the Excel 2007 version I have, there is no "guess" input in the rate function. There is a "guess" in the IRR function, but to be honest that seems to be legacy as the IRR function always returns the right value for me. I just checked. If I do -1000 in year 0, and +1 billion in year 50 (with 0 for the cash flows in between), the irr function gives 31.83% (I rounded incorrectly when I said 31.82%, it's 31.8257%)

So that's two quick and easy ways to find an answer in excel that excel can't seem to solve on its own.
My new blog: Recent Reads.
1960s Comic Book Nostalgia
Visit the Screw Loose Change blog.
Brainster is offline   Quote this post in a PM   Nominate this post for this month's language award Copy a direct link to this post Reply With Quote Back to Top