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.

### 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**.

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

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

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

**divides**the value in

**C**

**ell 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.

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

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

**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`

- Thirdly, press
**Enter**to get the**Depreciation Expense**.

**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`

- After that, press
**Enter**to get the result.

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

**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`

- Thirdly, press
**Enter**to get the result.

**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)`

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

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

**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

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.

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

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