Apache OpenOffice (AOO) Bugzilla – Issue 124964

RATE function blows up

Last modified: 2014-05-23 23:49:59 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.

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%

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.

Correct result too with Win7 x64 Pro: =IRR(C20:C24) give 81.96 % Could you attach a screen shot to show us?

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.

Created attachment 83457 [details] Screenshot of Calc spreadsheet from rollapp.com site

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.

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.

RATE(4;500;-600;500;0.8) = -1.940231 So, I don't see how providing a guess in the neighborhood helps.

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