Declining Balance Method

[00:00:00.00] [MUSIC PLAYING]

[00:00:08.79] IAN SCHNOOR: In this lesson, I'm going to show you how to use a different
depreciation methodology all together. We're going to look at how to use the declining balance
methodology, instead of straight line. Now, I have to tell you, in all the models I've ever built
and all the models I've ever seen, the vast majority of those models have depreciated assets using
straight line.

[00:00:31.71] Straight line depreciation is far and away the most common method that I have
ever seen in models. I think that's for a couple of reasons. Number one, straight line seems to be
the most common method that companies use on their accounting financial statements, number
one. And secondly, in models I think that people often aren't sure how they would use declining
balance. It's not quite as simple as it might sound to build. So I'm going to show you how to do
it.

[00:01:00.39] But there's a there's a hint and there's a trick. The hint to building and knowing
how to build a declining balance depreciation in a model has to do with what we did in the last
lesson. There's a reason I've created these in this order. If you followed along with me in the last
lesson where I showed you how to fully depreciate assets within the life of a model, that concept,
that technology, is going to be very easily adaptable to allow us to use a completely different
methodology altogether, the declining balance method.

[00:01:32.01] So let's jump in now. I want to show you exactly how this works. I really
encourage you to follow along with me and build this up in your models as well so that you
understand it.

[00:01:41.22] So let me go ahead and share my screen here. Here we go again. You should be
seeing it.

[00:01:46.86] And this should look familiar. I'm back to the depreciation with three years. And I
have my formula that you should be familiar with now. If my years match, take half. Otherwise,
the smaller, the minimum, of-- of the normal depreciation calculation or what's left over. I hope
that all make sense. And again, this was the formula above to show you how you can fully
depreciate assets within the life of the model.

[00:02:12.03] Now, before I actually get into building it, let me show you and remind you where
the depreciation years came from. On the Assumption page, they came from here. If you recall,
there's a Depreciation box. This is where we typed in the 25, and this is where we typed in the
30, in the Depreciation box.

[00:02:29.58] But there's also a row here that says Straight Line. It's not actually being used. It's
really more for informational purposes.
[00:02:36.84] Occasionally, people will say to me, Ian, does it make sense to turn this into a
switch? Does it make sense to make this cell a switch so that you can open it up so that you can
choose and decide if you want to use a straight line or declining balance? I mean it's a nice skill
to have. I think it's probably unnecessary in most instances.

[00:02:57.96] It would be very unusual in a model to switch between straight line and declining.
Companies rarely, if ever, change their depreciation method. Once they've picked a
methodology, they tend to use it forever.

[00:03:11.04] So if you happen to know that the company you're modeling uses straight line,
you'll build your model straight line. And if the company you are modeling uses declining
balance, then you'll build your model with declining balance. I can't really see the need to have a
switch to toggle between them. But you're welcome to. I mean you'll quickly figure out how
once I show you how to do the calculation. I just don't think it's going to be necessary very often.

[00:03:37.53] But let's first now jump in. Let's go back in and go in and make sure you
understand what we're trying to do. Now, I'll be honest. Straight line is a nice calculation because
it literally just takes the same amount every year, right? On our existing assets-- you'll see here--
on our existing assets, in this one, it says take my last year's assets, divide by 25. And then it will
be the same number 15.9 every year because we're doing it evenly straight line over the next x
number-- for 25 more years. It'll be 15.9 a year.

[00:04:14.01] Same thing with the new assets, it's half in the first. But then it's the same, same,
same, until the end. I mean if I went back to 30, then it's half in the first year and then same,
same, same, same. The number is the same. So that's sort of nice-- that's a nice idea of straight
line is the concept is simple.

[00:04:32.34] With declining balance what we're doing is we are saying the concept of declining
balance says depreciate a percentage of what's left over. So you need to depreciate a percentage
of what's left over. And I'm saying it that way on purpose. I hope that kind of rings a bell, right?

[00:04:50.88] Declining balance you are depreciating a percentage of what's left over. Well, we
just did that. Where did we talk about how to figure out the amount left over? I showed you how
to calculate what's left over in our last lesson when we had to fully depreciate the assets, right?
Remember at the end it says, here's what's left over-- the original Capex minus the sum of all the
prior years. So we're going to need a lot of this similar technology to use a declining balance
methodology.

[00:05:19.19] So let's do this. Let me show you how to build it. Let's build it for the new Capex
because this is the piece that's a little trickier.

[00:05:26.06] So what you would do first of all is if you're going to use declining balance, you
don't make an assumption for a years. You would have an assumption for the percentage, the
declining depreciation percentage, I would say-- depreciation percentage on the new assets. So
instead of saying years, it would say percentage.
[00:05:44.18] Let's-- I don't know-- let's assume 10%. I'm going to type in 10%. And now make
this a percentage. So I've just made this 10%. Obviously, you would do this on the Assumption
page and then link it. But I'm just going to type it in here so I'm not jumping around as much
now.

[00:05:59.39] Ignore the yellow numbers now. They're all messed up. I'm going to have to fix
my formulas. So just ignore the numbers.

[00:06:07.07] Let's go in and learn how to get the formula working. And I think you're going to
discover it's really simple. It's a very simple modification. So let's go through it step by step.

[00:06:16.76] First of all, it says if the year I'm in is equal to the year I bought the assets, well,
the old way said take the Capex, divide by years, and then divide by 2. Well, the same concept,
by the way, the mid-year convention, or the half year convention, would still apply. Even if
you're using declining balance, you would still take half in the first year.

[00:06:39.64] So what I'm going to say is if we are in the first year, take the Capex. But now,
don't divide by 10%. Multiply. We're going to multiply by the depreciation rate and then divide
by 2.

[00:06:52.48] So if we're in the first year, then take the Capex times 10% and divide by 2. Let's
just stop there. This should be working. And, yes, sure enough, you'll see in Year 1 it's working.

[00:07:02.98] 10% of 16, of course, is 1.6. 10%, you can see this, times the Capex 16 is 1.6. And
because we're taking half in the first year, you now know this is working. It's 0.8. It's half of
what it would be otherwise. So we're taking half in the first year. That part's working.

[00:07:21.64] Now, let's get into the remainder-- otherwise. Well, in the otherwise, you actually
do not need a min. We no longer need a min. So I can remove the MIN function.

[00:07:32.86] What I do want to say is I want to say, if we're in the first year take half,
otherwise-- otherwise-- I'm going to say take the Capex minus the sum of all the prior year's--
Capex minus the sum of all the prior years. And now what I'm going to do is take whatever this
value is, whatever this works out to, Capex minus the sum of all the previous years, and I will
carefully put my cursor in between the last two parentheses and multiply that value by the
depreciation percentage here.

[00:08:07.63] So one more time-- what it's doing is it's saying, hey, if we're not in the first year,
if we're not on the first year, then what's left over? What's left over? And multiply that by the
10%. And that should work.

[00:08:21.94] So let me just copy this into Year 2. Let's see if it's working in Year 2. It appears to
be. It appears to be. Just without actually proving it exactly, it appears to be working. I showed
you a moment ago that in the first year it was going to be 1.6. But then we took half of the 1.6,
which means in the next year, it should be a little bit less than 1.6 because there are less assets
remaining.
[00:08:49.55] And as I copy it over, what we expect to see with declining balance is the
depreciation amount drops every year because it's still taking 10%, but now it's 10% of a smaller
base, a smaller asset base. And it really appears to be working. So I'm saying, again, if I'm in the
first year, take the 16 times 10% and take half of it.

[00:09:12.32] Otherwise, what's left? What's left to depreciate? Capex minus all the prior years.
You see I'm using the same SUM function by locking the beginning J and then multiplying that
by the depreciation rate.

[00:09:27.24] By the way, occasionally people ask me-- I think I may or may not have
mentioned this in the last lesson. But if I even if I did, I'll mention again. Why does the SUM
function need to start with this column behind me. It's because if I'm in K, I want to reference J.
Yeah, I mentioned this. I'll say one more time.

[00:09:44.31] Because I can't include myself, I need to build a SUM function that starts the year
behind me, and it has to end behind me as well. If you include yourself in yourself, you will end
up accidentally building a circular reference. So just want to reiterate that point.

[00:09:59.40] So I'm taking the sum of all the prior years, multiplying it by 10%. And that gives
me the results I need. And now I should be able to copy this into the entire triangle. And it
should be working. It looks like it is.

[00:10:10.65] Let me just prove it with some larger numbers. Let's pretend that the declining
balance methodology was not 10%, but rather it was a big one, 50%. By putting in 50%, you're
going to see that it's easy to kind of see what's happening just by looking at the numbers.

[00:10:30.45] In the first year, it's showing 4. Does that make sense? Well, 50% of 16, right--
obviously, 50% of 16 would be 8. 8. But in the first year, it'll just take 4.

[00:10:43.86] So if we have depreciated 4, it means after Year 1, we have 12 million of assets

[00:10:53.82] So if there's 12 left and we depreciate half of it in Year 2, it's taking depreciation
half of 12, which is 6. And now, if we've depreciated 6, it means the value of the assets is down
to 6. And if we depreciate half of that, it gets us to 3.

[00:11:13.36] And then in the subsequent year, if I only have 3 million of assets and it
depreciates half of that, it depreciates 1.5. I think, prove this to yourself, walk through it. I think
you'll be able to see that by using this declining balance formula, it's working perfectly exactly as
you would expect.

[00:11:29.17] But as I mentioned, the whole key and the trick to this approach is to recognize
that we are building on what we did in the last lesson. You have to understand how to show how
much is left. And once you know that, you just multiply by a depreciation rate, a percentage,
instead of dividing by the number of years. And that's effectively the main difference.
[00:11:54.49] So that effectively wraps up what we are doing on the declining balance
methodology. I will also tell you that Excel has a bunch of built-in depreciation functions. So
there is a function in Excel called SLN, which is Straight Line Depreciation. There is a function
called equals DB, which is for declining balance depreciation. There is a function called the
DDB, which is double declining balance depreciation. And there's a function called equals SYD,
sum of the year's digits.

[00:12:25.18] You're welcome to try these. These can be handy, especially if there is salvage or
remaining value at the end of the of the life if you want to assume that there's going to be some
residual value when you're finished with it. Admittedly, I don't see those functions used a lot.

[00:12:43.24] I mean this is such a simple concept. I don't usually see people using this, but
rather they build their own formulas. And I think it's pretty nice. Often clients, bosses, want to
actually see the calculation. And so it's nice to know how to do it yourself within a formula.

[00:12:57.04] So that wraps up here how to use declining balance methodology within your
waterfall, within your depreciation schedule. We've covered a lot of ground here in terms of
building the depreciation schedule. There's really only one more last thing that we're going to
cover in this unit.

[00:13:16.00] And that is how would you turn this schedule into more of a fixed asset schedule?
It's pretty simple. But if you didn't want this to be a depreciation schedule, but you wanted it to
be called your fixed asset schedule to include more than just depreciation, maybe you want to
show the Capex, the depreciation, and your PPE, your property, plant, and equipment balance,
sheet balances, I'll show you a nice design to build that in.

[00:13:42.07] And that would be the last thing to see in this unit. So I'll see you for the next one
where we finish off all of our lessons on depreciation.

[00:13:51.43] [MUSIC PLAYING]