Excel: Calculating Depreciation Using the Production Method

ID: Q78388

The information in this article applies to:

SUMMARY

Microsoft Excel includes functions to calculate straight line, sum of the years digits, and double-declining balance depreciation (SLN, DDB, SYD). A fourth depreciation method, the production method, is widely used in business to calculate depreciation on items that can produce discrete units. Items such as vehicles and machinery are most often depreciated in this manner. The custom function macro shown below calculates depreciation using this method.


MORE INFORMATION

  1. Enter the following into a macro sheet: 

    
      A1: Prod_Depr
      A2: =Result(1)
      A3: =Argument("usage",1)
      A4: =Argument("cost",1)
      A5: =Argument("residual_value",1)
      A6: =Argument("useful_life",1)
      A7: =usage*((cost-residual_value)/useful_life)
      A8: =Return(A7)


  2. Select cell A1. From the Formula menu, choose Define Name. Select the Function option and choose the OK button.


  3. You can use the custom function from your worksheet by choosing Paste Function from the Formula menu. The custom function should be at the bottom of the list of functions. Select it and choose the OK button.


  4. Enter the arguments for the function per the following descriptions:
    - "usage" is the amount of usage, in units, the item has received during the depreciation period. This can be measured in miles or hours used.
    - "Cost" is the original price paid for the item.
    - "Residual_value" is the estimated salvage or trade-in value.
    - "useful_ life" is the planned usage, in units, of the item during its lifetime.


Note that it may be easier to enter the formula shown in cell A7 directly into your worksheet if you do not intend to use the function frequently.

Example

Suppose a truck is purchased for $25,000, and has a residual value of $1500. The useful life of the truck is 250,000 miles. During a specific period of operation, the truck is driven for 15,000 miles. Using the above function, the depreciation for that period is $1410.


REFERENCES

"Online Help," version 5.0
"User's Guide," version 3.0, page 567

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 4.0 4.0a 4.00a 5.0 


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 23, 1999