Excel DB Function
Calculating the depression of an asset is important as it allows firms to earn revenue from the assets they possess by spending on them over a specific period. It assigns the cost of a tangible or physical asset over its practical life. Therefore, depression analyses are very useful since they represent how much of an asset’s value of a company has already been used.
In this tutorial, you will discover the definition of the DB function, its syntax, parameter, key points of this function, and various real-time examples to understand how this formula works to return the depreciation of an asset for a given period of time.
What is DB Function?
“The DB is an inbuilt Excel function used to return the depreciation of an asset for a given period of time by utilizing the fixed-declining balance technique.“
The Excel DB function is a depreciation formula. The function DB stands for ‘Declining Balance’. The depreciation value is computed based on the cost of the initial asset, the salvage value, the number of periods that you will be depreciating the asset, and, optionally, the total number of months in the initial year of the depreciation.
The DB function is a built-in function categorized under Excel Financial Function. This function uses a fixed value of depreciation for computation purposes.
Syntax
Parameter
Cost (required): This parameter represents the actual cost of the asset.
Salvage (required): This parameter represents the salvage value after the asset has been fully depreciated.
Life (required): This parameter represents the useful life of the asset or the number of periods that you will be depreciating the asset.
Period (required): This parameter represents the period that the user wish to calculate the depreciation for.
number_months (optional): This argument represents the number of months in the first year of depreciation. If user doesn’t specify this parameter, by default, this function will take 12 months in the first year.
Return Type
The Excel DB function return the depreciation of an asset for a specified period using the fixed-declining balance method.
DB Function- Points to Remember
- MS Excel stores date as serial numbers. By default, January 1, 1900, is serial number 1 and corresponding dates have a serial number greater than 1.
- The fixed-declining balance method computes the depreciation value at a fixed rate. DB function utilizes the below given formulas to calculate depreciation for a period:
(cost – depreciation of prior periods) * rate
Here, Rate = 1 – ((salvage / cost) ^ (1 / life))
Depreciation for first and last period is calculated using the following formulas:
First Period: cost x rate x month / 12
Last Period: ((cost – total depreciation periods) * rate * (12 – month)) / 12
- This function throws a Num! error if either of the following condition is true:
- If the given cost or the supplied salvage argument is < 0;
- If the given life or the supplied period argument is ≤ 0;
- If the given [month] argument is ≤ 0 or is > 12;
- If the given period > life (and the [month] argument is omitted);
- If the given period > life+1.
- This function throws #VALUE! Error if any of the given parameters are non-numeric.
Examples:
#DB Example1: Calculate the investment using the DB function for the given project.
In the below table, we are given the data (initial cost, salvage value, life, period) of 6 years. On the basis of the given data, compute the depreciation for an asset where initial cost of $200,000. The asset’s salvage value after a timespan of 5 years is $20,000.
Follow the below-given steps to return the depreciation of an asset for a given period of time using the Excel DB () function:
Step 1: Select a cell to calculate DB
Place your mouse cursor below your data set so you can find the depreciation value for the given details. In our case, we have selected cell C9 of our Excel worksheet.
Refer to the given below image:
STEP 2: Enter the DB function
To calculate the depreciation value, we will start typing the function with the equal to (=) sign followed by the inbuilt DB function. Our formula will become: = DB(
STEP 3: Insert all the parameters
- At first, this function will ask you to specify the cost parameter. Here, we will specify the actual cost of the asset. The formula will be = DB (C3,
- The next argument is Salvage. This parameter should contain salvage value after the asset has been fully depreciated. The formula will be = = DB(C3,C4
- The next argument we will specify the number of periods that you will be depreciating the asset. The formula will be = DB(C3, C4, C5)
- In this parameter, we will specify the period that you wish to calculate the depreciation for. The formula will be = DB(C3, C4, C5, C6)
- At last, we have an optional parameter representing the number of months in the first year of depreciation. If user doesn’t specify this parameter, by default, this function will take 12 months in the first year.
The overall formula becomes: =DB(C3,C4,C5,C6)
Step 4: The DB function will return the output
As a result, the DB function will return the depreciation value of an asset for a given period of time.
Step 5: Drag and Repeat the function to other cells
Select the formula cell and drag the cursor towards the right corner of the rectangular box. You will notice that your cursor will change into a plus (+) icon.
Drag the icon to the rightmost five cells, and the formula will be replicated to all the cells changing the respective values accordingly.
So, using the inbuilt Excel DB function in excel above, we computed the depreciation values for different data sets.
#DB Example2: Calculate the investment using the DB function for the given project.
Let’s solve another example to get a better picture of the DB function. In the below-given data, we are using the same data as given above with an additional month field. For this example, the depreciation value starts 6 months into Year 1.
Follow the below-given steps to the depreciation of an asset for a given period of time using the Excel DB () function:
Step 1: Select a cell to calculate DB
Place your mouse cursor below your data set so you can find the depreciation value for the given details. In our case, we have selected cell C9 of our Excel worksheet.
Refer to the given below image:
STEP 2: Enter the DB function
To calculate the depreciation value, we will start typing the function with the equal to (=) sign followed by the inbuilt DB function. Our formula will become: = DB(
STEP 3: Insert all the parameters
- At first, this function will ask you to specify the cost parameter. Here, we will specify the actual cost of the asset. The formula will be = DB (C3,
- The next argument is Salvage. This parameter should contain salvage value after the asset has been fully depreciated. The formula will be = = DB(C3,C4
- The next argument we will specify the number of periods that you will be depreciating the asset. The formula will be = DB(C3, C4, C5)
- In this parameter, we will specify the period that you wish to calculate the depreciation for. The formula will be = DB(C3, C4, C5, C6)
- At last, we have an optional parameter representing the number of months in the first year of depreciation. If user doesn’t specify this parameter, by default, this function will take 12 months in the first year. In our case the months is 6.
The overall formula becomes: =DB(C3,C4,C5,C6,C7)
Step 4: The DB function will return the output
As a result, the DB function will return the depreciation value of an asset for a given period of time.
Step 5: Drag and Repeat the function to other cells
Select the formula cell and drag the cursor towards the right corner of the rectangular box. You will notice that your cursor will change into a plus (+) icon.
Drag the icon to the rightmost five cells, and the formula will be replicated to all the cells changing the respective values accordingly.
So, using the inbuilt Excel DB function in excel above, we computed the depreciation values for different data sets.
#DB Example3: DB Function returns Num! Error.
Errors and functions run side by side as a single mistake in your function will throw an Excel error. Similarly, any single mistake in arguments or wrong data will give you a Num! error output. We have taken the figures in the below data, but each year contains some mismatch. Let’s see how it impacts our output.
Follow the below-given steps to calculate internal rate of return for irregular cash flows using the Excel DB () function:
Step 1: Select a cell to calculate DB
Place your mouse cursor below your data set so you can type the DB function and get the output. In our case, we have selected cell E6 of our Excel worksheet.
Refer to the given below image:
STEP 2: Type the DB function
In E6, we will start typing the function with the equal to (=) sign followed by the DB function. Therefore, our function will become: = DB(
STEP 3: Insert all the parameters
Repeat the steps we covered in Step 3 of the above two examples. After inserting all the parameters, we will have the following formula:
=DB(C3,C4,C5,C6,C7)
Step 4: The DB function will return the output
As a result, you will get the #NUM! error for all the inputs because of the following reasons:
- In the first cell, the given cost or the supplied salvage argument is smaller than 0;
- In the second cell, the supplied period argument is smaller than 0;
- In the third cell, the given optional month argument is ≤ 0 or is > 12;
- In the fourth cell, the given period > life (and the optional month parameter is omitted);
- In the fifth cell, the given period > life+1.
That’s it! We explored all necessary examples, now go ahead and take the leverage of the inbuilt DB function to compute the depreciation value.
