How To Calculate Depreciation Charges Using Straight Line & Double Decline Balance Methods In Excel

Almost all fixed asset depreciate (with land an exception). So every business should know how to calculate the depreciation charges on their fixed assets. Depreciation can be calculated manually but a spreadsheet management software like Microsoft Excel simplifies the whole job for you.

WHAT IS DEPRECIATION?

Depreciation can be seen as the gradual or total fall in the value of a fixed asset. It can also be defined as the wear and tear of any fixed asset which results to loss of value.

In the other way, depreciation is also seen as that part of an asset consumed annually throughout the period the asset is used by the company.

This is the chapter 5 of the Free Microsoft Excel Online Tutorial Series at Microsoft Tutorials. This chapter 5 has two parts and this is part 1. In this part, I will explicitly explain Depreciation, Causes of Depreciation and how to calculate depreciation charges using the Straight Line Method and Double Decline Balance Method manually and with MS Excel syntax step by step. Each of these methods has a unique formula as you will see in this part 1.

In Part 2, I will explain how to calculate depreciation charges using the Sum of Years Digits Method manually and with MS Excel. 

In the previous chapter (chapter 4), I explained how to design Stock Ledger Account in MS Excel. Also see the full tutorial course content of this FREE Microsoft Excel Tutorial Series.

Some fixed assets that depreciate include:

1. Office Equipment

2. Plant and Machinery

3. Land and Building

4. Furniture and Fittings

5. Tools, etc.

FACTORS / CAUSES OF DEPRECIATION

1. USAGE:

Continuous use of a fixed asset results in wear and tear which leads to loss in value of the asset.

2. PASSAGE OF TIME (DURATION):

Whether an asset is in use or not, its value tends to depreciate with time.

3. OBSOLESCE (OUT DATED OR OUT OF FASHION):

A fixed asset tends to depreciate if it is no longer produced or used. This type of depreciation is mainly caused by change in technology. For example, a change in the model of cars, phone, etc.

4. PHYSICAL FACTORS:

Some physical factors like rain, sunshine, etc. cause damage to assets like building, plants and machinery, etc. and as a result leads to loss in value.

FACTORS TO CONSIDER BEFORE CHARGING DEPRECIATION

Before you go ahead to charge for the depreciation of your fixed assets, there are many factors you really need to consider. I will be using some of these terminologies in this chapter. So you need to get used to them now. 

Some of these factors include:

1. COST:

This is the amount required to procure or purchase an asset which must be greater or equal to the salvage or scrap value.

2. SALVAGE OR SCRAP VALUE:

This is the worth or the amount an asset can be sold after its useful life. It is also known as the Residual Value of an asset.

3. ESTIMATED USEFUL LIFE:

This is the length of time an asset is expected to be in use before it becomes a scrap. In other words, estimated useful life is the number of periods (which can be in days, weeks, months or years) it takes an asset to depreciate to its useful life.

METHODS OF CALCULATING DEPRECIATION

There are many different methods of calculating depreciation charges on a fixed asset, but I will only list and explain the three most common methods and also show you how to do the calculation both manually and using Microsoft Excel Spreadsheet.

These 3 major methods include:

1. Straight Line (SLN) Method.

2. Diminishing Balance (DB) Method or Double Decline Balance (DDB) Method.

3. Sum of Years Digit (SYD) Method.

STRAIGHT LINE (SLN) METHOD:

In this method, a specific amount of money is charged year after year as the asset depreciates over its useful life.

For the manual calculation, the syntax or formula is:

(Cost – Scrap Value)/Useful Life

The SLN MS Excel Syntax is:

=SLN(Cost, Salvage, Life) then press the enter key

Where:

Cost is the amount you bought the asset.

Salvage or Scrap Value is the worth of the asset after its useful life.

Life or Useful Life is the time that the asset takes to attain its scrap value.

PRACTICAL ILLUSTRATION OF THE STRAIGHT LINE METHOD

Now, let me show you a practical example of how to calculate the depreciation of an asset using the Straight Line Method.

QUESTION:

An Android phone was bought for $20000 with 5 years useful life after which it becomes a scrap with $5000 value. Calculate the annual depreciation charge using the straight line method.

SOLUTION:

SOLVING SLN METHOD MANUALLY;

Cost = $20000

Salvage value = $5000

Useful life = 5 years

Therefore Annual Depreciation Charge = (Cost – Scrap Value)/Useful Life

= ($20000 - $5000) / 5 = ($15000) / 5 = $3000

This means that an annual depreciation charge of $3000 will be charged on the asset for 5 years.

USING MICROSOFT EXCEL;

Prepare your worksheet as shown below.

Then type this formula into cell B4:

=SLN(B1, B2, B3) then press the enter key.

Excel returns $3000 in cell B4.

NOTE:

You should get the same answer you got when you solve it manually, which

DOUBLE DECLINE BALANCE (DDB) METHOD:

Under this method, a specific percentage is charged to the balance of the asset as the asset depreciates. This means that the depreciation charge on the asset will be higher in the early age of the asset than on its later age.

For the manual calculation, the syntax or formula is:

(Rate * Cost)/(Useful Life * 100)

The DDB MS Excel Syntax is:

=DDB(Cost, Salvage, Life, Period, Factor) then press the enter key

Where:

Cost is the amount you bought the asset.

Salvage or Scrap Value is the worth of the asset after its useful life.

Life or Useful Life is the time that the asset takes to attain its scrap value.

Period is the age of the asset.

Rate or Factor is the Depreciation rate of the asset usually expressed in percentage.

PRACTICAL ILLUSTRATION OF THE DOUBLE DECLINE BALANCE (DDB) METHOD

This time, I will show you a practical example of how to calculate the depreciation of an asset using the Double Decline Balance Method.

QUESTION:

Philips Bakery bought an asset of $200000 with 6 years useful life after which it becomes a scrap. Calculate the depreciation charge for the period of 4 years assuming 25% was given as the depreciation charge using the DDB method

SOLUTION:

SOLVING DDB METHOD MANUALLY;

Cost = $200000

Useful life = 6 years

Rate = 25%

The depreciation charges for the four years are calculated as follows using the above given formula and substituting values directly:

Depreciation Charge for the 1^st Year

= (25 * 200000) / (6 * 100) = $8333.33

Worth of Asset at the beginning of the 2^nd Year

= (200000 – 8333.33) = $191666.67

Depreciation Charge for the 2^nd Year

= (25 * 191666.67) / (6 * 100) = $7986.11

Worth of Asset at the beginning of the 3^rd Year

= (191666.67 – 7986.11) = $183680.56

Depreciation Charge for the 3^rd Year

= (25 * 183680.56) / (6 * 100) = $7653.36

Worth of Asset at the beginning of the 4^th Year

= (183680.56 – 7653.36) = $176027.20

Depreciation Charge for the 4^th Year

= (25 * 176027.20) / (6 * 100) = $7334.47

Worth of Asset at the end of the 4^th Year

= (176027.20 – 7334.47) = $168692.73

Calculation Tips:

Current Worth or Value = Previous Worth - Previous Depreciation Charge

Therefore the annual depreciation charge for the 1^st, 2^nd, 3^rd and 4^th year are:

$8333.33, $7986.11, $7653.36 and $7334.47 respectively

USING MICROSOFT EXCEL;

Prepare your worksheet and enter the data as shown below.

Place your cell pointer in the following specified cells and type the following syntaxes:

For the Subsequent Worth:

In cell C2: =B2 – B7

In cell D2: =C2 – C7

In cell E2: =D2 – D7

For the Depreciation Charges:

In cell B7: =DDB(B2, 0, B4, B5, B6)

In cell C7: =DDB(C2, 0, C4, C5, C6)

In cell D7: =DDB(D2, 0, D4, D5, D6)

In cell E7: =DDB(E2, 0, E4, E5, E6)

If you got everything correctly, your worksheet should now look like the one shown below.

CONCLUSION NOTES:

Excel may display an error message, check the error code and make the necessary corrections, revise Chapter 1 of Part 3 where I explained MS Excel error messages and their solutions in detail now.

The depreciation charge values for the 4 years using MS Excel DDB syntax are slightly different from the values gotten when manually. The difference results from the approximation you make when solving it manually. When solving in real life challenges, avoid approximating values. Use the values exactly as given by your calculator.

This is the end of part 1 of chapter 5 of this online tutorial series. 

In the next part (Part 2 of chapter 5), I will explain how to calculate depreciation charges using the Sum of Years Digits and then give you some practical exercises.

Was this tutorial helpful to you?

Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.

Also click Here to subscribe for free so that you will get our latest game updates in your email.