Excel offers five different functions for calculating depreciation. Consider an asset with a cost $ 10000, liquidation (residual) value $ 1000 and useful life 10 periods (years). The results of all five functions are shown below. We will describe each of these functions in more detail below.
Most assets lose most of their value early in their useful lives. Functions Turn it on (SOUTH), FUO (DB), DDOB (DDB) and PUO (VDB) take this factor into account.
Premier League
Function Premier League (SLN) is as simple as a straight line. Each year, depreciation charges are considered equal.
Function Premier League performs the following calculations:
- Depreciation charges = ($10000–$1000)/10 = $900.
- If we deduct the amount received from the original cost of the asset 10 times, then its depreciation value will change from $10000 to $1000 over 10 years (this is shown at the bottom of the first figure at the beginning of the article).
Turn it on
Function Turn it on (SYD) is also simple – it calculates depreciation using the sum of annual numbers method. As shown below, this function also requires the number of periods to be specified.
Function Turn it on performs the following calculations:
- A useful life of 10 years gives the sum of the numbers 10+9+8+7+6+5+4+3+2+1 = 55
- The asset loses $10 in value over the period under consideration (9000 years).
- Depreciation amount 1 = 10/55*$9000 = $1636.36;
Depreciation amount 2 = 9/55*$9000 = $1472.73 and so on.
- If we subtract all the resulting depreciation from the original cost of the asset of $10000, we get a residual value of $1000 after a useful life of 10 years (see the bottom of the first figure at the beginning of the article).
FUO
Function FUO (DB) is a bit more complicated. The fixed depreciation method is used to calculate depreciation.
Function FUO performs the following calculations:
- Rate = 1–((residual_cost/initial_cost)^(1/lifetime)) = 1–($1000/$10000)^(1/10)) = 0.206. The result is rounded to thousandths.
- Depreciation amount period 1 = $10000*0.206 = $2060.00;
Depreciation amount period 2 = ($10000-$2060.00)*0.206 = $1635.64 and so on.
- If we subtract all the resulting depreciation from the original cost of the asset of $10000, we get a residual value of $995.88 after a useful life of 10 years (see the bottom of the first figure at the beginning of the article).
Note: Function FUO has an optional fifth argument. This argument can be used if you want to specify the number of months of operation in the first billing year (if this argument is omitted, then the number of months of operation in the first year is assumed to be 12). For example, if the asset was acquired at the beginning of the second quarter of the year, i.e. in the first year, the life of the asset was 9 months, then for the fifth argument of the function you need to specify the value 9. In this case, there is some difference in the formulas that Excel uses to calculate depreciation for the first and last period (the last period will be the 11th year, consisting only from 3 months of operation).
DDOB
Function DDOB (DDB) – doubling the balance, again from among the prime ones. However, when using this function, the required residual value is not always achieved.
Function DDOB performs the following calculations:
- With a useful life of 10 years, we get the rate 1/10 = 0.1. The method used by the feature is called the double-remainder method, hence we have to double the bet (factor = 2).
- Depreciation amount period 1 = $10000*0.2 = $2000;
Depreciation amount period 2 = ($10000-$2000)*0.2 = $1600 and so on.
As already mentioned, when using this function, the required residual value is not always achieved. In this example, if you subtract all the depreciation received from the original cost of the asset of $10000, then after 10 years we get the value of the residual value of $1073.74 (see the bottom of the first figure at the beginning of the article). Read on to find out how to fix this situation.
Note: The DDOB function has an optional fifth argument. The value of this argument specifies a different factor for the declining balance interest rate.
PUO
Function PUO (VDB) uses the double decrement method by default. The fourth argument specifies the start period, the fifth argument specifies the end period.
Function PUO performs the same calculations as the function DDOB. However, if necessary, it switches to the “straight line” calculation mode when necessary (highlighted in yellow) in order to reach the value of the residual value (see the bottom of the first figure at the beginning of the article). Switching to the “straight line” calculation mode occurs only if the depreciation value according to the “straight line» exceeds the amount of depreciation according to the «double reduction of the balance».
In the eighth period, the amount of depreciation under the method of double declining balance = $419.43. At this stage, we have an amount to write off depreciation equal to $2097.15-$1000 (see the bottom of the first figure at the beginning of the article). If we use the “straight line” method for further calculations, then for the remaining three periods we get the depreciation value of $1097/3=$365.72. This value does not exceed the value obtained by the double deductible method, so there is no switch to the “straight line” method.
In the ninth period, the amount of depreciation under the method of double declining balance = $335.54. At this stage, we have an amount to write off depreciation equal to $1677.72-$1000 (see the bottom of the first figure at the beginning of the article). If we use the “straight line” method for further calculations, then for the remaining two periods we get the depreciation value of $677.72/2 = $338.86. This value is higher than the value obtained by the double deductible method, so it switches to the straight line method.
Note: Function PUO much more flexible than the function DDOB. With its help, you can calculate the amount of depreciation for several periods at once.
The function contains the sixth and seventh optional arguments. With the sixth argument, you can define another coefficient for the declining balance interest rate. If the seventh argument is set to TRUE (TRUE), then switching to the “straight line” calculation mode does not occur.