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.

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**.

1. Office Equipment

2. Plant and Machinery

3. Land and Building

4. Furniture and Fittings

5. Tools, etc.

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

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

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.

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.

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:

This is the amount required to procure or purchase an asset which must be greater or equal to the 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.

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.

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.

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

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

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.

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.

Prepare your worksheet as shown below.

Then type this formula into cell B4:

Excel returns $3000 in cell B4.

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

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.

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

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

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:

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

= (200000 – 8333.33) =**
$191666.67**

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

= (191666.67 – 7986.11) =** $183680.56**

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

= (183680.56 – 7653.36) =** $176027.20**

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

= (176027.20 – 7334.47) =** $168692.73**

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

Prepare your worksheet and enter the data as shown below.

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

In cell C2: **=B2 – B7**

In cell D2: **=C2 – C7**

In cell E2: **=D2 – D7**

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.

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.

##
Recommended MS Excel Textbook

##

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.

Get this book (Kindle format): **Designing Professional Spreadsheet Management Systems Using Microsoft Excel 2013 and 2016**.

Click **Here** to know more about the book.

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.

Labels: Microsoft Excel