Documentation/How Tos/Calc: VDB function
From Apache OpenOffice Wiki
< Documentation | How Tos
CALC FUNCTIONS
Depreciation
Payment Streams, Annuities, Loans
Securities
Coupons
Miscellaneous
Contents
VDB
Returns the depreciation of an asset for a given year using a variable declining-balance method.
Syntax:
VDB(originalcost; salvagevalue; lifetime; periodstart; periodend; factor; nostraightline)
- originalcost: the initial cost of the asset.
- salvagevalue: is the value at the end of the depreciation (sometimes called the salvage value of the asset).
- lifetime: the number of years over which the asset is being depreciated.
- periodstart, periodend: the start and end year numbers defining the period for which the depreciation is calculated. These may be fractional, from 0 to lifetime.
- factor: the factor to set the depreciation rate (2 if omitted).
- nostraightline: TRUE to prevent VDB from using any straight line depreciation (FALSE if omitted - ie if omitted, straight line depreciation will be used where appropriate)
- To calculate depreciation, VDB uses a fixed rate. When factor = 2 this is the double-declining-balance method (because it is double the straight-line rate that would depreciate the asset to zero). The rate is given by:
- rate = factor / lifetime.
- The depreciation each year is calculated at the end of the year as:
- book_value_at_start_of_year * rate.
- However if straight line depreciation to salvagevalue would be greater than this, that is used instead. Straight line depreciation to salvagevalue for the year is given by:
- (book_value_at_start_of_year - salvagevalue) / years_of_life_left.
- To prevent the book value falling below salvagevalue, depreciation in any year is capped at:
- book_value_at_start_of_year - salvagevalue.
- This defines book values at the end of each year. VDB returns the depreciation for the period periodstart to periodend. 0 is the start of the asset's life, and lifetime the end. Thus the period 0 to 2 returns the depreciation in the first two years of the asset's life. The depreciation is calculated as the book value at periodstart less the book value at periodend.
- Fractions are allowed: the period 1.25 to 3.5 returns the depreciation from the end of the first quarter in the second year to the end of the second quarter in the fourth year. The book value for a fractional period is found by linearly interpolating from the year-end book values.
- The depreciation figure returned is always rounded to 2 decimal places.
Example:
VDB(100000; 5000; 10; 0; 1)
- returns 20000 in currency units, the depreciation in the first year - that is 100000 * 2 / 10 (double declining-balance depreciation).
VDB(100000; 5000; 10; 0; 2)
- returns 36000 in currency units, the depreciation in the first two years - that is 20000 in the first year plus 80000 * 2 / 10 (double declining-balance depreciation).
VDB(100000; 5000; 10; 1.5; 2.5)
- returns 14400 in currency units, the depreciation from halfway through the second year to halfway through the third year. The book value for period = 1 is 80000 and for period = 2 is 64000. Using linear interpolation the book value for period = 1.5 is 64000 + (80000-64000) * 0.5 = 72000; similarly the book value for period = 2.5 is 57600. The depreciation returned is the difference between these book values, ie 72000 - 57600 = 14400.
VDB(100000; 5000; 10; 7; 8)
- returns 5303.60 in currency units, the depreciation in the 8th year. The book value at the start of the 8th year is 20910.80 (100000 - VDB(100000; 5000; 10; 0; 7)). The straight line depreciation (blue line) is (20910.80 - 5000)/3 = 5303.60, which is greater than the double declining-balance depreciation (red line; it is above the blue line because depreciation is less).
VDB(100000; 5000; 10; 9; 10)
- returns 5303.60 in currency units, as above; straight line depreciation is constant.
VDB(100000; 5000; 10; 0; 10; 2; TRUE)
- returns 89262.58 in currency units. With straight line depreciation disabled the total depreciation over the lifetime falls short of the 95000 required to reduce the book value to 5000 (red line).
VDB(100000; 20000; 10; 0; 1)
- returns 20000 in currency units, the depreciation in the first year - that is 100000 * 2 / 10 (double declining-balance depreciation).
VDB(100000; 20000; 10; 7; 8)
- returns 971.52 in currency units, just enough depreciation to cap the book value at the salvagevalue (blue line).
VDB(100000; 20000; 10; 8; 10)
- returns 0 in currency units; no further depreciation is possible.
Issues:
- Rounding the result to 2 decimal places follows Excel's practice, but is unfortunate for those whose currency is not normally expressed with 2 decimal places.
- VDB(100000;20000;10;7.25;7.75) returns an erroneous result in Excel and in Calc (different in each). This seems to affect only fractional periods in the year when depreciation is being capped. See Issue 91651.
See Also