Documentation/Calc Functions/AMORLINC

Other languages:

AMORLINC

Category:

Financial Analysis

Summary:

Calculates the depreciation (or amortization) of an asset during a specific period using a linear depreciation model. This method depreciates the asset by a constant value every full accounting period. If the asset is acquired part-way through an accounting period, then depreciation for that period is calculated on a pro rata basis.

AMORLINC is intended for users of the French accounting system.

Syntax:

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

• Using AMORLINC, the depreciated life of the asset comprises an initial fractional period up to the end of the first accounting period, then a number of full accounting periods, and then a final fractional period.
• If Period = 0, the depreciation is calculated on a pro rata basis. For this period the formula for AMORLINC is:
$\displaystyle{ \operatorname{AMORLINC_{0}}~=~Cost~*~Rate~*~\operatorname{YEARFRAC}(Date~Purchased; First~Period; Basis) }$
If Date Purchased is equal to First Period, then $\displaystyle{ \operatorname{AMORLINC_{0}}~=~0 }$
• In general, the depreciation is calculated for N full accounting periods (periods 1, 2, 3, …, N), where N is given by:
$\displaystyle{ N~=~\operatorname{INT}\left ( \frac{(Cost~-~Salvage~-~\operatorname{AMORLINC_{0}})}{Cost~\times ~Rate} \right ) }$
For these full periods (1 ≤ PeriodN), the depreciation is $\displaystyle{ Cost~\times ~Rate }$.
• In most cases further depreciation occurs in a final accounting period (Period equals N+1). This is likely to be a partial period, since the total depreciation over all periods cannot exceed the difference between the cost and salvage values. The depreciation in this period is given by:
$\displaystyle{ \operatorname{AMORLINC_{N+1}}~=~Cost~-~Salvage~-~\operatorname{AMORLINC_{0}}~-~N~ \times ~Cost~ \times ~Rate }$
• For periods beyond the depreciated life of the asset (Period > N+1), AMORLINC returns the value 0.0.
• There are two key differences between the behavior of AMORLINC 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.
These differences may limit interoperability when you have a Calc spreadsheet containing AMORLINC calls that is later opened in Excel.

Examples:

Simple example with multiple calls to AMORLINC

The following table illustrates the behavior of the AMORLINC function.

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

The data in cells B1:B4 and B6:B7 contain the values for most of the arguments passed in AMORLINC 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 =AMORLINC($B$1; $B$2; $B$3; $B$4; $D2;$B$6;$B$7); cell E3 contains the formula =AMORLINC($B$1;$B$2;$B$3;$B$4;$D3; $B$6; $B$7); and so on.

The values returned from the AMORLINC 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:

• Period 0. Since the asset was acquired midway through the accounting period, the depreciation for period 0 is expected to be around half of its value in subsequent full years. This is reflected in the example data (90 compared to 180).
• For this example data, depreciation is calculated during five full accounting periods (periods 1 to 5). During these periods, the depreciation is calculated as $\displaystyle{ 1200 \times 0.15~=~180 }$.
• The total depreciation up to the end of period 5 is 990. Since an asset cannot be deprecated below its salvage value, the depreciation in period 6 is limited to $\displaystyle{ 1200~-~200~-~990~=~10 }$ and the depreciation in any subsequent period is zero.

Formula Description Returns
=AMORLINC(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. 59.375
=AMORLINC(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. 58.5833333333333
=AMORLINC(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). 285
=AMORLINC(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. 200

AMORLINC