Issue 109420

Summary: financial function VDB returns incorrect values
Product: Calc Reporter: dcameron <darrel.cameron>
Component: uiAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact: zhaoshzh <aoo.zhaoshzh>
Severity: Normal    
Priority: P3 CC: aoo.zhaoshzh, elish, issues, openoffice, rainerbielefeld_ooo_qa
Version: 3.3.0 or older (OOo)   
Target Milestone: ---   
Hardware: PC   
OS: All   
URL: https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_VDB_function
See Also: https://issues.apache.org/ooo/show_bug.cgi?id=91651
Issue Type: DEFECT Latest Confirmation in: 4.1.0-beta
Developer Difficulty: ---
Attachments:
Description Flags
example spreadsheet
none
with IRS data
none
Description of VDB function with manual calculations/examples
none
VDB function manual calculator none

Description dcameron 2010-02-18 22:19:21 UTC
The financial function VDB incorrectly calculates depreciation amounts under the
following conditions:

Cost = $10,000
Salvage = $0
Life = 7
Factor = 1.5 (150% Declining Balance Half Year Convention)

A comparison between the results given by the VDB function and that found using
Excel or the Table A-14 from IRS Publication 946 is shown below.

Year     Start    End     VDB          Excel      IRS
1         0.0     0.5     $1017.43     $1071.43   $1071
2         0.5     1.5     $1913.27     $1913.27   $1913
3         1.5     2.5     $1503.28     $1503.28   $1503
4         2.5     3.5     $1224.89     $1224.89   $1225
5         3.5     4.5     $1237.39     $1224.89   $1225
6         4.5     5.5     $1237.39     $1224.89   $1225
7         5.5     6.5     $1237.39     $1224.89   $1225
8         6.5     7.0     $618.70      $612.45    $613

The total depreciation over the life of the asset calculated by VDB (openoffice)
is $10043.75, it should be $10000.00.
Comment 1 Edwin Sharp 2013-11-19 20:47:53 UTC
Created attachment 81960 [details]
example spreadsheet
Comment 2 Edwin Sharp 2013-11-19 20:49:43 UTC
Confirmed with

AOO410m1(Build:9750)  -  Rev. 1539999
2013-11-09_04:08:11 - Rev. 1540252
Debian
Comment 3 Rainer Bielefeld 2014-03-25 08:08:33 UTC
Version has been modified erroneous, so back to most early version

Unclear:
(a) Linux only?
(b) Source for a correct calculation? I can't tell whether reporter uses function
    in the appropriate way.

Additional Info:
(c) Gnumeric shows the same results for sample document, I will check with Excel
    later
Comment 4 Rainer Bielefeld 2014-03-25 08:20:09 UTC
My results with reporter's sample document:
Excel indeed has different results. Result D10 in Excel is invariant to changing Start/End values to exact years instead of that strange 0,5 years start interval.
AOO shows D10=10000 if I modify Start / End values to exact years.
Whatever that all might mean, I have no knowledge there.
Comment 5 Edwin Sharp 2014-03-25 09:49:02 UTC
Created attachment 82968 [details]
with IRS data
Comment 6 Edwin Sharp 2014-03-25 09:50:06 UTC
See attachment 82968 [details]
Comment 7 Rainer Bielefeld 2014-03-25 10:08:50 UTC
Well, the IRS list can be compared easily with the AOO results. Does AOO VDB support "Half Year Convention"? Help is really poor, without knowledge concerning the mathematics behind it helps nothing
Comment 8 Rainer Bielefeld 2014-03-25 10:51:54 UTC
Also see "Issue 91651 - VDB function: Calc and Excel produce different results; both wrong"

Additional sources:
Help (See URL above)
ODF 1.2 <http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018330_715980110>
Comment 9 drking 2014-03-26 08:49:09 UTC
This page gives the US IRS system, including some maths

http://www.irs.gov/publications/p946/ch04.html#en_US_2013_publink1000107554 

eg: the section "Figuring the Deduction Without Using the Tables"

Excel, Gnumeric, LibreOffice, ODFF and everywhere else I've looked spectacularly fail to explain the function - I think it was thrown in by Microsoft simply so they could say they had a lot of functions. It's unuseable without a proper explanation, if fractional periods are given.

Certainly it looks as if when I did the wiki writeup for Ooo I didn't understand the half year convention well enough.

Of course calculating to half years, half months etc is a matter of convention and definition - there are several ways it could be done - and there's nothing to say that the US system is 'right' - perhaps eg Patagonia have a better system. This is interntaional software.

However I guess that Excel try to duplicate IRS policy, so that should be the target. Though what would happen if the IRS changed their policy? It does need a clearer write-up.

The other issue is that calcualting between periods 0.5-2.0 for example is probably meaningless, although should be defined. Maybe the aim should be to get a current asset value for any given start/end period and take the difference. For example (I think) the asset value at endperiod 1.5 could be defined as using the half year convention (because it's got a half in it) so is calculated using a first year's depreciation based on half a year. 1.2 could be a linear interpolation between some 2 periods (which?)

It certainly needs some careful thinking about.

One idea would be to examine Gnumeric's code - it's quite easy to read I think, and maybe someone clever really thought about this before. Just to save re-inventing the wheel and help understand the thing.

And after all this, no-one will use the function....
Comment 10 Edwin Sharp 2014-03-26 09:02:18 UTC
IMHO we are better off removing this unexplained function.
Knowing what we are doing > being compatible with Excel.
Comment 11 zhaoshzh 2014-03-26 23:53:52 UTC
duplicate with Issue 91651 .
Comment 12 drking 2014-03-27 08:29:27 UTC
The current Gnumeric code says:
*  Original source of the following functions (ScGetGDA, ScInterVDB, and
 *  get_vdb) is the OpenOffice version 1.0, `sc/source/core/tool/interpr2.cxx'.
 *
 *  RCSfile: interpr2.cxx,v
 *
 *  Revision: 1.11
 *
 *  last change: Author: er  Date: 2001/03/15 21:31:13

In other words they borrowed from OpenOffice.

The function is fine with whole number periods I think - so my inclination would be to leave it in, and try to understand the IRS system, which must be what Excel based it on. The fact that they don't document it with fractional periods, means that no-one can trust their function either. Sigh.
Comment 13 drking 2014-03-28 07:49:44 UTC
I think I now understand how the Excel VDB function works, and can replicate the maths by hand, including the IRS maths examples.  I'll double check and write it up.
Comment 14 drking 2014-04-01 10:44:13 UTC
Created attachment 83049 [details]
Description of VDB function with manual calculations/examples

I've attached my write-up of this function.

Rather a complex beast, I'm afraid, but there should be enough info here to allow a pretty decent understanding. I think it's sound. It certainly duplicates the IRS tables OK.

Someone needs to decide whether to follow Excel or ODFF for start | end periods of eg 0 | 2.875. I doubt whether ODFF is the best option - we've all struggled to discover what Excel do, including the worthy people who wrote ODFF, and I think the Excel approach is better, as discussed in the write-up.
Comment 15 drking 2014-04-04 13:37:08 UTC
Created attachment 83084 [details]
VDB function manual calculator

Here is an Excel spreadsheet which generates the results of the VDB function by hand.

This includes the consideration of salvage value. The salvage value cap is applied to full year depreciation; if the depreciation wanted includes a fractional period of the year, the fraction is taken of the capped full year (it is *not* that the fraction is taken of the uncapped full year then that fraction is capped)

I've also identified that when the flag is set to TRUE (either when straight line depreciation should be ignored) the function interprets fractional start and end periods differently! This is discussed in the second tab of the Excel document.

In essence this is a horribly complex function, which must be very prone to human error - particularly in the past, when it has seemingly never be fully documented.

If anyone ever fixes the bug, I'll update the help wiki.