DB in Google Sheets

What is DB Function in Google Sheets?

DB in Google Sheets stands is a financial function that is used to calculate the depreciation of an asset for a specific period using the fixed-declining balance method. It stands for “Declining Balance.” Here, the function applies a constant rate of depreciation each period to the remaining book value of the asset. This method is helpful for assets that lose more value in the earlier years of use. This includes electronic goods and vehicles.

Let us consider an example, where we buy a vehicle for $5,000 with a salvage value of $500 and a useful life of 10 years. To calculate the depreciation for the first year, we use the formula:

=DB(5000, 500, 10, 1).

The result gives you the depreciation expense for the first year, which helps you track the vehicle’s declining value over time.

The DB function calculates depreciation of an asset for a specific period using the fixed-declining balance method. This method applies higher depreciation in the earlier years of the asset’s life.
Its syntax is =DB(cost, salvage, life, period, [month]), where cost is the asset’s price, salvage is its value at the end of its useful life, life is the number of periods, period is the specific time to calculate for, and month is optional for partial first-year depreciation.
It is used when you wish to apply accelerated depreciation for assets like machinery or electronics that lose value quickly at the start.
It does not switch to straight-line depreciation automatically and requires valid positive inputs.

Syntax

The syntax of the DB function in Google Sheets is as follows:

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

Parameters:

cost – the initial cost of the asset.
salvage – the value of the asset at the end of its useful life.
life – the number of periods (usually years) over which the asset will be depreciated.
period – the specific period for which you want to calculate the depreciation for example 1 represents Year 1, 2 represents
“month” (optional) – it specifies the number of months in the first year. The default is 12.

How To Use DB Function in Google Sheets?

There are two ways to enter DB in Google Sheets.

Manually Entering the DB Function.

You can also enter the DB function directly in Google Sheets. Let’s go through a simple example. Suppose you purchase a machine worth $10,000. It has a salvage value of $1,000 and a useful life of 5 years. You want to calculate the depreciation for the first year using the DB function.

Step 1: Enter the data into the Google Sheet as follows:

Cost: $10,000
Salvage Value: $1,000
Useful Life (in years): 5
Period (Year): 1

Step 2: To calculate the depreciation for the first year, enter the following formula in cell B5. First enter an = sign, followed y the function name and an open parenthesis. Add the cell references or values directly and close the parentheses.

=DB(B1, B2, B3, B4)

It uses the formula DB(cost, salvage, life, period, [month]) to find depreciation.

Step 3: Press Enter, and the result will show the depreciation amount for the first year. The function returns $3,690, which represents the asset’s declining value in the first year.

From The Google Sheets Menu

You can easily insert the DB function from the Google Sheets menu.

First, go to the “Insert” menu option.
Go to “Functions”, then select “Financial.”
Choose the DB function. A function input box will appear where you can enter the required arguments and press Enter.

Examples

Here are practical examples to help you understand how the DB function works in financial scenarios. They show how to calculate the depreciation for different assets using the declining balance method in Google Sheets.

Example #1 – Calculate Office Equipment Depreciation

In this example, suppose you purchase office equipment worth $15,000. The estimated salvage value after its useful life is $3,000, and it is expected to last for 6 years. Calculate the depreciation for the first year using the DB function.

Step 1: Enter the following data into your Google Sheet:

Cost: $15,000
Salvage Value: $3,000
Useful Life (Years): 6
Period (Year): 1

Step 2: Enter the following formula in cell B5.

=DB(B1, B2, B3, B4)

Press Enter.

We get the first-year depreciation amount as $3.525, which shows the value the office equipment loses after one year of use.

Example #2 – Calculate Depreciation Expense Each Year in a Projected Income Statement

In this example, a company purchases some furniture for $25,000. The expected salvage value at the end of its useful life is $5,000, and the furniture will be used for 5 years. Here, we will calculate the depreciation expense for each year to include it in a projected income statement.

Step 1: Enter all the values for depreciation calculation in a Google sheet. It is as shown below.

Cost: 25000
Salvage Value: 5000
Useful Life (Years): 5

Step 2: Next, in Column C, list the years for which depreciation will be calculated.

DB Function in Google Sheets Example 2-1

Step 3: Apply the DB function in Column D to calculate the depreciation expense. For Year 1, we enter the following formula in cell D1.

=DB($B$2, $B$3, $B$4, C2)

Here, $B$1 is the cost, $B$2 is the salvage value, $B$3 is the useful life, and C1 is the current year or period.

DB Function in Google Sheets Example 2-2

Step 4: Drag the formula down for the remaining years to calculate depreciation for each subsequent year.

DB Function in Google Sheets Example 2-3

Using this method, we get the annual depreciation cost. This is especially helpful when preparing a projected income statement. It helps you accurately represent the declining value of assets.

Example #3 – Calculate Vehicle Depreciation with Partial First Year

A company purchased some electronic goods for $10,000 with an expected useful life of 5 years and a salvage value of $2,000. The goods we in use from October 1st, 2024. Calculate the depreciation for the first year, that is, 2024, using the DB function.

Step 1: Determine the number of months in the first year. The goods were used in October, November, and December in 2024, which is a total of 3 months.

Step 2: Let us calculate the full year depreciation. Use DB in Google Sheets as follows:

Here,

Cost: $30,000 (initial cost of the asset)
Salvage: $5,000 (expected salvage value)
Life: 5 (useful life in years)
Period: 1 (the first year)

=DB(30000, 5000, 5, 1).

It calculates the depreciation for the full year.

Step 3: To calculate the partial-year depreciation, divide the full-year depreciation by 12 to get the monthly depreciation.

Multiply the monthly depreciation by 3 as the goods were in service for 3 months in 2024. months).

Calculate the total depreciation for the first year:

=DB(30000, 5000, 5, 1) /12 * 3

DB Function in Google Sheets Example 3-1

The depreciation for the first year (2024) is approximately $2,257.50.

Important Things to Note

If the period specified exceeds the asset’s useful life, the function returns #NUM! error.
Many accounting systems switch from declining balance to straight-line depreciation for accuracy in later years, which cannot be done with the DB function.
The function returns errors the #NUM! or #VALUE! errors if any of the key inputs like cost, salvage, life, or period are negative or zero.

Frequently Asked Questions (FAQs)

What are some errors one encounters when using DB in Google Sheets?

Some the errors we obtain include:

The #VALUE! error which occurs when we use non-numeric arguments.

The #NUM! error occurs when:
The cost or salvage is less than zero.
The life or period argument is not a positive number.
Month argument is assigned a value less than or equal to zero or greater than 12.
When the period argument exceeds the life argument.

When should we use the DB function?

We can use the DB function when we must apply accelerated depreciation especially for assets that lose value quickly, like electronics and vehicles.

Are there any limitations to consider when using the DB function?

Let us consider some of the limitations when using the DB function.

1. As one must have noticed, the DB function relies on historical data of past periods to make predictions about future growth. However, the market may be subject to significant changes in its dynamics or consumer behavior. This may affect the accuracy of the result.
2. The function assumes a constant rate of depreciation which may not suit assets that lose value erratically.
3. The optional month parameter can cause errors if not used properly. It is used when the depreciation for the first year has only a few months of use and should be calculated properly.

Download Template

This article must help understand DB Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

DB Function In Google Sheets Download

DB in Google Sheets