Excel Finance Class 81: XNPV Function XIRR Function – See Algorithm That XNPV uses

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

test attribution text

Add Comment