Before watching this screencast, make sure you have watched the screencast just on depreciation. In this screencast, I'm going to talk about the common depreciation methods that are used, and then I'm going to show you how we can implement these methods in Excel. Common depreciation methods include straight-line -depreciation, sum-of-years method, also known as the sum-of-the-years' digits method, double declining balance method, and units of production method. This screencast will be separated into two parts. The first part I'm going to cover straight-line depreciation and some of years method, and in the second part I will cover double declining balance and units of production method. I talked about straight line depreciation in the previous screencast on depreciation. But straight-line depreciation assumes that your asset depreciates in a linear fashion, starting with the purchase cost at time 0 and ending with the salvage value that you can get for that asset after its useful life. So here we've got a useful life of five years, and at that point, the salvage value is $2,000. That means the value of that asset over the time that you own it is decreasing from 10,000 down to 2,000, and that's a difference of $8,000. The formula for calculating depreciation for straight-line depreciation is shown here. We take the purchase cost, subtract the salvage value, and that's in parentheses, so that's going to give us $8,000 in this case, and then you divide it by how many years you're going to be able to use that asset for. So its useful life, in this case, that's five years, $8,000 divided by 5 years is $1,600 per year. Let me show you how we can implement this in Excel. There's actually a straight-line depreciation function built into Excel. I've got this file called depreciation. First of all, I have named cell C3, I've named that cost just so we can use it in all of our different methods, and formulas, and functions here. We have a salvage value that I've named salvage, and we also have a useful life that's going to be five years. In straight line depreciation, if I just want to use that formula that I showed you, I'm going to start in cell B11 because I have a zeroth year row in each of these tables just because we want the starting value. That's going to be useful when we plot. For example, here on this plot, we already have at time 0, it's at its cost. I have a zeroth row, but we're going to start our function here in the second row, which is year 1. The depreciation in year 1, If I just want to use that formula that I showed you, I can take the cost minus the salvage value, and we're going to divide that by the useful life, and that means we get a depreciation in that first year of 1,600. Because this is straight-line depreciation, I can just drag this down because it's exactly the same every year. Depreciation for straight-line, depreciation is the same. There is a straight line depreciation function, SLN for straight-line, this returns the straight-line depreciation of an asset for one period. So the cost I can just type in cost, salvage, and the life. So it's those three arguments that go into that function. Essentially, all it's doing is doing the exact same calculation that I did manually here. But you might forget that formula, and so you can just use the function SLN. I can drag this down. Because I have those absolute references, the named variables, I can just drag it down and I don't have to worry about any relative references. At the end of each year, I want to put in a value. So at the end of the first year, the value is always going to be the previous year's value at the end of that year, and we're going to subtract any depreciation. I'll just use our SLN function here. I can press Enter, and I'm just going to go ahead and drag that formula down because we're always taking, for example, if I go to year 3, we're taking the previous year, the value at the end, 6,800, and subtracting that 1,600. So this is how we can compute the value as a function of the year. I didn't mean to copy that as far down as I did, so let's just delete that last row. On this start file I have it, so it's going to plot this, and so you see that we have our straight line depreciation as a function of the year, and we have our value on the y-axis. The second depreciation method that I'm going to talk about is called the sum-of-years method. This is an accelerated depreciation method. I've got an example shown here, which means that the value over time is faster than straight-line depreciation in the first couple of years. You get more depreciation, you can claim more depreciation in the earlier years. The first step is to sum the years of useful life. Here we have a useful life of five. So what we do is we just sum those. We take 1, 2, 3, 4, 5 and that's sums 215. The second step, the depreciation for year x, we're going to depreciate years 1, 2, 3, 4, and 5 is equal to the remaining life divided by the sum. So we're going to divide by 15, and we take that fraction when we divide the remaining life by the sum, we're going to get a fraction, and we're just going to multiply that fraction by the difference between purchase cost and salvage value. That's $8,000, the overall amount that the asset depreciates over those five years. As an example, in the first year, we have five years of useful life remaining. Don't get too confused here. This is the first year, which doesn't mean we have four years left. It's basically at the beginning of that year, you're going to have five years left, and that's what goes up top here. In the first year then our ratio is five divided by the sum, which is five-fifteenths or one-third, and we multiply that by the difference between our purchase cost of 10,000 minus our salvage value of 2,000, that's 8,000, and we get $2,667. That's the depreciation then in the first year using this method. In the second year, we have four years remaining, we take 4 divided by 15 and multiply by 8,000, and we get depreciation of 2,133, and we can keep going. So in the third year, 3 divided by 15, we do that calculation, we get a depreciation of 1,600, and then we get two-fifteenths and one-fifteenth each multiplied by 8,000, and that's how we do this method. There is a function in Excel for doing this, but I want to first show you how we can do this in Excel without using the sum-of-years function. So over here on the right, I'm going to start in the second row here. Again, I'll show you in a minute how we can use the SYD function sum of years, but let me just show you how we can do this in Excel using our own custom formula. I'm going to take the total number of years, which is five, because that's the total number, that's our useful life, I'm going to subtract the year over here, and I'm going to add one. So let me just stop there. I've got it as a currency right now, so just neglect that. But that will go, it'll start from five and it'll go backwards. This integer that I just calculated is the remaining life, and that's going to go on the top of that fraction that we create. Then what I'm going to do is I'm going to divide that by 15. You could also use sum of these, and I'm going to press F4. That calculation is going to give us five-fifteenths for the first year, which is what we need to calculate the depreciation for the first year. Then simply I multiply that by the difference between cost and salvage. When we do that, we get the depreciation for that first year of $2,667, which is what we got earlier in the slide that I showed. I can simply drag this down, and that calculates our depreciation using the sum-of-years depreciation method. Now, there is a SYD function that's built in. It's really easy to use. We can just put in the cost, salvage the life, and then the period. Because this is not constant, we have to put in the period that we're considering, and this is going to be year 1, that's our first period. Then I can close this parentheses, I can press Enter, and we get the same thing. So I'm going to go ahead and copy this down, and we see that we get the same depreciation as we got when I did this using a different custom formula. Again, to compute the value at the end of each year, I can take the previous year's value and I can subtract the depreciation, and I can go ahead and copy this down. I've got it, so we automatically plotted that over here, and you see that the sum of years depreciation method is an accelerated version, it's faster, and especially in the first couple years than the straight-line depreciation method. So I will show you how to do the double declining balance method for depreciation and the units of production method in the second part of this screencast,
