Excel formula for units of production depreciation

Excel formula for units of production depreciation

In production, depreciation occurs whenever an asset losses its value due to heavy use.

When considering the units of production, depreciation is not calculated based on the duration an asset is used, but rather, it is calculated based on the number of units produced. For instance, if an asset K produces 50 units and the same asset P produces 30 units, then K will depreciate more than P because it produced more units.

Units of production depreciation have in-depth applications in the manufacturing sector. The rate of depreciation increases when the Asset is used heavily.

This method is mostly applicable in plants and machinery Assets because plants and machinery experience wear and tear if they are used heavily.

There are several depreciation methods, and there are built-in functions in Excel that assist in the calculation of depreciation.

The excel formulas for each depreciation method are given below:

 

1. Straight Line Depreciation (SLN)

This is the basic form of depreciation where the value of the asset depreciates by a fixed amount for a given period of the Asset’s lifetime.

Excel SLN formula is used in calculating the rate of asset depreciation for a single period.

Syntax of the formula is: SLN( cost, salvage, life )

For the Arguments:

>> Cost is the initial amount spent in buying the Asset

>> Salvage is the valuation of the Asset after calculation of depreciation

>> Life is the number of periods of depreciation of the Asset

Example::

1. If the cost of your machinery (Asset) is $1,000 and the residual value after 5 years is $100, then the SLN depreciation of the Asset for the year can be calculated using the formula:

=SLN( 1000, 100, 5 )

The result of this operation is 180.00.

2. Check the image below to see how the SLN excel formula is used in the calculation of units of production depreciation for a year or month. Different values for cost, salvage, and lifetime are used for each calculation.

 

 

 

 

2. Declining Balance Depreciation (DB)

The Excel DB function is used in calculating the rate at which an asset depreciates for each period in its lifetime.

The Excel formula for DB is:

DB( cost, salvage, life, period, [month] )

For the Arguments:

>> Cost is the initial amount spent in buying the Asset

>> Salvage is the valuation of the Asset after calculation of depreciation

>> Life is the number of periods of depreciation of the Asset

>> Period is the number of periods that are used in the calculation

>> [month] is used optionally, but it is an integer that represents the number of months involved in the first period of deprecation.

For instance, when calculating for the last depreciation period, the value for [month] is 12.

More so, the default value of [month] is 12 in cases where it is omitted.

Example:

1. Assuming we are calculating the one year DB for a plant (Asset) bought with $1000 and with a residual value of $100. The formula is stated below:

=DB (1000, 100, 5, 1)

The result of this operation is 369.00.

2. Check the images below to see how the DB excel formula is used in the calculation of units of production depreciation for a year. Different values for cost, salvage, lifetime, period, and month are used for each calculation.