How Do I Calculate Fixed Asset Depreciation Using Excel?

What Is Depreciation?

Depreciation is a common accounting method that allocates the cost of a company's fixed assets over the assets' useful life. In other words, it allows a portion of a company's cost of fixed assets to be spread out over the periods in which the fixed assets helped generate revenue.

Businesses depreciate long-term assets for both tax and accounting purposes. For tax purposes, businesses can deduct the cost of the tangible assets they purchase as business expenses. Microsoft Excel has built-in depreciation functions for multiple depreciation methods including the straight-line method, the sum of the years' digits method, the declining balance method (the DB function), the double-declining balance accelerated method (the DDB function), the variable declining balance method (VDB function), and the units of production method, although this method requires a non-branded Excel template.

Understanding Fixed Asset Depreciation

Suppose company XYZ bought a production machine with a useful life of five years for $5,000 and the salvage value is $500. To calculate the depreciation value, Excel has built-in functions. The first step is to enter the numbers and their corresponding headings in the appropriate cells.

• Type "cost" into cell A1 and "$5,000" into B1.
• Next, type "salvage value" into cell A2 and "$500" into cell B2.
• Type "useful life" into cell A3 and "5" into cell B3.
• Type "period" into cell A5 and enter the number of periods one through five into cells A6 through A10.
• In cell B5, type "straight-line method."

Straight-Line Basis

To calculate the depreciation value using the straight-line basis, or straight-line method (SLN), Excel uses a built-in function, SLN, which takes the arguments: cost, salvage, and life.

In cell B6, type "=SLN(B1,B2,B3)," which gives a value of $900 for period one. Since this method spreads out the depreciation value over its useful life, you can see that the values from B6 through B10 are $900.

Sum of the Years' Digits (SYD)

To calculate the depreciation using the sum of the years' digits (SYD) method, Excel calculates a fraction by which the fixed asset should be depreciated, using: (years left of useful life) ÷ (sum of useful life).

In Excel, the function SYD depreciates an asset using this method. In cell C5, enter "sum of years date." Enter "=SYD(B1,B2,B3,6)" into cell C6. Calculate the other depreciation values using the sum of the years' digits method in Excel with this function.

Other Methods

Excel is capable of calculating any depreciation method, including:

• The declining balance method, using the DB function
• The double-declining balance accelerated method with the DDB function
• The variable declining balance method with the VDB function
• The units of production method

Most assets lose more value at the beginning of their useful life. The SYD, DB, DDB, and VDB functions apply this property. The DB function uses a fixed rate to calculate the depreciation values. In some cases, the salvage is difficult to reach using the DDB function. The VDB function performs the same calculations as the DDB function but switches to a straight line to reach the salvage value. For the straight-line method, the depreciation is the same each year.

Excel templates are available for most depreciation calculations.