Welcome to Finance in Excel video number 78.
Hey, if you want to download this workbook for chapter 8,
click on the link directly below the video
and scroll all the way down to the Excel Finance section.
Hey, in this video here, we want to review
what we've learned about capital budgeting, at least the math
part of it, valuing, discounting future cash flows to estimate
market value.
And more importantly, we want to see
what to do when you have irregular cash flows
and irregular time periods.
Now, here's our situation.
You are consider buying a machine that
will yield $35,000 net cash over the next 10 years.
If you must earn a minimum return on investment,
that's a required return of 15%.
Should we buy the machine if it's a cost that much there?
Well, in this-- so we're going to do a little review.
We're going to do a present value, look at NPV,
and then we'll look at this new function XNVP.
And we'll see irregular cash flows and irregular time
periods.
Well, now this right here, we see this as an annuity.
So we can do this very simply with the present value
function.
The rate-- that's going to be our period
rate, which is annual, comma.
NPR is 10 years.
Payment is going to be in positive $135,000.
There's no future value amount, so we simply
close parentheses, and boom.
We're willing to pay $175,000 approximately.
That's how much we're willing to pay at our risk level.
And it's only selling for $165,000.
Looks like a good deal.
Now, the difference, I'm going to say negative that plus this,
because it's negative.
And that's our net present value,
our value added if we take on this project.
Now, the net present value function,
the rate is going to be our 15%, comma, our values.
Well, we have them down here.
Notice we don't need to do this particular one this way,
because it's an annuity.
And so we use the present value.
But we could use this to get the same answer.
And then we plus this negative right here.
And we get the same number there.
So when it's an annuity, you want to use the present value.
It's nice, sweet, and simple, takes up less spreadsheet
real estate.
But you could use this.
Now, when would you switch over to this?
Of course, the time periods are--
the time between each cash flow is exactly the same.
But the cash flows are different.
Then you switch over to the net present value.
And again, our rate, we're going to assume is this 15% here.
Actually I'm going to go click right there and then escape.
This would be our RRR.
We'll say 15% equals net present value.
We'll take that, comma, and these irregular cash
flows here, different amounts at different times--
that's what's great about the net present value--
and then add the cost, which is a negative cash
flow at time zero.
So again, I'm getting--
so we get a positive number.
And as we've seen the rule for net present value,
if it's positive, that means we're adding value.
And so that's a project we can take.
Now, let's get to the conclusion here.
What in the world are we going to do when we have these time
periods and these cash flows?
So 7, 8-- ooh, and then it skips--
9, 10, 11, 12, 13.
So the time periods are not the same.
Luckily there is XNPV function.
Now, in earlier versions, 2003 and earlier,
you have to go add the Analysis ToolPak
under Add-Ins the Tool menu, if I can remember way back then.
But now it's built in.
So you got the XNPV.
And it wants a rate.
I'm going to say 15%.
Just like the net present value function, the values,
those are going to be these.
And guess what?
It handles cash flow zero, unlike the net present value--
comma, and then the dates.
That's it.
Now we're going to--
OK, so there you go.
There is just-- that is so awesome.
So this function really solves all of our problems.
We don't have to do individual calculations.
Now we have irregular times, irregular amounts,
and boom, XNPV.
Now, if you were to discount back
each one of these individually using the present value
function, you would get an amount of $110.21 negative.
And by the way, this is negative.
So that means we don't take this project.
But what's going on here?
Well, the thing is is if you go up--
see, the present value function is looking at these periods
right here.
And so it assumes that it's discounting back
one year, three years, or periods in the general terms.
But the XNPV does something slightly different.
Because it has to deal with actual dates, it's going to--
the algorithm is slightly different,
which may give us a couple pennies difference.
Let's go up.
I'm going to click right here and just show you.
If you go-- and any time you have
a question about a function, if you open up the Functions
Arguments and click here, it's just amazing,
the one place in Microsoft Help that actually works.
And if you scroll down, usually they have an algorithm.
And there it is.
It's got to calculate of the date difference.
And it uses 365 in the denominator there.
So it's hard coded that in.
And we know that some years don't have exactly 365 days.
So that's why it would be a few pennies off.
I actually repeated the algorithm here just
to do the same kind of discounting here.
And you can look at that if you want here--
but just an amazing, great function in Excel, XNVP.
And if you don't mind using 365 days
in a year as your estimate, it's all estimates anyway.
When we're discounting back future cash flows,
it's all unknown future.
And we've said all these cash flows are going to occur there.
So XNVP, just awesome function.
All right, see you next video.