Use Of DB
Purchase Price :
|
£5,000
| ||||||
Life in Years :
|
5
| ||||||
Salvage value :
|
£200
| ||||||
Year
|
Deprecation
| ||||||
1
|
£2,375.00
|
=DB(E3,E5,E4,D8)
| |||||
2
|
£1,246.88
|
=DB(E3,E5,E4,D9)
| |||||
3
|
£654.61
|
=DB(E3,E5,E4,D10)
| |||||
4
|
£343.67
|
=DB(E3,E5,E4,D11)
| |||||
5
|
£180.43
|
=DB(E3,E5,E4,D12)
| |||||
Total Depreciation :
|
£4,800.58
|
* See example 4 below.
| |||||
What Does It Do ?
| |||||||
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 years depreciation.
| |||||||
Any subsequent years use the same percentage, using the original value of the item less
| |||||||
the depreciation of the previous years.
| |||||||
The percentage used in the depreciation is not set by the user, the function calculates
| |||||||
the necessary percentage, which will be vary 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.
| |||||||
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.
| |||||||
Formatting
| |||||||
No special formatting is needed.
| |||||||
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.
| |||||||
Purchase Price :
|
£5,000
| ||||||
Salvage value :
|
£1,000
| ||||||
Life in Years :
|
5
| ||||||
Year
|
Deprecation
|
% Deprc
| |||||
1
|
£1,375.00
|
27.50%
| |||||
2
|
£996.88
|
27.50%
| |||||
3
|
£722.73
|
27.50%
| |||||
4
|
£523.98
|
27.50%
| |||||
5
|
£379.89
|
27.50%
| |||||
=DB(E47,E48,E49,D56)
| |||||||
Total Depreciation :
|
£3,998.48
| ||||||
Example 2
| |||||||
This example is similar to the previous, with the exception of the deprecation being calculated
| |||||||
on a monthly basis. This has been done by multiplying the years by 12.
| |||||||
Purchase Price :
|
£5,000
| ||||||
Life in Years :
|
£5
| ||||||
Salvage value :
|
100
| ||||||
Month
|
Deprecation
| ||||||
56
|
£8.79
| ||||||
57
|
£8.24
| ||||||
58
|
£7.72
| ||||||
59
|
£7.23
| ||||||
60
|
£6.78
| ||||||
=DB(E66,E68,E67*12,D75)
| |||||||
Example 3
| |||||||
This example shows how the length of the first years ownership has been taken into account.
| |||||||
Purchase Price :
|
£5,000
| ||||||
Life in Years :
|
5
| ||||||
Salvage value :
|
£1,000
| ||||||
First Year Ownership In Months :
|
6
| ||||||
Year
|
Deprecation
|
% Deprc
| |||||
1
|
£687.50
|
13.75%
| |||||
2
|
£1,185.94
|
27.50%
| |||||
3
|
£859.80
|
27.50%
| |||||
4
|
£623.36
|
27.50%
| |||||
5
|
£451.93
|
27.50%
| |||||
=DB(E74,E76,E75,D84,E77)
| |||||||
Total Depreciation :
|
£3,808.54
| ||||||
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.
| |||||||
Example 4
| |||||||
This example has been created with both the Excel calculated percentage and the 'real'
| |||||||
percentage calculated manually.
| |||||||
The Excel Deprecation uses the =DB() function.
| |||||||
The Real Deprecation uses a manual calculation.
| |||||||
This is the 'real' deprecation percentage, calculated manually :
|
27.522034%
| ||||||
=1-((E117/E116)^(1/E118))
| |||||||
Purchase Price :
|
£5,000
|
= 1 - ((salvage / cost) ^ (1 / life)).
| |||||
Salvage value :
|
£1,000
| ||||||
Life in Years :
|
5
| ||||||
Year
|
Excel
Deprecation |
Real
Depreciation |
Excel
% Deprc | ||||
1
|
£1,375.0000
|
£1,376.1017
|
27.500%
| ||||
2
|
£996.8750
|
£997.3705
|
27.500%
| ||||
3
|
£722.7344
|
£722.8739
|
27.500%
| ||||
4
|
£523.9824
|
£523.9243
|
27.500%
| ||||
5
|
£379.8873
|
£379.7297
|
27.500%
| ||||
Total Depreciation :
|
£3,998.48
|
£4,000.00
| |||||
Error difference :
|
£1.52
|
कोई टिप्पणी नहीं:
एक टिप्पणी भेजें