Welcome to Finance in Excel video number 85.
And if you want to download this workbook for chapter 9,
click on the link directly below the video.
And scroll all the way down to the Finance Excel Class
section.
Oh, in this video, we're going to talk about MACRS.
What is MACRS?
MACRS is a depreciation method defined by tax law.
And since when we buy an asset, the cash flow
goes out at time zero, that--
all the cash for the asset is at time zero
nowhere else in the remaining years, except for the tax
implications from depreciation.
So in earlier videos, we used the straight line depreciation
method.
That's not the one we should be using.
We should be using the MACRS tax tables like this,
because they will help us calculate the taxes paid
or taxes avoided.
Sometimes if we have a loss, we can avoid paying taxes.
And those are the cash flows relevant for figuring out
future cash flows for the present value calculation.
So we want to look at MACRS in this video.
So first off, you have to figure out what you are depreciating.
And over here on this sheet, MACRS--
this is straight from the tax law.
And so you could see there's 3-year property, 5, 7, 10, 15,
20.
And you have to actually figure out which category your asset
falls into.
And then you have tables like this.
But in this class, we'll just use this abbreviated table.
So research equipment, special tool's
going to be depreciated for--
as a 3-year class; autos, computers 5-year;
industrial equipment, office furniture 7-year.
And here are the percentages we use.
And guess what?
The MACRS depreciation-- in year 1, it just says-- for a 3-year,
it says take the full asset price.
So we're going to do a computer example--
25,000 and multiply by that.
And that's your depreciation.
The next year-- wow.
You get to multiply it by 0.44 of the original full cost.
These rates will bring it down to 0--
5, 7.
So let's go ahead and see how this works.
We're going to say our marginal tax rate is 0.34 for when
we sell the asset at the end.
We'll have to use that tax rate.
Otherwise, we'll be using these.
We have a computer system that costs $25,000.
The property class is going to be computers.
So we have to do 5-year.
So I'm just going to put 5-year here.
Right now, the beginning-- we're going
to do the beginning book value.
I'm going to scroll over.
So we can see this column here.
I want to do beginning book value, the actual depreciation,
and then the end book value.
We have to do this whole table to figure out
depreciation expenses for each operating cash flow.
We also need to look at end book value when we sell it.
And we'll see an example when we sell it
with market value above book value and less than book value.
So the beginning balance--
I'm just going to say, boop-- that one right there.
Tab, Tab-- the depreciation expense is pretty easy.
You say-- and I'm going to lock it on that cell right there.
And I'm going to hit F4, because the way it works
is every single cell is going to take the full amount
and multiply it by whatever the appropriate rate is--
so times-- and I'm going to say for 5-year, this is year 1.
So I click on that.
Notice when we copy this down, it
will move directly correctly through all of the rates
we want.
So we deliver-- go down through one year more
than the class sizes.
All right.
So that's our depreciation for each period.
The end book value is going to be,
hey, whatever the beginning book value minus our depreciation.
And now, we can copy this down-- oops,
except for if I copy it down here, there's a zero there.
Now, what's the begin book value in year 2?
Oh, of course, it's the end from December 31--
that's this.
And so on January 1, that is the end becomes the begin.
Now, I'm going to copy this down.
And it will work, right, because it's relative.
So I'll reference this minus that.
So I'm going to copy this one down.
And I'm going to copy this one down right here.
And then it should all work fine now.
We can go ahead and add up our non-cash depreciation expense
right here.
And what amount should we get?
We should get exactly the original historical cost,
because MACRS assumes that there is no salvage.
It depreciates it down to zero.
All right.
Now if you want, you can--
if you have a table like this, we could explicitly
show the calculation, right?
So I'd say equals this-- and I'm going to hit F4.
And now I'm going to use the Join symbol--
the Join symbol.
That's Shift-7.
That's called an ampersand.
And in double quotes, I'm going to put some text.
I'm going to put a multiplication symbol,
end double quote.
So if you enter this formula right here,
this is a text formula that says,
hey, I'm going to join this piece and that.
And so now, this cell contains both of those things.
Now, we need to join one more thing.
We're going to join this rate right here.
That'll be a relative cell reference.
So now we're joining to Join symbol.
That means we're joining 1, 2, 3 things.
And now, I can copy this down, right?
And so that is the explicit calculation right there.
All right.
Now, we're not going to look at the individual period tax
implications.
We'll do that in the next video.
We're just learning about how to calculate the depreciation
in this video--
however, in this video, we do want to see--
or learn about the implications for taxes and depreciation
when you sell the asset.
Now, we're going to look at two examples here.
When selling at the end, if market value--
that just means the amount you sell your asset for--
is greater than book value, then we
have to pay taxes on the difference between market value
and end MACRS book value.
In essence, we're going to see that we
have depreciated it too much.
So in our example, we sell for $4,500 at the end of year 4.
So we have to find n book value.
So I'm going to say equals that.
Oh, and we can see right away a market value
is greater than end book value.
Now, in this case, it means that we have depreciated too much.
So book value is calculated by subtracting out
these non-cash depreciation expenses.
So we went-- we're already down for in our book value
down to 4,320.
That means the difference between these two
tells us how much depreciation we took on our tax statement
to gain a tax saving that we shouldn't have, right?
So let's figure out the difference--
not that we shouldn't have.
But now we have to recapture it.
So we had a tax savings.
And now because we sold it for more,
we have to calculate the difference
and then figure out how much tax to pay.
Now, it can get quite complicated
when you're selling an asset, whether it's a capital
gain or the ordinary tax rate.
In this class, we'll just assume it's the ordinary tax rate.
So we simply say, hey, that's the depreciation we just
recaptured times our marginal tax rate.
And guess what?
That is a tax going out.
So when we calculate our cash flow,
we can't just throw the cash coming in for the asset--
selling the asset, right?
Also got to look at the tax implication here--
so that would be 4,500 minus the taxes paid out.
Now, it's kind of the--
so that's the total cash we'd have at the end of this project
if we're winding it down at the end of period 4.
Now, there's one other situation, right?
Let's look at this one right here.
What happens if book value is greater than market value?
We get a tax benefit, because then we have a loss.
So here it is.
We're going to assume we sell it for 4,000-- oh,
at the end of year 2, right?
So by the way, these tables-- you just
fill them out like this.
And then at any point in time where you sell it,
you just look at the appropriate numbers.
All right.
So book end book value--
ooh, 12,000, right?
So our book value says it's worth 12,000.
And we sell it for 4,000.
So we have a loss.
Now, we're going to do this--
the difference equals this minus that.
So I'm going to do market value minus the book value,
because then it will tell us, oh, yeah,
that minus sign tells us it is a loss.
So then, the tax benefit--
we say that times this, because we get to record this
as a loss on our tax record.
And this is a subtraction.
So we avoid paying those taxes.
So at the end, what do we do?
We say the cash actually coming in is 4,000.
And now, this is a minus here.
I really should have done that here
just because this is the benefit.
So this is like a cash flow in.
So I'm going to say minus of that.
And so then, the cash flow becomes 4,000 plus the benefit.
And the benefit is we avoided paying those taxes.
Now in this video, we just briefly saw MACRS--
how to build a little table; begin book value;
the depreciation; the end book value; what
happens if we sell it when market value is greater
than book value; also when we sell it,
what to do if book value is greater than market value.
In our next video, we'll put it all together.
We use all this MACRS depreciation cash
flow implications.
And we'll do a big comprehensive example
for cash flows and net present value.
All right.
See you next video.