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..