Documentation/Calc Functions/AMORDEGRC

    From The Document Foundation Wiki

    Function name:

    AMORDEGRC

    Category:

    Financial Analysis

    Summary:

    Calculates the depreciation (or amortization) of an asset during a specific period using a degressive depreciation model. This method depreciates at a higher rate at the beginning and the rate decreases over the useful life of the asset. If the asset is acquired part-way through an accounting period, then depreciation for that period is calculated on a pro rata basis.

    AMORDEGRC is intended for users of the French accounting system.

    Syntax:

    AMORDEGRC(Cost; Date Purchased; First Period; Salvage; Period; Rate[; Basis])

    Returns:

    Returns a non-negative real number that is the depreciation of the asset during the specified period, expressed in the same currency units as the asset's cost and salvage values.

    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.

    Date Purchased is a date, or a reference to a cell containing that date, which is the start date of the depreciation. For a tangible asset, this is usually the date of acquisition.

    First Period is a date, or a reference to a cell containing that date, which is the end date of the first accounting period during which depreciation is calculated.

    Salvage is a positive 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.

    Period is a non-negative integer, or a reference to a cell containing that integer, which specifies the accounting period for which the depreciation value is returned. The value 0 indicates the period that ends on the date given by First Period. Subsequent accounting periods are numbered 1, 2, 3, and so on.

    Rate is a positive real number (expressed as a percentage, such as 2.5%, or a decimal fraction, such as 0.025), or a reference to a cell containing that number, which is the annual rate at which the value of the asset is depreciated.


    Basis is an integer in the range 0 to 4, or a reference to a cell containing that integer, which indicates how the year is to be calculated. For more background information, visit Wikipedia's Day count convention page.

    Basis Calculation
    0 or missing US method (NASD), 12 months of 30 days each
    1 Exact number of days in months, exact number of days in year.
    2 Exact number of days in month, year has 360 days.
    3 Exact number of days in month, year has 365 days.
    4 European method, 12 months of 30 days each.


    • If either of Date Purchased or First Period is not a valid date, then AMORDEGRC returns a #VALUE! error.
    • If any of Cost, Salvage, Period, or Rate is non-numeric, then AMORDEGRC reports a #VALUE! error.
    • If Basis is non-numeric, then AMORDEGRC reports an invalid argument error (Err:502).
    • If Date Purchased is later than First Period, then AMORDEGRC reports an invalid argument error (Err:502).
    • If either of Cost or Rate is less than or equal to 0, then AMORDEGRC reports an invalid argument error (Err:502).
    • If Salvage is less than 0 or greater than Cost, then AMORDEGRC reports an invalid argument error (Err:502).
    • If either of Period or Basis is a non-integer value, then AMORDEGRC truncates it to an integer value.
    • If Period is less than 0 then AMORDEGRC reports an invalid argument error (Err:502).
    • If, after any truncation, Basis is not equal to 0, 1, 2, 3, or 4, then AMORDEGRC reports an invalid argument error (Err:502).

    Additional details:

    • AMORDEGRC utilizes a depreciation factor (f) that depends on the lifetime ([math]\displaystyle{ t~=~\frac{1}{Rate} }[/math]) of the asset. The purpose of this factor is to increase the rate of depreciation for assets with a longer lifetime and it is defined as follows:
    Depreciation factors used in AMORDEGRC
    Lifetime of asset (t) Depreciation factor (f)
    t < 3 1
    3 <= t < 5 1.5
    5 <= t <=6 2
    t > 6 2.5
    • If Period = 0, the depreciation is calculated on a pro rata basis. For this period the formula for AMORDEGRC is:
    [math]\displaystyle{ \operatorname{AMORDEGRC_{0}}~=~f*Cost~*~Rate~*~\operatorname{YEARFRAC}(Date~Purchased; First~Period; Basis) }[/math]
    If Date Purchased is equal to First Period, then [math]\displaystyle{ \operatorname{AMORDEGRC_{0}}~=~0 }[/math]
    • For subsequent periods, AMORDEGRC calculates the depreciation based on the cost or value of the asset before depreciation minus the total depreciation in previous periods.
    • If Period = 1, the depreciation is calculated as follows.
    [math]\displaystyle{ \operatorname{AMORDEGRC_{1}}~=~f*(Cost-AMORDEGRC_{0})~*~Rate }[/math]
    • If Period = 2, the depreciation is calculated as follows.
    [math]\displaystyle{ \operatorname{AMORDEGRC_{2}}~=~f*(Cost-AMORDEGRC_{0}-AMORDEGRC_{1})~*~Rate }[/math]
    • This processing continues for subsequent periods until the end of the depreciated life. The total depreciation over all periods will not be greater than the value of the asset before any depreciation, given in the Cost argument.
    • There are some differences between the behavior of AMORDEGRC in Calc and Microsoft Excel.
    1. Excel does not support option 2 for the Basis argument (actual number of days in month, 360 days in year).
    2. Calc and Excel behave differently when the date of purchase coincides with the end of an accounting period. Calc regards the initial period 0 as of zero length, and thus returns zero depreciation. Excel regards the initial period 0 as the first full period.
    3. As the Period argument is increased, the latest period that returns a non-zero depreciation value may return different values for Calc and Excel.
    4. There may be minor rounding differences between the values returned by Excel and Calc.
    These differences may limit interoperability when you have a Calc spreadsheet containing AMORDEGRC calls that is later opened in Excel.

    Examples:

    Simple example with multiple calls to AMORDEGRC

    The following table illustrates the behavior of the AMORDEGRC function.

    A B C D E
    1 Cost 1200 Period Depreciation
    2 Date Purchased 2022-07-01 0 225
    3 First Period 2022-12-31 1 366
    4 Salvage 200 2 228
    5 Period - 3 143
    6 Rate 15% 4 119
    7 Basis 0 5 0
    8 6 0
    9 7 0
    10 8 0
    11
    12 Total 1081

    The data in cells B1:B4 and B6:B7 contain the values for most of the arguments passed in AMORDEGRC function calls. The function is called nine times, once for each value of the Period argument given in cells D2 to D10. Cell E2 contains the formula =AMORDEGRC($B$1; $B$2; $B$3; $B$4; $D2; $B$6; $B$7); cell E3 contains the formula =AMORDEGRC($B$1; $B$2; $B$3; $B$4; $D3, $B$6, $B$7); and so on. The values returned from the AMORDEGRC calls are shown in cells E2:E10 and cell E12 contains the sum of all the values in cells E2:E10. The following features can be seen in these results:

    • For this example data, depreciation is calculated during one partial period (period 0) and four full accounting periods (periods 1 to 4).
    • Period 0. Since the asset was acquired midway through the accounting period, the depreciation for period 0 is calculated on a pro rata basis as [math]\displaystyle{ 1200 \times 0.5 \times 2.5 \times 0.15~=~225 }[/math].
    • Period 1. For this full accounting period, the depreciation is calculated as [math]\displaystyle{ (1200-225) \times 2.5 \times 0.15~=~365.625 }[/math] and AMORDEGRC rounds this value to 366.
    • The depreciation values for periods 2 and 3 are calculated in a similar way to period 1.
    • During periods 0 through to 3, the total depreciation is 962. Since Cost - Salvage - 962 is greater than 0, AMORDEGRC determines that there should be a final depreciation expense in period 4. However, instead of using the same algorithm as steps 1 to 3, the number returned for period 4 is half the difference between the original value of the Cost argument (1200 in this case) minus the total depreciation in the previous periods (962 in this case), that is [math]\displaystyle{ 0.5 \times 238 }[/math] or [math]\displaystyle{ 119 }[/math].
    • For periods 5 or later, AMORDEGRC returns 0.

    Additional examples

    Formula Description Returns
    =AMORDEGRC(1500; "2001-04-01"; "2001-06-15"; 454; 0; 0.19; 2) Here the function calculates the pro rata depreciation for period 0, which is a short period of approximately 2.5 months. 119
    =AMORDEGRC(1500; "2001-04-01"; "2001-06-15"; 454; 0; 0.19) Here the function again calculates the pro rata depreciation for period 0, which is a short period of approximately 2.5 months. However, in this case, the value of the Basis argument is not specified and so a default of 0 is applied, giving a slightly different result to the previous example. 117
    =AMORDEGRC(1500; "2001-04-01"; "2001-06-15"; 454; 1; 0.19; 2) Here the function calculates the amount of depreciation for a full accounting period (period 1). 525
    =AMORDEGRC(2000; DATE(2020; 2; 1); DATE(2020; 12; 31); 10; 4; 10%; 0) Here the function calculates the amount of depreciation for a full accounting period (period 4). Note that it is often good practice to utilize the DATE function instead of typing formatted date values. 163

    Related LibreOffice functions:

    AMORLINC

    DB

    DDB

    SLN

    SYD

    VDB

    YEARFRAC

    ODF standard:

    None

    Related (or similar) Excel functions:

    AMORDEGRC