Welcome to Finance in Excel video number 81.
Hey, if you want to download this workbook for chapter 9,
click on the link directly below the video.
And scroll all the way down to the Finance Excel Class
section.
Hey, we got to talk about the XNPV and the XIRR function.
But mostly we want to talk about this one--
totally amazing function.
If you were doing discounted cash flow
analysis to value assets, absolutely have to use this.
Now, this function will solve a lot of problems.
It is what you want to use if you're doing discounted cash
flow analysis in most cases.
However, in earlier versions, you
may have to add the Analysis ToolPak.
So 2007 and 2010, this function is already there.
In earlier version, you have to go to the Tools menu, Add-Ins,
and add the Analysis ToolPak.
Now the XNPV function solves a bunch
of problems we've had so far, or some of which we haven't talked
about.
We actually already did one video on this last chapter,
but we're going to be much more detailed here.
The problem is is oftentimes you maybe
don't have the exact time, the equal time between each cash
flow.
So here you can see four years out, we get this cash flow.
But then we don't get another cash flow until six years out.
And so the NPV function wouldn't be able to handle that.
If when we did NPV in earlier chapters, we did a RATE comma.
And then we highlighted the values not including zero.
But guess what?
It just counts.
This function counts-- 1, 2, 3, 4, 5.
So it would assume that this is 5 when really it's 6.
So XNPV will solve that problem.
Also we saw with the NPV function,
we always had to go plus the negative cash flow
at time 0, which is not a big deal.
But it's as if someone who first programmed this function
didn't realize that we needed that time 0 cash flow.
And guess what?
XNPV, absolutely no problem.
You just highlight from time 0 all the way to the end.
So there's a couple things that just make it
totally much easier.
Let's just see how this works.
And then I'm actually going to break it down and show you
the algorithm of how it calculates, because it can get
a slightly different answer than the PV function, which
a lot of people use when they get into this situation.
It's no problem.
PV function or the XNPV will be a few dollars or pennies off.
But we're talking about estimation here anyway.
So estimation, a few pennies or dollars usually doesn't matter.
Now let's just go ahead and see.
XNPV-- it needs a rate, still needs a discount rate,
because we're discounting it at our required return--
comma the values, including time 0--
I love that-- comma and then the dates,
including the date for time 0.
And that's it.
Irregular cash flows, irregular time periods, and it
includes time 0.
Now let's see how we did it--
in this class, we actually did do it the long way using the PV
function in earlier videos.
But let's see how that works, because we
want to compare the answer we get using this to the XNPV.
So present value, that just means
we have these future value amounts,
and we need to discount them back one at a time.
So the rate F4 NPER, that one, comma.
There is no PMT.
These are all lump sum, so comma comma and then
the lump sum, close parentheses.
Now because we're going to dump--
if you're using present value of a negative,
it gives you a positive.
But we don't want that, because we're
going to put these into the--
add them up as a cash flow.
So we need to actually put a negative here.
And now when I copy this down--
and I like that.
So we can see that here this will work,
because we didn't use the Date column.
But we very carefully typed 0, 1, 2, 3, 4,
and then put a 6 there.
So it knows, the PV function knows,
this cash flow should be discounted back six periods.
Now I'm going to come over here.
And I'm going to add these up.
This is net present value using the PV function--
Alt-Equals.
And I'm going to highlight these.
So you can see it's about $10 off.
And actually, I want to do cash flows for PV.
And then I'm going to wrap text, Home ribbon.
And this should be cash flows.
I think that's how you spell algorithm.
OK, so why is there a difference, a $10 difference?
And the reason why is the algorithm.
So I want to show you the algorithm about how XNPV works.
And the reason-- it comes down to it--
when it's out here at this cash flow,
it's going to say, hey, how do I calculate
the number of periods?
It's going to take the later date minus the earlier date.
And in Excel when you subtract a later date, you
take a later date and subtract an earlier date,
it gives you number of days between two dates.
So if we take the number of days and divide it by 365,
it'll give us an estimation of years.
Now, in order to see how the XNPV algorithm works,
I'm going to start my formula right here.
Now we're going to do the longhand formula
for discounting a future lump sum.
So I'm going to say equals.
Here's the future value, lump sum, divided
by 1 plus our discount rate.
And I'm going to have to lock that with the F4 key,
because we're going to copy this up and down.
Now, the only trick is, this is an annual rate.
And so whatever exponent we put for our number of periods
to discount this over, it's got to be in years.
So we do caret, which is our exponent.
And as we mentioned, we're going to have to subtract two dates
and divide by--
so subtracting two dates will give us the number of days.
And we divide by 365--
so two open parentheses and then later date minus earlier date.
Now, as we copy this formula up and down,
this one needs to go down.
But that always needs to be locked.
So I'm going to hit the F4 key, close parentheses.
That's the number of days divided by 365.
Now, just right now, we could just
do a little investigating to prove
to ourself that this actually is the number of days
between these two dates.
So I'm going to highlight that and hit the F9--
2,192.
So I'm going to take all the days divided by 365.
And notice because we're using days and an estimation,
because some years don't have--
actually that's-- this right here,
sometimes since we're subtracting dates,
the dates know that there's leap year.
This number does not.
So we'll get a little inaccuracy there.
Now I'm going to Control-Z. All right,
let's highlight this whole thing and prove to ourselves that
it's actually the exponent is in years--
sure enough, 6.0054.
Control-Z, close parentheses and Control-Enter.
Now we-- oops.
We need to copy this up and down.
I'm going to check it here.
OK, so it's got the later date minus the earlier date.
It worked fine.
Let's look up here.
And by the way, why is this working for the 0?
Oh, because this date minus this date is 0.
That means the whole exponent is 0, which means,
oh, anything raised to the 0 is 1, so that thing divided by 1.
OK, so the trick now is to add it all up.
So we're checking to see the net present values in XNPV
algorithm.
So Alt-Equals.
And then we get the same amount here.
And so it's just a matter of--
for PV, we were actually using just whole years.
And for the XNPV, we were actually
using day, so a little bit of a difference in our estimation.
But ultimately because this is so easy to use, we can--
and it's all estimation.
These future cash flows are all estimation.
This will be just fine.
Now let's look at the XIRR.
The XIRR is the same as the XNPV in that it can handle values,
including time 0, irregular amounts
comma and then the dates, dates that are different.
Now, we go ahead and if a guess is only necessary
if we get an error.
If there is, it's an iterating function.
And so sometimes you can have trouble in iteration.
So you could put in a guess, maybe something close to that.
But we shouldn't have a problem here.
So there is the XIRR.
Now we want to look at even--
we've already seen how awesome this is.
XNPV solved the irregular time period.
And it now includes time 0.
But watch this.
Up here it was all years.
We were just separated by years.
Down here, look at this.
We have a project.
It doesn't matter what date, because the exponent is using
days in calculating an exponent, which is
a partial year or a whole year.
You can put anything.
So this project, at time 0, we put in spend this amount.
And then we have these cash flows coming in months.
These are months.
And then next cash flow doesn't come in for another year
and then another year.
And then here, whoa, days--
three 700s coming in, boom, boom, boom, days in a row.
Wow.
And you're probably not going to have cash flows coming
in on days like this.
But we want to illustrate that the XNPV function will handle
that, just no problem at all.
So the rate-- there's a discount rate, values, including time 0,
dates.
Doesn't matter what kind of dates
as long as they're real dates, serial number dates.
There we have it.
The long way equals, and we'll take
the present value of this divided by 1 plus our discount
rate, F4 caret.
And then we got to take this date minus the--
this is the later date minus the earlier date.
So I'm going to lock the earlier date, because as we go down,
we want the purple box, the first bigger date,
to move relatively.
Close parentheses divided by 365.
Come over here, if I can do this.
If I come over here, oh, look I typed that.
This should be NPV.
And then I'm going to add this up.
Again, this is-- you wouldn't do this column here
if you're out there working.
But it does-- it is helpful to learn how this actually
is working and why it's a little bit different than the PV
method.
Finally, we could do our XIRR.
Got to be kidding me-- just the values, time 0, and the dates.
Now that would be hard to calculate by hand.
I'm sure glad that's XIRR.
All right, that's a lot about the amazing function
XNPV and XIRR.
We'll see you next video.