February 26, 2014

Calculate Depreciation of Asset using Straight Line Method in Excel

How to calculate the depreciation of an asset using the straight line method
Since the last two weeks many students have been talking about depreciation calculations in general and how we can use Microsoft Excel to do the calculations quickly.
First of all, what is depreciation? Depreciation is the decline in value of an asset like a computer, furniture, car, etc. in a business in a financial period. The decline or depreciation happens mainly due to wear and tear. The income tax department recognizes this expense and allows the business to deduct a certain amount every financial year in which the asset is used. The amount of deduction allowed varies from country to country and is well defined for most assets. Land is generally not included.
Where in our account books do we make an entry for depreciation? In the expense account. The asset value decreases by the same amount in our asset book entry to complete the double entry system. We calculate our gross income. To arrive at our net income we minus the different expenses like salaries, repairs and maintenance, rent, etc. to arrive at our ‘net income before depreciation and taxes’. Now we minus the depreciation expense to get our final net income on which we pay our tax.

Let’s take an example. A small business runs a computer training center. It needs furniture, computers, etc – assets that it will use over a period of time, say, 3 to 5 years before it replaces them with newer products. This also means that the assets are depreciating over this lifetime. Finally they may sell the stuff and buy new ones. The money that they receive on selling the depreciated assets is known as salvage value or scrap value. To calculate the depreciation now you need the:

  1. Purchase price
  2. lifetime of the assets
  3. Salvage value

The formula for calculating the depreciation using the straight line method is:
annual depreciation expense=(cost of fixed asset – salvage value)/lifetime of asset
Book value = original cost – accumulated depreciation
Book value at the end of a financial year becomes book value at the beginning of next year (opening balance). The asset is depreciated when the book value equals scrap or salvage value.
Let’s have a look at the training video

Further reading:
Fixed asset record with straight line depreciation