Join Curt Frye for an in-depth discussion in this video SYD: Calculating depreciation for a specified period, part of Excel 2010: Financial Functions in Depth.
- View Offline
There are many acceptable methods for calculating depreciation schedules, some of which are more aggressive than others. The straight-line depreciation method is very conservative and the declining balance method is more aggressive. The sum of years depreciation method is somewhere between the two and in this movie I'll show you how to use the SYD function to calculate the sum of years depreciation value for a given year. The sum of years method is named for the way the number of the year in the asset's used for life is used to calculate the amount of depreciation allowed. The SYD function uses four arguments.
The first is the initial cost of the asset. The second is its salvage value. That is the amount for which you can sell the asset when it's reached the end of its useful life. Then the number of years of its economic life and in this case that's 30 years, and then finally the period for which you're calculating sum of years depreciation. So let's go ahead and create our function. I click in cell C9, type in equal sign, and then syd for sum of years depreciation, left parentheses and then we can start filling in our arguments.
So the cost is in cell C3, the salvage value in C4, typing a comma, then the life is in cell C5, comma again, and then the number of the period is in cell C6. Type a right parentheses, make sure everything looks right, and I believe it is, then press the Tab key so I don't scroll down. And we can see that the depreciation on this asset in year one is about $20,900. Let's see how that changes throughout the life of the asset. So I'll change the period from 1 to 11, so it's in its 11th year, and then press Enter and the depreciation goes down to about 14,000.
Now I'll change it to 21 so it's in its 21st year of its 30 year economic life and press Enter. depreciation drops by about half and then finally in its last year, which is the 30th year, we see that we get a very small amount of depreciation. The way that you calculate sum of years depreciation is actually fairly interesting, but it's beyond the scope of this movie. If you want to use the sum of years depreciation method you can use the SYD function. You'll only need these four arguments and you can calculate the depreciation values you need.
- Analyzing loans, payments, and interest
- Discovering the interest rate of an annuity
- Determining depreciation using the straight line, declining balance, double-declining balance, and other methods
- Calculating the future value of an investment with variable returns
- Finding the discount rate of a security
- Converting between fractional prices and decimal prices
- Determining the yield of securities that pay interest periodically