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 =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 =SY and Tab to select the Sum of Year 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 =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 Year 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.

Practice Video: Depreciation Functions (SLN, SYD, DDB)

Excel Skills for Business: Advanced

Meet the Instructors