In accounting world depreciation is one key calculation to be done for every period to make the reports and is also an important part of financial management decision making process in some cases. However, depreciation calculations can sometimes be tedious and long if we have to do it for multiple classes of assets for multiple years. It gets even more complex if entity has different depreciation policies for different assets under different circumstances.
In this tutorial:
However, excel can lend us big help in making depreciation calculation super fast, easy and accurate as well. In Excel you can not only make your own formula to calculate depreciation but in recent versions Excel has dedicated depreciation functions to calculate depreciation expense. Combining amortization and depreciation methods in Excel 2010 version we have 7 dedicated formula/functions for this purpose under the category financial.
Following is the list of depreciation functions in Excel:
- AMORDEGRC – Short for AMORtissement DEGRessif Comptabilite. This is a french name for French reducing balance amortization/depreciation method. It is a french method and due to the same reason excel has used french base to name this function.
- AMORLINC – Short for AMORtissement LINeaire Comptabilite. This is a french name for French straight line amortization/depreciation method. It is a french method and due to the same reason excel has used french base to name this function. It is slightly different from regular straight-line depreciation method.
- DB – Short for Declining Balance method of depreciation. Also known as reducing balance method.
- DDB – Short Double Declining Balance method of depreciation. A variant of simple declining balance method with a difference that depreciation coefficient is usually 2 but it can be any other number.
- SLN – Short for Straight-Line method of depreciation
- SYD – Short for Sum of Years Digits method of depreciation
- VDB – Variable Declining Balance method of depreciation. Also known as declining balance to straight line crossover method.
Calculating depreciation in Excel – Things you need
For all of the seven methods listed above you need following three pieces of information. Although each formula has its own information requirement which we will discuss with each formula but the following three are common in all the excel depreciation formula:
- Cost of the asset; is the cost at which asset is purchased and made available for intended use. Remember in accounting cost of asset is not only the cost you paid at the time of purchase.
- Salvage or scrap or residual value of asset; is the value which is expected that asset will bring when it is ultimately scrapped at the end of its useful life. Remember it is an estimate at the time of purchase.
- Life or useful life of asset; the time period for which asset is expected to stay in service.
Download the Practice Workbook: Join *FREE* Excel Maven Program and get access to tutorial files for this post.
File name: 0034 Calculating Depreciation in Excel.xlsx
AMORDEGRC – French reducing balance method of depreciation/amortization
Syntax:
=AMORDEGRC(cost,date_purchased,first_period,salvage,period,rate,[basis])
cost: cost of the asset
date_purchased: date at which asset was purchased
first_period: date at which first accounting period close
salvage: residual value of asset at the end of its useful life
period: accounting period for which depreciation is to be calculated
rate: rate of depreciation
basis (optional): basis of year or date system to be used for the purpose of calculation.
Case of hidden application of Depreciation coefficient
Under this method depreciation coefficient is bonded with useful life of asset and the same is applied when this formula is used. Depreciation coefficient is simply an integer by which depreciation is accelerated. User has no control over selection of coefficient in this method as Excel automatically selects it considering the useful life of asset. Summary of this is as follows:
| Useful life | Depreciation Coefficient |
| Less than 3 years | 1 |
| Greater than 3 but less than 5 years | 1.5 |
| Greater than 5 but less than 6 years | 2 |
| Greater than 6 years | 2.5 |
Basis – Basis of year / Date System / Basis of calender
Basis or basis of year is simply the type of calender used by entity. There are many calender basis in use around the world but the ones Excel supports five of them and they are identified by their respective identifier which is optional to be mentioned in calculation while applying this formula. Summary of identifier and their respective calenders are as follows:
| Basis | Calender |
| 0 or omitted | US 30/360 |
| 1 | Actual number of days |
| 2 | Actual number of days within a month but 360 days year |
| 3 | Actual number of days within a month but 465 days year |
| 4 | European 30/360 days |
Example
Step 1: Open tab named AMORDEGRC and observe the data. It has the cost of the asset, date purchase of asset and the date same year ends for accounting purposes. Residual value of asset and the period for which you want to calculate depreciation and rate of depreciation. Basis are optional and you may keep it empty or put in identifier number.
Step 2: In cell B15 put in the following formula: =AMORDEGRC(B7,B8,B9,B10,B11,B12,B13) and hit enter. This will give you the depreciation expense for the data provided.
Following is the live worksheet where you can make changes and see results in real time.
AMORLINC – French straight-line method of depreciation/amortization
Sytanx:
=AMORLINC(cost,date_purchased,first_period,salvage,period,rate,[basis])
cost: cost of the asset
date_purchased: date at which asset was purchased
first_period: date at which first accounting period close
salvage: residual value of asset at the end of its useful life
period: accounting period for which depreciation is to be calculated
rate: rate of depreciation
basis (optional): basis of year or date system to be used for the purpose of calculation.
Basis – Basis of year / Date System / Basis of calender
Basis or basis of year is simply the type of calender used by entity. There are many calender basis in use around the world but the ones Excel supports five of them and they are identified by their respective identifier which is optional to be mentioned in calculation while applying this formula. Summary of identifier and their respective calenders are as follows:
| Basis | Calender |
| 0 or omitted | US 30/360 |
| 1 | Actual number of days |
| 2 | Actual number of days within a month but 360 days year |
| 3 | Actual number of days within a month but 465 days year |
| 4 | European 30/360 days |
Example
Step 1: Open the tab named AMORLINC and observe the data provided
Step 2: In cell B15 put in the following formula: =AMORLINC(B7,B8,B9,B10,B11,B12,B13) and hit enter button. This will give you depreciation expense for the data provided.
To practice this method use the following live excel snippet:
Difference between AMORLINC and AMORDEGRC
Both of these methods are almost the same even the syntax of formula is same. However, in AMORDEGRC a hidden calculation on depreciation is done automatically which is basically the depreciation coefficient. Such coefficient is not applied in AMORLINC calculation.
DB – Declining Balance method of depreciation
Syntax:
=DB(cost,salvage,life,period,[month])
Cost: cost of the asset
salvage: residual value of asset at the end of its useful life
life: useful life of asset
period: accounting period for which depreciation is to be calculated
month (optional): month of the year in which asset was purchased. It is optional. If mentioned the calculation will be done considering the month of purchase on prorata basis. If not mentioned Excel assumes the asset was purchased at the beginning of the period.
Example:
Step 1: Open tab named DB and observe the data provided.
Step 2: In cell B13 put in the following formula: =DB(B7,B8,B9,B10,B11) and hit enter and it will provide the depreciation expense.
Following live excel preview provides you great way to practice this method for real:
DDB – Double Declining Balance method of depreciation
Syntax:
=DDB(cost,salvage,life,period,[factor])
cost: cost of the asset
salvage: residual value of asset at the end of its useful life
life: useful life of asset
period: accounting period for which depreciation is to be calculated
factor (optional): depreciation coefficient by which you want depreciation to accelerate. It can be any number. If omitted Excel assumes the factor/coefficient of 2.
Example
Step 1: Open the tab named DDB and observe the data provided.
Step 2: In cell B13 put this formula: =DDB(B7,B8,B9,B10,B11) and hit enter. It will be give you depreciation expense with double declining balance method on the basis of data provided.
Following is a live worksheet where you can make changes and see the resulting effect
SLN – Straight-line method of depreciation
Syntax:
=SLN(cost,salvage,life)
cost: cost of the asset
salvage: residual value of asset at the end of its useful life
life: useful life of asset
Example
The heart favourite depreciation method among accountants for numerous types of assets. To calculate depreciation using straight-line method follow these steps:
Step 1: Open tab named SLN and observe the minimal data. No wonder straight-line method is simplest in Excel as well.
Step 2: In cell B11 enter the following formula: =SLN(B7,B8,B9). Hit enter and depreciation expense is calculated in the blink of an eye.
You can practice these steps in the following live excel worksheet
SYD – Sum of Years Digits method
Syntax:
=SYD(cost,salvage,life,per)
cost: cost of the asset
salvage: residual value of asset at the end of its useful life
life: useful life of asset
per: accounting period for which depreciation is to be calculated
Example
Another widely known depreciation model. Following steps help you carryout depreciation using this method:
Step 1: Make sure SYD tab is active. Observe the data.
Step 2: In cell B12 enter this formula: =SYD(B7,B8,B9,B10). On hitting enter depreciation expense will be calculated for you.
You can practice the use of this method in the following live worksheet as well:
VDB – Variable Declining Balance method of depreciation
Syntax:
=VDB(cost,salvage,life,start_period,end_period,[factor],[no_switch])
cost: cost of the asset
salvage: residual value of asset at the end of its useful life
life: useful life of asset
start_period: period from which you want to commence depreciation calculation
end_period: period up to which you want to calculate depreciation expense
factor (optional): depreciation coefficient. If not mentioned then excel assumes the factor of 2 i.e. double declining rate.
no_switch (optional): whether you want method to switch from declining balance method to straight line if depreciation according to straight-line method is greater than depreciation under declining balance method. Values are either TRUE or FALSE. If TRUE the method will not switch and if FALSE or omitted then method will switch to straight-line.
Example
One of the lesser known depreciation methods. The readers who are not aware how this method works should read this article. To carry out depreciation on the basis of this method following steps are helpful:
Step 1: Click on tab named VDB and make this worksheet active. Observe the data
Step 2: In cell B15 enter this formula: =VDB(B7,B8,B9,B10,B11,B12,B13) and hit enter. Depreciation is the resultant
You can practice the application of this depreciation method in Excel with the following live worksheet
