How To Calculate Depreciation Charges Using The Syd Method In Ms Excel

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) METHOD

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.

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.

For the manual calculation using the SYD method, the syntax or formula is:

(Year of Calculation / Sum of Years Digits) * (Cost – Salvage Value)

Where:

Sum of Years Digits is calculated with this formula:

(n² + n) /2

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.

NOTES:

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:

(Year of Calculation / Sum of Years Digits) * Actual Cost

The MS Excel Syntax for the SYD method is:

=SYD(Cost, Salvage, Life, Period) then press the enter key

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

QUESTION:

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.

SOLUTION:

SOLVING MANUALLY;

Cost = $20000

Salvage value = $5000

Useful life (n) = 5 years

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

Sum of Years Digits = (n² - n) / 2 = (5² - 5) / 2 = 15

OR

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:

Depreciation Charge for the 1^st Year

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

Depreciation Charge for the 2^nd Year

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

Depreciation Charge for the 3^rd Year

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

Depreciation Charge for the 4^th Year

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

Depreciation Charge for the 4^th Year

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

NOTES:

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

USING MICROSOFT 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 Charges:

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.

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

PRACTICAL EXERCISES ON DEPRECIATION

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.