Subscribe Us

Enter your email address:

Loading

DB Function in Excel

Labels:


This function calculates deprecation based upon a fixed percentage. The first year is depreciated by the fixed percentage. The second year uses the same percentage, but uses the original value of the item less the first year’s depreciation. Any subsequent years use the same percentage, using the original value of the item less the depreciation of the previous years. The user does not set the percentage used in the depreciation; the function calculates the necessary percentage, which will be varying based upon the values inputted by the user.

An additional feature of this function is the ability to take into account when the item was originally purchased. If the item was purchased part way through the financial year, the first years depreciation will be based on the remaining part of the year.


C
D
E
3

Purchase Price :
£5,000
4

Life in Years :
              5
5

Salvage value :
£200


D
E
F
7
Year
Deprecation

8
1
£2,375.00
 =DB(E3,E5,E4,D8)
9
2
£1,246.88
 =DB(E3,E5,E4,D9)
10
3
£654.61
 =DB(E3,E5,E4,D10)
11
4
£343.67
 =DB(E3,E5,E4,D11)
12
5
£180.43
 =DB(E3,E5,E4,D12)
 13
Total Depreciation :
£4,800.58
* See example 4 below.

Syntax

=DB(PurchasePrice,SalvageValue,Life,PeriodToCalculate,FirstYearMonth)

The FirstYearMonth is the month in which the item was purchased during the first financial year. This is an optional value, if it not used the function will assume 12 as the value.

Example 1

This example shows the percentage used in the depreciation. Year 1 depreciation is based upon the original Purchase Price alone. Year 2 depreciation is based upon the original Purchase Price minus Year 1 deprecation. Year 3 deprecation is based upon original Purchase Price minus Year 1 + Year 2 deprecation. The % Deprc has been calculated purely to demonstrate what % is being used.


C
D
E
47

Purchase Price :
£5,000
48

Salvage value :
£1,000
49

Life in Years :
                 5


D
E
F
G
51
Year
Deprecation

% Deprc
52
1
£1,375.00

27.50%
53
2
£996.88

27.50%
54
3
£722.73

27.50%
55
4
£523.98

27.50%
56
5
£379.89

27.50%
=DB(E47,E48,E49,D56)



Total Depreciation :
£3,998.48

Why Is The Answer Wrong ?

In all of the examples above the total depreceation may not be exactly the expected value. This is due to the way in which the percentage value for the depreceation has been calculated by the =DB() fumction. The percentage rate is calculated by Execl using the formula = 1 - ((salvage / cost) ^ (1 / life)). The result of this calculation is then rounded to three decimal places. Although this rounding may only make a minor change to the percentage rate, when applied to large values, the differnce is compounded resulting in what could be considered as approximate values for the the depreceation.

'



0 comments:

Post a Comment

Post a Comment