Download Sum Of Years Depreciation Calculator Template in Excel, OpenOffice Calc & Google Sheets

Download the Sum of Years Depreciation Calculator Template in Excel, OpenOffice Calc & Google Sheets to depreciate higher costs on the asset in the initial years.

Additionally, this template calculates the applicable depreciation percentage for the first year. It also provides the Depreciation Schedule for the life span of the asset.

Key Takeaways

• Sum of Years’ Digits (SYD) is an accelerated depreciation method where higher depreciation is charged in the initial years.
• It allocates depreciation based on the remaining useful life of the asset.
• The total of depreciation percentages across all years always equals 100%.
• Suitable for assets that lose value quickly in early years (e.g., vehicles, machinery).
• Excel provides a built-in SYD function to automate calculations.
• Helps in tax planning by reducing taxable income in early years (where applicable).

What Is Sum of Years Depreciation?

Sum of Years Depreciation is one of the accelerated depreciation methods in which the asset depreciates for a higher value in the initial years as compared to later years.

Under this method, you need to first add the digits for each year. For example, if the life span is 3 years, then 3 + 2 + 1 = 6.

Starting from the last year, in reverse chronology, divide the year by the sum of years to determine the depreciation percentage.

For example, the first year’s depreciation rate will be 3/6 X 100 = 50.00%, the second year 2/6 X 100 = 33.33%, and the last year will be 1/6 X 100 = 16.67. The sum of depreciation rates for all years should always be 100%.

(50.00 + 33.33 + 16.67)=100.

Businesses use this method as it provides higher depreciation in earlier years as compared to later years. Moreover, an asset loses most of its value in the beginning year of its useful life.

The classic example for such an asset is a company vehicle. Furthermore, businesses use this for assets that are heavily used when they are new.

Formula To Calculate Sum of Years Depreciation

Follow the below-given steps to calculate depreciation by the Sum of Years Depreciation Method:

1. Add the digits for each year. (3+2+1= 6)
2. Starting from the last year in reverse chronology, divide the year by the sum of years to determine the depreciation percentage. 3/6, 2/6, 1/6 for 1st, 2nd and 3rd years respectively.
3. Multiply the depreciation rate by the value of the asset less the salvage value. (Cost Value – Scrap Value)

Sum of Years Depreciation = (Cost Value – Scrap Value) X (Year / Sum of Years)

Microsoft Excel includes the SYD Function for calculating sum-of-years depreciation for a given asset. The syntax for the SYD function is as follows:

=SYD (cost, salvage, life, period)

Where:

Cost = Purchase price + Addtional costs incurred on asset to acquire and run.

Salvage Value = The estimated value of asset at the end of the life span.

Life = Estimated total number of years the asset will be in working condition.

Period = Year for which the depreciation is calculated.

How To Calculate Partial Depreciation?

Partial Depreciation means calculating proportionate depreciation on the asset depending on the date of purchase of the asset during the year.

Generally, assets are bought during the year and not always bought or sold at the beginning of the accounting year. Hence, we need to partially depreciate the asset for that particular year.

Use the formula given below to calculate partial depreciation.

Formula To Find Partial Depreciation

Partial Depreciation = (Annual Depreciation/ 12) X No of months

Where:

Annual Depreciation/12 gives you monthly depreciation of the asset and then multipliy it with no of months.

Download Sum of Years Depreciation Calculator (Excel, OpenOffice Calc & Google Sheets)

We have created a simple and easy Sum of Years Depreciation Calculator with predefined formulas and functions. Just insert a few details, and it will prepare the depreciation schedule for the given asset.

Download your desired format and start using it.

Microsoft Excel OpenOffice Calc Google Sheet

Additionally, you can download other accounting templates like Straight-Line Depreciation Calculator, Declining Balance Depreciation Calculator, Accounts Payable Template, Accounts Receivable Template, Profit and Loss Statement Template, Balance Sheet Template, etc.

Moreover, you can download other HR templates like Payroll Template With Attendance, Salary Slip Excel Template India, Simple Salary Sheet, and Employee Salary Sheet depending on the company’s requirements.

Feel free to contact us for the customization of this template as per your requirement. We also design new templates based on your needs. You can hire us for our services on Fiverr or directly contact us at info@msofficegeek.com.

Contents of Sum of Years Depreciation Calculator

This template consists of two sections: the Data Input section and the Depreciation schedule. Let us understand each section in detail.

Data Input Section

The data input section consists of the following heads:

Value of Asset
Additional Costs Of Set-up
Total Value
Life Span in Years (Estimated)
Estimated Scrap Value
Actual Scrap Value
Difference
Total Depreciable Value
Book Value at the end of Life Span
First Year Depreciation
First Year Depreciation %

Enter the cost of the asset and any additional costs incurred to make it functional. Leave it blank if there are no additional costs. This will calculate the total value of an asset for you.

Salvage value is the end value of an asset after the life span is over. Life span is the number of years an asset is expected to work.

The cell below this shows the difference between the estimated scrap value and the actual scrap value. It can either be below or above the estimated value. Thus, if the difference is 0, then the asset was sold for the estimated salvage price.

Suppose it is sold for a value higher than the estimated scrap value; then the business makes a profit. On the contrary, if the asset is sold for a price less than the estimated scrap price, then it is a loss.

This is depreciation profit/loss, not actual profit/loss. It is the gain/loss against the estimated value of the asset. If it is profit, the last line will turn green and display the word “Profit”, “Loss” if it is a loss and turn red, and 0 if it is NIL.

The total depreciable value is the cost value minus the salvage value. Book value at the end of the life span is the cost price minus accumulated depreciation.

Moreover, the depreciation rate is calculated following the above-mentioned formula. The depreciation rate changes every year as it is calculated based on the year for which depreciation is calculated.

Sum of Years Depreciation Schedule

This section is auto-populated based on the data inserted in the above section. It displays the following:

Year
Depreciation
Book Value

The template uses the SYD Function to calculate the sum-of-years depreciation. IT applies the following formula:

=IFERROR(IF(B16=0,””,SYD($D$9,$D$11,$D$10,B16)),””)

Where:

B16 = Year, D9 = Cost Value, D11 = Salvage Value, and D10 = life span of the asset.

Advantages of Sum of Years Depreciation Method

• This method better matches costs to revenues. It takes more depreciation in the early years of an asset’s useful life.
• Reflects more accurately the difference in usage of different assets from one period to the next compared to standard depreciation.
• Saves income tax as it deducts higher depreciation expense in earlier years.
• This means the method is practical, as an asset is usually more productive in its early years.
• Ensures distortion of earnings.

Disadvantages of Sum of Years Depreciation Method

• Computations are a little harder compared to standard depreciation.
• Puts a negative effect on the balance sheet in the initial years, as heavy depreciation in the initial year’s profit decreases.
• Loss of interest is not taken into account for the money invested to purchase the asset.

Frequently Asked Questions

If you like this article, kindly share it on different social media platforms. So that your friends and colleagues can also benefit from it. Sharing is Caring.

Moreover, send us your queries or suggestions in the comment section below. We will be more than happy to assist you.