### DB Function Dictionary

Function Dictionary | Function Examples | Function Categories |

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 | |||||||||