Excel Finance Class 27: Asset Valuation Using Discounted Cash Flow Analysis and PV Function

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

test attribution text

Add Comment