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