What is amortization?
Depreciation is a common accounting technique that spreads the cost of a company’s fixed assets over the useful life of the assets. In other words, it spreads a portion of the cost of a company’s fixed assets over the periods when the fixed assets helped generate revenue.
Companies write off their long-lived assets for tax and accounting purposes. For tax purposes, companies can deduct the acquisition cost of property, plant and equipment as a business expense. Microsoft Excel has built-in depreciation functions for several depreciation methods, including the straight-line method, the sum of the digits of the years method, the declining balance method (DB function), the accelerated declining balance method (DDB function), the variable declining balance method (VDB function) and unit-of-production method, although this method requires an unbranded Excel template.
Understand the depreciation of fixed assets
Suppose Company XYZ buys a five-year manufacturing machine for $5,000 and the salvage value is $500. To calculate the depreciation value, Excel has built-in functions. The first step is to fill in the numbers and the corresponding headings in the appropriate cells.
- Enter “Cost” in cell A1 and “$5,000” in cell B1.
- Then enter “residual value” in cell A2 and “$500” in cell B2.
- Enter “Useful Life” in cell A3.
- Enter “period” in cell A5 and enter the number of periods from one through five in cells A6 through A10.
- In cell B5, type “linear method”.
base of the straight line
To calculate the value of depreciation using the straight-line method, or straight-line method (SLN), Excel uses a built-in function, SLN, that accepts the arguments: cost, recovery, and lifespan.
In cell B6, enter “=SLN and ($B$1,$B$2,$B$3)” which gives a value of $900 for the first period. Because this method spreads the value of depreciation over the useful life, you can see that the values from B6 to B10 are $900.
Sum of Year Digits (SYD)
To calculate depreciation using the Sum of the Years (SYD) method, Excel calculates a fraction by which the asset should be depreciated as: (remaining years of useful life) ÷ (sum of useful life).
In Excel, the SYD function allows an asset to be depreciated using this method. In cell C5, enter “Date of Sum of Years”. Enter “=SYD($B$1,$B$2,$B$3,A6)” in cell C6. Use this function to calculate other depreciation values using the annual sum method in Excel.
other methods
Excel can calculate any depreciation method including:
- The declining balance method using the DB function
- The accelerated method of double degression using the DDB function
- The variable degression method with the VDB function.
- Production process units.
Most assets lose more value early in their useful life. The SYD, DB, DDB, and VDB functions apply this property. The DB function uses a fixed rate to calculate depreciation values. In some cases, it is difficult to restore using the DDB function. The VDB function performs the same calculations as the DDB function, but arrives directly at the remainder. With the straight-line method, depreciation is the same every year.
Excel templates are available for most depreciation calculations.
https://www.investopedia.com/ask/answers/032515/how-do-i-calculate-fixed-asset-depreciation-using-excel.asp