Excel Finance Class 79: Investment Criteria: NPV, IRR, Payback, AAR, Profitability Index

Excel Finance Class 79: Investment Criteria: NPV, IRR, Payback, AAR, Profitability Index

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.


  1. I can't find the xls file on the website! Please write out the specific url! I could not find busn233ch08.xls

  2. Thank you. but how to calculate the irr for this problem. discount rate is 10% ( you have an investment of 100000$ (year 0 or base) and for 1st year cost 10000 , benefit 25000 . 2nd year cost 10000, benefit 50000. 3rd year cost 10000, benefit 50000.4th year cost 10000, benefit 50000. 5th year cost 10000, benefit 50000. thank you

Leave a Reply

Your email address will not be published. Required fields are marked *