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 1st Year
= (25 * 200000) / (6 * 100) = $8333.33
Worth of Asset at the
beginning of the 2nd Year
= (200000 – 8333.33) =
$191666.67
Depreciation Charge
for the 2nd Year
= (25 * 191666.67) / (6 * 100) = $7986.11
Worth of Asset at the
beginning of the 3rd Year
= (191666.67 – 7986.11) = $183680.56
Depreciation Charge
for the 3rd Year
= (25 * 183680.56) / (6 * 100) = $7653.36
Worth of Asset at the
beginning of the 4th Year
= (183680.56 – 7653.36) = $176027.20
Depreciation Charge
for the 4th Year
= (25 * 176027.20) / (6 * 100) = $7334.47
Worth of Asset at the
end of the 4th 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 1st,
2nd, 3rd and 4th 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.
No comments:
Post a Comment
WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!