Excel: Calculating Depreciation Using the Production Method

• Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
• Microsoft Excel for OS/2, versions 2.2, 3.0

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.
   

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