There are many acceptable methods for calculating depreciation schedules, some of which are more aggressive than others. Straight-line depreciation is conservative—the declining balance method is more aggressive. Sum-of-years depreciation is somewhere between the two. In this video, I’ll show you how to use the SYD function to calculate the sum-of-years depreciation value for a given year.
- Governments allow businesses a lot of leeway in claiming depreciation on assets as an offset against taxable revenue. The straight line depreciation method, which takes the same amount every year, is the most conservative approach. While declining balance and double declining balance are more aggressive. The sum-of-years approach is somewhere between declining balance and straight line depreciation. In this movie I will show you how to use the syd function to calculate the sum-of-years depreciation value for a given year.
My sample file is specified.0204, and that's a workbook you can find in the chapter two folder of the exercise files collection. For sum-of-years depreciation, you need to know four different things. Those are the original cost of the item, the salvage value, and that is what you can sell the item for at the end of its useful life, or economic life, as it's called by accountants. In this case we'll assume it's 30 years, so perhaps this is a small building, or other permanent structure, and we'll start by calculating the depreciation for period one.
To create the formula, I'll click in cell C-8, type an equal sign, and the function name is syd. Again, sum-of-years depreciation. Type a left parenthesis, and all you need to do is enter in the references for my four arguments. The cost is 400,000 dollars. Then a comma, click cell C-4 for the salvage value of 75,000. Then a comma, the economic life is 30 years.
That's in C-5, comma, and the period we'll start with is in cell C-6 and that's one. So in other words, the first year. Then I'll type a right parenthesis, close the argument list, enter, and we see for the first year we're going to be able to claim 20,967.74. So just under 21,000. Let's say that we switch to period 10. So I'll click cell C-6 and remember 21,000, type 10 for year 10 and enter, and we get 14,677, so about a third less.
If we go to year 20, so 14,600, and 20, we go to about half of that at 7,688, and then in the final year, year 30, we can claim only 698.92. So as with declining balance and double declining balance, we were able to claim more of a depreciation tax offset, or depreciation tax shield, earlier on in the economic life of the asset.
- Define NPER.
- Determine the appropriate method of depreciation to use on tax returns.
- Recall which function is used to evaluate a fixed-rate investment or an annuity that makes periodic payments to the beneficiary.
- Identify the term for the rate of return earned from a zero-risk investment.
- Explain the purpose of the XNPV function.
- Recognize the type of bond that pays interest before it matures.
- Name three arguments needed to use the TBILLYIELD function.
Skill Level Intermediate
1. Analyzing Loans, Payments, and Interest
2. Calculating Depreciation
3. Determining Values and Rates of Return
4. Calculating Bond Coupon Dates and Security Durations
5. Calculating Security Prices and Yields
6. Calculating Prices and Yields of Securities with Odd Periods
Additional resources1m 24s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.