Welcome to Finance and Excel video number 73.
Hey, if you want to download this workbook for chapter 8,
or the PowerPoints for chapter 8,
click on the link directly below the video
and scroll all the way down to the Finance Excel section.
In this video, we're got to talk about the IRR,
the internal rate of return.
But before we do that, let's just remind ourselves,
we did net present value and we built a net present value
profile.
What does this chart show us?
Here's our required rate of return.
This is our discount rate.
This is the one inside the firm.
They decide what their required rate of return is.
This is saying, includes all the risks
they might associate with the project,
then they say, OK, we require this return,
or we're not going to accept anything less than this return,
and we're not going to accept the project.
So what do we do?
When we calculate net present value,
we plotted net present value given the cash flows,
right, at all sorts of different rates.
We can see whatever that rate is right there,
anything less than that-- that means our required return--
we get a positive net present value.
Anything greater than this, we're
going to get a negative net present value.
And so this net present value profile
can easily tell us what that intersecting rate is.
That's called the internal rate of return.
It just means that is the hurdle, right?
So if this is 16%, anytime we have a required rate
of return less than 16% we are definitely
going to accept this project.
Anything greater-- so if the internal rate of return
of 16 and we said, no, no, no, no, our required returns
should be 19%, then we're not going to accept the project.
All right, this shouldn't come as a surprise to us
because we've already solved for rate.
Back in chapter 5, with annuities and multiple cash
flows, we knew some cash flows for a particular loan,
and we calculated the actual rate using the rate function.
Same with bonds, right?
We used the rate function.
Well, we knew the cash flows for a bond
and we determined yield to market, right?
So here we are in chapter 8.
We have multiple cash flows for buying assets.
Just as the yield to market yield to market
was internal rate of cash flows for bonds,
the IRR will be the internal rate of cash flows for capital
budgeting.
And they have a specific function called IRR.
We don't have to use rate.
And it is the easiest amongst all of the financial functions
to use.
IRR as is also incredibly common and popular
as an investment criteria.
Let's look at this chart right here.
Again, sometimes a picture tells a thousand words.
Let's just think about this.
Here's all these positive net present values,
here's all these negatives.
Well, what about exactly when it crosses the line?
What is net present value?
It's 0.
So that's the definition of the internal rate of return.
It's a rate at which net present value is equal to zero.
And if you were to set up the big, long math formula,
you would set up your discounted cash flow formula
and then just set it equal to 0 and solve for rate.
Luckily, we don't have to do that.
We can use the IRR function.
But again, what is the concept here?
If you just get that it is the hurdle, dividing line,
between positive net present value and negative net
present value.
Accepting the project, not accepting-- or rejecting--
the project.
That is what internal rate of return is going to mean for us.
All right, here's our rule.
Obviously if the IRR is greater than our required return--
this is the one that's internal to the cash flows,
meaning we will calculate this rate from the cash flows.
This is the one that's decided in advance inside the business,
right?
And we can see this picture over here, right?
Here's IRR.
Is this greater than all of these?
Yes.
And so that's what the rule means.
Anytime IRR is greater than our internal required--
sorry, I shouldn't use the word internal--
inside the business, their required rate of return,
then we accept the project.
This is the most important alternative
to net present value.
And it is the easiest to calculate
if you're using Excel, because the formula inputs will
be the cash flows only.
It's very popular out in the working world, also.
There are two problems with it which we'll see,
but besides those two problems, the internal rate of return--
or the IRR--
function and the net present value function
will give you the same decision.
Also, the reason why it's so popular
is because it's a percentage rate,
and people like to talk in terms of rates.
Now here's the IRR function.
There's a couple important things.
There's two arguments.
The values means you just type the cash flows into the cells
and then highlight them.
The guess, that's-- you don't usually need to put a guess
if it's having a hard time calculating because this is
an iterating function, meaning it goes back and forth,
and back and forth and zeros in on the answer.
Sometimes you have to give it a guess.
The values, these are going to be
the range of the cells with the cash flows.
Cash out is negative-- as we've done all through this class--
cash in is positive.
Range of values must contain at least one
positive and one negative.
A guess is not required.
If you get a #NUM error, then you might want to try a guess.
Cash flows must happen at the end of each period,
and look at this!
The programmers actually paid attention
when they did this function, IRR,
whereas when they did net present value
they didn't pay much attention.
Cash flow starts at time zero, right?
So all you've got to do is put 0, 1, 2, 3, 4, 5,
all the cash flows, and boom.
It'll calculate the internal rate of return.
Cash flows do not have to be equal in amount.
Again, totally awesome.
But the time between each cash flow must be the same.
IRR will give you the period rate as always.
All right, our
Example.
We're going to use the same example.
This is the fourth investment decision criteria
we're looking at.
We're looking at IRR here.
Let's go over to Excel.
Same cash flows we've had all through this chapter.
It's going to cost $160,000 and then
these are our three cash flows.
Our required rate of return is 12, right?
So IRR-- you're not going to believe this-- equals IRR.
0, 1, 2, 3, 4.
1, 2, 3.
So all of them.
That's it.
And it tells us 16%.
I've got to increase the column width here.
It's very simple.
If the internal rate of return is 16,
and we only have a required rate of return of 12%,
then we definitely take the project.
Right?
We want 12 and it's going to give us 16.
Because remember, this is the internal rate.
That means, from those cash flows,
what is the return going to be?
Again, just think back to when we did bonds and annuities
in chapter 5.
We were able to determine the rate.
So if the required return is less than or equal to 16--
actually they say just less than.
The way it works is if you happen
to get a required rate of return exactly
equal to the internal rate of return,
maybe you're supposed to be indifferent.
But if this is the return you want and you're exactly
at this, then why not take it?
So if the required return is less than or equal to 16.49--
and you can see if I increase the decimals there,
you can see that's the actual number with all
the decimal showing.
Actually, there's probably a few more, too, in there.
But at any time our required return is less than this right
here, then we accept.
Otherwise, we reject the project.
So because 12% percent is less than the 16.5,
we accept the project.
And when the required return is 16.5,
or whatever this is right here, net present value
will be exactly equal to 0.
Now we're going to build a chart for this one,
just like we did with the net present value.
Let's go ahead and do this here.
We're going to calculate net present value,
and then we're going to chart.
Because remember, this net present value profile,
when that crosses the x-axis, that
is the internal rate of return, or the crossover rate.
All right, so are you ready?
Equals net present value.
And our rate, I'm going to go one cell
to the left, comma, the values.
These ones right here.
And I'm going to hit the F4 key to lock it, close parentheses.
Plus, and then that railroad track or pound
sign right there, and I'm going to hit the F4 key.
All right, and I'm going to double click and send this
down.
Let's go to the bottom and make sure we got it right.
It looks-- see, it's got that.
And those are still locked.
Looking good.
I'm going to click Escape.
All right, now we want to do that chart
just like we did before.
We want a different color for when it's negative.
I'm going to go steal this function, Copy
without the equals sign, and what's true about this column?
Well actually, let's make the chart and then look at it,
and then we'll come back and do that formula,
and add the formula here and then
add that data to the chart.
So Insert, Area, and then right here.
Right?
So at this point right here it's all negative.
And so we need to add a second data series.
Actually, let me--
I'm going to click there and delete.
We'll add all that stuff back later.
I'm going to point to the edge here, and hold Shift, and make
the chart a little bit smaller.
Point to the edge and pull it down here.
OK, so now anytime net present value is negative,
we want some values in this column, otherwise we don't.
I'm going to come here, I'm going to type Equals
If, and our logical test is going to be--
and I'm going to Control-V. I copies that from over there.
Anytime that is less than 0--
and just out of curiosity, we did this chart one time before
and we were way down here.
What do you think it's going to be right now?
Is it going to be true or false?
This is a logical test.
If I hit the F9 key, false, right?
Because that 60,000 is not less than control, less than 0.
Control-Z, comma.
And you know what?
The last video, we did the formula this way.
There's another way to do this.
Let's just say if that, one cell to my left, is less than 0--
which is the net present value--
comma, what do we want?
We want one cell to my left.
Otherwise, we want "", which is the, in Excel formula language,
that means blank.
So I'm going to close parentheses, Control-Enter,
double click and send it down.
OK, so now we can see, right here,
it's going to give us all of those values.
Now we can click on the chart and we're
going to add that data series.
Up to the Chart Tools, Design, Select Data.
OK, now we need to do a lot of things here.
We need to fix this up just like we did last time.
Those are the wrong items, so I'm going to click right there
and remove, because we don't need that series.
This one right here, that's the net present value,
that's the one we want.
But we want to edit this, and I'm
going to highlight these percentages.
I'm going to click there, and then Control-Shift-Down Arrow.
And then I'm going to click OK.
Oh, no.
I'm going to click OK and scroll up.
I'm going to click there and delete.
Click on the chart and go back up here,
because we need to add this data series here.
Add.
The series name is the minus net present value.
I'm going to highlight that and delete.
It's very important to delete that because it'll get
in the way of you don't.
And then highlight just the numbers all the way down,
Control-Shift-Down Arrow.
Then click OK.
Edit, Control-Shift-Down Arrow.
OK.
Click OK.
OK, and now they're all--
actually, that doesn't look very good.
Let's go up here to layout.
And up here, I want to select the minus net present value.
Oh, OK.
We have a problem here.
We need to-- actually, we could probably edit it right here.
You could see it's highlighted right there.
You can either go back up to the design and then select data,
or you can click and drag this down.
If you can notice, somehow I didn't have the right range
there, but now it's there.
If you go back up to Design and then here,
the other way to have done it was to Edit it, right?
And so now you can see we've got the right label there,
and now it's looking from 13 to 44.
So that's correct.
Click OK, click OK.
And then make this a little bit bigger.
Click on this axis, Control-1 because we
want to go to number.
Maybe show decimal place as 0.
Click Close.
That's looking OK.
We need to add some labels for our horizontal axis
and vertical.
So I'm going to go to Layout.
Chart Axis Titles, horizontal below.
I'm going to type RRR, open parentheses, discount rate.
Now how come this label here, when I hit Enter,
why isn't that IRR?
Because these are all of our required rates of return.
It's just the single point at which this crosses the axis,
where net present value is 0, that's the IRR.
All right, and then we need a label over here,
so I'm going to go to Title, Vertical,
I'm going to type net present value, Enter.
All right, I'm going to drag this in a little bit.
All right, so it's just important when we're--
visually, it helps us to understand
exactly what this means.
Net present value equals to 0.
It's our hurdle rate.
Let's look at one more thing over in the PowerPoints.
We want to ask about our decision criteria--
IRR.
Are does the IRR rule account for the time value of money?
Yes it does.
Does the IRR rule account for the risk of the cash flows?
Yes.
We're comparing this rate to the required rate
of return, which has the risk.
Does the IRR rule provide an indication
about the increase in value?
It does, right?
Because we can compare it to our expected increase in value,
which, in our example, was 12%.
Right?
So yeah.
We're going to be getting more, right?
So it expresses the increase in value
as a percentage, not as a dollar amount.
The net present value expressed it as a dollar amount,
the IRR expresses it as a percent.
Should we consider IRR rule as our primary decision criteria?
Well, no, because of two circumstances which we'll see
in the next video.
But in fact, let's just jump to slide 50.
There it is.
NPV & IRR will generally give us the same decision
if we have conventional cash flows.
And we have a whole video on this one coming up,
but cash flows at time 0 is negative,
and the remaining cash flows are positive.
Anytime that's the situation, it's
OK to use either one of these.
IRR or NPV, both give the same answer,
and projects are in independent.
And this only comes into play when
you have more than one project or investment
you're considering, right?
And we'll talk about that in a video coming up.
But when both of these are true--
which means the projects are independent.
Who cares if you accept this one project?
So the decision to accept or reject this project
does not affect the decision to accept or reject
any other project.
If that's true, and the cash flows are conventional,
you can use either one.
All right, and those are the two circumstances.
And we'll see the problem when you have non-conventional cash
flows and we have mutually-exclusive projects.
But otherwise, this rule works great.
Advantages of IRR-- knowing a return
is intuitively appealing, and in fact that's
why, throughout history, it's been the most popular, right?
Because people like to talk in terms of percentages.
It is a simple way to communicate
the value of the project to someone who doesn't know all
the estimation details, right?
You just have that percentage.
That is the beauty and the advantage of percentages,
oftentimes.
If the IRR is high enough when you calculate it,
it's very high, you may not need to estimate a required return,
which is often a difficult task.
In the working world, many people use IRR.
So let's summarize.
We haven't gotten to the profitability index yet.
That's a video a couple of videos ahead.
But so far, we've done present value
and we accepted the project.
Payback period, reject.
Average accounting return, reject.
And internal rate of return we accepted.
All right, in our next video we'll talk about the IRR
again for non-conventional cash flow
and mutually-exclusive projects.
All right, see you next video.