Apache OpenOffice (AOO) Bugzilla – Issue 109420
financial function VDB returns incorrect values
Last modified: 2014-04-04 13:37:08 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.
Created attachment 81960 [details] example spreadsheet
Confirmed with AOO410m1(Build:9750) - Rev. 1539999 2013-11-09_04:08:11 - Rev. 1540252 Debian
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
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.
Created attachment 82968 [details] with IRS data
See attachment 82968 [details]
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
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>
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....
IMHO we are better off removing this unexplained function. Knowing what we are doing > being compatible with Excel.
duplicate with Issue 91651 .
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.
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.
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.
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.