LibreOffice Calc's RATE function sometimes produces different results as some versions of MS Office

[F.H.]

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

[Doug Lomax (dlomax11)]

I was able to verify on my system the error:
I tested this on
LibreOffice 3.5.7.2 Build ID: 350m1(Build:2),
Ubuntu 12.04 LTS with a HP AMD Phenom(tm) II X4 840T Processor × 4, using 32 bit.

Although I was unable to determine if the function was applied correctly. It seems something is off
according to this wike I found.

http://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_RATE_function

Good luck. Sorry I couldn't help more.

[F.H. (fheinsen)]

Attached is a spreadsheet which calculates a rate based on the same cash flows using IRR and RATE.

In Excel, both functions return the same result.

In Google Apps spreadhseet, both functions return the same result.

In LibreOffice Calc, the RATE function returns a different number!!!

[penalvch (penalvch)]

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?

[F.H. (fheinsen)]

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.

[penalvch (penalvch)]

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 .

[F.H. (fheinsen)]

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!!!

[F.H. (fheinsen)]

PS. It would be a LOT better if LibreOffice returned a #NUM! error instead of an incorrect number.

[penalvch (penalvch)]

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?

[F.H. (fheinsen)]

I really don't know what else to tell you...

Did you try them in Google Spreadsheets?

[Björn Michaelsen (bjoern-michaelsen)]

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.

[Björn Michaelsen (bjoern-michaelsen)]

suggesting as a 4.1 MAB because of differences in result, please drop from MAB when considered non-critical.

[Björn Michaelsen (bjoern-michaelsen)]

upstreamed, note upstream comments

[F.H. (fheinsen)]

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!!!

[Björn Michaelsen (bjoern-michaelsen)]

please continue discussion upstream, but restrict yourself to comments that help triaging the issue.

[F.H. (fheinsen)]

Björn: will do. Sorry if my comment was too long or had too much info. Thank you so much for your help!

[Björn Michaelsen (bjoern-michaelsen)]

code pointer:

http://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr2.cxx#1382

[F.H. (fheinsen)]

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

[Björn Michaelsen (bjoern-michaelsen)]

Still present on master, therefore in confirmed as NEW.

[penalvch (penalvch)]

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

[F.H. (fheinsen)]

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.

[Gerard-fargeot (gerard-fargeot)]

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.

[julien2412 (serval2412-6)]

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

[Markus Mohrhard (moggi)]

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.

[F.H. (fheinsen)]

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.

[Michael Meeks (michael-meeks)]

If this affects 3.6 it should be a 3.6 MAB, not 4.1 - so moving ...

[Eike Rathke (erack)]

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.

[Libreoffice-commits (libreoffice-commits)]

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.

[Libreoffice-commits (libreoffice-commits)]

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.

[Eike Rathke (erack)]

Pending review
for 4-0 as https://gerrit.libreoffice.org/4327
for 3-6 as https://gerrit.libreoffice.org/4328

[Libreoffice-commits (libreoffice-commits)]

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.

[Libreoffice-commits (libreoffice-commits)]

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.

[Os2sa (os2sa)]

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).

[Eike Rathke (erack)]

@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

[Os2sa (os2sa)]

(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.

[Björn Michaelsen (bjoern-michaelsen)]

Fixed released with saucy as it was fixed upstream with 4.1.0.1.