I'm the original reporter of the bug on Launchpad. This bug looks critical to me, because it can directly lead to financial losses from incorrect rate-of-return calculations in spreadsheets.
Let me explain the severity of the bug with an example. If one invests, say, $250 today in exchange for receiving an annual payment of $35 forever in perpetuity, the annual return will be 35/250 = 0.14, or 14%/year.[1] Now imagine that instead of receiving the annual payments of $35 in perpetuity, one will receive them only for a fixed number of years. In this case, the rate of return will necessarily be lower than 14%/year. The lower the number of years, the lower the rate of return; the greater the number of years, the closer to 14%/year the rate of return will be. Indeed, the rate of return asymptotically approaches 14%/year as the number of years increases to infinity.[2] This is *exactly* what I get in other spreadsheet programs, but NOT in LibreOffice. For example, when I try these formulas in Google Spreadsheets, 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 When I try those formulas in LibreOffice, many of the answers are nonsensical! It would be better for LibreOffice to return an error than an incorrect number! -- [1] See formula in http://en.wikipedia.org/wiki/Time_value_of_money#Present_value_of_a_perpetuity [2] See explanation in http://en.wikipedia.org/wiki/Time_value_of_money -- 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: Type "=RATE(50,35,-250,0)" on any cell, and press Enter. The result should be 0.1398, but LibreOffice shows -1.9474! 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. Reproducible in Raring. WORKAROUND: Use Gnumeric. apt-cache policy gnumeric gnumeric: Installed: 1.12.1-1ubuntu1 Candidate: 1.12.1-1ubuntu1 Version table: *** 1.12.1-1ubuntu1 0 500 http://archive.ubuntu.com/ubuntu/ raring/universe i386 Packages 100 /var/lib/dpkg/status 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
