Issue 124964 - RATE function blows up
Summary: RATE function blows up
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: 4.1.0
Hardware: All All
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: needmoreinfo
Depends on:
Blocks:
 
Reported: 2014-05-23 00:36 UTC by MaineDave
Modified: 2014-05-23 23:49 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments
Shows RATE function blowing up (26.02 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-05-23 00:36 UTC, MaineDave
no flags Details
Screenshot of Calc spreadsheet from rollapp.com site (97.79 KB, application/pdf)
2014-05-23 15:30 UTC, MaineDave
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description MaineDave 2014-05-23 00:36:46 UTC
Created attachment 83454 [details]
Shows RATE function blowing up

The attached spreadsheet compares the value calculated with the RATE function with the value calculated with the IRR value.  These values are validated against the value calculated by the HP 10B II financial calculator.  Essentially, RATE and IRR give the same value when the number of payments is 3.  They give wildly different values when the number of payments is 4.  The values calculated by IRR always agree with the HP calculator.
Comment 1 MaineDave 2014-05-23 00:41:29 UTC
I see the problem on a Mac, running OS X 10.8.5.  The wild RATE value for four payments is -196.93%, while the correct value is +81.96%
Comment 2 mroe 2014-05-23 06:10:19 UTC
I cannot confirm the problem. With the attached example document I get the right value (81.96%) on Linux and on MacOS 10.8.2.
Comment 3 oooforum (fr) 2014-05-23 14:40:04 UTC
Correct result too with Win7 x64 Pro: =IRR(C20:C24) give 81.96 %

Could you attach a screen shot to show us?
Comment 4 MaineDave 2014-05-23 15:29:52 UTC
OK, let's get something clear here.  As the original post said, the IRR function returns the correct result.  It is the RATE function that is malfunctioning.

I have re-verified this issue.  When the number of payments for the RATE function (in the previously attached spreadsheet) is changed from 3 to 4 (in cell C7), the Rate of return (in cell C11) changes from 80.59% to -196.93%.

I see this behavior on the system described earlier.  I also see it on a Mac running 10.6.8, with OpenOffice 3.4.1, on a Toshiba laptop running Windows 7 and OpenOffice 3.3.0, and on in the cloud at www.rollapp.com/app/oocalc running OpenOffice 3.4.1.

I will attached a screenshot from the rollapp.com site.
Comment 5 MaineDave 2014-05-23 15:30:54 UTC
Created attachment 83457 [details]
Screenshot of Calc spreadsheet from rollapp.com site
Comment 6 MaineDave 2014-05-23 18:06:55 UTC
To put it as simply as possible, according to OO Calc,

RATE(3;500;-600;500) = 80.59%

RATE(4;500;-600;500) = -196.93%

which is clearly wrong, as more payments should yield a better rate of return.
Comment 7 Regina Henschel 2014-05-23 22:02:28 UTC
The call =RATE(Nper;Payment;Pv,Fv;PayType;Guess)) results a solution "Rate" of the equation
0=Fv + Pv*(1+Rate)^Nper + Payment*(1+Rate*PayType)*((1+Rate)^Nper-1)/Rate.

This equation has in many cases not a single solution but more than one solution. 
For most values of Nper, there exist no closed solution, but a solution has to be found by iteration. Use the value "Guess" to make the algorithm converge to the expected range. When you omit this parameter, the default value of 10% is used, which is far away form the range expected in your example.

We already discussed the problem in bug 15090 and on the mailing list at that time. The then decision was to keep the negative value. Gnumeric produces a value in the expected range, Excel produces an error.

Can you provide a formula for a guess, which would work in all cases, where the algorithm converges to such negative value using the default guess?

I set it to "Confirmed", because the behavior is indeed as described. Possible resolution can be "Wontfix" or a change to an enhancement request.
Comment 8 MaineDave 2014-05-23 23:38:43 UTC
RATE(4;500;-600;500;0.8) = -1.940231

So, I don't see how providing a guess in the neighborhood helps.
Comment 9 MaineDave 2014-05-23 23:49:59 UTC
(In reply to MaineDave from comment #8)
> RATE(4;500;-600;500;0.8) = -1.940231
> 
> So, I don't see how providing a guess in the neighborhood helps.

My mistake there.

RATE(4;500;-600;500;0;0.8) = 0.819622

So, a guess in the neighborhood does make the difference.

Obviously, the HP calculator has figured out how to come up with the right answer.  So there is an algorithm that works in silicon.  Also, the IRR function has figured it out as well.

If "won't fix" were the resolution, you would be choosing to leave it broken.  Getting the wrong answer in a financial calculation can be very significant.  It tends to render everything about OO Calc suspect, an aspersion that IMO would be a tragedy.