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


calculate markup percentprintable ledger balance sheetwhat is factoring of accounts receivableamortisation of assetsrent payable journal entryyear end closing entries accountingcalculate the asset turnover ratiocoupon bonds examplesdeclining depreciation formulanet fixed asset turnover ratiouncredited chequesfixed overheadsingle entry book keepingdefine creditors in accountingcash flow margin formulainstallment note definitionproject profitability index calculatoradjusted and unadjusted trial balancesample accounting ledgeradvance cash receipt formatzero based budgeting definitionhow to calculate pv ratiojournal entry for accrued expensestotal labor variance formulasales accrualsnormal balance of cashabsorption in cost accountingimprest balancedays receivable formularoa dupontdeclining balance depreciation calculatorincome statement contribution margincapital lease calculatorfinancial leverage ratio formuladamaged inventory journal entryjournal entry for accrual of expensesfob delivery definitiondiscounting of receivablesshare repurchase journal entryinterest formula compounded continuouslypayback period calculation excelreceivable conversion periodroce ratiois prepaid expense an assetincome statement bad debt expenseunrealized gains and losses balance sheetpresent value of annuity table pdfprofit markup calculatorstock repurchase journal entrycash withdrawal from bank entry in tallydefine chequesoperating asset turnover ratioirr excellmarkup calculationsfob on invoiceformula of annuityannuity calculator excelworking out gp percentageexcel function pmthow to calculate future value of money in excelcoupon bond pricingamortized discountformula for double declining depreciationcalculate npv excelpmt function to calculate monthly paymentcapital receipts examplesimprest petty cashexcel formula for discount rateannuity due calculatorvertical analysis for income statementmarkup formulaaccounting credit memo journal entrydepreciation accounting entriesaccounting worksheet exampleinventory turnover times formularetained earning net incomeperpetual inventory system journal entriesdupont equation examplepromissory note journal entryar turnover days formula