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!!! -- You received this bug notification because you are a member of Desktop Packages, which is subscribed to libreoffice in Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office Status in LibreOffice Productivity Suite: Confirmed Status in “libreoffice” package in Ubuntu: Incomplete Bug description: STEPS TO REPRODUCE: Type "=RATE(50,35,-250,0)" on any cell, and press Enter. The result should be 0.1398, but LibreOffice shows -1.9474! ADDITIONAL INFORMATION: I'm using LibreOffice 3.5.7.2, Build ID: 350m1(Build:2), on Ubuntu 12.04, 64-bit version, with up-to-date packages. To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- Mailing list: https://launchpad.net/~desktop-packages Post to : [email protected] Unsubscribe : https://launchpad.net/~desktop-packages More help : https://help.launchpad.net/ListHelp
