AMORDEGRC Function of Excel – Linear Depreciation of an Asset

In an accounting system, we might be willing to calculate the depreciation for the each of accounting periods. In this tutorial, we would learn to use the AMORDEGRC function in excel for calculating depreciation in excel. The AMORDEGRC formula in excel helps to find depreciation for each of the years (upcoming).

The following graph explains the linear depreciation of the asset. The slope of the graph is negative. In conclusion, we can say that the value of the asset decreases with time.

Let’s being with excel AMORDEGRC formula. Here we go 😎

When to Use AMORDEGRC Function of Excel

The excel AMORGEGRC function calculates the linear depreciation of an asset with each of its accounting periods. In other words, the function uses the depreciation coefficient to find resultant depreciation. The assets can have a different rate of depreciation.

The AMORDEGRC function is categorized under the financial functions of Excel.

Syntax and Arguments

=AMORDEGRC(cost, date_purchased, first_period, salvage, period, rate, [basis])

The below points explain the standard function arguments for the AMORDEGRC function.

• cost – It is the actual value of the asset at the beginning.

• date_purchased – This is the date on which the asset was purchased. On this date, the depreciation calculation would start.

• first_period – This is the date when the very first accounting period will end.

• salvage – This argument refers to the value of the asset at the end of its useful life.

• period – This argument represents the accounting period.

• [basis] – This is an optional argument. It is the method for the day count. Therefore, it can have the following values.

Points To Remember About AMORDEGRC formula

One should always keep the following points in mind about the AMORDEGRC function before its actual usage.

• The argument [basis] can have 5 values. However, the default value is 0.

• The accounting period starts from 0. The value is 0 from the purchasing date of an asset to the end of first-period date. After that 1 will be for one year after the end of first-period date, and so on.

• The arguments purchase_date and first_period should be a valid excel date. The valid dates in Excel start from 1 January 1900. The function will give a #VALUE! error for any invalid date.

• Salvage must be smaller than the cost otherwise function will give a #NUM! error.

• Sometimes, the dates in excel might not be in the correct format. In such a scenario, you may use the DATE excel function for correct date format.

• The depreciation decreases with the increasing accounting period. However, Depreciation (reduction in the value of asset) will be more for the first accounting period than the third accounting period.

• The AMORDEGRC function uses depreciation coefficient to calculate depreciation. After that, Depreciation coefficient is calculated using the asset life, where asset life = 1/rate. Following are the depreciation coefficients.

Example To Learn Usage of Excel’s AMORDEGRC Formula

Let us now perform some examples for the AMORDEGRC function of Excel.

Ex. 1 – Simplest Example for AMORDEGRC Function

In this example, let us say there is an asset worth $1000. The depreciation rate is 0.15 or 15%. Asset purchased on 31 June 2021. The first period starts after 31 Dec 2021. The salvage of the asset is 120.

We will use the following formula of Excel to get the depreciation amount.

=AMORDEGRC($B$1,$B$2,$B$3,$B$4,D2,$B$5)

Here, D2 is the period of depreciation. It begins from 0, 1, 2, 3, 4, 5, and 6.

As a result, the excel formula returns depreciation for each of the years, as shown in the screenshot below:

Similarly, we can calculate the new value of the asset. What we need to do is, subtract depreciation from the cost of the asset at the beginning.

=$B$1-E2

We have used the excel fill handle tool to copy down the formula to find depreciation and the value of the asset for all the accounting periods.

Explanation – Firstly, in this formula to calculate depreciation, we have passed six required function arguments. For period 0 (from 31 Jun 2021 to 31 Dec 2021) the depreciation on the asset of $1000 comes out to be 188. As a result, the actual book value of the asset will decrease to $812. The function has first determined the life of the asset by the rate (Life of asset =1/rate) at the backend and then used the depreciation coefficient to give us the results.

With the increasing value of the accounting period, we can see the value of assets start dropping.

DATE Function to pass function arguments of AMORDEGRC formula

Instead of manually typing the date, we can even use the DATE function of Excel to pass the purchase and first_period argument of the AMORDEGRC formula. The above formula will become:

=AMORDEGRC($B$1,DATE(2021,6,30),DATE(2021,12,31),$B$4,D2,$B$5)

We have only replaced cell B2 with the DATE Function of excel to pass the purchase_date function argument in the correct format. Similarly, we can replace cell B3 with the DATE function in the first_period function argument. As a result, this will prevent errors.

Thank you for reading 😉