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

## Recommended MS Excel Textbook

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

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

In the
Get this book (Kindle format):

**Designing Professional Spreadsheet Management Systems Using Microsoft Excel 2013 and 2016**.**Here**to know more about the book.

**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.
## No comments:

## Post a Comment

WHAT'S ON YOUR MIND?

WE LOVE TO HEAR FROM YOU!