8 ways to calculate depreciation in Excel


You can access the two accompanying videos
here and here and a workbook with examples of using the various depreciation methods.

Note that the content that follows is based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.

SECTION 1

4 depreciation functions and an example

All four depreciation functions covered in this section have three required arguments in common, as follows:

  • cost — original cost of the asset;
  • salvage — salvage value of the asset (ie, the book value of the asset after it is fully depreciated); and
  • life — useful life of the asset (ie, how long the asset is estimated to be used in operations).

Those three arguments are the only ones used by the SLN function, which calculates straightline depreciation.

The SYD function calculates the sumofyears’ digits depreciation and adds a fourth required argument, per. The syntax is =SYD(cost, salvage, life, per) with per defined as the period to calculate the depreciation. The unit used for the period must be the same as the unit used for the life; eg, years, months, etc.

The DB function is used for calculating fixed decliningbalance depreciation and contains five arguments: cost, salvage, life, periodand month. The first four arguments are required, and the last one is optional. Period is required and represents the period to calculate the depreciation. As with per in the SYD function, the unit used for the period must be the same as the unit used for the life; eg years, months, etc. The optional argument, monthrefers to the number of months in the first year. If it is left blank, Excel will assume there were 12 months in the first year.

The DDB function is used for calculating doubledecliningbalance depreciation (or some other factor of decliningbalance depreciation) and contains five arguments. The first four (cost, salvage, lifeand period) are required and the same as used in the DB function. The fifth argument, factoris optional and determines by what factor to multiply the rate of depreciation. If it is left blank, Excel will assume the factor is 2 — the straightline depreciation rate times two, which is doubledecliningbalance depreciation.

Depreciation example with first four functions

Let’s go through an example using the four methods of depreciation described so far. Assume that our company has an asset with an initial cost of $50,000, a salvage value of $10,000, and a useful life of five years and 3,000 units, as shown in the screenshot below. Our job is to create a depreciation schedule for the asset using all four types of depreciation.

Leave a Comment

Businesswebsiteindex