How to Calculate Depreciation in Excel

Introducing Excel's Depreciation Functions

Depreciation is the way that accountants spread the purchase cost of an asset over its useful life. Assets are by definition expensive and long-lived so it makes sense to allocate the cost over many years. This matches the cost to the benefit provided to your business. While you can learn the formulas and definitions of each depreciation method, Excel makes it easy with the SLN, DB, DDB, SYD and VDB functions.

‍

Straight Line Depreciation and Beyond

Straight Line Depreciation is the rock star of depreciation methods and will be the one you use 99% of the time. Luckily, it is also the most simple to calculate. But it is important to know the other depreciation methods and how to calculate them. They are Declining Balance, Double Declining Balance, Sum of Years Digits and Variable Declining Balance. To get started, download the example workbook using the button below and open the file up.

‍

Getting Started

To calculate depreciation, you will always need to know three things:

• Purchase Price: how much the asset costs
• Useful Life: how many years the asset will be in service. Usually your company will have guidelines that use the asset's category to determine useful life. For example, a server will have a shorter useful life than a building.
• Salvage Value: Also known as the scrap or residual value, this is how much the asset will be worth at the end of its useful life. This number can be $0.

‍

‍

Straight Line Depreciation with SLN

Straight line depreciation evenly divides the purchase price less any salvage value by the useful life in years. In Excel it looks like this:

• =SLN(2000,200,5) or =SLN(price, salvage,life)

‍

Declining Balance Depreciation with DB

Declining balance is an accelerated depreciation method that depreciates an asset faster in its early years. Its used for items that may become obsolete quickly, like computers. This is the formula to calculate declining balance depreciation:

‍

• (cost - total depreciation from prior periods) * (1 - ((salvage / cost) ^ (1 / life)), rounded to three decimal places)

In Excel it looks like this:

• =DB(2000,200,5,1) or =DB (cost, salvage, life, period, [month])

‍

‍

Double Declining Balance Depreciation with DDB

Double declining balance is another accelerated depreciation method. This is the formula used to calculate double declining balance depreciation:

‍

• Min( (cost - total depreciation from prior periods) * (factor/life), (cost - salvage - total depreciation from prior periods) )

In Excel it looks like this:

• =DDB(2000,200,5,1, 2) or =DDB (cost, salvage, life, period, [factor])

‍

Sum of Years Digits Depreciation with SYD

Sum of years digits is yet another accelerated depreciation method. It uses the current year and total years to calculate a percentage to use as the depreciation factor. This is the formula used to calculate sum of years digits depreciation:

• (( cost - salvage) * (life - per + 1) * 2 ) / (life)(life+1)

‍

In Excel it looks like this:

‍

• =SYD(2000,200,5,1) or =SYD (cost, salvage, life, period)

‍

Variable Declining Balance Depreciation with VDB

Variable Declining Balance is the best of both worlds - it starts out as double declining balance then switches to straight line depreciation. Omit the last no switch argument or set it to False to change to straight line depreciation.

In Excel it looks like this:

‍

• =VDB(2000,200,5,4, 5, FALSE) or =VDB (cost, salvage, life, start period, end period,[factor], [no switch])