Bug Hunting Session
Bug 101204 - VDB function: Calc and Excel produce different results; both wrong
Summary: VDB function: Calc and Excel produce different results; both wrong
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Winfried Donkers (retired)
URL:
Whiteboard: target:5.3.0
Keywords:
Depends on:
Blocks:
 
Reported: 2016-07-29 17:14 UTC by raal
Modified: 2016-11-23 12:47 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
document used for testing (15.39 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-08-02 05:53 UTC, Winfried Donkers (retired)
Details

Note You need to log in before you can comment on or make changes to this bug.
Description raal 2016-07-29 17:14:58 UTC
This is copy of bug from AOO bugzilla: https://bz.apache.org/ooo/show_bug.cgi?id=91651  and https://bz.apache.org/ooo/show_bug.cgi?id=109420


VDB(100000;20000;10;7;8) returns 971.52 (correct)
VDB(100000;20000;10;7.25;7.75) returns 3276.80 (incorrect - can't be more!)
Excel97 returns 0 - that's wrong too. Should be 971.52/2 I think.
Comment 1 MM 2016-07-29 20:29:41 UTC
Wrong, so new...
Also if you set the type (from the incorrect example) <= 7.2 you'll get an 'Err:502'.
Comment 2 JoNi 2016-07-29 22:22:31 UTC
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
Comment 3 Winfried Donkers (retired) 2016-08-01 05:57:42 UTC
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.
Comment 4 Winfried Donkers (retired) 2016-08-01 09:46:05 UTC
(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.
Comment 5 Winfried Donkers (retired) 2016-08-02 05:53:08 UTC
Created attachment 126516 [details]
document used for testing
Comment 6 Winfried Donkers (retired) 2016-08-02 08:04:03 UTC
(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.
Comment 7 Commit Notification 2016-08-16 15:34:59 UTC
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.
Comment 8 Eike Rathke 2016-08-16 15:55:40 UTC
Submitted as ODF-TC comment: https://lists.oasis-open.org/archives/office-comment/201608/msg00000.html
Comment 9 Commit Notification 2016-11-23 12:47:15 UTC
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.