How to Apply Declining Balance Depreciation Formula in Excel

Get FREE Advanced Excel Exercises with Solutions!

All corporate entities acquire assets for their own purposes. The assets could be either objective or abstract i.e., services. It uses the assets it bought for a while. However, the asset value changes after the customary number of years. With time, it gets smaller in value because when we try to sell an old product, we get less money. In this article, we will discuss the methods to calculate the declining balance depreciation with a formula in Excel.


What Are Declining Balance Depreciation Factors?

There are 3 major factors in declining balance depreciation calculation.

  • Initial Cost: The term “Initial Cost” refers to the sum of money needed for the major building, refurbishment, or expansion of a facility.
  • Salvage Value: Salvage value, which is dependent on what a firm anticipates getting in barter for such an asset after the end of its lifecycle, is the expected book value of an asset after depreciation is complete.
  • Useful Life (yr): The term “Useful Life” refers to an estimation of the typical time period during which an asset is still regarded as usable just before its value completely depreciates.

How to Apply Declining Balance Depreciation Formula in Excel: 6 Examples

In Excel, we can calculate Declining Balance Depreciation using formula in 6 different ways.

In this dataset, we’ve defined the Initial Cost of a certain asset for a corporation as $100,000. 10 years are assumed for the Useful Life, and $10,000 is assumed for the Salvage Value.

A dataset, with Initial Cost, Useful Life, and Salvage Value: Calculate Declining Balance Depreciation with Formula


1. Using DB Function in Excel

Assets do not depreciate linearly over time. The number of depreciations changes every year. To consider this fact, Excel has the built-in DB function to calculate depreciation. DB stands for Declining Balance.

📌 Steps:

  • To start the calculation, the important thing to remember is that we can calculate depreciation using the Declining Balance method by entering a formula. However, Excel has a built-in function to make things easier for us. The name of the function is similar to the method name, DB function.
  • Now we will insert the following formula in cell C9 
=DB($C$4,$C$5,$C$6,B10)

Here,

$C$4= Initial Cost

$C$5= Salvage Value

$C$6= Useful Life (yr)

B10= Period

The DB function performs the following calculations.

  • Fixed-rate in DB method = 1 – ((salvage Value/Initial cost) ^ (1 / life))
  • So, Fixed rate= 1 – (10000/10,0000)^(1/10) = 1 – 0.7943282347 = 0.206
  • Depreciation value period 1 = 100,000 * 0.206 =20,600
  • Depreciation value period 2 = (10,000 – 2,060.00) * 0.206 = 16356

Calculation of depreciation using Declining Balance Depreciation Formula

  • Now, we will drag the fill handle downwards to calculate depreciation in different periods of the year. As in the following image we have calculated depreciation for the first year, the second year the tenth year.

Drag the fill handle downwards after emtering Declining Balance Depreciation Formula

  • For Period 0, we have no depreciation. In that case, the value of a certain product will be the same as the Initial Cost. So, we have entered the Initial Cost in the cell of D9.

Period 0 have no depreciation

  • As we have calculated depreciation for Period 1, now we will subtract depreciation from the Initial value. After subtracting the value of the product will be 79,400 and the simple subtraction formula will be the following:
=D9-C10

Here,

D9= Initial Cost

C10= Depreciation

D9-C10= Asset Value in Period 1

Subtracting Declining Balance Depreciation from the Initial value

  • Now we will get the asset value for the rest of the cells if we drag down the fill handle like in the below image.

Getting the asset value for the rest of the cells


2. Applying DDB Function (Double Declining Balance Depreciation Formula) in Excel

In the second method, we are going to use the DDB function or the Double Declining Balance function. While using this function to calculate depreciation, the double declining method may not return the salvage value at the end-of-life span of a product. Let’s follow the instructions below to calculate double declining depreciation in Excel using the DDB function!

📌 Steps:

  • Now in cell C9, we will enter the following formula to calculate depreciation:
=DDB($C$4,$C$5,$C$6,B10)

Here,

$C$4= Initial Cost

$C$5= Salvage Value

$C$6= Useful Life (yr)

B10= Period

The DDB function performs the following calculations.

  • Fixed-rate in DDB method = 2* (salvage Value/ Initial cost) 
  • So, Fixed rate= 2* (10000/10,0000) = 1 / 5 = 0.2
  • Depreciation value period 1 = 100,000 * 0.2 =20,000
  • Depreciation value period 2 = (10,0000 – 20,000) * 0.2 = 16,000 and so on.

Applying DDB Function (Double Declining Balance Depreciation Formula) in Excel

  • After entering a formula, our current task will be dragging the fill handle downwards to copy the formula for the rest of the periods.
  • Now like in the following image we will have depreciation in each corresponding year.

have depreciation in each corresponding year

  • Now we will subtract depreciation from the Initial value, as we have calculated depreciation for Period 1. After subtracting the value of the product will be 79,400 and the simple subtraction formula in cell D10 will be the following:
=D9-C10

Here,

D9= Initial Cost

C10= Depreciation

D9-C10= Asset Value in Period 1

  • If we now drag down the fill handle like in the figure below, we will obtain the asset value for the remaining cells.

Drag down the fill handle like in the figure

Read More: How to Use Formula to Calculate Car Depreciation in Excel


3. Using VDB Function or Variable Declining Balance Function in Excel

Depreciation actually varies with the product we are concerned with. For example, a bulldozer’s depreciation will be different from a real estate property. Moreover, assets do not depreciate linearly over time. The depreciation rate may change every year. To consider this fact, Excel has the built-in VDB function or Variable Declining Balance function to calculate depreciation.

📌 Steps:

  • The Calculation process of the VDB function is similar to the DDB function. But VDB turns to the straight Line method to reach salvage value.
  • Now in cell C9, we will enter the following formula to calculate depreciation:
=VDB($C$4,$C$5,$C$6,B10-1,B10)

Here,

$C$4= Initial Cost

$C$5= Salvage Value

$C$6= Useful Life (yr)

B10-1= Starting Period argument will be 1-1=0

B10= Ending Period argument will be 1

Entering the formula to calculate depreciation

  • Our current objective is to slide the fill handle downward to replicate the formula for the remaining periods after entering the formula.
  • Depreciation in the corresponding year is seen in the accompanying image.

Depreciation in the corresponding year is seen in the accompanying image

  • We will now deduct depreciation after calculating it for Period 1 from the initial value. The value of the product will be 80,000 after subtraction, and the straightforward subtraction formula is as follows:
=D9-C10

Here,

D9= Initial Cost

C10= Depreciation

D9-C10= Asset Value in Period 1

Deducting depreciation after calculating it

  • If we drag down the fill handle like in the figure below, we will now see the asset value for the remaining cells.

Dragging down the fill handle like in the figure

Read More: How to Calculate Straight Line Depreciation Using Formula in Excel


4. Applying SLN Function

SLN or Straight Line function is the simplest method for calculating depreciation. Asset depreciation is not a linear process. However, taking into account the fact that depreciation may be calculated easily in the SLN approach, Excel contains a built-in SLN function.

📌 Steps:

  • At this moment in cell C9, we will enter the following formula to calculate depreciation:
=SLN($C$4,$C$5,$C$6)

Here,

$C$4= Initial Cost

$C$5= Salvage Value

$C$6= Useful Life (yr)

B10= Period

The SLN function performs the following calculations.

  • Deprecation Value = (100,000 – 10,000) / 10 = 9000.00.
  • If we subtract this value 10 times, the asset depreciates from 100,000 to 10,000 in 10 years.

Entering the following formula to calculate depreciation

  • For the remaining periods after entering the formula, our current goal is to slide the fill handle lower to duplicate the formula.
  • The accompanying picture shows depreciation in the relevant year.

Sliding the fill handle lower to duplicate the formula

  • Now that we have obtained depreciation for Period 1, we will subtract it from Initial Cost. After subtraction, the product’s value will be 91,000, and the simple subtraction formula is as follows:
=D9-C10

Here,

D9= Initial Cost

C10= Depreciation

D9-C10= Asset Value in Period 1

Obtained depreciation for Period 1 will be subtracted it from Initial Cost

  • The asset value for the remaining cells will be obtained if we now drag down the fill handle as shown in the image below.

drag down the fill handle


5. Using SYD Function

A basic way for determining depreciation is the SYD (Sum of Years’ Digits) calculation. Depreciation of assets is a nonlinear process. However, Excel has a built-in SYD function since it can be used to compute depreciation quickly using the SYD technique.

📌 Steps:

  • We will now input the following formula to compute depreciation in cell C9.:
=SYD($C$4,$C$5,$C$6,B10)

Here,

$C$4= Initial Cost

$C$5= Salvage Value

$C$6= Useful Life (yr)

B10= Period

The SYD function performs the following calculations.

  • 55 years are the sum of the years 10 + 9 + 8 + 7 + 6 + 5 + 4 + 3 + 2 + 1.
  • Losses Total  Value = (100,000 – 10,000) = 90,000.00.
  • Rate of depreciation= Remaining useful year/ sum of the years
  • Period 1 depreciation value = 10/55 * 90,000 = 16,364.
  • Period 2 depreciation value = 9/55 * 90,000 = 14,727

Input the following formula to compute depreciation

  • Our current goal for the remaining periods after entering the formula is to slide the fill handle lower to replicate the formula.
  • The accompanying image depicts the year’s depreciation.

The accompanying image depicts the year's depreciation

  • Finally, we got the depreciation for Period 1, we will deduct it from Initial Cost. After subtraction, the product’s value will be 83,636, and the simple subtraction formula is as follows:
=D9-C10

Here,

D9= Initial Cost

C10= Depreciation

D9-C10= Asset Value in Period 1

got the depreciation for Period 1, we will deduct it from Initial Cost

  • Finally, dragging down the fill handle as seen in the figure below will yield the asset value for the remaining cells.

Dragging down the fill handle as seen in the figure


6. Applying Units of Production Formula of Depreciation for Declining Balance

When we consider obsolescence in the calculation of depreciation, the units of production depreciation method are a handy tool to make the analysis. We have seen that the depreciation changes every year. Moreover, like in the tech industry, products get obsolete with time and the availability of necessary parts of that product in the market. But Excel has no built-in function to calculate depreciation using units of production depreciation. So, we will do this by entering a formula.

📌 Steps:

  • We will now input the following formula to compute depreciation in cell C9:
=(($C$4-$C$5)/$C$7)*C11

Here,

$C$4= Initial Cost

$C$5= Salvage Value

$C$7= Estimated Units to be Produced

C11= Actual Unit Produced

Applying Units of Production Formula of Depreciation for Declining Balance in Excel

  • Now, we will drag the fill handle down to complete the series.

Dragging the fill handle down to complete the series

  • Now, we have got a depreciation for Period 1, and we will minus it from Initial Cost. And then, the product’s value will be 93,250, and the formula of subtraction is as follows:
=D9-C10

Here,

D9= Initial Cost

C10= Depreciation

D9-C10= Asset Value in Period 1

Applying Units of Production Formula of Depreciation for Declining Balance in Excel

  • At last, we will drag the Fill Handle downward to make the end of the calculation of depreciation using the units of production method. So, you got the depreciation of the declining balance by using the production method’s formula in excel.

Applying Units of Production Formula of Depreciation for Declining Balance in Excel

Read More: How to Calculate Accumulated Depreciation in Excel


Some More Formulas to Calculate Declining Balance Depreciation in Excel

1. Discounted Cash Flow Formula

By discounting the anticipated future cash flows, the discounted cash flow (DCF) analysis approach is being used to value investments. In the investing sector as well as corporate financial management, DCF analysis is frequently employed since it may be used to evaluate a stock, company, or project, among many other assets or activities.


2. Insurance Policy Method

There are many commonalities between the two approaches, including the investment of depreciation as well as the replacement of equipment with the proceeds at the end of its useful lifespan. Though one significant contrast between the two approaches is: Inside the insurance policy method, services are covered for the necessary amount, and the first insurance premium is paid each year. And being under the sinking fund method, investments are made at the end of each year after the acquisition of assets. The method of an insurance policy is less dangerous since the maturity amount will undoubtedly be obtained; however, the method of a sinking fund might be riskier because the market value of the assets can fluctuate.


3. Annuity Method

The goal of the annuity method of depreciation is to achieve a steady rate of return on a property. It is most frequently applied to much more pricey capital assets with longer estimated useful lives.


4. WDV Method of Depreciation Formula

When computing depreciation, the written-down value technique, or WDV method, is a handy tool to deal the depreciation. The Diminishing Balance Method or Declining Balance Method are other names for this method. There are two approaches that are typically used to calculate depreciation.

Written Down Value (WDV), Straight Line Technique (SLM) Company policy does not put any restrictions on the use of any method. The Income-tax Act mandates that only the WDV technique be used to determine depreciation, despite the fact that Companies often utilize SLM.

When estimating assets’ net worth each year, we use this technique for a constant rate of depreciation.

Written Down Value Method = (Cost of Asset – Salvage Value of the Asset) * Rate of Depreciation (%)

How to Choose a Suitable Method for a Company to Calculate Depreciation

Most public companies use the SLN method to calculate depreciation. Moreover, the depreciation of buildings and furniture is calculated through a straight-line approach.

On the other hand, the SYD or Sum of Years’ Digits method depreciates more in the earlier lifespan of a product than the later period of that product.

Usually, to calculate the depreciation of transportation, we apply the SYD or Sum of Years’ Digits method.


How to Use an Excel Template for Monthly Depreciation Calculation

Determining cumulative depreciation is made simple and easy to understand using the straight-line technique. The depreciation under this technique stays constant throughout the asset’s lifetime for each succeeding year. Following are the steps to compute depreciation using this approach:

  • The amount that can be depreciated is calculated by deducting the asset’s cost from its scrap value.
  • Cost without the residual value equals total depreciation over the lifespan of a product.
  • Now, the entire amount of depreciation needs to be divided by the asset’s lifetime in years.
  • Total depreciation divided by useful lifetime equals annual depreciation.
  • Lastly, after getting the value for the year using this technique, some of our Excel users may prompt to calculate the monthly depreciation, which can be obtained after dividing it by 12.

An Excel Calculator for Depreciation of Fixed Asset

At the last moment, to make the workbook Excel user-friendly we have added a Depreciation Calculator where you can quickly calculate your depreciation of a certain product. To accomplish the process, you have to put your data for say Initial Cost, Useful Life, and Salvage Value in the Depreciation Calculator. Moreover, the Periodor the nth year is required to complete the calculation. A sample of a Depreciation Calculator is given in the following image.

a Depreciation Calculator where you can quickly calculate your depreciation of a certain product


Download Practice Workbook

You can download the practice workbook from the following download button.


Conclusion

Follow these steps and stages to the declining balance depreciation formula in Excel. You are welcome to download the workbook and use it for your own practice. If you have any questions, concerns, or suggestions, please leave them in the comments section.


Related Articles


<< Go Back to Depreciation Formula In Excel|Excel Formulas for Finance|Excel for Finance|Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Ikram Amit
Al Ikram Amit

Al Ikram Amit, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a key Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Amit not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently delivering exceptional, high-quality content that adds significant... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo