0:03

Alex's last task for the week is to calculate

Â a depreciation schedule for the office furniture assets.

Â Excel offers several functions that will help us calculate depreciation,

Â and there are different methods that you could apply to different situations.

Â The first method we're going to look at is the straight line method.

Â This assumes that an asset depreciates by the same amount each year.

Â So, for example, if we have some office furniture,

Â which we pay $20,000 for,

Â we assume the office furniture will last about 10 years,

Â and we hope to sell it or trade it in for $3,000 at the end of the 10 years.

Â This is called the salvage value.

Â We're going to use the straight line method to

Â calculate how much it depreciates each year.

Â The calculation is going to work by subtracting the salvage value from the asset cost,

Â and then dividing by the number of years of life.

Â But we don't need to perform this calculation because

Â Excel straight line function will do it for us.

Â So, we're going to click into cell B16,

Â and type equals SLN and tab.

Â The straight line depreciation function expects three arguments,

Â the cost of the asset,

Â the salvage value, which of course can be zero, and the life.

Â And when we close our brackets,

Â there is the amount it's going to depreciate by each year.

Â So having calculated that,

Â we can just drag that across and if we have a look at the graph here,

Â it's depreciating at a nice constant rate,

Â but stopping at the $3,000 mark.

Â In the table below,

Â we've added some calculations to take

Â the original asset value and subtract the depreciation amount.

Â So you can actually now see how much the asset will be worth after each year.

Â So that was one method,

Â but as you probably know,

Â not all assets depreciate by the same amount each year.

Â Many assets depreciate far more in the first few years of their life.

Â For example, motor vehicles.

Â So another method you could apply is the sum of year digits.

Â This method assumes that most of

Â the depreciation will happen in the early part of the asset's life.

Â The calculation works a little bit differently,

Â it does the sum of all the periods.

Â So for example, we would add one, two,

Â to three, all the way up to 10 to get 55.

Â It then depreciates by a fraction of that 55 each year.

Â So for example, the first year it will depreciate by 10 over 55.

Â The next year, by nine over 55 and so on.

Â But again, we don't need to worry about the maths

Â because Excel has a function to do it for us and.

Â It's called SYD.

Â We're going to click into cell B17 this time,

Â and type equals SY and tab to select the sum of your digits.

Â Once again, we must specify the cost,

Â the salvage, and the life.

Â But the SYD requires an additional argument,

Â which is the period number.

Â We have listed these over here.

Â So I'm just going to click on the one and press enter.

Â You'll notice the depreciation here is nearly double of the straight line value.

Â We're going to copy this across now.

Â And if you have a look at the figures towards the end,

Â the depreciation has come down significantly.

Â Also, looking at the chart you will notice that we are

Â getting an accelerated depreciation at the beginning,

Â which is then tailing off towards the end of the life.

Â Finally, a third method that is quite commonly

Â used is the Double declining balance method.

Â This is another accelerated method.

Â The double declining method takes the straight line percentage,

Â and applies a multiple to it.

Â By default, it doubles it.

Â Hence, the name double declining.

Â So for example, over 10 years,

Â we expect our asset to depreciate by 10 percent a year.

Â The double declining balance method will double

Â this to depreciate by 20 percent each year.

Â Let's see how it works.

Â So we're going to click into cell B18,

Â and type equals DDB and tab.

Â Once again, we're going to specify the cost,

Â the salvage, the life, and the period.

Â You will notice there's an optional argument factor.

Â If you don't specify this,

Â it will use 20 percent.

Â But you can use an alternative factor if you wish.

Â We're going to go with the default.

Â So we're going to close our brackets,

Â and now just for a check we're expecting to get 20 percent depreciation on $20,000,

Â which would be $4,000.

Â Let's check if that works. And there you go.

Â We're then going to drag that across,

Â and that is our double declining balance method.

Â Now, one thing to be aware of with the double declining balance,

Â is while it will often yield the salvage cost,

Â it does not always.

Â So if my salvage amount was actually $2,000,

Â because of the way the calculation is done,

Â it does not get to exactly $2,000.

Â So it does take it into account,

Â but it doesn't always yield exactly the salvage amount stipulated.

Â So, there we have three different depreciation methods,

Â straight line, the sum of your digits,

Â and double declining balance.

Â And just to finish off,

Â I should put that in my chart.

Â So there is our finished chart showing the three different methods side by side.

Â We have looked at quite a few of Excel's financial functions this week.

Â Make sure you have a go with the practice challenge,

Â so that you become more familiar and have lots of practice at these functions.

Â