[Bug 1150956] Re: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office
Fixed released with saucy as it was fixed upstream with 4.1.0.1. ** Changed in: libreoffice (Ubuntu) Status: Incomplete = Fix Released -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
(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. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
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. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
If this affects 3.6 it should be a 3.6 MAB, not 4.1 - so moving ... -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
Pending review for 4-0 as https://gerrit.libreoffice.org/4327 for 3-6 as https://gerrit.libreoffice.org/4328 -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
@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 -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
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=8ff1492548cdfdee71a900e6d35c530c082a5c52h=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. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
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=2b81b9967f8269e4fe7cef8e6ede490ae3b66d94h=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. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
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=fa270848936d2ca9ddd312fc551ab189896b9417h=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. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
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. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
Created attachment 81110 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). -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] Re: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office
** Changed in: df-libreoffice Status: Confirmed = Fix Released -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
Created attachment 80500 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. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
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. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
code pointer: http://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr2.cxx#1382 -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
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.1371 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 Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
Still present on master, therefore in confirmed as NEW. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
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 -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
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. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
Created attachment 79960 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. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956]
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 -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] Re: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office
** Description changed: - 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! + 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. + 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 ** Tags added: amd64 i386 precise raring -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] Re: LibreOffice Calc's RATE function sometimes produces incorrect results!!!
I really don't know what else to tell you... Did you try them in Google Spreadsheets? -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces incorrect results!!! To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] Re: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office
** Summary changed: - LibreOffice Calc's RATE function sometimes produces incorrect results!!! + LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] Re: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office
upstreamed, note upstream comments ** Bug watch added: freedesktop.org Bugzilla #65082 https://bugs.freedesktop.org/show_bug.cgi?id=65082 ** Also affects: df-libreoffice via https://bugs.freedesktop.org/show_bug.cgi?id=65082 Importance: Unknown Status: Unknown -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] Re: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office
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.1371 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 Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] Re: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office
Launchpad has imported 2 comments from the remote bug at https://bugs.freedesktop.org/show_bug.cgi?id=65082. If you reply to an imported comment from within Launchpad, your comment will be sent to the remote bug automatically. Read more about Launchpad's inter-bugtracker facilities at https://help.launchpad.net/InterBugTracking. On 2013-05-28T14:59:06+00:00 Björn Michaelsen wrote: 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. Reply at: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1150956/comments/10 On 2013-05-28T15:00:59+00:00 Björn Michaelsen wrote: suggesting as a 4.1 MAB because of differences in result, please drop from MAB when considered non-critical. Reply at: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1150956/comments/11 ** Changed in: df-libreoffice Status: Unknown = Confirmed ** Changed in: df-libreoffice Importance: Unknown = Medium -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] Re: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office
please continue discussion upstream, but restrict yourself to comments that help triaging the issue. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] Re: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office
Björn: will do. Sorry if my comment was too long or had too much info. Thank you so much for your help! -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] Re: LibreOffice Calc's RATE function sometimes produces incorrect results!!!
Christopher: What specific version of Excel? Excel 2003 on a Windows XP VM under VirtualBox. To get the answer, you must enable iterative calculations, increase the number of iterations, and lower the maximum change per iteration. Otherwise you will get the #NUM! error. Unfortunately, this was not a different, exact command Try these, then: =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) These RATE functions are supposed to return the internal rate of return of investing -250 in exchange for receiving a recurring payiment of respectively 36, 37, 38, 39, and 40 per period for a total of 50 periods, with no payment at the end. LibreOffice returns a nonsensical answer in ALL of these cases!!! -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces incorrect results!!! To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] Re: LibreOffice Calc's RATE function sometimes produces incorrect results!!!
PS. It would be a LOT better if LibreOffice returned a #NUM! error instead of an incorrect number. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces incorrect results!!! To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] Re: LibreOffice Calc's RATE function sometimes produces incorrect results!!!
F.H., regarding the following 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. Excel Options - Formulas - checked checkbox Enable iterative calculation - Maximum Iterations 32767 (maximum allowed by Excel) - Maximum Change 1E-99 (maximum allowed by Excel). Could you please advise? -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces incorrect results!!! To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] Re: LibreOffice Calc's RATE function sometimes produces incorrect results!!!
F.H., regarding your comments https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1150956/comments/4 : ...and on Microsoft Excel: What specific version of Excel? =RATE(50,35,-250,0) Unfortunately, this was not a different, exact command as previously requested in https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1150956/comments/3 . -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces incorrect results!!! To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] [NEW] LibreOffice Calc's RATE function sometimes produces incorrect results!!!
You have been subscribed to a public 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. ** Affects: libreoffice (Ubuntu) Importance: Undecided Status: Incomplete -- LibreOffice Calc's RATE function sometimes produces incorrect results!!! https://bugs.launchpad.net/bugs/1150956 You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] Re: LibreOffice Calc's RATE function sometimes produces incorrect results!!!
F.H. thank you for reporting this and helping make Ubuntu better. As per Excel Microsoft Office Professional Plus 2010 Excel Version 14.0.6023.1000 (32-bit), the result of: =RATE(B5,B4,B3,0) is: #NUM! Could you please provide a different, exact command that demonstrates this problem? ** Project changed: df-libreoffice = libreoffice (Ubuntu) ** Changed in: libreoffice (Ubuntu) Status: New = Incomplete -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces incorrect results!!! To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] Re: LibreOffice Calc's RATE function sometimes produces incorrect results!!!
Christopher -- sure. I just tried this on Google Drive's Spreadsheet and on Microsoft Excel: =RATE(50,35,-250,0) The result in both cases is approximately 0.1398. When I try the same formula in LibreOffice, I get a nonsensical answer. (I sure hope no one out there is using this function to calculate important financial figures!) I'm using LibreOffice 3.5.7.2 Build ID: 350m1(Build:2) on Ubuntu 12.04 64-bit. Hope this is helpful. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces incorrect results!!! To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] Re: RATE function sometimes produces incorrect results!
** Package changed: libreoffice (Ubuntu) = df-libreoffice ** Summary changed: - RATE function sometimes produces incorrect results! + LibreOffice Calc's RATE function sometimes produces incorrect results!!! -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: LibreOffice Calc's RATE function sometimes produces incorrect results!!! To manage notifications about this bug go to: https://bugs.launchpad.net/df-libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1150956] [NEW] RATE function sometimes produces incorrect results!
Public bug reported: 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. ** Affects: libreoffice (Ubuntu) Importance: Undecided Status: New -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1150956 Title: RATE function sometimes produces incorrect results! To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1150956/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
