Documentation/Calc Functions/VDB

    From The Document Foundation Wiki
    Other languages:

    Function name:

    VDB

    Category:

    Financial Analysis

    Summary:

    Calculates the depreciation of an asset for an arbitrary time interval, based primarily on a variable rate declining balance method. VDB is an acronym for Variable Declining Balance. This method calculates the depreciation based on the current book value of the asset (rather than its original cost) and this decreases from one period to the next. This leads to the depreciation being greater in earlier periods and less in later periods.

    In contrast to the DDB function, which utilizes the same variable rate declining balance method, VDB offers additional flexibility as follows:

    • VDB enables the user to define the depreciation time as a start and end period within the asset's useful life. The start and end periods may have fractional parts.
    • VDB provides an option that allows the asset to be depreciated initially using the variable rate declining balance method, switching to straight line depreciation when that is calculated to be greater than the declining balance depreciation.

    Syntax:

    VDB(Cost; Salvage; Life; Start; End[; Factor[; NoSwitch]])

    Returns:

    Returns a non-negative real number, which is the depreciation of the asset for a specified time interval. The number returned is in the same currency unit as the specified cost and salvage values, and by default is formatted in a locale-dependent currency format.

    Arguments:

    Cost is a non-negative real number, or a reference to a cell containing that number, which is the cost or value of the asset before depreciation.

    Salvage is a non-negative real number, or a reference to a cell containing that number, which is the residual value of the asset at the end of its depreciated life. Salvage must be in the same currency units as Cost.

    Life is a non-negative real number, or a reference to a cell containing that number, which defines the number of periods over which the asset is depreciated.

    Start is a non-negative real number, or a reference to a cell containing that number, which specifies the point in the asset's life at which VDB should begin calculating depreciation. Start must be in the same time units as Life.

    End is a non-negative real number, or a reference to a cell containing that number, which specifies the point in the asset's life at which VDB should finish calculating depreciation. End must be in the same time units as Life and Start.

    Factor is a positive real number, or a reference to a cell containing that number, which specifies the depreciation factor to be used for the variable rate declining balance method. If a value is not specified, the default is 2. If Factor = 2, then VDB uses the double declining balance method. If Factor = 3, then VDB uses the triple declining balance method.

    NoSwitch is a logical value, or a reference to a cell containing a logical value, which determines whether VDB is able to switch to straight line depreciation when that is greater than declining balance depreciation. If NoSwitch is either omitted, or FALSE, or set to 0 (zero), then VDB automatically switches to straight line depreciation when that is greater than declining balance depreciation. If NoSwitch is either TRUE or set to a non-zero numeric value, then VDB never switches to straight line depreciation.

    • If any argument is non-numeric then VDB reports a #VALUE! error.
    • The function reports an invalid argument error (Err:502) in any of the following circumstances:
      • Cost < 0.
      • Salvage > Cost.
      • Factor ≤ 0.
      • Start < 0.
      • End > Life.
      • End < Start.

    Additional details:

    • To calculate the depreciation using the declining balance method, VDB uses a fixed rate throughout the asset's life, given by:
      [math]\displaystyle{ Rate~=~Factor~\times~\frac{1}{Life} }[/math]
    • The depreciation in each period for the declining balance method is calculated as:
      [math]\displaystyle{ \text{MIN}(Book~value \times Rate; Book~value~-~Salvage) }[/math]
      where Book value refers to the asset's value at the start of the period, which is calculated as Cost less the total depreciation expenses to date.
    • The straight line depreciation for each period is calculated as:
      [math]\displaystyle{ \frac{Book~value~–~Salvage}{Periods~remaining} }[/math]
    • For more background information about depreciation, visit Wikipedia's Depreciation page.

    Examples:

    Note that the formatting of the return values given in the following examples might assume the English (USA) locale.

    Simple example with multiple calls to VDB

    The following table illustrates the behavior of the VDB function.

    Sample data for VDB function
    A B C D E F G H I J
    1 Cost: 1200 Start End DDB VDB
    (No Switch)
    VDB
    (Switch)
    Difference
    (No Switch)
    Difference
    (Switch)
    2 Salvage: 200 0 1 $257.14 $257.14 $257.14 $942.86 $942.86
    3 Life: 7 1 2 $202.04 $202.04 $202.04 $740.82 $740.82
    4 Factor: 1.5 2 3 $158.75 $158.75 $158.75 $582.07 $582.07
    5 3 4 $124.73 $124.73 $124.73 $457.34 $457.34
    6 4 5 $98.00 $98.00 $98.00 $359.34 $359.34
    7 5 6 $77.00 $77.00 $79.67 $282.34 $279.67
    8 6 7 $60.50 $60.50 $79.67 $221.84 $200.00
    9
    10 Total $978.16 $978.16 $1,000.00
    A B C D E F G H I J

    The purpose of the examples in the above table is to show results from the VDB function alongside corresponding results from the DDB function for comparison. Column F presents results from the DDB function. Column G presents results from the VDB function with the NoSwitch argument set to TRUE. Column H presents results from the VDB function with the NoSwitch argument set to FALSE.

    The various cell ranges in this spreadsheet contain data as follows:

    • Cells B1:B4 contain values for four of the arguments passed in the various DDB and VDB function calls of cells F2:H8.
    • Cells D2:D8 each contain a value for the Start argument that is passed to the two VDB function calls in the same spreadsheet row. These values are not used in calls to the DDB function.
    • Cells E2:E8 each contain a value for the End argument that is passed to the two VDB function calls in the same spreadsheet row. Each value is also passed as the Period argument to the DDB function call in the same spreadsheet row.
    • Cells F2:F8 show the results returned by the seven DDB function calls. Cell F2 contains the formula =DDB($B$1; $B$2; $B$3; $E2; $B$4); cell F3 contains the formula =DDB($B$1; $B$2; $B$3; $E3; $B$4); and so on.
    • Cells G2:G8 show the results returned by the seven VDB function calls for which depreciation is always calculated using the declining balance method, never switching to the straight line method. Cell G2 contains the formula =VDB($B$1; $B$2; $B$3; $D2; $E2; $B$4; TRUE()); cell G3 contains the formula =VDB($B$1; $B$2; $B$3; $D3; $E3; $B$4; TRUE()); and so on.
    • Cells H2:H8 show the results returned by the seven VDB function calls for which part of the depreciation may be calculated using the straight line method. Cell H2 contains the formula =VDB($B$1; $B$2; $B$3; $D2; $E2; $B$4; FALSE()); cell H3 contains the formula =VDB($B$1; $B$2; $B$3; $D3; $E3; $B$4; FALSE()); and so on.
    • Cells F10:H10 show the total depreciation calculated over the life of the asset for each case.

    The following features can be seen in these results:

    • All values in cells F2:F8 match the corresponding values in cells G2:G8. As expected, DDB and VDB generate the same results when the latter is never allowed to switch to the straight line depreciation method.
    • Period 1. All function calls return the same value, $257.14. The declining balance method uses a fixed rate given by:
      [math]\displaystyle{ ~Factor \times \frac{1}{Life} = 1.5 \times \frac{1}{7} = 0.214286 }[/math]
      Hence the depreciation for this period is 0.214286 × 1200 = 257.14. Note that by default, the results from both DDB and VDB are formatted as currency.
    • Period 2. The value of the asset at the start of this period is 1200 - 257.14 = 942.86. Therefore the depreciation for this period is 0.214286 × 942.86 = 202.04.
    • Period 3. The value of the asset at the start of this period is 942.86 - 202.04 = 740.82. Therefore the depreciation for this period is 0.214286 × 740.82 = 158.75.
    • Period 4. The value of the asset at the start of this period is 740.82 - 158.75 = 582.07. Therefore the depreciation for this period is 0.214286 × 582.07 = 124.73.
    • Period 5. The value of the asset at the start of this period is 582.07 - 124.73 = 457.34. Therefore the depreciation for this period is 0.214286 × 457.34 = 98.00.
    • Period 6. In this period we see VDB switching to the linear depreciation method where it can do so, and this is indicated by the difference between the values in cells G7 and H7. The value of the asset at the start of this period is 457.34 - 98.00 = 359.34. Therefore the declining balance depreciation for this period is 0.214286 × 359.34 = 77.00. However, the straight line depreciation for this period would be given by:
      [math]\displaystyle{ ~\frac {Cost~–~Salvage~–~Depreciation~so~far}{Periods~remaining} }[/math] =

      [math]\displaystyle{ ~\frac {1200-200-840.66}{2}=79.67 }[/math]
      as displayed in cell H7. The straight line value (79.67) is used because it is greater than the declining balance value (77.00).
    • Period 7. In this last period of depreciation, there is again a difference between the values in cells G8 and H8, with the calculated straight line value (79.67) shown in cell H8.
    • Note that the total depreciation of 1,000.00 shown in cell H10 is exactly equal to the difference between the cost and salvage values and this is a consequence of using the straight line method for later depreciation periods. However, when all depreciation calculations use the declining balance method, the total depreciation may be less than the difference between the cost and salvage values, as shown in cells F10 and G10.

    Additional examples

    Formula Description Returns
    =VDB(35000; 7500; 36; 10; 20; 3; 1)

    Calculate the declining balance depreciation for an asset that initially costs 35000 currency units and has a salvage value of 7500 currency units. The useful life of the asset is three years, with the depreciation to be carried out over 36 monthly periods. The depreciation is to be calculated from month 10 to month 20, using a depreciation factor of 3. The final argument is a non-zero numeric value and so VDB should never switch to straight line depreciation.

    $7,161.64
    =VDB(35000; 7500; 36; 10; 20)

    Calculate the declining balance depreciation for an asset that initially costs 35000 currency units and has a salvage value of 7500 currency units. The useful life of the asset is three years, with the depreciation to be carried out over 36 monthly periods. The depreciation is to be calculated from month 10 to month 20, using the default depreciation factor 2. The final argument is omitted, so that VDB can switch to straight line depreciation when appropriate.

    $8,603.80
    =VDB(35000; 7500; 36; 10.5; 20.5)

    Calculate the declining balance depreciation for an asset that initially costs 35000 currency units and has a salvage value of 7500 currency units. The useful life of the asset is three years, with the depreciation to be carried out over 36 monthly periods. The depreciation is to be calculated from month 10.5 to month 20.5, using the default depreciation factor 2. The final argument is omitted, so that VDB can switch to straight line depreciation when appropriate. Note that the formula =VDB(35000; 7500; 36; 10.5; 15.5) returns $4,776.02 and the formula =VDB(35000; 7500; 36; 15.5; 20.5) returns $3,588.79; adding these two values again gives $8,364.81.

    $8,364.81

    Related LibreOffice functions:

    AMORDEGRC

    AMORLINC

    DB

    DDB

    SLN

    SYD

    ODF standard:

    Section 6.12.50, part 2

    Related (or similar) Excel functions:

    VDB