Method 1 – Apply SLN Function to Calculate Depreciation
Steps:
- Go to C7 and write down the following formula
=SLN(C4,C5,C6)
- Press ENTER to get the output.
Method 2 – Use SYD Function to Calculate Depreciation
Steps:
- Go to C8 and write down the following formula
=SYD(C4,C5,C6,C7)
- Press ENTER to get the output.
Explanation:
- The amount of depreciation for the 3rd year is $5,833.33. The amount of depreciation will change if you change the per argument.
- The accumulated depreciation after 8 years will be $(50,000-15,000) or $35,000.
- Using the formula, you will get the amount of depreciation from the 1st to 8th year. The values are $7,777.78 , $6,805.56 , $5,833.33 , $4,861.11, $3,888.89, $2,916.67, $1,944.44, $972.22 respectively. The sum of these values will be $35,000.
Method 3 – Apply DDB Function to Calculate Depreciation
Steps:
- Go to C8 and write down the following formula.
=DDB(C4,C5,C6,C7)
- Press ENTER to get the output.
Method 4 – Use the DB Function to Calculate Depreciation
Steps:
- Go to C8 and write the following formula
=DB(C4,C5,C6,C7,C8)
- Press ENTER to get the output.
Note:
The month argument indicates the number of months in the first year. In this case, it is 10. Excel will consider 10 months for the first year while calculating depreciation.
Method 5 – Apply VDB Function to Calculate Depreciation
Steps:
- Go to C10 and write down the formula
=VDB(C4,C5,C6,C7,C8,C9,TRUE)
- Then, press ENTER to get the output.
Explanation:
- Since we are calculating the depreciation from the 48th to the 52nd month, the useful life is converted to the month.
- The Factor defines that the rate at which the balance declines is 5.
- The No_switch is used to determine the nature of the depreciation method. If the value is TRUE, Excel will not switch to a straight-line depreciation method.
Things to Remember
- Assets do not depreciate linearly.
- If not mentioned, Excel will consider Factor 2.
Download Practice Workbook
Download this workbook and practice while going through the article.
Depreciation Formula In Excel: Knowledge Hub
- How to Calculate Straight Line Depreciation Using Formula in Excel
- How to Use WDV Method of Depreciation Formula in Excel
- How to Apply Declining Balance Depreciation Formula in Excel
- Calculate Sum of Years Digits Depreciation with Formula in Excel
- Units of Production Depreciation Method with Formula in Excel
- How to Use MACRS Depreciation Formula in Excel
- How to Calculate Accumulated Depreciation in Excel
- How to Use Formula to Calculate Car Depreciation in Excel
- How to Calculate Double Declining Depreciation in Excel
<< Go Back to Excel Formulas for Finance|Excel for Finance|Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
