In the last lecture, we have seen different methods of depreciation in this lecture, we will learn
how to implement those depreciation techniques in Excel.
Now we have discussed in-depth theory on how to calculate depreciation values for all these methods,
but there is no need to perform all those calculations in Excel, Excel, provide US and formula to
calculate depreciation for all of these three methods.
So first, we are going to discuss a straight line depreciation, then we are going to discuss the decline
balance depreciation method and at last we are going to discuss some of year's budget depreciation metall.
So here is our case, the asset value is one hundred thousand, the salvage value at the end of five
year is only 10000.
So we have to appreciate this asset for five years.
First, let's discuss about Straight-line matter, so the formula for calculating a straight line depreciation
is as L and.
You need to give three parameters, the first one is the cost and cost, you have to give the purchase
value or the original value of your asset, which is this.
Then the second parameter is salvage value.
In our case, the salvage value is thousand.
If salvage value is not given, then you have to consider zero as your salvage value and you can just
zero over here.
And the third parameter, you have to give the life of the product, and in our case, the life is five
years.
So we will select this.
Now, if you hit, enter.
You will get the depreciation value.
Now, since we are using a straight line method, this depreciation value will be gone, spent for all
the five years.
So we can expand this formula to our next sales as well.
But before expanding, let's fix the referencing of all the cells that we are using in this formula.
In that way, if we expand the formula, these cells are not going to change.
So the shortcut for absolute representing is EFORE.
So just select all the cells you want to fix and then just press F4 and you will find other symbols
in front of raw numbers and call them names.
So after using absolute referencing, we can expand this formula.
So you can see that we are getting the same value of 18000 and all the five years, and just to check
whether the cell referencing is working fine, so just select any of the cells below and click on this.
The and you can see that the cell referencing is working fine and we are selecting the correct cells.
So in a straight line, we are getting 18000 as depreciation value for all the five years, so you can
add depreciation value of eighteen thousand for all the PNL statements of five years.
Now, the second method is.
Double declining balance.
And here the formalized BTB.
Just right, Debbie.
So here there are four parameters.
The first three parameters are the same as the parameters for the straight line method and for the last
parameter, we have a period in which we have to mention the period for which we want to calculate the
depreciation amount.
This parameter was not needed in SLN matter because in SLN method, the depreciation amount remained
the same for all the periods.
So the first parameter is cost, which is the actual value.
Then the second parameter is salvage value, which is this.
The third parameter is life of the asset, which is this.
And then the last parameter is this period parameter.
If we enter, we will get the depreciation amount.
So in the world climbing method, we are getting the precision of around 40000 and Bédard, one known
here, also we can expand this formula.
But before that, we need to fix the cell referencing.
And you can see that we want this C9 cell to be changed when we are dragging the formula.
So for the second cell, we want it to be two.
For the third cell, we want to be three and so on till the bitter end, equal to five.
So in this formula, we will fix C3, C4 and C5 cells on the.
So just select and preserve for now, once you have fixed yourself referencing, you can drag this formula
down to a plate and rest of the cells.
So you can clearly see the difference between the declining balance and the straight line matter and
the straight line, I thought we were getting a constant appreciation value for all the five years and
in the world climbing, you can see that for the first few years the depreciation amount is larger than
our straight line mattered.
And for the rest of remaining years, the depreciation amount is less than our straight line return.
The declining balance also points for salvage value.
So in our case, our actual value was hundred thousand, salvage value was 10000.
So we are depreciating our asset by around 90000.
So the sum of all these values should be 90000.
You can see that the some of depreciating values is ninety thousand, we can apply the same thing in
a straight line matter also, and we will get the same result in a straight line method as well.
So both of these matters are accounting for salvage value.
The next method is some of your digicam.
We already discussed the theory behind it.
Now let's apply it and excel, the formula here is as widely.
So we can ride equal to Suadi.
And then we have the same parameters.
First, we have the cost, which is the actual cost.
Second, we have the salvage value, which is this third, we have the life of the asset, which is
this, and then we have to mention the period and the last parameter.
So just like the world reclaiming.
We need to give Ford parameters.
So in some of it didn't matter for the first year, we are getting 30000 as our depreciation amount
for this asset.
Now, before extending this formula to other rules, let's the referencing of SI three, four and see
five said.
We are not using absolutely flensing on CNN because we want.
This cell to be changed when we are expanding the formula, let's expand no.
So you can see that and some of your budget might as well, the deposition amount is declaiming with
each year.
Now, let's calculate the sum of.
All this debris shifting the position amount and you can see that we are depreciating our asset by 90000.
So at the end of 50, it will get 10000 as our salvage value.
So that's how we apply the appreciation and excel.
There is no need to calculate it manually in Excel, you can just use these three formulas to calculate
depreciation amount according to your depreciation logic.