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.
SUM OF YEARS DIGITS (SYD)
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.
This is part 2 of chapter 5 of the Free Online Excel
Tutorial Series at Microsoft Tutorial website. In this part, I will explicitly
explain how to calculate depreciation charges on fixed assets using the Sum of
Years Digits Method and the formulas to calculate it both manually and using a
spreadsheet software like Microsoft Excel 2016, 2013 and other lower versions. I
will also give you some practical exercises on depreciation to test your
knowledge on this chapter.
For the manual calculation using the SYD method, the syntax or formula is:
(Year of Calculation
/ Sum of Years Digits) * (Cost – Salvage Value)
Sum of Years Digits is
calculated with this formula:
Where n = Useful
life of the asset.
Year of Calculation is
the particular year you want to find its depreciation charge value.
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.
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 –
So the manual formula for SYD method could be re-written as:
(Year of Calculation
/ Sum of Years Digits) * Actual Cost
The MS Excel Syntax for the SYD method is:
Life, Period) then press the enter key
ILLUSTRATION OF THE SUM OF YEARS DIGITS METHOD
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.
Useful life (n) = 5 years
Actual Cost = Cost – Salvage = $1000 - $100 = $900
Sum of Years Digits = (n2 - n) / 2 = (52
- 5) / 2 = 15
The depreciation charges for the five years of useful life
are calculated as follows using the above given formula and substituting values
for the 1st Year
= (5 / 15) * (900) =
for the 2nd Year
= (4 / 15) * (900) =
for the 3rd Year
= (3 / 15) * (900) =
for the 4th Year
= (2 / 15) * (900) =
for the 4th Year
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
EXCEL FOR THE SYD METHOD;
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 Depreciation
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
Recommended MS Excel Textbook
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.
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
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