Summary: | VDB function: Calc and Excel produce different results; both wrong | ||
---|---|---|---|
Product: | LibreOffice | Reporter: | raal <raal> |
Component: | Calc | Assignee: | Winfried Donkers <winfrieddonkers> |
Status: | RESOLVED FIXED | ||
Severity: | normal | CC: | erack, raal, winfrieddonkers |
Priority: | medium | ||
Version: | Inherited From OOo | ||
Hardware: | All | ||
OS: | All | ||
Whiteboard: | target:5.3.0 | ||
Crash report or crash signature: | Regression By: | ||
Attachments: | document used for testing |
Description
raal
2016-07-29 17:14:58 UTC
Wrong, so new... Also if you set the type (from the incorrect example) <= 7.2 you'll get an 'Err:502'. workaround: fill optional values with default values VDB(100000;20000;10;7.25;7.75;2;1) returns 485,76 like excel VDB(100000;20000;10;7.25;7.75;2;TRUE) returns 485,76 Working on a proper fix. BTW, the problem is somewhat academic. If you want to depreciate in 40 periods, just use 40 for periods/life argument and keep using integer values for first and last period arguments. (In reply to JoNi from comment #2) > workaround: > fill optional values with default values > VDB(100000;20000;10;7.25;7.75;2;1) returns 485,76 > like excel > VDB(100000;20000;10;7.25;7.75;2;TRUE) returns 485,76 Beware: TRUE is not the default value for argument Type, FALSE is the default value. VDB(100000;20000;10;7.25;7.75;2;0) returns the same incorrect value as with optional arguments left out. FYI: in this particular use case there should be no difference between Type FALSE and Type TRUE in the results, so the workaround seems to work. Created attachment 126516 [details]
document used for testing
(In reply to raal from comment #0) > This is copy of bug from AOO bugzilla: > https://bz.apache.org/ooo/show_bug.cgi?id=109420 The issues mentioned in this bug report become much less significant once the bug https://bz.apache.org/ooo/show_bug.cgi?id=91651 is fixed: Year Start End VDB before VDB fixed Excel IRS 1 0.0 0.5 $ 1017.43 $ 1071.43 $ 1071.43 $ 1071 2 0.5 1.5 $ 1913.27 $ 1913.27 $ 1913.27 $ 1913 3 1.5 2.5 $ 1503.28 $ 1503.28 $ 1503.28 $ 1503 4 2.5 3.5 $ 1224.89 $ 1267.77 $ 1224.89 $ 1225 5 3.5 4.5 $ 1237.39 $ 1212.65 $ 1224.89 $ 1225 6 4.5 5.5 $ 1237.39 $ 1212.65 $ 1224.89 $ 1225 7 5.5 6.5 $ 1237.39 $ 1212.65 $ 1224.89 $ 1225 8 6.5 7.0 $ 618.70 $ 606.32 $ 612.45 $ 613 sum $10043.75 $10000.00 $10000.00 $10000 As explained in the AOO bugreport (109420), this is a special (not common) use case and a comparison with IRS, which only applies for the USA. Given that the incorrect results before the fix (sum should always be $10000) have been rectified with the fix and that the common use cases are OK now, I propose to accept the above differences. A consideration for this proposal is also that AFAIK no user has so far reported unacceptable results with VDB; I guess that VDB is seldom to never used for the use case mentioned above. Note: ATM the 'fix' has not yet been pushed to master, but probably will be within the next couple of days. Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=a81eac7ce80732f983eb06713ac79c9cd4c5340f tdf#101204 make VDB return correct results for fractional periods. 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. Submitted as ODF-TC comment: https://lists.oasis-open.org/archives/office-comment/201608/msg00000.html Zdeněk Crhonek committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=382af8a6fa2d44d28539b3eb563f694114916733 update DVB test case (add tests for bug tdf#101204) 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. |