How to Calculate Depreciation Using SLN, SYD and DB Functions in Excel

In this lesson, we will learn how to calculate depreciation in Microsoft Excel:

In Microsoft Excel, we have numerous functions which will help you for calculating depreciation. Functions with methods as mentioned below:

1. SLN (Used for Straight Line Method).
2. SYD (Used for Sum of Year Digit Method).
3. DB (Used for Declining Balance Method).

For Example: ESS company purchases a machine for $200,000 with an estimated salvage value of $20,000 and a useful life of 5 years. Calculate depreciation using all methods.

Function # 1: SLN (Used for Straight Line Method).

In Microsoft Excel, you can easily calculate depreciation of asset by using straight line method.

According straight line method; how much the amount or value of an asset reduced during a specific period of time. 

Formula of SLN Function:

=SLN(cost, salvage, life)

By following below mentioned steps, you can easily calculate depreciation by using SLN function:

Step 1: First Create a Depreciation computation sheet as mentioned below:

Step 2: Now Insert the SLN formula in Column "G3" as highlighted in below snapshot:

Step 3: Now next step is to Subtract the year-1 depreciation value from cost of machine to obtain current Asset value as mentioned below:

Step 4: Now apply both formulas into all empty cells as mentioned below:

Note: to calculate 2nd year Asset value, you will use this formula (Asset value - Depreciation value).

Now you can see that straight line method working properly by using this SLN function as mentioned below:

Function # 2: SYD (Sum of Year Digit Method).

In Microsoft Excel, you can easily calculate depreciation of asset by using sum of year digit method. 

Sum of year digit method, adds useful life of each year together. e.g. A life of 5 years has a sum of 1+2+3+4+5 =  15.

year 5 is 33.33% of 15, year 4 is 26.66% of 15 etc.

on the basis of these percentages, you can allocate the total depreciation of an asset.

Formula of SYD Function:

=SYD(cost, salvage, life, period)

By following below mentioned steps, you can easily calculate depreciation by using SYD function:

Step 1: First Create the Depreciation computation sheet as mentioned below:

Step 2: Now Insert the SYD formula in Column "G3" as highlighted in below snapshot:

Step 3: Now next step is to subtract the Year-1 depreciation value from cost of machine to get the current Asset value as mentioned below:

Step 4: Now Apply both formulas to all empty cells as mentioned below:

Note: To calculate second year asset value, you will use this formula (Asset value - Depreciation value)

Now you can see that sum of year digit method working properly by using this SYD function as mentioned in below snapshot:

Function # 3: DB (Used for Declining Balance Method).

In Microsoft Excel, you can easily calculate depreciation by using declining balance method.

Declining balance method in Excel refer to as DB function which calculates depreciation on fixed percentage basis.

The most interesting feature of this function is the ability to calculate depreciation from when the item was originally purchased.

If the item was purchased in middle of the financial year, then this function calculates depreciation on pro-rata basis  or the first year depreciation will be based on the remaining part of the year.

Formula of DB Function:

=DB(cost, salvage, life, period, first year month)

Note: First year month is an optional value, you will not use this, so the function will automatically assume 12 as the value. 

By following below mentioned steps, you can easily calculate depreciation by using DB function:

Step 1: First Create the Depreciation computation sheet as mentioned below:

Step 2: Now Insert the DB formula in column "G3" as highlighted in below snapshot:

Step 3: Now next step is to subtract the Year-1 depreciation value from cost of machine to obtain the current Asset value as mentioned below:

Step 4: Now apply both formulas to all all empty cells as mentioned below:

Note: To calculate second year value asset value, you will use this formula (Asset value - Depreciation value).

Now you can see that, declining balance method working properly by using Excel DB function as mentioned below:

We hope that you like this lesson.

---Thank You---