Welcome to Finance in Excel, video number 79.
Hey, if you want to download this workbook for Chapter 8,
click on the link directly below the video.
And scroll way down to the Finance Excel section.
Hey, this is the last video in Chapter 8,
talking about investment criteria.
Should we buy a particular asset?
We've looked at, individually, different methods.
We've talked about the net present value, internal rate
of return, payback, profitability index,
and average accounting return.
We're going to look at them all together in one example.
So here's our time.
Here's our cash flows.
And here's the net income for each period.
Well, net present value, as we've
talked about throughout this chapter, is the best method.
And we are lucky.
In Excel, we have the net present value function.
We give it our required rate of return.
This is our discount rate.
This is the rate which includes the risk, comma.
And we're going to discount back our estimated future cash
flows.
When you're using net present value, you start at time one
and go forward.
Do not include time zero.
And then you subtract the cost.
So this is the net present value.
And subtract the cost.
So because that's negative, I put a plus.
Now, the IRR is closely related.
And we can use it here, because we have conventional cash
flows, which means there's a negative
and all the rest are positive.
And we're not comparing mutually exclusive projects.
So we can simply highlight all of the cash flows.
And get this--
IRR is great, because you can include time zero and all
the ones forward.
And it tells you the internal rate.
That's the rate innate to these particular cash flows.
The parallel in our bond chapter-- we said,
hey, here's our cash flows from our bond.
What is the yield to market?
That meant the rate inherent in those particular cash flows.
So it means we'd earn 14%.
If our hurdle rate, required rate is 15%,
are we going to invest in this project?
No.
By the way, here, too, it was what?
This is negative.
Net present value is negative.
That means if you took on this project,
you would decrease the value of the firm.
So no.
Now payback-- we're going to have to calculate payback.
And in advance we said, if this project pays back within three
years, then we will take it.
This method doesn't take into consideration time
value of money or risk, or even whether value is added.
But nevertheless, people do use it.
It's oftentimes used for smaller projects.
And what we need to do is figure out how quickly it pays back.
Well, there's a $150,000 outlay.
And then each year we get this period.
So I'm going to start by going negative this.
And I'm going to say minus this.
So the first year-- after the first year, we recover 38,500.
And that's how much we have left to collect.
Now I'm going to say from that, I
want to subtract this second cash.
So on this formula I can copy it down,
because it's always going to look from the period
before, and subtract the appropriate amount.
So I'm going to drag this all the way down.
And the first negative says, well,
somewhere during this year I'm going to get paid back.
Now, that's how I did it in the video before.
I'm going to show you an alternative here.
Equals minus this, and now I'm going
to say minus the sum of this one.
And I'm going to hit Shift-colon, and close
parentheses.
Now, that's a funny formula--
B3 to B3.
But watch this.
If I click right there and hit the F4 key, that locks it.
That means now this has got one range, one cell
reference in the range that's locked and one that's not.
This is called an expandable range.
Let's see how this works Control-Enter,
and I drag it down.
That means I've got to go up here and lock
this one too-- boop!
Control-Enter, double-click and send it down.
I get the same answers, with a little bit less trouble
in creating my formulas.
Now, let's see how this works.
B3 remained locked.
But this one is allowed to move relatively,
so in its expanding range.
When I come down here and hit F2,
you can see the green is expanding.
Hit here, F2, to put in [INAUDIBLE]..
The green range is expanding.
All right, that's a better formula
for this particular calculation.
All right, now, we know it got paid off, almost in year three
but somewhere into year four.
So the way we do this, we would say, hey, that's year three.
And how much cash--
what amount was left to pay back after year three?
I'm going to add-- well, this amount.
And we're going to divide by, which means compare to.
And this is year four.
We need to come up here to year four.
And click there.
It assumes an even payback, which may not necessarily
be true.
But for estimation purposes, we can
say that it took 3.9 years to pay this back.
So again-- well, this is no, because we've got
to pay back within three years.
So 3.9, no way.
The profitability index equals net present value,
because we calculate the net present value
at 15%, comma, and all of the cash flows, one and forward.
But for profitability index, unlike
a straight net present value, we don't subtract the cost.
We divide by the cost.
And since it's negative, I need to do a negative sign there.
Oh-- 0.98.
Remember the profitability index rule says less than one,
don't accept.
Greater than one, we accept.
Now, I usually like to go copy and do
profitability index minus one.
So I do equals and Control-V-- whoops--
and then minus one.
And this tells us the decimal equivalent or the proportion--
or if we had a percent, the percentage--
value added.
So we lose, in essence, two pennies for every $1.00
of cost.
Finally, we have our accounting return.
We already have our net income for each year.
So we need to average that.
And then we need to figure out what the average book value is.
And again, we're assuming straight line salvage
value of zero.
So that means we can just take the original cost divided
by two.
And that gives us the average book value.
So I'm going to say, equals average.
In an earlier video, we did this.
And we did it in multiple cells and showed you
how it was all calculated.
But I'm going to do it altogether here.
So that's the net income.
And that's the denominator--
sorry, that's the numerator, the one on top, divided by.
And now we need to say, in parentheses, negative this.
So I get a positive divided by two.
In an earlier video, I showed you why that calculation works.
So 3.4 is our average accounting return.
Now, if we've set in advance--
just like we did with the payback,
we set this in advance for average accounting return.
Our required is 0.3.
So we say yes for this measure here.
But this one, all the rest are no.
And as we mentioned in earlier videos,
the accounting one does not--
just like the payback, neither one of these
consider the time value of money.
They don't consider the risk.
And they don't tell us, really, how much value is added.
So no surprise that one gave us a yes.
But this is the last video.
And we've looked at all these different methods.
There are many different methods.
This one is the preferred method,
because it looks at time value of money, risk,
and tells us how much value is added.
That one's the best.
But out in the working world, people use all of these.
And oftentimes, they do multiple calculations.
And if all of these are saying no and this one's saying yes,
you probably are going, probably not a good idea
to invest in this project.
All right, lots of fun in Chapter 9.
We'll see you next chapter.