Microsoft Excel is a very powerful software that is used by people from all around the world. It is a very handy tool that can easily do complicated calculations using different functions. In this article, we will use various functions to show you 9 easy ways how to calculate accumulated depreciation in Excel.
Introduction to Accumulated Depreciation
Accumulated depreciation is the decline in the value of an asset over time until the end of its useful life. For instance, if a company buys an asset for $50,000 that has a useful life of 5 years and a $20,000 salvage value, the asset will lose ($50,000-$20,000)/5= $6,000 value each year until the end of its useful life.
How to Calculate Accumulated Depreciation in Excel: 9 Easy Ways
In this article, we will learn how to calculate accumulated depreciation in Excel in 9 easy ways. We will use the following dataset for this purpose. In the dataset, the cost of the product, salvage value, estimated life, purchase date, depreciation rate, etc are provided. We will calculate the accumulated depreciation using these data.
1. Apply Simple Depreciation Formula
We can easily calculate the accumulated depreciation by using a simple depreciation mathematical formula. The steps to do so are given below:
Steps:
- First of all, click on the left button of the mouse to select empty cell D5.
- Then write down the following formula in the formula bar.
=($D$15-$D$16)*B5/$D$17- Here, $D$15 refers to the value of cell D15, $D$16 refers to the value of cell D16 and $D$17 is an absolute reference to cell D17.
- Next, Press the Enter button and you will see the cumulative depreciation of the period of year 1.
- To get the cumulative depreciation value for periods 2 to 7 years, double-click the bottom right corner of cell D5.
- Cell D11 and D13 will show the cumulative depreciation of the 7-year period which is also the accumulated depreciation.
2. Use the Units of Production Formula
In this method, you will learn another mathematical formula (Units of Production Formula) to determine the accumulated depreciation. Let’s follow the instructions below to calculate the units of production depreciation with formula in Excel!
Steps:
- First, bring your cursor to cell D5 and click on it.
- Next, type the following formula in that cell.
=($D$15-$D$16)*C5/$D$18- In this formula, $D$15, $D$16, $D$18 are absolute references to cells D15, D16, and D18 respectively and C5 refers to the value of cell C5.
- Now on your keyboard, hit Enter. Depreciation of the 1st period will be shown in cell D5.
- After that, drag the bottom right corner of cell D5 to cell D11.
- Thus you will get the results for all 7 years.
- Now to find the value of accumulated depreciation, go to cell D13 and write down the following formula.
=SUM(D5:D11)- Here, D5:D11 refers to all cell values from D5 to D11.
- The SUM function will add up all the depreciation of 7 years and give the accumulated depreciation.
3. Insert SYD Function
We can do the accumulated depreciation calculation using functions too. In this method, we will show you how to use the SYD function to calculate accumulated depreciation in Excel.
Steps:
- To begin with, select cell D5 and write down the formula given below.
=SYD($D$15,$D$16,$D$17,B5)- In this formula, $D$15, $D$16, $D$17 are absolute references to cells D15, D16 and D18 respectively and B5 refers to cell B5.
- Then, hit the Enter button to see the depreciation in cell D5.
- After that, double-click on the bottom right corner of cell D5 to get the depreciation value for all data.
- Now we will see how to determine the accumulated depreciation from all the depreciation values. To do that, type the sum formula in cell D13.
=SUM(D5:D11)- Here, D5:D11 refers to cells D5 through D11.
- Now if you press Enter, you will get the accumulated depreciation in cell D13.
Read More: Calculate Sum of Years Digits Depreciation with Formula in Excel
4. Apply Straight-Line Method
In this procedure, you will see how to use the SLN function to calculate accumulated depreciation. Follow the steps below to calculate straight-line depreciation with formula in Excel.
Steps:
- First, use your mouse to select D5.
- Now you need to type the SLN formula in that cell.
=SLN($D$15,$D$16,$D$17)- In this formula, $D$15 refers to the value of cell D15, $D$16 refers to the value of cell D16 and $D$17 is an absolute reference to cell D17.
- To get the result in cell D5, press Enter.
- Then bring your cursor to the bottom right corner of cell D5 and click the left button of the mouse two times.
- This will give you depreciation value for periods 2 through 7.
- Now we will use the SUM function to determine the accumulated depreciation. To do that, use the following formula, where D5:D11 refers to the depreciation of cells D5 to D11.
- Hit Enter and see the desired accumulated depreciation value.
Read More: How to Use SLM Method of Depreciation Formula in Excel
5. Utilize VDB Function
In this part of the article, we will learn the use of the VDB function. We can determine the accumulated depreciation by using this function. Keep on reading to see how to do it.
Steps:
- In the beginning, go to cell D5 and click on it.
- Next, write down the following formula in the formula bar.
- Hit the Enter button on the keyboard to find out the depreciation of 1st year.
- Now double-click the bottom right corner of cell D5. This will give you depreciation for the whole period.
- After that, use the following sum formula to calculate accumulated depreciation.
=SUM(D5:D11)- Here, D5:D11 refers to all the values of D5 to D11.
- Finally, press Enter and get the accumulated depreciation.
6. Use DB Function
In this method, you will learn the use of the DB function to calculate accumulated depreciation.
Steps:
- To begin with, you need to select cell D5 and type the below formula.
=DB($D$15,$D$16,$D$17,B5)- In this formula, $D$15, $D$16, $D$17 are absolute references to cells D15, D16 and D17 respectively and B5 refers to the value of cell B5.
- Next, hit Enter and get the result in cell D5.
- Move your cursor to the bottom right corner of cell D5 and double-click on it.
- This will show the results for all 7 years.
- Use the following formula to get the accumulated depreciation.
=SUM(D5:D11)
- Press Enter on the keyboard. It will sum up all the depreciation and give the accumulated depreciation.
7. Apply DDB Function
Now we will demonstrate the calculation of accumulated depreciation using the DDB function. Read the following steps to learn how to do it.
Steps:
- Click on cell D5 and write down the following formula.
=DDB($D$15,$D$16,$D$17,B5)- Here, $D$15, $D$16, $D$17 are absolute references to cells D15, D16 and D17 respectively and B5 refers to the value of cell CB5.
- Hit the Enter button on your keyboard.
- It will give you the result of the 1st period.
- To find out the depreciation of the full 7 years period, double-click the bottom right corner of cell D5.
- Now you can write the following sum formula to get the accumulated depreciation.
=SUM(D5:D11)
- It will add up all the values from D5 to D11 and give the desired result.
8. Insert AMORLINC Function
We can also do the above calculation by the french declining balance method. For this purpose, we will use the AMORLINC function.
Steps:
- First of all, type the following formula in cell D5.
=AMORLINC($D$16,$D$20,$D$21,$D$17,B5,$D$22)- In this formula, $D$16,$D$20,$D$21,$D$17 refers to cells D16, D20, D21 and D17 B5 refers to the value of cell B5 and $D$22 is absolute reference to cell D22.
- Now press the Enter button and find the result in cell D5.
- Next, double-click the bottom right corner of cell D5 and you will get results for the whole period.
- Now to find out the accumulated depreciation, use the sum formula, where D5:D12 refers to cells D5 through D12.
=SUM(D5:D12)
- Finally, hit Enter and see the desired accumulated depreciation.
Read More: How to Apply Declining Balance Depreciation Formula in Excel
9. Use AMORDEGRC Function
In the last method, you will see the use of the AMORDEGRC function to determine accumulated depreciation. Follow the steps below to learn.
Steps:
- First, select empty cell D5.
- Next, write down the AMORDEGRC formula.
=AMORDEGRC($D$16,$D$20,$D$21,$D$17,B5,$D$22)
- To get the result for the 1st year, hit the Enter button.
- After that, you need to double-click the bottom right corner of cell D5.
- This gives you all the depreciation value.
- Now it is time to find the accumulated depreciation. You can use the SUM function for this purpose, where D5:D12 refers to cells D5 to D12.
=SUM(D5:D12)
- Finally, press the Enter button and get the accumulated depreciation.
Things to Remember
- While using a formula, don’t forget to give proper cell references or you won’t get the desired results.
- Use the absolute reference sign ($) for the formulas otherwise, you can get erroneous results.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
Concluding Remarks
Thanks for making it this far. I hope you find this article useful. Now you know 9 different methods to calculate accumulated depreciation in Excel. Please let us know if you have any further queries and feel free to give us any recommendations in the comment section below.
Related Articles
- How to Use WDV Method of Depreciation Formula in Excel
- How to Use MACRS Depreciation Formula in Excel
- How to Use Formula to Calculate Car Depreciation in Excel
- How to Calculate Double Declining Depreciation in Excel
<< Go Back to Depreciation Formula In Excel|Excel Formulas for Finance|Excel for Finance|Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
