Bug 65082 - RATE() function produces different results as some versions of MS Office
Summary: RATE() function produces different results as some versions of MS Office
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.7.2 release
Hardware: Other All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:4.2.0 target:4.1.0.1 target:4....
Keywords:
Depends on:
Blocks: mab3.6
  Show dependency treegraph
 
Reported: 2013-05-28 14:59 UTC by Björn Michaelsen
Modified: 2013-06-21 16:40 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
picture of rate (94.69 KB, image/jpeg)
2013-05-29 12:53 UTC, GerardF
Details
Test files for edge cases of RATE function (ODS and CSV) (20.70 KB, application/x-gzip)
2013-06-08 03:20 UTC, F.H.
Details
LibO_4.1.0.1(left) vs LibO_4.0.5.0(right) (64.77 KB, image/png)
2013-06-20 13:30 UTC, ape
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Björn Michaelsen 2013-05-28 14:59:06 UTC
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.
Comment 1 Björn Michaelsen 2013-05-28 15:00:59 UTC
suggesting as a 4.1 MAB because of differences in result, please drop from MAB when considered non-critical.
Comment 2 Björn Michaelsen 2013-05-28 17:24:34 UTC
code pointer:

http://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr2.cxx#1382
Comment 3 F.H. 2013-05-28 17:56:47 UTC
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
Comment 4 Björn Michaelsen 2013-05-28 18:05:56 UTC
Still present on master, therefore in confirmed as NEW.
Comment 5 Christopher M. Penalver 2013-05-29 11:41:09 UTC
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
Comment 6 F.H. 2013-05-29 12:00:29 UTC
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.
Comment 7 GerardF 2013-05-29 12:53:53 UTC
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.
Comment 8 Julien Nabet 2013-05-29 19:48:16 UTC
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
Comment 9 Markus Mohrhard 2013-06-04 20:51:42 UTC
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.
Comment 10 F.H. 2013-06-08 03:20:26 UTC
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.
Comment 11 Michael Meeks 2013-06-16 19:56:53 UTC
If this affects 3.6 it should be a 3.6 MAB, not 4.1 - so moving ...
Comment 12 Eike Rathke 2013-06-17 17:36:20 UTC
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.
Comment 13 Commit Notification 2013-06-17 19:38:50 UTC
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.
Comment 14 Commit Notification 2013-06-17 19:47:59 UTC
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.
Comment 15 Eike Rathke 2013-06-17 20:10:18 UTC
Pending review
for 4-0 as https://gerrit.libreoffice.org/4327
for 3-6 as https://gerrit.libreoffice.org/4328
Comment 16 Commit Notification 2013-06-18 10:16:24 UTC
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.
Comment 17 Commit Notification 2013-06-18 10:34:07 UTC
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.
Comment 18 ape 2013-06-20 13:30:18 UTC
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).
Comment 19 Eike Rathke 2013-06-20 13:54:38 UTC
@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
Comment 20 ape 2013-06-21 16:40:43 UTC
(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.