Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/108271

From BetaArchive Wiki
Knowledge Base


Calculating Depreciation with the Production Method

Article ID: 108271

Article Last Modified on 10/11/2006



APPLIES TO

  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 98 for Macintosh



This article was previously published under Q108271

SUMMARY

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.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements.

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. The function below will calculate the depreciation for that period.

  1. Create a new workbook in Microsoft Excel. Insert a module sheet, and 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 will return a value of 1410.

NOTE: The arguments for the function were based on 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: It may be easier to type the formula

= usage * ((cost - residual_value) / useful_life


directly into your worksheet if you do not intend to use the custom macro function frequently.


Additional query words: 8.00 XL98 XL97 XL7 XL5 XL Production Depreciation

Keywords: kbhowto kbprogramming kbdtacode KB108271