Sum Of Years Depreciation Calculator Template

Sum Of Years Depreciation Calculator Template in Excel, OpenOffice Calc & Google Sheet 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.

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 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 last year first in reverse chronology, divide the year by the sum of years to determine the deprecation 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 last year will be 1/6 X 100 = 16.67. The sum of deprecation rate 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 useful life.

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

Formula To Calculate Sum of Years Depreciation

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

1. Add the digits for each year. (3+2+1= 6)
2. Starting from last year first in reverse chronology, divide the year by the sum of years to determine the deprecation percentage. 3/6, 2/6, 1/6 for 1st, 2nd and 3rd years respectively.
3. Multiply the depreciation rate with 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 consists of 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 date of purchase of the asset during the year.

Generally, assets are bought around 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 Sheet)

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, and 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 requirement.

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

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 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 other compare 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 as compared to standard depreciation.
• Puts a negative effect on the balance sheet in the initial years as due to 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 the same. Sharing is Caring.

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