How to Calculate Depreciation in Excel: 5 Easy Methods

Method 1 – Apply SLN Function to Calculate Depreciation

Steps:

Go to C7 and write down the following formula

=SLN(C4,C5,C6)

SLN Function to calculate depreciation in excel

Press ENTER to get the output.

Method 2 – Use SYD Function to Calculate Depreciation

Steps:

Go to C8 and write down the following formula

=SYD(C4,C5,C6,C7)

SYD Function to calculate depreciation in excel

Press ENTER to get the output.

Explanation:

The amount of depreciation for the 3rd year is $5,833.33. The amount of depreciation will change if you change the per argument.
The accumulated depreciation after 8 years will be $(50,000-15,000) or $35,000.
Using the formula, you will get the amount of depreciation from the 1st to 8th year. The values are $7,777.78 , $6,805.56 , $5,833.33 , $4,861.11, $3,888.89, $2,916.67, $1,944.44, $972.22 respectively. The sum of these values will be $35,000.

Method 3 – Apply DDB Function to Calculate Depreciation

Steps:

Go to C8 and write down the following formula.

=DDB(C4,C5,C6,C7)

DDB Function to calculate depreciation in excel

Press ENTER to get the output.

Method 4 – Use the DB Function to Calculate Depreciation

Steps:

Go to C8 and write the following formula

=DB(C4,C5,C6,C7,C8)

DB Function to calculate depreciation in excel

Press ENTER to get the output.

Note:

The month argument indicates the number of months in the first year. In this case, it is 10. Excel will consider 10 months for the first year while calculating depreciation.

Method 5 – Apply VDB Function to Calculate Depreciation

Steps:

Go to C10 and write down the formula

=VDB(C4,C5,C6,C7,C8,C9,TRUE)

Then, press ENTER to get the output.

VDB Function to calculate depreciation in excel

Explanation:

Since we are calculating the depreciation from the 48th to the 52nd month, the useful life is converted to the month.
The Factor defines that the rate at which the balance declines is 5.
The No_switch is used to determine the nature of the depreciation method. If the value is TRUE, Excel will not switch to a straight-line depreciation method.