From downstream bug: 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. Master shows the same -1.9474 result when last tested. Note that the RATE() function is iterative and even docs of MS Office say they might have zero or more solutions: http://office.microsoft.com/en-001/excel-help/rate-function-HP010342819.aspx As differences in calculation are sensitive this still warrants investigation.
suggesting as a 4.1 MAB because of differences in result, please drop from MAB when considered non-critical.
code pointer: http://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr2.cxx#1382
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
Still present on master, therefore in confirmed as NEW.
Upstreaming downstream discussion https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1150956/comments/8 : F.H. / Björn Michaelsen, regarding the problem noted in the Description https://bugs.freedesktop.org/show_bug.cgi?id=65082#c0 in Microsoft Office Professional Plus 2010 Word Version 14.0.6023.1000 (32-bit): =RATE(50,35,-250,0) =RATE(50,36,-250,0) =RATE(50,37,-250,0) =RATE(50,38,-250,0) =RATE(50,39,-250,0) =RATE(50,40,-250,0) all of these produce errors. This is after changing Excel Options -> Formulas -> checked checkbox Enable iterative calculation -> Maximum Iterations 32767 (maximum allowed by Excel) -> Maximum Change 1E-99 (maximum allowed by Excel). In Gnumeric, the results are: 13.98% 14.38% 14.79% 15.19% 15.59% 15.99% 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
Christopher: To see the results asymptotically approaching 0.14, increase the number of periods, not the annuity payment (in other words, keep the annuity payment at 35) -- i.e., =RATE([change this number],35,-250,0). As I wrote above, returning an error is a *lot* better than returning the wrong number, because the RATE() function is used only for financial calculations. When there's money on the line, a non-working spreadsheet that reports an error is better than a working one that gives the user a bad answer. I would NOT have reported this bug if LibreOffice had returned an error.
Created attachment 79960 [details] picture of rate See attached picture to see what Reporter means. For building the chart, I use the RATE function with the "guess parameter" to PMT/PV. This workaround make the RATE function returning expected value. It is just a workaround, bug is still here and important.
On pc Debian x86-64 with master sources updated today and brand new LO profile, the example given by Björn "=RATE(50,35,-250)" gives Err:508
Can someone please add a test document with some problematic cases that still don't work in 4-1/master and expected results to the bug report? If the file is licensed MPL/LGPL we can also use it for automatic tests after the bug fix. See https://wiki.documentfoundation.org/Development/Calc_Import_Unit_Tests for more details how such documents can help us make sure that formulas produce the correct result in corner cases.
Created attachment 80500 [details] Test files for edge cases of RATE function (ODS and CSV) The attached .tar.gz archive contains (1) an ODF file that produces bad results on edge cases with the RATE() function and (2) a CSV version of the same file but with only correct results. I'm the author of both files and hereby license them jointly under MPL and LGPL. Please let me know if this suffices.
If this affects 3.6 it should be a 3.6 MAB, not 4.1 - so moving ...
The Newton goal seek used returns a possible but undesired root for the default Guess value of 0.1 (10%) in these cases, already specifying a Guess value of 0.14 delivers the expected results.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=9ee7be4efb494351c4be096ffa04cdbd85cdc3d4 resolved fdo#65082 RATE function should not find roots <= -1 The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=8ff1492548cdfdee71a900e6d35c530c082a5c52&h=libreoffice-4-1 resolved fdo#65082 RATE function should not find roots <= -1 It will be available in LibreOffice 4.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review for 4-0 as https://gerrit.libreoffice.org/4327 for 3-6 as https://gerrit.libreoffice.org/4328
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=fa270848936d2ca9ddd312fc551ab189896b9417&h=libreoffice-4-0 resolved fdo#65082 RATE function should not find roots <= -1 It will be available in LibreOffice 4.0.5. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-3-6": http://cgit.freedesktop.org/libreoffice/core/commit/?id=2b81b9967f8269e4fe7cef8e6ede490ae3b66d94&h=libreoffice-3-6 resolved fdo#65082 RATE function should not find roots <= -1 It will be available in LibreOffice 3.6.7. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Created attachment 81110 [details] LibO_4.1.0.1(left) vs LibO_4.0.5.0(right) The bug is present in LibO-4.1.0.1(rc1), but "bugs-changelog-libreoffice-4-1-release-4.1.0.1 (1).log" claims that this bug was fixed. LibO-Dev_4.0.5.0_Win_x86 (build: 2013-06-19_03.27.42) hasn’t this error. Look an image (an attachment). -- The bug reopened for the program LibO-4.1.0.1(rc1).
@ape: (In reply to comment #18) > The bug is present in LibO-4.1.0.1(rc1), but > "bugs-changelog-libreoffice-4-1-release-4.1.0.1 (1).log" claims that this > bug was fixed. LibO-Dev_4.0.5.0_Win_x86 (build: 2013-06-19_03.27.42) hasn’t > this error. Look an image (an attachment). Were the formula cells recalculated after loading the document? Otherwise the cached values will be displayed. You can force a hard recalc with Shift+Ctrl+F9
(In reply to comment #19) > 1. Were the formula cells recalculated after loading the document? Otherwise > the cached values will be displayed. > 2. You can force a hard recalc withShift+Ctrl+F9 -- 1. Yes, after “LibO-4.1” and “LibO-4.0.5” opened the file. 2. Recalculation of values ([Ctrl]+[Shift]+[F9]) gives the correct result.