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

Units of Production Depreciation Method with Formula in Excel

There are many ways of calculating Depreciation. Units of Production is one of them. The main objective of this article is to explain how to calculate the Units of Production Depreciation method with formula in Excel.


Download Practice Workbook

Here, I have provided the practice workbook. You can download it from here.


Units of Production Depreciation Method

The Units of Production is a method of calculating the Depreciation of an asset as time passes. In this method, the value of an asset is mainly related to the number of units produced rather than the number of years. To formula for the Units of Production Depreciation method is:

Depreciation Expense = ((Asset Value – Salvage Value) / Estimated Useful Unit) * Units Per Year

Step-by-Step Procedures to Calculate Units of Production Depreciation Method with Formula in Excel

I have taken the following dataset to explain this article. Here, the Asset Cost, Salvage Value, and estimated Useful Units in miles are provided. This dataset also contains the units per year over 5 years. The example is for a truck and the units produced are considered as Miles Driven. I will use this dataset to show you the step-by-step procedures to calculate the Units of Production Depreciation method with formula in Excel.

to Calculate Units of Production Depreciation Method with Formula in Excel


Step-01: Calculate Depreciable Cost

In this first step, I will show you how you can calculate the Depreciable Cost in units of production method. Let’s see the steps.

  • Firstly, create a section for Depreciable Cost.

Calculate Depreciable Cost in Units of Production Depreciation Formula in Excel

  • Secondly, select the cell where you want the Depreciable Cost. Here, I selected Cell C9.
  • Thirdly, in Cell C9 write the following formula.
=C5-C6

  • After that, press Enter to get the result.

Here, the formula subtracts the value in cell C6 from the value in cell C5.

Read More: How to Create Vehicle Depreciation Calculator in Excel


Step-02: Evaluate Depreciation Per Unit

Now, I will calculate Depreciation Per Unit. Let me show you how you can do it.

  • In the beginning, create a section for Depreciation Per Unit.

Evaluate Depreciation Per Unit for Units of Production Depreciation Formula in Excel

  • Next, select the cell where you want the Depreciation Per Unit.
  • Then, write the following formula in that selected cell.
=C9/C7

  • After that, press Enter to get the result.

Now, the formula divides the value in Cell C9 by the value in Cell C7 and returns it as result.

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


Step-03: Find the Opening Book Value

Here, I will show you how to find out the Opening Book Value in units of production method. Let’s see the steps.

  • First, create a table containing the following columns.

Find Opening Book Value in Units of Production Depreciation Formula in Excel

  • Then, select the cell where you want to calculate the first Opening Book Value. Here, I selected Cell C14.
  • Next, in Cell C14 write the following formula.
=C5

  • Afterward, press Enter to get the result.

Here, the formula will show the Asset Value as the first Opening Book Value.
  • After that, select the cell where you want the second Opening Book Value.
  • Then, write the following formula in that selected cell.
=G14

  • Next, press Enter to the result.

Now, the formula returns the Closing Book Value of the first year as the Opening Book Value of the second year.
  • Further, drag the Fill Handle down to copy the formula.

  • Finally, you can see that I have copied the formula to the other cells. And, the values will show up when I complete the rest of the table.

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


Step-04: Determine Depreciation Expense

In this step, I will calculate the Depreciation Expenses. For this, you will need to multiply the Depreciation Per Unit by the Unit Produced in a year. Let’s see how you can do it for this example.

  • Firstly, select the cell where you want the Depreciation Expense. Here, I selected Cell E14.
  • Secondly, in Cell E14 I wrote the following formula.
=D14*$C$10

Determine Depreciation Expense in Units of Production Depreciation Formula in Excel

  • Thirdly, press Enter to get the Depreciation Expense.

Formula for Units of Produnction Depreciation Calculation in Excel

Here, I multiplied the value in cell D14 which is Miles Driven by the value in cell C10 which is the Depreciation Per Unit. I used Absolute Cell Reference for cell C10 because I want it to be fixed for every cell.
  • Afterward, drag the Fill Handle down to copy the formula.

  • Finally, you can see that I have copied the formula to all the other cells and got my desired result.

Read More: How to Calculate Straight Line Depreciation Using Formula in Excel


Step-05: Calculate Accumulated Depreciation

Now, I will show you how to calculate the Accumulate Depreciation. Let’s see the steps.

  • To begin with, select the cell where you want the first Accumulated Depreciation.
  • Then, write the following formula in that selected cell.
=E14

Calculate Accumulated Depreciation in Units of Production Depreciation Formula in Excel

  • After that, press Enter to get the result.

Here, the formula returns the Depreciation Expense for the first year.
  • Then, select the cell where you want the second Accumulated Depreciation. Here, I selected cell F15.
  • Afterward, in cell F15 write the following formula.
=E15+F14

  • Next, press Enter to get the result.

Now, the formula returns the summation of the Depreciation Expense of that year and Accumulated Depreciation from the previous year.
  • Then, drag the Fill Handle down to copy the formula.

  • Finally, you can see that I have copied the formula to the other cells and got all the Accumulated Depreciation.

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


Step-06: Find the Closing Book Value

Here, I will show you how you can find out the Closing Book Value in the Units of Production Depreciation method. Let me show you the steps.

  • Firstly, select the cell where you want the Closing Book Value.
  • Secondly, write the following formula in that selected cell.
=C14-E14

Find Closing Book Value in Units of Production Depreciation Formula in Excel

  • Thirdly, press Enter to get the result.

Here, the Depreciation Cost is subtracted from the Opening Book Value.
  • After that, drag the Fill Handle down to copy the formula.

  • Lastly, you can see that I have copied the formula to the other cells and got the Closing Book Value.

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


Step-07: Evaluate Total and Adjust It Accordingly

At this point, I will calculate the Total and then adjust it accordingly if an adjustment is needed. Let’s see the steps.

  • In the beginning, select the cell where you want the Total. Here, I selected Cell E19.
  • Then, in Cell E19 write the following formula.
=SUM(E14:E18)

Evaluate Total and Adjust It Accordingly in Units of Production Depreciation Formula in Excel

  • After that, press Enter and you will get the Total.

Here, in the SUM function, I selected cell range E14:E18 as numbers. The formula returns the summation of the cell range E14:E18.
  • You can see that the Total does not match the Depreciable Cost. So, you will need to adjust that.
  • To do that, select the last row of the table.
  • After that, right-click on the selected row.
  • Then, select Delete.

  • Consequently, the Delete dialog box will appear.
  • Select Shift cells up.
  • After that, select OK.

  • Now, you will see the last row is deleted. But, the Total is still greater than the Depreciable Cost.

  • To adjust that, select the last cell of Depreciation Expense. Here, I selected Cell E17.
  • After that, in Cell E17 write the following formula.
=G16-C6

  • Next, press Enter to get the result.

Here, the Salvage Value is subtracted from the Closing Book Value from the previous year to adjust the Depreciation Expense.
  • In the end, you can see that the Total now matches the Depreciable Cost.

Final Output of Units of Production Depreciation Formula in Excel


Final Output

In the following image, you can see the final output of the calculation of the Units of Production Depreciation method with a formula in Excel.

Final Output of Units of Production Depreciation Formula in Excel


Practice Section

Here, I have provided a practice sheet for you to practice how to calculate the Units of Production Depreciation method with formula in Excel.

Practice Sheet for Units of Production Depreciation Formula in Excel


Conclusion

To conclude, I tried to cover how to calculate the Units of Production Depreciation method with formula in Excel in this article with 7 easy steps. I hope this article was clear to you. For more articles, stay connected with ExcelDemy. Lastly, if you have any questions, feel free to let me know in the comment section below.


Related Articles

Mashhura Jahan

Mashhura Jahan

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo