Depreciation is a 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. Microsoft Excel has built-in depreciation functions for multiple depreciation methods.
For example, suppose company ZYX bought a production machine with a useful life of five years for $5,000, and the salvage value is $500. If you want to find the depreciation value, Excel has easy-to-use, built-in functions to calculate depreciation. But first, we have 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.
- 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."
If you want to calculate the depreciation value using the straight-line basis, or straight-line method; Excel has a built-in function, SLN, which takes the arguments: cost, salvage, and life.
In cell B6, type "=SLN and ($B$1,$B$2,$B$3)," which gives a value of $900 for period one. Since this method spreads out the depreciation value over its useful life, you can see the values from B6 through B10 are $900.
If you want to calculate the depreciation using the sum of the years' digits method, Excel has this function as well. This method 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 can be used to depreciate an asset with this method. In cell C5, enter "sum of years date." Enter "=SYD($B$1,$B$2,$B$3,A6)" into cell C6. Calculate the other depreciation values using the sum of the years' digits method in Excel with this function.