Comment 11 for bug 1150956

Björn: thank you for upstreaming it.

Let me try and explain why LibreOffice returning -1.9474... as the result of =RATE(50,35,-250,0) is really a *very* bad thing, and should be marked as a CRITICAL bug. I''ll try to provide an intuitive explanation without delving into too much math.

Let's start with the simplest example. If you invest $250 today in exchange for receiving an annual payment of $35 *forever in perpetuity*, your annual return will be 35/250 = 0.14, or 14%/year. You can see that the formula for a perpetual annuity is Present Value = Annuity / Rate; hence Rate = Annuity / Present Value) in lots of websites. Here's the Wikipedia page on it:
http://en.wikipedia.org/wiki/Time_value_of_money#Present_value_of_a_perpetuity

Now imagine that instead of receiving the annual payments of $35 in perpetuity, you will receive them only for a fixed number of years. In this case, your annual return will necessarily be lower than 14%/year. The greater the number of years, the closer to 14%/year your annual rate of return will be. Specifically, your internal rate of return will asymptotically approach 14%/year as the number of years during which you will receive the annual payments increases to infinity.

This is ***EXACTLY*** what I get in other spreadsheet programs, but NOT in LibreOffice. IT'S MATH. Since you guys are having problems with Excel, try these formulas in Google Spreadsheets, and you will see how the results asymptotically approach 14%:

RATE(10, 35, -250, 0) returns 0.06637326
RATE(20, 35, -250, 0) returns 0.12724192
RATE(30, 35, -250, 0) returns 0.13702841
RATE(40, 35, -250, 0) returns 0.13923873
RATE(50, 35, -250, 0) returns 0.13979829
RATE(60, 35, -250, 0) returns 0.13994592
RATE(70, 35, -250, 0) returns 0.13998544
RATE(80, 35, -250, 0) returns 0.13999607
RATE(90, 35, -250, 0) returns 0.13999894
RATE(100, 35, -250, 0) returns 0.13999971

Then go and try those formulas in LibreOffice.

As I wrote before, returning an error would be a *LOT* better than the wrong number!!!