DB

Returns the depreciation of an asset for a given year using the fixed rate declining-balance method.

Syntax:

DB(originalcost, salvagevalue, lifetime, year, months1styear)

originalcost: the initial cost of the asset.

salvagevalue: is the value at the end of the depreciation (sometimes called the salvage value of the asset).

lifetime: the number of years over which the asset is being depreciated.

year: the year number for which the depreciation is calculated.

months1styear: the number of months in the first year (defaults to 12 if omitted).

To calculate depreciation, DB uses a fixed rate throughout the asset’s life, given by

   rate = 1 - (salvagevalue/originalcost)(1/lifetime)

rounded to 3 decimal places.

The depreciation in any year is given by

   value_at_start_of_year * rate

where value_at_start_of_year = originalcost - depreciation_so_far.

If months1styear is less than 12 the depreciation rate used for the first and last years is

   rate * number_of_months_in_year / 12.

Example:

DB(10000, 1000, 5, 1)

returns approximately 3690 in currency units, which is the depreciation in the first year for an asset which cost 10000 and is written down to 1000 over 5 years.

How the DB function works:

Let's use the DB function for the CNC Machine (Asset ID: A101).

Scenario: Calculating Depreciation for the CNC Machine in Year 1

You call the DB function with the following inputs, which are the values from your Assets table for Asset ID A101:

• Cost: $50,000
• Salvage: $5,000
• Life: 10 years
• Period (Year): 1

The function performs its internal calculations:

The DB function first calculates a precise depreciation rate that takes into account the asset's salvage value. This is different from the simpler "double-declining balance" method.

Step 1: Calculate the Depreciation Rate

The formula for the rate is:

$\text{Rate}=1-{\left(\frac{\text{Salvage}}{\text{Cost}}\right)}^{\frac{1}{\text{Life}}}$

Plugging in the values for the CNC Machine:

$\text{Rate}=1-{\left(\frac{\$5,000}{\$50,000}\right)}^{\frac{1}{10}}\approx 0.20567177$

The function then rounds this rate to three decimal places:

Depreciation Rate≈0.206

Step 2: Calculate Depreciation for Year 1

Depreciation for Year 1=Beginning Book Value×Depreciation Rate

Depreciation for Year 1=$50,000×0.206=$10,300.00

The DB function returns the value $10,300.00. This is the depreciation expense for the CNC Machine in its first year.

Scenario: Calculating Depreciation for the CNC Machine in Year 2

Now, you want to calculate the depreciation for the second year. You call the DB function again with the updated information:

• Cost: $50,000
• Salvage: $5,000
• Life: 10 years
• Period (Year): 2
• Current Book Value (internal to the function): $50,000 - $10,300 (Year 1 Depreciation) = $39,700

The function performs its calculations for the second year:

• Depreciation for Year 2: $39,700 x 0.206 = $8,178.20

The DB function returns the value $8,178.20.

This DB function simplifies the complex, year-by-year calculation of depreciation, allowing the accountant to simply input the asset's details and the desired year to get the correct depreciation amount.