Excel DB Function

The Excel DB function has the syntax shown below.

DB (cost, salvage, life, period, month)
Arguments used in the Excel DB function
Cost = The initial cost of the asset
Salvage = The salvage or residual value of the asset
Life = The useful life of the asset
Period = The accounting period the depreciation is required for
Month = The number of months in the first year

Use of the Excel DB Function

The Excel DB function is used to calculate the declining balance depreciation of a long term asset based on a calculated depreciation rate.

Declining Balance Depreciation Rate

The declining balance depreciation rate implicit in the Excel DB function is based on the formula as follows:

Declining Balance Depreciation Rate = 1 – (Salvage Value / Cost)(1/Years)

So for example, if a business has purchased equipment costing 15,000 and expects it to have a useful life of 5 years and an estimated salvage value of 1,166, then the declining balance depreciation rate calculation using the formula above would be as follows:

Rate = 1 - (Salvage Value / Cost)(1/Years)
Rate = 1 - (1,166 / 15,000)(1/5) = 40%

This rate will reduce the cost of the asset (15,000) down to its salvage value (1,166) after a period of 5 years.

Excel DB Function Example

If a business purchases an asset costing 15,000 which is estimated to have a useful life of 5 years and a salvage value of 1,166, then the declining balance depreciation for period 1 based on the straight line rate, is calculated using the Excel DB function as follows:

Cost = 15,000
Salvage = 1,166
Life = 5 years
Period = 1
Month = Default = 12
Depreciation = DB (cost, salvage, life, period, month)
Depreciation = DB (15000,1166,5,1)
Depreciation = 6,000

In this example, the depreciation was required for period 1, if the depreciation had been required for period 3, then the period value would be set to 3 as follows:

Cost = 15,000
Salvage = 1,166
Life = 5 years
Period = 3
Month = Default = 12
Depreciation = DB (cost, salvage, life, period, month)
Depreciation = DB (15000,1166,5,3)
Depreciation = 2,160

Acquisition During the Year

In the above example the month argument was left blank and defaults to a value of 12 months, meaning that the asset is purchased at the start of year 1.

If the asset is purchased part way through the year and the business adopts a policy of only charging depreciation from the month of acquisition, then the month argument should be set to the number of months in which the asset was in use during the year.

For example, if the asset was purchased at the end on month five, there are seven months of the year remaining for which depreciation needs to be charged, and the month argument should be set to 7.

The declining balance depreciation for period 1 is then calculated using the Excel DB function as follows:

Cost = 15,000
Salvage = 1,166
Life = 5 years
Period = 1
Month = 7
Depreciation = DB (cost, salvage, life, period, month)
Depreciation = DB (15000,1166,5,1,7)
Depreciation = 3,500

And for month 3

Cost = 15,000
Salvage = 1,166
Life = 5 years
Period = 3
Month = 7
Depreciation = DB (cost, salvage, life, period, month)
Depreciation = DB (15000,1166,5,3,7)
Depreciation = 2,760

Using the Excel DB Function for Monthly Periods

In the above example the Excel DB function was used to calculate the annual depreciation. The function could equally well be used to calculate the monthly depreciation by defining the life in terms of months instead of years.

Suppose for example, the business purchased an asset costing 15,000 with an estimated salvage value of 1,166 and a useful life of 60 months, then using the Excel DB function the declining balance depreciation for say month 9, is calculated as follows:

Cost = 15,000
Salvage = 1,166
Life = 60 months
Period = 9
Month = Default = 12
Depreciation = DB (cost, salvage, life, period, month)
Depreciation = DB (15000,1166,60,9)
Depreciation = 447

The declining balance depreciation for period 9 is calculated as 447 a month.

The Excel DB function is one of many Excel finance functions used in financial calculations, discover another at the links below.

Excel DB Function November 6th, 2016Team

You May Also Like


Related pages


fv annuity formulamaterial requistioncredit turnover ratio formularetained earning equationraw materials inventory formulapurchased merchandise on account journal entrydefine amortisecalculating margin and markupprepaid expense is an assetprovision for doubtful accounts journal entryhow to prepare a contribution format income statementoverstated in accountingjournal voucher entriesunearned income in accountingamortised cost meaningpresent value of an annuity chartwhen are annuity due payments madewhat is the journal entry for prepaid rentfactoring without recourseallowance method accountingadjusting entries for accrualscontra asset definitionsales turnover ratio formulacalculating the payback periodformula for stockholders equityretained earning debit or creditretaining earnings formulaaccounting notes payable journal entriesperpetual inventory fifodouble declining amortizationannuity present value formulaequity method of accounting for joint venturescomputing inventory turnoverliquidation journal entriesfuture value of money formula excelrent revenue journal entryperpetual and periodic inventory system journal entriesliquidity ratio acid testaccrued payrollaccounts receivable turnover rateerrors not affecting the trial balancemarketable securities on balance sheetaccounts payable days ratioformula for reducing balance methodhow to record depreciation expense journal entrybank reconciliation exerciseinventory valuation lower of cost or marketpv of annuity due calculatorassets employed formulacash receipt voucher formatpetty cash balancewhy accumulated depreciation is creditbook rate of return formulashrinkage meaningformula for payback periodsunk cost definitionexample of accumulated depreciationpetty cash book samplemarketable securities examplefixed overhead expenseswhere does treasury stock go on the balance sheethow to compute contribution margin per unitledger templategross margin equalsaged accounts receivablestock turnover ratio formula in daysledger entry in accountingdefine notes receivableconverting effective interest rate to nominaldebtors collection period ratiodifferent types of source documentstotal assets turnover formulasample nonprofit chart of accountsledger template excel