Syntax

DB(cost, salvage, number of periods, period, [month])

The last parameter month is optional. It allows for a broken first and last period of depreciation. i.e. If the item was purchased at the start of month 10 in the first period then the first period should only have three months of depreciation in it and the last period will only have 9 months of depreciation left in it. If omitted then the function assumes that the full 12 months depreciation applies in the first period. This option does not have to a be an integer, for example 2.5 months could be used. When using this option be sure to calculate the depreciation amount in the final part period which will be the number of periods + 1 th period.

Example

If an item was purchased for $20,000 to be depreciated over 3 years with an expected residual or salvage value of $10,000 then the function would be (for the first period):

    =DB(20000,10000,3,1)

The function would return $4,120

Discussion

This function determines the fixed depreciation rate that is applied each period and then iterates through the number of periods to find the depreciation amount for the period requested.

However you should be aware that at the stage of finding the fixed depreciation rate this function rounds to three decimal places. This is explained in the help to this function. This means that in many cases the final figure you end up with will not be exactly the salvage value you wanted.

If we continue the above example and use this function to calculate the depreciation for each of the three periods then we would get the following:

    Period      Depreciation (DB Function)        Value
       0                                      20,000.00
       1               4,120.00               15,880.00
       2               3,271.28               12,608.72
       3               2,597.40               10,011.32

The salvage value is $11.32 higher than the requested 10,000 due to the rounding the function does behind the scenes. This may be immaterial to most accountants, but if it matters to you then consider using a manual formula to calculate the results.

Manual Calculation

Manual calculation of this value offers the clear advantage of avoiding the rounding issue with the function. Using the formula below will give more exact results than the function:

    = value in previous period * (1 – (salvage / cost)^(1 / number of periods) )

If we use this formula with the same figures as the above example we get:

    Period       Depreciation (Formula)           Value
       0                                      20,000.00
       1               4,125.99               15,874.01
       2               3,274.80               12,599.21
       3               2,599.21               10,000.00

Use in Accounting

Under the Australian Equivalent to the International Financial Reporting Standards:

• Depreciation should be started when the item is available for use and concludes when either the item is classified as held for sale or the item is derecognised as an asset (written off). Depreciation does not cease just because an item becomes idle or is retired from active use unless it has already been fully depreciated. (AASB116-55)
• Reducing balance depreciation is appropriate if the pattern of usage of the asset fits the reducing balance model (AASB116-62). In the past this method of depreciation has been difficult to calculate manually and so a simpler reducing balance method was invented, the sum of digits reducing balance method (Excel function SYD). However, with the modern advent of cheap computing power and tools like Excel this method is no longer difficult for anyone to implement.
• The depreciation method chosen should be reviewed at least at the end of each annual reporting period (AASB116-61).
• If the pattern of usage changes then the depreciation method should be changed to reflect the current pattern of use (AASB116-61).

Alternative Uses

This function could be used in any case where you need a fixed percentage to take you from a starting figure to a final figure in a given number of periods. The function will accept a cost lower than the “salvage” value so that it can be used to model investment or appreciation situations however it will return a negative number when used this way. It normally returns a positive depreciation amount so a negative is an appreciation amount. For this sort of use simply prefix the function with a minus to reverse the sign of the result. For example:

• You have 10,000 dollars and want to know how much you need to save/earn to achieve 20,000 in five years:

    Period            Formula         Savings (DB Function)       Value
       0                                                      10,000.00
       1     = -DB(10000,20000,5,1)          1,490.00         11,490.00
       2     = -DB(10000,20000,5,2)          1,712.01         13,202.01
       3     = -DB(10000,20000,5,3)          1,967.10         15,169.11
       4     = -DB(10000,20000,5,4)          2,260.20         17,429.31
       5     = -DB(10000,20000,5,5)          2,596,97         20,026.27

• You have 10,000 dollars and want to know what percentage increase per year (interest rate) you would need to reach 20,000 in five years. (Note: that to return the rate, always use period 1 and divide the result by the purchase price (or the starting value) ):

    = -DB(10000,20000,5,1) / 10000
    = 14.90% (formatted as a percentage)

• Your sales are 100,000 this year and you have a goal of reaching 1,000,000 within five years. You want to know the percentage sales growth you need each year (could be used for units or dollars):

    = -DB(100000,1000000,5,1) / 100000
    = 58.50% (formatted as a percentage)

Disclaimer

The author is not a qualified accountant and makes no claim, warranty or other assurances that the accounting information given above is accurate or that it applies in your jurisdiction.