Apart from the SLN and DDB methods of calculating depreciation
charges on fixed assets like furniture, machinery, tools, etc. This method
works on a basis of age as you will see later in this tutorial. Depreciation is
a very important topic in accounting and business management.

The Sum of Years Digits (SYD) method states that an asset
contributes more to the profit of a company or business organization when it is
new than when it is old. Therefore, a higher depreciation charge should be
charged at the early stage of the asset. The Sum of Years Digits Method is very
similar to the Double Decline Balance Method.

In the **previous part (Part 1)**, I explained how to use the Straight Line (SLN) method and the
Double Decline Balance (DDB) Method to calculate depreciation charges both
manually and using MS Excel. Also see the **full tutorial course content of this Microsoft Excel Tutorial Series**.

Where **n **= Useful
life of the asset.^{}

If there is residual or salvage value, you must deduct it
from the cost of the assets so that you get the **Actual Cost** which is **Cost –
Salvage Value**.

So the manual formula for SYD method could be re-written as:

For the Sum of Years Digits method, I will show you a more practical
example of how to calculate the depreciation of an asset using both the manual
formula and MS Excel Syntax.

An asset was bought for $1000 with 5 years useful life and a
scrap value of $100. Compute its depreciation schedule using the Sum of Years
Digits (SYD) method.

Cost = $20000

Salvage value = $5000

Useful life (n) = 5 years

Actual Cost = Cost – Salvage = $1000 - $100 = $900

Sum of Years Digits = (n^{2} - n) / 2 = (5^{2}
- 5) / 2 = 15

5 + 4 + 3 + 2 + 1 = 15

The depreciation charges for the five years of useful life
are calculated as follows using the above given formula and substituting values
directly:

= (5 / 15) * (900)** =
$300**

= (4 / 15) * (900)** =
$240**

= (3 / 15) * (900)** =
$180**

= (2 / 15) * (900)** =
$120**

= (1 / 15) * (900)** =
$60**

If you calculated the value correctly, you should get back
the salvage after deducting the depreciation charges from the **Cost**

i.e. $1000 – ($300 + $240 + $180 + $120 + $60) = $1000 -
$900 = $100 = **Salvage Value**

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 B6: **=SYD(B2,
B3, B4, B5)**

In cell C6: **=SYD(C2,
C3, C4, C5)**

In cell D6: **=SYD(D2,
D3, D4, D5)**

In cell E6: **=SYD(E2,
E3, E4, E5)**

In cell F6: **=SYD(F2,
F3, F4, F5)**

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 answer you got when you used the manual method should
match with the ones you got using the MS Excel SYD syntax because there was no
approximation.

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

Let me now test your knowledge.

Please try to do these practical exercises on your own. It will
go a long way to help you.

1. What are the causes of depreciation?

2. State 3 reasons why a provision for depreciation is charged
on a fixed asset.

3. Johnson Enterprise purchased a delivery van for $50000. The van
has an estimated useful life of 8 years with nil scrap value. Calculate the
annual depreciation charge using the Straight Line Method.

4. Joe-Links Services bought an asset at a cost of $20000. Other
expenses incurred in the charge and installation amounted to $5000. The company
depreciates its assets at the rate of 20%. On cost per annual on a straight
line basis, calculate he depreciation charge.

5. Joe-Links Services purchased some equipment for $10000 with
5 years useful life after which it becomes a scrap. Calculate the depreciation
charges for the period of 4 years assuming 20% was given as depreciation rate using
the Double Decline Balance (DDB) Method.

6. An asset was bought for $20000 with useful life of 5 years
and a salvage value of $1000. Compute its depreciation charge using Sum of
years Digits (SYD) Method.

7. Mr. Wilson bought a set of computers at a price of $250000
each with a scrap value of $15000 after 3 years of usage. Prepare a
depreciation table to calculate the reduction in value of the computers in
straight line basis with variable periods (in years) 4, 5, 6, 7 and 8 and a
scrap values of $5000, $7000, $10000, $13000 and $15000 respectively.

This
is the end of chapter 5 of this online tutorial series. In the **next chapter (chapter 6)**, I will
explain the PMT function and What If Analysis and Excel Powerful tools – Goal Seek, Scenarios and
Data tables.

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