How Do I Calculate Fixed Asset Depreciation Using Excel?

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 functions for multiple depreciation methods, including the:

• Straight-line method (SLN function)
• Sum of the years’ digits method (SYD function)
• Declining balance method (DB function)
• Double-declining balance accelerated method (DDB function)
• Variable declining balance method (VDB function)
• Units of production method, although this method requires a non-branded Excel template

Key Takeaways

• Microsoft Excel has built-in functions for multiple depreciation methods for businesses’ fixed assets.
• The straight-line method (or straight-line basis) uses a built-in function, SLN, which takes the arguments: cost, salvage, and life.
• The sum of the years’ digits method uses the function SYD to calculate a fraction: (years left of useful life) ÷ (sum of useful life).
• Excel can also calculate the declining balance method (DB function), double-declining balance accelerated method (DDB function), variable declining balance method (VDB function), and units of production method (requiring a template outside the Excel brand).

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
• Double-declining balance accelerated method, using the DDB function
• Variable declining balance method, using the VDB function
• 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.

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.

What Is a Fixed Asset?

Fixed assets are items that a company uses over the long term to operate its business. They commonly appear on a company balance sheet as property, plant, and equipment (PP&E), help generate income, and are subject to depreciation to account for their loss in value over time.

Examples of fixed assets include buildings, computer equipment, furniture, land, machinery, software, and vehicles.

What Is Excel?

Excel is a spreadsheet program developed by Microsoft that allows users to analyze, calculate, format, and organize data in a spreadsheet, a grid-like format of rows and columns. By creating graphs and tables, Excel users can compute and visualize complex datasets.

The Bottom Line

Microsoft not only has built-in functions on Excel for multiple fixed asset depreciation methods, but also offers websites to learn more about fixed asset depreciation and those methods.

The Internal Revenue Service (IRS) also has multiple websites with information about depreciation.