Syntax. AMORDEGRC(Cost,Date,First_Period,Residual_Value,Period,Rate,Basis)
Definition. This function calculates the depreciation amount for assets depending on the desired period based on the French accounting system. The calculated amount is rounded to an integer.
Arguments
Cost (required) The purchase cost (net purchase price plus incidental purchase expenses minus reduction of purchase cost) of an asset. If you don’t use a number, the error
#VALUE!is displayed. If the entered number is negative, the error#NUMBER!is displayed.Date_Purchased (required) The purchase date of the asset; in other words, the beginning of the depreciation.
First_Period (required) Refers to the date at the end of the first period in the depreciation period. The period gets the number zero.
Salvage_Value (required) The anticipated remaining value of the asset after the years of depreciation. If you are using a number that is larger than Cost, the error
#NUMBER!is returned. The same error value is displayed if a negative number is used.Period (required) Specifies the time period for which the depreciation amount should be calculated. Only use integers that are greater than or equal to zero.
Rate (required) The (initially linear) depreciation percentage.
Basis (optional) Defines the method you want to use for counting the days. The possible values are shown in Table 15-2.
Table 15-2. Methods for Counting the Days of the Year
Basis | Method | Meaning |
|---|---|---|
0 | 30/360 (NASD method) | Each months has 30 days; each year has 12 months. If the start date is the 31st of a month, this date is converted to the 30th of the same month. If the end date is the 31st of a month and the start date is a date before the 30th of a month, the end date is converted to the 1st of the next month. The end date is always the 30th of the same month. |
1 | Exact/Exact | Each month has the same amount of days as in the commonly used calendar. The sum of all months is the number of days in the year. |
2 | Exact/360 days | Each month has the same amount of days as in the commonly used calendar. A year always has 360 days. |
3 | Exact/365 days | Each month has the same amount of days as in the commonly used calendar. A year always has 365 days. |
4 | 30/360 (European method) | Each months has 30 days; each year has 12 months. Each start and end date that is on the 31st of a month is converted into the 30th of the same month. |
Background. Depreciation is used to determine the loss of value of an asset and make that loss visible. It should not be confused with depreciation because of abrasion/use, which refers to the cost of purchasing an asset as part of operation expenses from a tax point of view.
The depreciation percentage that is given by the rate is initially interpreted as a linear depreciation rate and thus determines the depreciation duration (5 percent equals 20 years, 10 percent equals 10 years, 20 percent equals 5 years, and so on). Degressive depreciation is created by an additional weighting (multiplication) of the depreciation rate with a factor according to the following rule:
Factor 1.5 for a rate larger than 25 percent (corresponding to a depreciation duration of three to four years).
Factor 2 for a rate between 16.66 and 25 percent (corresponding to a depreciation duration of five to six years).
Factor 2.5 for a rate of less than 16.67 percent (corresponding to a depreciation duration of more than six years).
If you are depreciating to a residual value of zero, this value cannot be reached within the required periods (it becomes smaller and smaller, but only vanishes when the decimal places are also removed by rounding). This is why the residual value in the third period before the end is divided over the two last periods.
If you are depreciating to a residual value that is larger than zero, the depreciation ends in the period in which a book value that is less than or equal to the given residual value is reached. This function returns a depreciation value of zero in the following periods. However, you must use the final book value as the final depreciation value.
Note that the depreciation amount of the first period gets the number zero. It is prorated only for the year, and the days are counted as explained earlier in Table 15-2.
Example. An asset is purchased on June 6, 2010, for a cost of $1,000.00. It is depreciated in the first year with a depreciation of 10 percent, independent of the assumed residual value of $142.00. The result is achieved with the formula
=AMORDEGRC(1000,"6/6/2010","12/31/2010",0,0,10%,4)
The numbers usually reference the cells where these numbers are located. You can try to calculate this amount by using the formula =DAYS360("6/6/2010","12/31/2010",TRUE) to determine the number of days. The formula should look like this:
=ROUND(1000*10%*2.5*DAYS360("6/6/2010","12/31/2010",TRUE)/360,0)The later depreciation amounts can be determined more easily from the respective book values at the end of the previous period multiplied by 10 percent x 2.5.