Calculating depreciation, which is a decrease in an assets value, for a given period is vital for any business. It helps give an accurate picture of total assets which allows a business to have a better idea of its future cash flow or revenue strength. Luckily you can easily calculate depreciation in Microsoft® Excel® using either the straight line or the double declining method. Today, we’ll focus on the straight line method.
Applies To: Microsoft Excel 2007, 2010 and 2013
Download the free asset register template
In the example below we nest the IF, AND and SLN functions to calculate depreciation for one period. The SLN function returns the straight line depreciation of an asset for one period. It has the following arguments:
- Cost: The initial cost of an asset
- Salvage: The value at the end of the depreciation.
- Life: The number of periods over which the asset is depreciated
1. Select cell O8.
2. Select Logical function in the Formulas tab as shown below.
3. Select the IF function.
4. Enter as below.
- The following conditions must be satisfied before depreciation is calculated.
-
- The assets were not sold in the current financial period (K8<= 0)
- The assets were nor purchased in the current financial period(H8<$B$4)
- The useful life must be greater than 0(J8<>0)
- Should all the conditions given above be satisfied then depreciation can be calculated.
- F8 represents the cost
- J8 is the life span of the assets
- Salvage the value at the end of depreciation is not given
- If one of the conditions is not met , then zero will be returned
5. Select OK and copy the formula down.
As you can see, depreciation can easily be calculated for one period using the SLN function. In our next tip we shall explain how to calculate depreciation using the double declining method.
