Created attachment 125815 [details] test file =AMORLINC(10000;DATE(2012;3;1);DATE(2012;12;31);-1500;1;0,3;4) -> result 3000 =AMORLINC(10000;DATE(2012;3;1);DATE(2012;12;31);1500;-1;0,3;4) -> result 0 Result should be #error. =AMORLINC(10000;DATE(2012;3;1);DATE(2012;12;31);1500;1;1,3;4) -> result -2297,222 result should be 0.
Created attachment 125817 [details] Amorlinc test file Hi @raal, Please take a look to the attached file =AMORLINC(10000;DATE(2012;3;1);DATE(2012;12;31);1500; 0 ;1,3;4) -> result 10797,22 =10000/360*299*1,3 =AMORLINC(10000;DATE(2012;3;1);DATE(2012;12;31);1500; 1 ;1,3;4) -> result -2297,22 the excess Total . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8500,00 I'm not sure negative periods should give an error. I think not a bug.
Please @raal, If you have excel, does it work equal?
The results from Calc differ from those from Excel; I will investigate and see if a fix is possible. Note: this function is an 'Excel Analysis package Add-On function'.
(In reply to m.a.riosv from comment #2) > Please @raal, If you have excel, does it work equal? Hi Miguel, excel throws #error and 0 - different results then Calc.
ODFF: 6.12.4 AMORLINC Syntax: AMORLINC( Number cost ; DateParam purchaseDate ; DateParam firstPeriodEndDate ; Number salvage ; Integer period ; Number rate [ ; Basis basis = 0 ] ) Constraints: cost > 0; purchaseDate <= firstPeriodEndDate; salvage >= 0; period >= 0; rate > 0
(In reply to raal from comment #5) > ODFF: 6.12.4 AMORLINC > Syntax: AMORLINC( Number cost ; DateParam purchaseDate ; DateParam > firstPeriodEndDate ; Number salvage ; Integer period ; Number rate [ ; Basis > basis = 0 ] ) > > Constraints: cost > 0; purchaseDate <= firstPeriodEndDate; salvage >= 0; > period >= 0; rate > 0 Well, that describes the problem. Excel has other constraints: no argument must have a value < 0. There is only one real solution: creating a separate ODFF and Excel version of the function. This is the case for many other functions, with NAME for the ODFF version and e.g. NAME_XCL, NAME_ADD for the Excel version. @Raal: I propose to make bug 100528 a duplicate of bug 100523 and handle both at once. They have exactly the same problem.
Perhaps it is on interest this comment on the OpenOffice wiki. https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_AMORLINC_function " Issues: Calc and Excel calculate differently when the date of purchase is the end of a period. Excel regards the initial period 0 as the first full period. Calc (it is believed correctly) regards the initial period 0 as of zero length, and thus correctly returns zero depreciation. Example: AMORLINC(1000;"2008-12-31";"2008-12-31";100;0;0.25;1) returns 0 in Calc and 250 in Excel. In this example the final fractional period (giving 150 depreciation) is period 3 in Excel and period 4 in Calc. "
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=67188d0e552c9dc50905f7f693d2bb24751a11c5 tdf#100528 Add constraints for AMORLINC function It will be available in 5.3.0. 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.
(In reply to Winfried Donkers from comment #6) > Well, that describes the problem. Excel has other constraints: no argument > must have a value < 0. How is that different from the ODFF constraints, except that it would allow cost=0 and rate=0 which are pretty much nonsense? Besides, I see no evidence for that, neither in https://support.office.com/en-US/article/AMORLINC-function-7D417B45-F7F5-4DBA-A0A5-3451A81079A8 that does not say anything about constraints, nor in http://www.excelfunctions.net/Excel-Amorlinc-Function.html In fact the latter states for invalid numbers: salvage < 0, period < 0, rate ≤ 0
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=2fd00e59ad6cf55c4fc621724de863947ef6dcf6 tdf#100528 follow up; filter nonsense results. It will be available in 5.3.0. 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.