Excel Function – AMORDEGRC

The AMORDEGRC function in Microsoft Excel is a financial function designed to calculate the depreciation of an asset for each accounting period using a depreciation coefficient. Unlike the AMORLINC function, AMORDEGRC applies a depreciation coefficient based on the life of the asset. This function is particularly tailored for the French accounting system, where it is essential to account for the depreciation of assets accurately over time.

What is the AMORDEGRC Function?

The AMORDEGRC, also known as French declining balance function, returns the depreciation for each accounting period, considering the cost of the asset, the date of purchase, the end of the first period, the salvage value, the period, the rate of depreciation, and the basis for the calculation. This function is especially useful when an asset is purchased in the middle of an accounting period, as it prorates the depreciation accordingly.

Syntax of the AMORDEGRC Function

The syntax for the AMORDEGRC function is as follows:

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

• cost: The initial cost of the asset.
• date_purchased: The date the asset was purchased.
• first_period: The end date of the first period.
• salvage: The salvage value of the asset at the end of its useful life.
• period: The period for which the depreciation is calculated.
• rate: The rate of depreciation.
• basis: (Optional) The year basis to be used. This can be:
  • 0 or omitted: 360 days (NASD method)
  • 1: Actual/actual
  • 3: Actual/365
  • 4: European 30/360

How to Use the AMORDEGRC Function

To use the AMORDEGRC function effectively, it is crucial to understand each parameter and how it impacts the depreciation calculation. Here is a step-by-step guide:

1. Determine the Cost of the Asset: This is the initial purchase price of the asset.
2. Identify the Purchase Date: The exact date when the asset was acquired.
3. Set the End Date of the First Period: This is the date marking the end of the first accounting period.
4. Calculate the Salvage Value: The estimated residual value of the asset at the end of its useful life.
5. Define the Depreciation Period: The specific period for which you want to calculate the depreciation.
6. Set the Depreciation Rate: The annual rate at which the asset depreciates.
7. Choose the Basis: The method for calculating the number of days in a year.

Practical Example

Let’s consider a practical example to illustrate the use of the AMORDEGRC function. Suppose a company purchases a machine for $10,000 on June 30, 2023. The end of the first period is December 31, 2023, the salvage value is $1,000, the depreciation rate is 20% per year, and the basis is the European 30/360 method.

The formula to calculate the depreciation for the first period would be:

=AMORDEGRC(10000, DATE(2023,6,30), DATE(2023,12,31), 1000, 1, 0.2, 4)

The result of the formula would be 3200, which is depreciated amount after the first period.

Comparison of AMORLINC AND AMORDEGRC side-by-side calculation

Here are you see after 7 periods, the item cannot depreciate anymore given the 15% depreciation rate will be over 100%. You see that for AMORLINC the maximum amount is always at 9000 depreciated amount leaving salvage 1000. While for AMORDEGRC can leave us with more depreciated amount than salvage amount.

Detailed Breakdown of Parameters

• Cost: The cost parameter is straightforward; it represents the purchase price of the asset. In our example, this is $10,000.
• Date Purchased: This parameter should be entered using the DATE function to avoid errors. For example, DATE(2023,6,30) ensures that Excel recognizes the date correctly.
• First Period: The end date of the first period is crucial for prorating the depreciation. Using DATE(2023,12,31) sets this parameter accurately.
• Salvage Value: The salvage value is the estimated value of the asset at the end of its useful life. In our example, this is $1,000.
• Period: This parameter specifies the period for which the depreciation is calculated. In our example, we are calculating for the first period, so this is set to 1.
• Rate: The depreciation rate is the annual rate at which the asset depreciates. In our example, this is 20%, or 0.2.
• Basis: The basis parameter determines the method for calculating the number of days in a year. The European 30/360 method is represented by 4.

Depreciation Coefficient

The AMORDEGRC function applies a depreciation coefficient based on the life of the asset. The coefficients are as follows:

• Between 3 and 4 years: 1.5
• Between 5 and 6 years: 2
• More than 6 years: 2.5

These coefficients adjust the depreciation rate, increasing it as the asset’s useful life extends.

Common Errors and Troubleshooting

When using the AMORDEGRC function, it is essential to be aware of common errors that can occur:

• #VALUE! Error: This error occurs if any of the date parameters are invalid. Ensure that dates are entered correctly using the DATE function.
• #NUM! Error: This error occurs if the cost is less than or equal to the salvage value, the rate is less than or equal to zero, or the basis is not between 0 and 4.

Advanced Usage and Tips

For advanced users, the AMORDEGRC function can be combined with other Excel functions to create more complex financial models. For example, you can use the IF function to handle different scenarios or the SUM function to aggregate depreciation over multiple periods.

Conclusion

The AMORDEGRC function is a powerful tool for calculating depreciation in Excel, particularly useful for those following the French accounting system. By understanding the syntax and parameters, users can accurately track the depreciation of assets, ensuring precise financial reporting and budgeting.