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.
Depreciation charges calculation using Sum of Years Digits method

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:
(n2 + 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 = (n2 - n) / 2 = (52 - 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 1st Year
= (5 / 15) * (900) = $300

Depreciation Charge for the 2nd Year
= (4 / 15) * (900) = $240

Depreciation Charge for the 3rd Year
= (3 / 15) * (900) = $180

Depreciation Charge for the 4th Year
= (2 / 15) * (900) = $120

Depreciation Charge for the 4th 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.
SYD data worksheet
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.
Final SYD data worksheet

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


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.

Recommended Entrepreneurship Books

the passionate entrepreneurs strategies book by buzzer joseph

The Passionate Entrepreneur’s Strategies: Learn how to discover business ideas that will be successful, grow multiple online income streams, make money from home, attract new and retain customers, applications of SWOT analysis in any business, skills that will help you succeed as an entrepreneur, self-discipline, etc. Lucrative Business Ideas Series Book 1. By Buzzer Joseph. Read more about the book.

how to make money from blogging and affiliate marketing book by buzzer joseph

How to Make Money from Blogging and Affiliate Marketing: Learn the Step by Step Guide of Creating, Growing and Optimizing your WordPress Blog from Scratch for Search Engines (SEO) and How to Make Money through Google AdSense, Affiliate Marketing and Other Strategies for Earning Passive Income Online. Lucrative Business Ideas Series Book 2. By Buzzer Joseph. Read more about the book.

how to make money self publishing kindle books by buzzer joseph

How to Make Money Self-Publishing Kindle Books for Beginners: Learn the Step by Step Guide to Self-Publish a Best Selling Book on Amazon KDP, How to Design Book Covers with Adobe Photoshop, Format, Edit and Proofread your Book Manuscript with Microsoft Word, Promote your Book on Various Online Platforms and Earn Monthly Passive Income Online. Lucrative Business Ideas Series Book 3. By Buzzer Joseph. Read more about the book.

diy grid and off-grid solar pv installation guide by buzzer joseph

Complete Solar PV System Installation Guide: DIY guide on how to install grid-connected and off-grid solar PV systems, how to build solar-powered electric cars, convert conventional cars to electric cars, online and offline ways to make money from solar energy sector (including stocks), electrical wiring and protection system installation guides, plus free resources for skill acquisition and personal development. Lucrative Business Ideas Series Book 4. By Buzzer Joseph. Read more about the book.

how to make money from cryptocurrency trading and investments by buzzer joseph

How to Make Money from Crypto Trading and Investing for Beginners: Step by step guide with screenshots on the basics of blockchain technology, bitcoin and altcoins, crypto security tips, cryptocurrency market analysis (FA and TA), long term and short term trading, how to calculate your profits in excel, legit ways to make money with cryptocurrencies, DeFi yield farming and staking, how to use any centralized and decentralized exchange platform and more, plus crypto investment bonus! Lucrative Business Ideas Series Book 5. By Buzzer Joseph. Read more about the book.

blockchain practical guide kindle book by buzzer joseph

Blockchain Practical Guide: Step by step guide with pictures, on how to monetize the latest blockchain technology trends: crypto trading and tools, especially Ethereum, DeFi, Staking, NFTs, Top 100 Play-to-Earn (P2E) and NFT games, Web 3.0, Metaverse, Virtual Reality (VR), Augmented Reality (AR), blockchain job opportunities, with crypto investment bonus! Lucrative Business Ideas Series Book 6. By Buzzer Joseph. Read more about the book.

how to make money with blockchain web3 apps kindle book by buzzer joseph and kelly joseph

How to Make Money with Blockchain Web3 Apps: Step by Step Guide with Screenshots and Videos on How to Make Money from Blockchain and Web3 Projects – NFTs, Play-to-Earn (P2E) Games, Move-to-Earn (M2E) Apps, Watch-to-Earn (W2E) Apps, SocialFi and GameFi Apps, with crypto investment bonus! Lucrative Business Ideas Series Book 7. By Buzzer Joseph and Kelly Joseph. Read more about the book.

No comments:

Post a Comment

WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!