Welcome to Excel in Finance video number 27.
Hey, if you want to download this workbook for chapter five
or the PDFs for chapter five, click on the link
directly below the video.
And scroll down to the Finance section.
Hey, in the last video, we saw how
to do present value calculation for future cash flow.
So here's the deal.
We wanted a positive cash flow of $1,000 at time period 4,
2,000 at six, and 6,000 at 10.
And what did we do?
We discounted these cash flows back, took out all the interest
for each individual amount, and got how much we
would put in the bank.
Now, let's take this one step further.
These are positive cash flows.
This is how much we're paying to get these future cash flows.
So we're saying, I'm willing, at this rate here,
to pay $5,998.61 to get these future cash flows.
Let's go over to the PDF.
Here's how we did it here.
Scroll ahead.
And we're going to take this same idea.
But we're going to apply it in a different way.
Here's our future positive cash flows.
But this isn't us putting money in a bank and withdrawing it.
This is a machine.
So this is called discounted cash flow analysis
to value an asset.
So here it is.
You're deciding between a bunch of machines to buy.
So what if you plan to buy a machine that will--
and you've estimated, will yield these cash
flows, positive, positive, positive, these years.
And it could be negative and positive.
And we'll see an example of that later.
No problem, these are future cash flows,
positive ones that you want.
So your question is, how much are you
willing to pay for these?
Now, here's the situation.
You go out.
And you see the machine cost was 100k.
And you've estimated that these are the amounts of cash flow
that you can generate from this particular machine.
So no problem, we do our discounted cash flow analysis.
Here's our time.
These are the cash flows.
These are the future positive future value amounts.
We discount it back.
And we'll use our PV function, just like we've been doing.
And here's these amounts.
We add them up.
And no way, we are willing, just like in our bank account
example, we are willing to pay this amount in cash
to get these future positive cash flow amounts.
So this is an asset, though.
These are cash flow amounts we've estimated.
So as you can see, a pretty useful way
to see if a particular project is worth buying.
Now, the question is, if we are willing to pay 124,000
and some change, and the machine only cost 100,000,
do you buy it?
Absolutely.
You're willing to pay all of this, and it's less.
Boom.
So this is discounted cash flow analysis
to decide whether to buy an asset.
Now, there's lots of estimating going on.
So you have to do your projected revenues, and expenses,
and tax benefit of depreciation, and all sorts of things
to get your cash flows.
And we'll actually do some of that later in chapter 9
or something like that.
But for us right now, here's our cash flows.
And we want to do our same calculation
and calculate the present value.
I'm going to come over here.
The discount rate-- remember it could be annual rate,
could be APR.
We show our math symbol i.
But usually when you're doing present value of discounted
cash flows, we say discount rate.
Now, you could do it at time zero.
And so I'm going to just going to start right here,
present value, the rate, 15%.
Now, that's pretty high.
Usually, internally when you're analyzing purchasing an asset
it's not just an interest rate.
It's actually a discount rate or the return
you must earn on your asset.
So it's usually pretty high.
I'm going to hit F4, comma, NPDR.
That's this right here.
Payment-- we're not going to use that argument yet.
Future value, I'm going to click right there.
Control, Enter, and copy it down.
These are negative.
So we come up here, equals SUM, and highlight this range.
Notice if I make a mistake, no problem.
As long as the dancing ants are still dancing,
I just highlight that range right there.
And there it is, relatively straightforward.
The hard part is all the estimating,
but relatively straightforward when
it comes to creating a template in Excel.
124,000, you're willing to pay that for the machine.
It's only 100,000, you better jump on it.
Now, I did the same math over here.
And if you were in charge of the spreadsheet,
you'd want to do it a couple different ways.
Make sure you got it calculating correctly, and added it all up,
and got the same thing now.
Now, I'm and actually come down here.
And I'm going to clear all this.
We don't need this.
On the Home ribbon, you can go to Clear, Clear All.
And that clears formatting and content.
Now, one last thing, or two last things.
Let's figure out the added benefit of taking this project,
because you can see we're willing to spend 124,000,
124k approximately.
But the machine only cost 100k.
So this is called the net present value.
This is when you compare the valuation on the future cash
flows, as you see it, compared to the original cost.
Now, this is a negative.
We can see with our eyes, it's a positive of about 24,000.
So I'm actually going to say equals negative this.
That gives me the positive, plus that.
And that's because it's a negative, 24,000.
So that's the added value that we get
from taking on this project.
Now, these cash flows-- let's just say right here
it was only 10,000.
Still, there's a positive amount.
Now, what if it was 10,000 here?
And so now, the amount we're willing to pay
is much less than the price.
So now, the asset looks over-valued.
And you could see the net present value is a minus.
This is chapter 5.
We're learning about the basics of future value cash flow
analysis.
In chapters 8 and 9, we'll actually
do a net present value technique,
and actually calculate and estimate
some of these cash flows.
So we'll delve in much more deeply into the process of what
is called capital budgeting.
All right, next video, we will start
to talk about interest rates.
All right, see you next video.