Comment 17 for bug 1150956

Revision history for this message
In , F.H. (fheinsen) wrote :

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