Documentation/Calc Functions/DB

    From The Document Foundation Wiki
    Other languages:

    Function name:

    DB

    Category:

    Financial Analysis

    Summary:

    Calculates the depreciation of an asset for a given period using the fixed rate declining-balance method. 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.

    DB uses a fixed depreciation rate that is a function of the initial value of the asset, its salvage value, and its estimated useful life.

    Syntax:

    DB(Cost; Salvage; Life; Period[; Month])

    Returns:

    Returns a non-negative real number which is the depreciation of the asset for a specified period using the fixed rate declining-balance method. 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 positive 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 real number in the range 0.0 < Life ≤ 1200.0, or a reference to a cell containing that number, which defines the number of periods over which the asset is depreciated.

    Period is a positive integer in the range 1 to Life + 1, or a reference to a cell containing that integer, which specifies the period for which the depreciation value is returned.

    Month is a positive integer in the range 1 to 12, or a reference to a cell containing that integer, which is the number of months for the first year of depreciation. If the argument is omitted, a default value of 12 is used.

    • If any argument is non-numeric then DB reports a #VALUE! error.
    • If Month is a non-integer value, then DB truncates it to an integer value.
    • The function reports an invalid argument error (Err:502) in any of the following circumstances:
    • Cost ≤ 0.0.
    • Salvage < 0.0 or Salvage > Cost.
    • Life ≤ 0.0 or Life > 1200.0.
    • Period ≤ 0 or Period > Life + 1;
    • Month < 1 or Month > 12 (after any truncation)
    • If 0.0 < Period < 1.0, then DB returns a depreciation value of 0.0.

    Additional details:

    • DB calculates depreciation over a period. If you specify a value other than 12 for the Month argument, then DB assumes that the duration of a depreciation period is 12 months or one year. If the Month argument is omitted or specified as 12, then your depreciation period need not be one year.
    • To calculate the depreciation, DB uses a fixed rate throughout the asset’s life, given by:
    [math]\displaystyle{ Rate~=~1- \left( \frac{Salvage}{Cost} \right)^{\frac{1}{Life}} }[/math]
    • The depreciation in any period i is calculated as:
    [math]\displaystyle{ Depreciation_{i}~=~Value\_at\_start\_of\_period \times Rate }[/math]
    Here
    [math]\displaystyle{ Value\_at\_start\_of\_period~=~Cost - Depreciation\_so\_far }[/math]
    • If Month is less than 12, the depreciation rate used for the first period is:
    [math]\displaystyle{ Rate \times \frac{Month}{12} }[/math]
    • If Month is less than 12, the depreciation rate used for the final period (Life + 1) is:
    [math]\displaystyle{ Rate \times \frac{12-Month}{12} }[/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 assume the English (USA) locale.

    Simple example with multiple calls to DB

    The following table illustrates the behavior of the DB function.

    A B C D E
    1 Cost 1200 Period Depreciation
    2 Salvage 200 1 $216.60
    3 Life 4 2 $355.01
    4 Month 6 3 $226.85
    5 4 $144.96
    6 5 $46.31
    7 6 Err:502
    8
    9 Total $989.73

    The data in cells B1:B4 contain the values for four of the arguments passed in DB function calls. The function is called six times, once for each value of the Period argument given in cells D2 to D7. Cell E2 contains the formula =DB($B$1; $B$2; $B$3; $D2; $B$4); cell E3 contains the formula =DB($B$1; $B$2; $B$3; $D3; $B$4); and so on.

    The values returned from the DB calls are shown in cells E2:E7 and cell E9 contains the sum of all the values in cells E2:E6 (cell E7 shows an error and is therefore excluded from this sum). The following features can be seen in these results:

    • Period 1. DB uses a fixed rate given by [math]\displaystyle{ \left(1- \left( \frac{salvage}{cost} \right)^{\frac{1}{life}}\right) }[/math] or [math]\displaystyle{ \left(1- \left( \frac{200}{1200} \right)^{\frac{1}{4}}\right)=0.361 }[/math] to three decimal places. Since the first period comprises 6 months, the depreciation for the period is [math]\displaystyle{ 0.5 \times 0.361 \times 1200 = 216.6 }[/math]. Note that by default, the results from DB are formatted as a currency.
    • Period 2. This period comprises a full year and the value of the asset at the start of that year is [math]\displaystyle{ 1200-216.6=983.4 }[/math]. Therefore the depreciation for this period is [math]\displaystyle{ 0.361 \times 983.4=355.01 }[/math].
    • Periods 3 and 4. The depreciation in these periods is calculated in a similar manner to period 2.
    • Period 5. As the lifetime of the asset is given as 4 years and period 1 was only 6 months long, a further 6 months of depreciation occurs during period 5.
    • Period 6. An invalid argument error is returned because the value of the Period argument is greater than the value of the Life argument plus 1.
    • Note that the total depreciation of 989.73 is not exactly equal to the difference between the cost and salvage values of 1000.

    Additional examples

    Formula Description Returns
    =DB(25000; 1000; 3; 2; 6) A computer system with an initial cost of 25,000 currency units is to be depreciated over a three year period, where the first period consists of six months. The salvage value is 1,000 currency units. The depreciation of the computer system over the second year is 11,037.95 currency units. The same result is obtained with the formula =DB(25000; 1000; 3; 2; 6.9) because the non-integer Month value is truncated to 6. $11,037.95
    =DB(25000; 1000; 3; 2) This is the same example as above with the difference that the Month argument is not specified, hence it is 12 by default. In this case the depreciation of the computer system over the second year is 5,625.90 currency units. $5,625.90

    Related LibreOffice functions:

    AMORDEGRC

    AMORLINC

    DDB

    SLN

    SYD

    VDB

    YEARFRAC

    ODF standard:

    Section 6.12.13, part 2

    Related (or similar) Excel functions:

    DB