XL2000: Calculating Depreciation with the Production Method

Microsoft Excel includes functions to calculate straight line (SLN), sum of the years digits (SYD), and double-declining balance (DDB) depreciation. 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. This article contains a custom function that calculates depreciation using this method.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs.
If you have limited programming experience, you may want to contact a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners - https://partner.microsoft.com/global/30000104 (https://partner.microsoft.com/global/30000104)

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice (http://support.microsoft.com/gp/advisoryservice)

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS (http://support.microsoft.com/default.aspx?scid=fh;en-us;cntactms)

Example

Suppose you purchase a truck for $25,000, and it has a residual value of $1500. The useful life of the truck is 250,000 miles. During a specific period of operation, you drive the truck 15,000 miles. To calculate the depreciation for that period, follow these steps:

1. Open a new workbook in Microsoft Excel. Insert a module sheet, and then type the following code:

   ' Defines the function and the variables.
   
   Function Prod_depr(usage as Double, cost as Double, _
      residual_value as Double, useful_life As Double) As Double
   
      ' Performs the mathematical computation.
      Prod_depr = usage * ((cost - residual_value) / useful_life)
   
   End Function
   					

2. In cell A1 of Sheet1, type the following formula:
   =Prod_depr(15000,25000,1500,250000)
   The function returns a value of 1410.

NOTE: The arguments for the function are based on the following definitions:

• Usage is the amount of usage, in units, that 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: It may be easier to type the formula directly into your worksheet if you do not intend to use the custom macro function frequently.