Units of production depreciation is a common and useful parameter for manufacturing firms to calculate the value of an asset based upon usage. In this guide, we’re going to show you How to calculate units of production depreciation in Excel.
How to calculate units of production depreciation
Although Excel includes several depreciation functions, there isn't a built-in function to calculate units of production depreciation. Fortunately, the mathematical notation is simple:
where
cost: The cost of the asset. This may include purchase, installation, delivery charge or incidental expenses.
salvage: The value of the asset at the end of the useful life. In other words, salvage value.
estimated unit of production: The estimation of the number of units produced by the asset over its useful life.
actual units produced in period: The number of units produced by the asset in a specific period. The formula returns the depreciation value by the considered dialog.
In the example shown, there is an asset with an initial cost of $20,000 and a salvage value of $4,000. The total units can be produced by the asset is estimated as 400 units.
If the asset will produce 200 units in its first year (period), the units of production depreciation value will be the $8,000, ((20,000 - 4,000) / 400) * 200.
Replace the actual produced unit value to find the depreciation over other periods. For example, it will be $6,000 for the 2nd year, because of 150 units and so on.
