DDB () function and double declining balance method

The discovery of the problem

[Example] a fixed asset of an enterprise is assumed to be the original value of 400,000 yuan, the expected service life is 5 years, the net residual value is expected to be 4%, the use of double declining balance method to calculate the annual depreciation costs.

Solution: Using the built-in financial functions of the Chinese Excel 97/2000, DDB (cost,salvage,life,period,factor) solves the problem.

1. The DDB () function is explained.

1. The cost is the original value of the fixed asset

2. Salvage is the estimated net residual value (= estimated residual value-estimated clean-up costs). The system defaults to 0, that is, the net residual value is 0

3. Life is expected to be used

4. Period for the first year

5. Factor is a decreasing balance. The system defaults to 2, that is, the double balance is decremented. If the value is 3 o'clock, it is three times times the balance diminishing.

2. Using functions

In the previous example, we know: cost=400000,salvage=400000*40%,life=5,factor=2 (default), in order to calculate the five-year depreciation fee, so that PERIOD=A1 (A1 cell has a value of 1)

In cell B2, enter: =ddb (400000,400000*4%,5,A2), and calculate the depreciation cost for 1-5 years with the formula to fill in the blanks

3. Finding problems

As we used the function for the first time, we did an experiment to insure that the "cumulative depreciation = Total Accrued depreciation" was established. Calculate cumulative total depreciation in cell B7: =sum (B2:B6), and "accrued depreciation totals" in cell B8: =400000-400000*4%. We found: Cumulative depreciation (368896) <> accrued depreciation Total (384000), with a 15104 difference, as shown in figure:

	A	B
1	First year	Annual depreciation amount under the DDB function (million)
2	1	￥160,000.00
3	2	￥96,000.00
4	3	￥57,600.00
5	4	￥34,560.00
6	5	￥20,736.00
7	Cumulative Total depreciation	￥368,896.00
8	Accrued depreciation	￥384,000.00
9	Difference	￥15,104.00

Analysis of the problem

In fact, this problem is not derived from Excel, which is involved in the accounting field is an important issue.

1. Diminishing depreciation

Decreasing depreciation is the method of decreasing depreciation cost in the period of use of fixed assets, and the double balance decreasing method is one of the main methods of decreasing depreciation, which is widely used in accounting practice.

A. Theoretical basis for diminishing depreciation

1. The service contribution ability of general fixed assets is always decreasing every year

2. Diminishing operational performance or performance can lead to additional operating costs, such as repair maintenance costs

3. The value of the present value of the remaining service value is lower in the prophase and less in the later period.

4. Due to the possibility of obsolescence of assets, the income in the future period is uncertain

5. Compared with the straight-line method, the use of the earlier period can pay less income tax, equivalent to obtain interest-free loans from the country

6. Compared with the straight-line method, it can be used to recover more investment in the early period, which is advantageous to the nonphysical wear of avoiding assets.

B. The theoretical operation of the double balance Decline method

In order to make a lot of depreciation expenses in the prophase of the use of fixed assets, and to reduce the depreciation cost, the depreciation cost is decreasing yearly, using 2/expected service life as depreciation rate. In particular: depreciation expense for the year = annual Fixed asset value * depreciation rate (regardless of net residual value). The law does, from a number of figures, complete "upfront expenses, in the latter part, the requirement of reducing the cost and depreciation cost is decreasing, but the inherent defect of the law is that it is impossible to complete all the accrued depreciation amounts in the prescribed years, which can be proved mathematically (here Conlio). Can not be in the prescribed period of time to complete all the accrued depreciation amount, the cost of capital can not be cost, into the calculation of profit and loss, which in accounting is not clear, contrary to the fixed value of the principle of production depreciation and basic requirements.

2. Double balance decline method of natural defects remedy scheme

Although this problem exists in the double declining balance method, it is indeed a very good declining depreciation scheme, and there is no more ideal (from the point of view of purpose and simplified) in addition to the method of using the sum of life. In order to save the double balance decline method, accounting has produced a number of solutions.

1. The portion of the variance that is not expected to be included in the estimated service life is included in the depreciation cost of the last year. (hereinafter referred to as Scheme I, hereinafter)

2. The average distribution of the difference in the estimated service life that cannot be completed is included in the annual depreciation fee.

3. Calculates depreciation charges using the double declining balance method, in the last two years of the expected service life, using the straight-line method to calculate the depreciation fee, that is, the depreciation fee for the last two years = (fixed asset value at the beginning of the second year-estimated net residuals)/2. Such programmes are used in a number of countries ' accounting systems.

4. Compares the depreciation charges for the year with the straight-line method and the double balance descending method before calculating the annual depreciation fee. If the depreciation cost under the straight-line method is lower than the depreciation cost calculated by the double declining balance method, the depreciation fee is still calculated using the double declining balance method; If the depreciation cost calculated under the straight-line method is greater than the calculation result under the double declining Balance method, the annual depreciation fee is calculated by the straight-line method in the year and the subsequent years.

 	A	B	C	d	E	F
1		scheme one	scheme two	program three	scheme four	line method
2	1	10000.00	10000.00	10000.00	5000.00
3	2	8000.00	8000.00	8000.00	5000.00
4	3	6400.00	6400.00	6400.00	5000.00
5	4	5120.00	5120.00	5120.00	5000.00
6	5	4096.00	4096.00	3413.33	5000.00
7	6	3276.80	3276.80	3413.33	5000.00
8	7	2621.44	2621.44	3413.33	5000.00
9	8	2097.15	2097.15	3413.33	5000.00
ten	9	1677.72	4194.31	3413.33	5000.00
one	10	6710.89	4194.30	3413.35	5000.00
12	Cumulative depreciation	50000.00	50000.00	50000.00	50000.00	50000.00

[Example] The original value of a fixed asset is 50000 yuan, the estimated service life is 10, using the above four schemes to calculate the depreciation cost of 1-10 years.

Solution: The process of solving problems I have listed in the table above.

Analysis: Before we analyze the problem, let us review the concept and theoretical basis of the regressive depreciation method.

It is not difficult to find from the above table that the depreciation costs for certain years in programme I, programme III are greater than the depreciation costs of their respective previous years, as shown in the following figure:

From the above figure we clearly found: scheme I, the scheme Sandu existence of late depreciation cost "Shinuba" image.

The depreciation expense should be decreasing year by yearly is the basic characteristic of the descending depreciation method, also is the foundation of the decline depreciation method argument. The service level of fixed assets has been decreasing with the increase of the number of years, and its contribution to the enterprise has been reduced correspondingly, from the ratio of income to expense, the depreciation cost of the profit and loss should be decreased year by year.

Scheme II better solve the problem of double declining balance method, I personally think this is the best of the four options. Although it was said that it used the principle of uneven allocation of the difference between the proportions. But I think that from the point of view of importance, the average amount of expenses incurred in the annual accrual of depreciation is not large, and the depreciation cost of each year is reduced smoothly (from the above figure); Furthermore, all depreciation methods are hypothetical, as long as they are justified.

Scheme Four also solves the problem of double balance decreasing method, in order to illustrate the convenience, the line method is used in column F. When calculating depreciation charges, we find that the depreciation cost of the fifth year under the traditional double balance decreasing method is 5000.00, so the depreciation cost of the =4096.00< line method is changed from fifth year to the straight-line method to avoid the depreciation cost "Shinuba" situation, But in this case, there are 6 of years in the use of straight-line method to calculate depreciation costs, this can also be called the "double Declining Balance method"?

Since financial accounting is subject to accounting standards and accounting system, since our country has stipulated the adoption of Scheme three, we should use this scheme unconditionally in our usual accounting.

We should also note that not all cases in which the double balance reduction method and the last two years have been replaced by a straight-line method will have the depreciation cost "Shinuba", and in most cases the depreciation costs calculated by using the double declining Balance method plus the last two-year straight-line method are normal. Experience tells us that this depreciation fee "Shinuba" occurs when the expected service life is longer and the value of the fixed asset is small. However, those enterprises or departments which use the double declining balance method to calculate the depreciation cost of fixed assets are generally used in the fields of rapid technological progress and nonphysical wear, and their assets are generally of greater value and shorter service life.

3. The reason that the DDB () function is not correctly solved

The DDB () function uses 2/expected service life as the depreciation rate in the lifetime of fixed assets without taking into account the issues we discussed above.

The DDB () function can not be solved correctly, it may be that Microsoft's experts are not very proficient in accounting, only Chichi, half; it may also be the solution to the problem of double declining balance method, so Excel has left their own thinking space. I agree more with the latter explanation.

Solution to the problem

Here we only use the double balance decreasing method and the last two years to use the straight-line method to deal with.

1. Thinking

Whatever the reason for the DDB () function not serving properly, we have only two choices, one is to not use the DDB () function, and the other is to try to solve the problem. We don't do "Shantou".

Now that the function is out of the question, let's start with the function--Customizing a function.

The DDB () function calculates the correct value in other years except for the last two years of depreciation, so the DDB () function can be used by us when calculating the first year to (estimated to be 3 years of service).

2. Custom function in Excel 97: Double Balance Descending method (Cost,life,period,salvage,fy)

1. Double declining Balance method for function name

2. The cost is the original value of the fixed asset

3. Life is expected to be used

4. Period for the first year

5. Salvage is the estimated residual value. The system defaults to 0, that is, the residual value is 0

6. Fy for projected clean-up costs. The system defaults to 0, that is, the cleanup fee is 0

3. Function of double balance Descending method () function

According to the explanation of the double balance decreasing method in our country, we compile the custom function that can calculate the annual depreciation cost correctly.

The annual depreciation charge retains two decimal places and adjusts the tail difference in the last year.

4. Double balance Descending method () The source code of the function

Function Double Balance Descending method (ByVal cost as Double, ByVal life as Integer, ByVal Period as Integer,_ Optional as Double = 0, Op tional Fy as Double = 0) as Double

Dim Zmcost as double, bqdb as double, sumdb as double, x as double, y as double
Dim life1 As Integer, n as Integer

Sumdb = 0
Life1 = Life-2

If (Period < life1 Or Period = life1) Then
Double Declining Balance method = Application.round (Application.ddb (Cost, life, Period), 2)
ElseIf (Period > Life1 and Period <= life) Then
For n = 1 to Life1 Step 1
Bqdb = Application.round (Application.ddb (Cost, life, N), 2)
Sumdb = Sumdb + bqdb
Next N
Zmcost = Cost-sumdb
Bqdb = Application.round (zmcost-salvage + Fy)/2, 2)

If Period = Life and (BQDB * 2 + sumdb) <> (Cost-salvage + Fy)) Then
Double Declining Balance method = Cost-salvage + fy-sumdb-bqdb
Else
Double Declining Balance method = Bqdb
End If
End If
End Function

5. Analysis of the source code

1. The code is written in the Visual Basic language, so the reader should have the basis of that language.

2. The code's handling of the last two years is not complicated: when period<= (Life-2), call the DDB () function in Excel 97/2000, that is, APPLICATION.DDB (); When Life>life-2, first [1, L Ife-3] Calculates the sum of the accumulated depreciation charges (Sumdb) and calculates the depreciation charges for the last two years = (Cost-salvage-+fy-sumdb)/2

3. The rest of the code is spent on keeping two decimal digits and the last-year adjustment of the tail difference.

4. Deficiencies, the function cannot return directly to the ￥#,# #0.00 format.