Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Calculate Accumulated Depreciation in Excel (9 Easy Ways)

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.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


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.


9 Suitable Methods to Calculate Accumulated Depreciation in Excel

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.

How to calculate accumulated depreciation in Excel


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.

Use simple depreciation formula to calculate accumulated depreciation in Excel

  • 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.

Use simple depreciation formula to calculate accumulated depreciation in Excel

Read More: How to Create Fixed Asset Depreciation Calculator in Excel


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.

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.

Use units of products formula to calculate accumulated depreciation in Excel

  • Now on your keyboard, hit Enter. Depreciation of the 1st period will be shown in cell D5.

Use units of products formula to calculate accumulated depreciation in Excel

  • After that, drag the bottom right corner of cell D5 to cell D11.

  • Thus you will get the results for all 7 years.

Use units of products formula to calculate accumulated depreciation in Excel

  • 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.

Use units of products formula to calculate accumulated depreciation in Excel

  • The SUM function will add up all the depreciation of 7 years and give the accumulated depreciation.

Use units of products formula to calculate accumulated depreciation in Excel

Read More: Units of Production Depreciation Method with Formula in Excel


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.

Use SYD function to calculate accumulated depreciation in Excel

  • Then, hit the Enter button to see the depreciation in cell D5.

Use SYD function to calculate accumulated depreciation in Excel

  • 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.

Use SYD function to calculate accumulated depreciation in Excel

  • Now if you press Enter, you will get the accumulated depreciation in cell D13.

Use SYD function to calculate accumulated depreciation in Excel

Read More: How to Create Rental Property Depreciation Calculator 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 learn.

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.

Apply SLN Function to calculate accumulated depreciation in Excel

  • To get the result in cell D5, press Enter.

Apply SLN Function to calculate accumulated depreciation in Excel

  • 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.

Apply SLN Function to calculate accumulated depreciation in Excel

  • Hit Enter and see the desired accumulated depreciation value.

Apply SLN Function to calculate accumulated depreciation in Excel

Read More: How to Calculate Straight Line Depreciation Using 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.

Use VDB function to calculate accumulated depreciation in Excel

  • 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.

Use VDB function to calculate accumulated depreciation in Excel

  • Finally, press Enter and get the accumulated depreciation.

Use VDB function to calculate accumulated depreciation in Excel

Read More: How to Create Monthly Depreciation Schedule in Excel


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.

Use DB function to calculate accumulated depreciation in Excel

  • 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)

Use DB function to calculate accumulated depreciation in Excel

  • Press Enter on the keyboard. It will sum up all the depreciation and give the accumulated depreciation.

Use DB function to calculate accumulated depreciation in Excel

Read More: How to Use WDV Method of Depreciation Formula in Excel


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.

Use DDB function to calculate accumulated depreciation in Excel

  • Hit the Enter button on your keyboard.
  • It will give you the result of the 1st period.

Use DDB function to calculate accumulated depreciation in Excel

  • 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)

Use DDB function to calculate accumulated depreciation in Excel

  • It will add up all the values from D5 to D11 and give the desired result.

Use DDB function to calculate accumulated depreciation in Excel

Read More: Calculate Sum of Years Digits Depreciation with Formula in Excel


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.

Insert AMORLINC Function to calculate accumulated depreciation in Excel

  • 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)

Insert AMORLINC Function to calculate accumulated depreciation in Excel

  • Finally, hit Enter and see the desired accumulated depreciation.

Insert AMORLINC Function to calculate accumulated depreciation in Excel

Read More: How to Use Formula to Calculate Car Depreciation 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)

Use AMORDEGRC Function to calculate accumulated depreciation in Excel

  • To get the result for the 1st year, hit the Enter button.

Use AMORDEGRC Function to calculate accumulated depreciation in Excel

  • 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.

Use AMORDEGRC Function to calculate accumulated depreciation in Excel

Read More: How to Create Vehicle Depreciation Calculator in Excel


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.

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

Alif Bin Hussain

Alif Bin Hussain

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Civil Engineering from Bangladesh University of Engineering & Technology (BUET). I am a fresh graduate with a great interest in research and development. I do my best to acquire new information and try to find out the most efficient solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo