Welcome to Finance in Excel video number 69.
Hey, if you want to download this workbook for chapter 8
or the PowerPoints for chapter 8,
click on the link directly below this video,
and scroll all the way down to the Excel Finance Class
section.
Oh, we're in chapter 8 talking about investment criteria
net present value.
We just talked about that in the last video.
We saw how to estimate market value for an asset using
discounted cash flow valuation.
And we saw how to use the net present value function.
Now, net present value, totally important
that we learn how to build a net present value profile.
So first, we're going to calculate
our net present value of the estimated cash
flows for a particular asset.
And then the profile means you just
figure out what net present value is
at various rates in a table.
And then you chart it to see it visually.
And we'll see how to chart it a few different ways.
All right.
So we've estimated.
So should we launch a new product
that will be around five years that has these cash flows?
Here's the cash flows out, all cash out, all cash in.
So I'm going to do my formula right here--
equals all the cash in plus--
and I have these as negative here, so we can add.
Cash flow times 0 is minus 100,000,
that's the current price.
So the question is, do we buy it at 100,000?
Is that price just right, too much, or too little?
So here's our cash flow.
So for end of year 1, we're going
to have these cash flows, et cetera, et cetera.
Now we're going to come down here.
And we saw in our last video, we got
to use the net present value function.
We have to do our required rate of return.
That's determined internally, inside the business,
which includes whatever risk this project has.
And we've determined that it is 0.2.
So we simply use our net present value.
The rate, that is the period rate, but our period is annual,
so we're going to use that annual rate.
And then simply, bloop, do not highlight time 0.
Remember, as we said before, net present value function,
it's as if the people who created it years ago
didn't completely understand it.
So if you're calculating net present value,
all the cash will start at time 1 or later.
The net present value understands
that this is 1, 2, 3, 4, 5-- so the sequence of the cash
flows not only helps the net present value
function calculate all the individual discounting,
but it also knows how many periods are.
We simply calculate that.
If I enter that, we can see that's how much we
are willing to pay, 99,000.
So if we determine that we're willing to pay 99,000,
and they're charging 100,000, forget it.
We're not going to do it.
But we have to complete our calculations.
That's not the net present value calculation.
You then have to subtract the costs.
But since we have it listed as negative, I'm going to add.
Ooh, that's too many decimals.
I don't want to see all those.
All right so that's negative.
And as we mentioned in the last video,
the rule is, if net present value is positive,
we can accept the project; it adds value to the firm.
If it's negative, we're not going to accept it.
We're going to reject it.
It will decrease, take away value from the firm.
So the answer is no.
All right.
Now, we've determined this discount rate.
What if it actually was not really 20% or 0.2,
but it was 0.18, oh, then we get a positive.
So I'm going to do 0.2.
All right.
So that is where the net present value profile comes in.
And it's important.
It'll help us visually see what happens to net present value
as the rates change.
And the crossover point will tell us something interesting
also.
So I'm going to come here and do my net present value function.
The rate-- ah, I'm going to do that.
And it goes all the way down to 36 comma.
And the value is I'm simply going to highlight my cash flow
values right there.
Hit the F4 key to lock it, close parentheses, plus that cost,
which is negative, F4 to lock it, Control-Enter, and then
double-click, and send it down.
Oh, look at this.
As the discount rate increases, the price goes down.
We've talked about this inverse relationship
between discounting and the current price of a whatever
it is, whether it's a bond or a project or another business
or machine or whatever it is, whatever asset you're valuing,
rate goes up, price goes down, inversely related.
Oh, and there it is, exactly 20%, we have that.
Now we want to chart this.
And this is going to give us some good visual information.
Now, to chart, I'm going to highlight
the label, both of these labels here, and then all the way
down.
I'm going to use the keyboard shortcut,
Control-Shift-Down-Arrow.
So I've highlighted our rate here and our price here.
This will be on the horizontal axis.
This will be on the vertical axis.
And we're going to do an area chart.
Insert area, and then this one right here.
And lo and behold, look at that.
It shows us there's the crossover point.
That will become important.
But visually, you can see, boom, right there,
all this is added value.
All of this is subtracting value from the firm.
So at all of these rates here, the discount rates
or required rates of return, we're adding value to the firm.
That means we have a positive net present value.
And here, we have negative.
So anything, right, that little crossover, and we'll
determine that later.
All of these rates if our required return is up in here,
we're going to not take the project.
Now we need to clean this up.
I can see all sorts of problems here.
Look at that.
Didn't even get the right numbers at all.
So first let's click here.
Actually, we don't need to click anywhere.
We need to select the Chart.
There's the design.
These are Chart Tools, context-sensitive Ribbons
design, and there it is, Select Data.
This is the key to charting oftentimes
because the chart just didn't get it at all.
But here, we can redirect it.
So we click there.
Now, on this side are the numbers.
And on the series, right?
And over here is the horizontal labels or category.
So I'm going to click Edit.
And it just didn't put-- it didn't get anything.
It put in the default 1, 2, 3 we can see here.
So we simply make sure that the cursor is there
and then highlight the percentages.
Bloop, and then click OK.
All right.
So we can already see that's starting to look better there.
I'm going to click OK.
I'm going to delete this for the time being.
We will come back and talk about the
why I have those labels set up the way they are there.
All right.
So there we have our rates.
And this is a mess here.
Let's go back up here.
I shouldn't have closed that.
And here we can edit.
Now, I'm going to click here.
It looks like that one right there, we don't need that one,
so I'm going to Remove.
And then by default, it took this one there.
So you see the chart was there-- totally mixed everything up.
That will be fine.
I'm going to click OK.
Now, here, I need to format this.
So I'm going to click on it.
And right-click Format Access or Control-1.
Control-1, and I want to do Number.
And I'm going to say zero decimal places
and maybe have a currency, so like that.
OK, so we see some dollar signs there.
All right.
We'll Come-- We don't want this.
I'm going to delete this.
Now we want to talk a little bit about this right
here is the positive net present values.
Here are the negatives.
And I would like to see a different color.
I want this to be one color and this
to be another different color.
I want to maybe use red for bleeding, losing money,
and then green for making money.
So I'm going to click here and go up to Home.
And how about just use this button right here.
I'm going to go to More Colors and select a Green
or something.
Now, that's all.
That's not what we want, but that's the starting point.
And oftentimes, with charts in Excel, you have to trick them.
So I'm going to scoot this up here.
And the way you trick them is you
add a second series to the chart.
So that's why this column is going
to be a positive net present value
and this one is going to be negative.
So in essence, we're going to plot a second column
and add it, but it's just going to have the negative values.
That allows you-- allow, and then
you have two data series here, and you can change the color.
So I'm going to do a formula like this.
Really, we could just come down here to the crossover point
and go equals net present value.
Actually, we, could cheat and put this in Edit mode and copy.
Copy in Edit mode, Escape, put it back
into Edit mode and Control-V, right?
So that's the idea how to do this.
Now we have some values here and nothing here.
Now I'm going to come back and change this because right now,
if we change an input up here, this wouldn't work.
So we'll come back and fix it.
But right now, that gives us the idea
we want to highlight including the label.
Actually, no, let's do it this way.
Boop.
Chart Tools, Design, and then come back up here.
Oh, so not only can you edit, delete, but you can add.
So I'm going to click Add.
And the series, it's a series name, boop, that one there.
Now, series values, it always comes by default
without 1 in it.
You've got to highlight it and hit Delete.
Be sure and delete that.
And then highlight just the values,
including all those blank cells, which will be interpreted as 0.
And already, we can see that is so cool.
Click OK.
I'm going to click Edit.
This is a new one, and it's looking at the wrong thing.
It didn't interpret it right.
So I'm going to highlight these.
I don't think that would matter here,
but just to keep both of them, so anytime we click on them
we can see we have the correct labels.
Click OK.
So that's looking good.
Now we can click on this one.
And sometimes it's tricky to select,
but you can come up to the Layout,
and then over here, boom, you can pick any elements
you want in the chart.
I'm going to say series minus net present value reject there.
You can also use your arrow keys.
I'm using my arrow keys.
If you've ever noticed, the arrow keys inside of chart
move through the different elements.
That one, see they're all highlighted.
This one is just that.
And those are mean that all the other ones are 0.
So now we can change the color Home near Red.
Oh, looking great already.
So visually, we can see whatever that
dividing point, whatever that rate is there,
that's called a crossover rate.
It's actually called the internal rate of return.
It means, for this cash flows, that's
the rate at which net present value is 0.
But nevertheless, for us, just think of it as a hurdle, right?
So that little rate, whatever it is,
if our required rate of return is less than that,
we are definitely getting positive net present value.
If it's greater than that hurdle rate there,
then we're going to have minus net present value.
Now we need to do some more things.
We have to add labels so that when we're looking at this,
we know.
So I'm going to go up to Layout, Axis Titles.
I'll do the vertical first right here.
And I'm just going to type this one.
Notice before we link the cells, but we don't really
need to do this.
This one is highlighted.
You start typing--
I'm going to type NPV.
Notice, when you're typing, it's all
of a sudden default up here, but hit Enter, and it enters it.
Let's add another one.
This time I'm going to go horizontal, Title Below.
I'm going to type RRR space open parenthesis Discount.
You can see it up here and then hit Enter.
All right.
So that's starting to look good.
Now we definitely want to add the legend back in.
So I'm going to go to Layout.
There it is, Legend.
And I don't care--
actually, I do care where it is.
I want it to serve as the chart title.
So I'm going to say Legend at Top.
Ah, that is looking great.
Now let's do something else here.
How about insert?
Actually, no, we're going to leave it just like that.
That is our net present value profile.
I'm going to try and change the size a little bit,
and point to the edge, and click and drag in like that.
See if I can come over here.
Now I want this dynamic.
Right now, if we changed some cash flows or something,
if I made this minus 80,000, that chart's
not working so well, right?
So we need to fix this.
Ooh, I'm going to Control-Z.
We want it to be more dynamic.
So I'm going to come over here.
And we want to think how we're going to change this.
Because I want whatever rate hurdle there is
causes this to be negative, I want all of these formulas
to update, right?
And so we have to think about this.
Well, what do we know?
If net present value is negative,
then I want to run this calculation.
Otherwise, I don't, in this column.
So here's how you do it.
I'm going to highlight that.
And I'm going to say if the logical test is going to be,
is net present value less than 0?
That's a logical test.
And you can see, right now, if I hit the F9 key,
it'll give us true, Control-Z. Obviously,
if I copy this up right now, we get a bunch of falses up here.
But when it's less than 0, I type a comma,
that's a logical task comma.
What do I want in the cell?
Control-V, I want to actually run that calculation.
Otherwise, what do I want?
Nothing.
And nothing is double quote, double quote,
close parentheses, Control-Enter, double-click
and send it down, and then copy it up.
Right.
So now you can see it's blank up here.
And the chart works fine.
But now let's try this.
And I'm not going to try such a big value.
I'm going to say minus 40,000.
And now you can see the chart work just fine.
Those values moved up because, what are we doing?
We're saying is the net present value less than o?
If it is, run this calculation, otherwise blank.
So that makes it dynamic.
We have our labels up there.
I'm in a Control-Z, just to get it back there.
Let's make this minus 10,000.
Ooh, look at that.
So now the rate crossover rate is way up here.
Now I want to actually calculate this crossover rate.
And we have a whole video or a couple of videos,
about five videos ahead, where we'll
talk about what's called the internal rate of return.
But since we're looking at this profile here,
and we can clearly understand that there is a rate there,
which is like the hurdle.
When our required rate of return is less than that hurdle,
we're going to accept the project.
Anything above, our internal rate above that hurdle,
we're not going to take this.
I'm going to Control-Z, and we're
going to calculate this here.
And it's called IRR.
And I want to make sure that required rate of return
is inside the company.
That's the company determining what the discount rate is.
IRR is a rate determined by these cash flows.
It's as if we could determine the innate interest
rate from the cash flows.
Oh, but wait a second.
We already did that.
Remember in earlier chapters, like for bonds,
could we look at the cash flows and figure out
what the yield to market was?
Yeah.
In chapter 5, I think it was, we knew
the cash flows from a loan, and we were
able to determine the rate.
Now, in those earlier examples, we used the rate function.
But guess what?
They're specifically for this situation,
a function called IRR.
Just like for our net present value,
there's a net present value function.
So I'm going to do equals IRR.
And this is the easiest financial function there is.
It's as if the people, when they got around to programming IRR,
just programmed it perfectly.
Because all we have to do is highlight the cash flows
including time zero.
And it will tell us the actual hurdle.
And so I think if we increase the decimals a bit--
I'm going to go to the Home ribbon, and then we can see.
That is the hurdle rate.
Now, what's so amazing about this,
not only can we get the net present value,
the internal rate of return, which is that point at which
we have positive or negative net present value--
and everything here in the chart,
everything will update if we just come up here.
I'm going to do that 10,000 one again, right?
So now the internal rate of return is 29, way up here.
And you could even draw a little line there and say, hey,
that is the point at which the difference between positive net
present value and negative net present value.
All right.
That's a little bit about the net present value profile,
building a table to show the net percent value
profile and a chart.
All right.
We'll see you next video.