Welcome to finance in Excel video number 80.
Hey, if you want to download this workbook, chapter 9,
click on the link directly below the video
and scroll all the way down to the Excel Finance class.
Hey, we've been talking about discounted cash flow analysis,
net present value, whether to--
valuation techniques for assets.
But we want to just in this video
look at patterns of cash flows.
Here's two projects.
They both cost $15,000.
But notice that the cash flows--
$8,000, $6,000, $1,000-- one, are exactly
the reverse of the other.
Now, you're probably never going to get cash flows
in projects like this.
But recognizing the difference between these
will help us understand cash flow patterns
and getting used to the data when you're doing--
these net present values and estimating future cash flows
is very helpful.
So first thing we want to notice is
that the total for both of these is exactly the same--
so if I click here and hit Alt-Equals, Control-Enter,
and copy it over.
So totals of cash flows are the same.
Hey, when would that happen?
If we were going to plot net present value
on this axis and required rate of return on this axis,
where would it be, because they're the same?
Hey, that's when the discount rate is zero.
So it'd be right on this axis right here.
Now, what we want to see here is--
the question I want to ask is, which one of these
is always going to be bigger?
Now, we're going to plot it.
We're going to actually calculate and prove it
to ourselves.
But you should be able to just look at these cash flows
and go, wait a second.
$8,000, $6,000, $5,000, $1,000-- they're reversed here.
That means all this project A, all of the bigger cash flows,
are earlier in the number of years.
Year 1, we get $8,000 back.
Immediately you think, bigger cash flow earlier.
There's less time to discount this and take out
the earnings or the interest.
Whereas this one, this one has to be discounted
across four time periods.
So that means it's going to take out
a lot more interest or earnings.
The whole idea behind present value
going backwards from a future value
to time 0, which is finding the present value,
you're taking out the earnings you think you're going to earn
or the interest-- so bigger cash flow later.
It's going to discount back and take out way more interest.
Cash flow or bigger cash flow earlier.
Less time to take out interest or earnings.
So automatically, this one right here,
because they're exactly the same total cash
flows, which means net present value at time 0,
cash flows earlier.
At all points, the net present value would be bigger.
So you don't even have to plot anything.
You just say, I want this project here.
Now if you were going to draw this--
and I'm actually going to draw this.
I'm going to go to Insert, Shapes.
I'm going to select a line.
I'm going to estimate $5,000, so that's somewhere right
about here.
I'm just going to go like this.
And let's say right here somewhere.
So that would be my estimate for A.
Actually I'm going to move the line out here like this.
And it crosses-- this net present value
profile crosses at net present value equals zero.
That's actually the point at which
IRR, whatever that rate is there, that's
the internal rate of return.
Well, let's do the same thing again.
I'm going to click right there, start at the same point.
But I'm just going to go to a different point here.
So this one right here--
if I go to there, right click, Edit Text,
and I'm going to say this one's A.
And we'll prove this to ourselves.
We're just estimating this.
This is something you should be able to just look
at these patterns of cash flow and go
immediately, net present value is higher at all points as
compared to B. So I'm going to--
actually, I'm going to point to Control, click on the edge,
and then drag over here.
This one will be--
and I'm going to edit this, double click
if I can get in there, take B. Oh, that didn't work.
I'm going to try that again--
Control-Click, and I'm going to let go
of the mouse but not Control.
And that'll copy it.
A-- all right, so those are the profiles we expect.
Because the cash flows are bigger later on,
this will be lower at all points.
Now let's go ahead and plot this.
I'm going to click right here--
equals net present value.
The rate, I'm going to click right there comma the values.
This is Project A, so I'm going to boop and then add
the negative value right there.
So that's wrong.
That's why I'm checking it down here.
You could see the green and the purple moved.
So I forgot to lock them.
So I'm going to have to put this one in Edit mode.
Click right there.
Actually I could highlight and hit the F4 key
and then click my cursor right there and hit the F4 key.
Control-Enter.
And now if I double click and send it down,
it will populate that column with a new formula.
I click in the [INAUDIBLE] and see I can see I got it right.
All right, now watch this.
I'm going to cheat here.
This is a trick.
I'm going to copy that and escape.
Put it into edit mode, F2, and then Control-V. And watch this.
I'm just going to move the green box.
I'm pointing to the edge of that little cursor right
there is the move cursor.
So then I got these cash flows with that rate.
Now I can plot this.
And if we're going to do--
we're going to do an xy scatter.
The x value is here.
However many columns you have over here,
it will interpret each one as a separate y value.
With this being the x, highlight that.
Go up to Insert Scatter.
I'm going to select this one right here.
And you could see we estimated with a straight line.
But now we can see--
pulling on the edge.
Click down here.
And I'm going to actually right click there.
Control-1, or right click, Format, and say,
put the legend at the top.
I'm going to add a label here.
Go up to the Chart Tools, Layout, Axis.
I'm going to go horizontal.
And the horizontal, I'm going to immediately type RRR, Enter.
That's required rate of return.
I'm going to go back up to Axis Title, Vertical, that one.
And I'm immediately going to type NPV, Enter.
So now we have our chart.
And just as we suspected, we are trying
to recognize cash flow patterns without even
having to plot it or calculate net present value.
But you can clearly see our guess
was A was going to be bigger at all points,
because bigger cash flows earlier in the project.
B was going to be smaller at all points,
because bigger cash flows later.
Our NPV function and our chart show that our estimate here
was correct.
Now, this will give you the actual values.
But this is just the concept, the idea,
the recognizing patterns of cash flows,
future cash flows, when doing discounted cash flow analysis
to value an asset.
All right, we'll see you next video.