The DB Function in Google Sheets calculates an asset’s depreciation using the fixed-declining balance method. This approach applies a higher depreciation rate early in the asset’s life, gradually reducing it over time. Understanding this method is essential since depreciation calculations vary across different methods, such as double declining balance (DDB) and straight-line (SLN).

Let’s start by reviewing the DB function’s syntax and arguments, followed by a formula example. Finally, I’ll demonstrate how to manually calculate depreciation using the fixed-declining balance formula.

DB Function Syntax and Arguments

Syntax:

DB(cost, salvage, life, period, [month])

Arguments:

• cost: The initial cost of the asset.
• salvage: The estimated value of the asset at the end of its useful life.
• life: The asset’s useful life, expressed in the number of periods.
• period: The specific period for which you want to calculate depreciation.
• month (optional): The number of months in the first year. If omitted, it defaults to 12.

Example of the DB Function in Google Sheets

Here’s an example to illustrate how the DB function works in Google Sheets.

Sample Data:

In cell B5, use the following formula to calculate depreciation for the first period:

=DB(B1, B2, B3, B4)

This returns $196,500.00 for the first period. Since the “month” argument is omitted, it assumes 12 months in the first year.

Including the “Month” Parameter

If we specify the “month” argument as 5, for example, the function calculates depreciation for a 5-month initial period:

=DB(B1, B2, B3, B4, 5)

Here, depreciation for the first period is $81,875.00, reflecting a shorter initial period and a lower depreciation amount.

Behind the DB Calculation: Fixed Declining Balance Method

To understand how the DB function calculates depreciation, let’s break down the fixed declining balance method manually.

Example Dataset:

Depreciation Formulas:

For all periods except the first, depreciation is calculated using this formula:

Depreciation = (Initial Cost – Accumulated Depreciation) * Depreciation Rate

The Depreciation Rate is calculated as:

1 – ((salvage / initial cost) ^ (1 / life))

Rounding this rate to three decimal places helps maintain accuracy.

For the first period, the formula adjusts to account for partial months:

Depreciation = Initial Cost * Depreciation Rate * (Month / 12)

Applying the Formulas to Sample Data

Using our sample dataset, let’s calculate depreciation for the first period:

=50000 * ROUND(1 - (5000 / 50000) ^ (1 / 5), 3) * 10 / 12

The result is $15,375.00.

For the second period, the formula is:

=(50000 - 15375) * ROUND(1 - (5000 / 50000) ^ (1 / 5), 3)

This returns $12,776.63.

The DB function in Google Sheets replicates the depreciation calculation for each period.

To set this up, enter the periods (1 to 6) in E2:E7. Then, enter the following DB formula in cell G2 and copy it down. Each cell in column G will calculate the depreciation for its corresponding period:

=DB($B$1, $B$2, $B$3, E2, $B$4)

Creating a DB Line Chart in Google Sheets

Enter the titles Year, Period, Opening Balance, Depreciation, and Closing Balance in D1:H1.

Populate the data:

• Enter the depreciation years in D2:D7.
• Enter the initial opening balance in F2.
• In H2, calculate the closing balance using the formula =F2 - G2, then drag it down to fill the column.
• In F3, enter the formula =H2 and drag it down to populate the opening balances for each period.

Now, the sample data for the DB line chart is ready.

To create the DB line chart:

• Use the data from D1:D7 and F1:F7 only (hide column E).
• Select the range D1:F7 and go to Insert > Chart.
• In the Chart Editor, choose “Line chart” as the chart type.

This will generate a line chart to visualize depreciation over time.

Related Reading

• Google Sheets DDB Function in Depreciation Calculation.
• How to Use the VDB Function in Google Sheets [Formula Examples].
• SYD Function in Google Sheets for Accelerated Depreciation.
• Google Sheets SLN Function in Straight Line Depreciation Calculation.
• How to Use the AMORLINC Function in Google Sheets.
• The Function DB in Excel vs DB in Google Sheets