Welcome to Excel Magic

Trick number 1,430. Hey, if you want to download

this Excel workbook file, either the Start

file or the Finish file so you could follow

along, click on the link below the video. Wow! Now we’re talking real fun. We get to talk about XNPV

and XIRR DAX functions for irregular cash

flow, net present value, and internal rate of

return calculations. Now anytime you get to talk

about financial cash flow analysis, whatever

tool you’re using, we’re talking fun

and usefulness. Now I’ve already done an

extensive 115 video playlist all about finance and all

the financial functions. And there are many different

financial functions in finance. So you can go look at

this reference video here, in particular video 81

talks about the amazing XNVP and XIRR. Now what distinguishes

the x functions from the normal NPV and IRR

is that we’re not limited to periodic even time payments. Both of these functions

can take any cash flows, regardless of the time

between the cash flows and give you net present

value and the internal rate of return. Now in this video

I’m not going to go into the algorithm for how

these two functions work. Go watch this video. I even show you

the exact algorithm including how XNPV uses

the proportion of a 365 day year for its irregular

time calculations, as opposed to the

present value that just uses increments of years. So I’m not going to

cover that in this video. But if you want to learn,

there is the video. There’s a whole playlist. Now I’ve already done

one DAX example here. You can come look at

it in the workbook. It does the exact same example

as video 81 in this series. We want to scroll down here

and look at the example we’re going to do. Here’s the table

we’re going to use. And we have a column

called Project, so Project A.

There are the dates for each one of the cash flows. And there are the cash flows. By the way, this example

right here, Project A, is the same as the one above. But this table has

many different projects we’re considering and

many different cash flows. And so we want an easy way

to build one formula that will instantly give us

net present value and IRR for each one of the projects. We also have this look-up

table or dimension table that has the required

rate of return for each one of the projects. That’s the hurdle over which

if we cannot get above these rates, we can’t consider the

project as a possible project to take on. Now if I click in

the Pivot table, I’ve already added both

the Dimension Required Rate of Return for Projects table. That’s this one, and

f many Cash Flows Projects, that’s this table. If we go over to

PowerPivot data model, I’ve already added the tables

and built a relationship. So the project has a unique

list here with the required rate of return for each project. Then of course, we

have many cash flows associated with the project

over here, including the date and cash flow. If we go over to Data view,

here’s our f many cash flows. And we want to build

our formula down here. So I’m going to click

in the measure grid up in the Formula bar, Net

Present Value for Project, colon, equal sign. And guess what? It’s named the exact same

thing as over in Excel. We’re going to start with

XNPV, Net Present Value. That means the

discounted cash flows. What are all of those cash

flows worth today at time zero given a particular

required rate of return? Now we have a table that

we have to iterate over. So f, and we have two tables. This is from the example

you can go and look at. But the one we’re

interested in is f many cash flow project,

tab, comma, the values, those are the cash flow, so fm. There is our table. And we down arrow

to cash flows, tab. Now remember this cash

flow column is all the cash flows for all the projects,

but because the Pivot table is going to have the

project in the row area, that filter or

criteria will flow in. So for example,

Project A will flow in and filtered down the

cash flow column just to Project A, comma. And then we need the dates,

fm, down arrow to Date tab. And again, what’s so beautiful

about the x functions for NPV an IRR is you don’t

need to have equal time periods between each

date, like you would if you were using

the present value function or the

future value function or PMT function, comma. And then we need our rate. Now a couple videos ago I did

a few different DAX videos. And a few of the comments said

how do you access a value over in the Excel spreadsheet? Well, here’s how you do it. You use the values function,

Values tab, values. We’ll look at the Pivot

table filter context and give us a unique list. So if we have project from

the look-up or dimension IRR for Projects table,

because there is only one of those projects

in each row, values will look to the filter

context and deliver just that row in the

dimension or Look-up table. So I in a down arrow DRR for

project, and the RRR that’s the required return. Now when we get down to

the grand total cell, there will be a

problem because it will deliver multiple values. But for each row

in the values area, values will do its job

getting just a single RRR. Close parentheses,

there is our rate, close parentheses and Enter. There is our error. And if you hover your

cursor, at the very end it says a table

of multiple values was supplied when a

single value was expected. So we have to turn this off

when it gets to the grand total. So the way we’re

going to do that is by using the if function. And logical test? We’re just going to ask

the question has one value. Does the column DRR for project,

project have exactly one value, close parentheses? Of course, the

filter context flows in, and for the values area– for each row in the

values area has one value will return a

true, comma, value. If true, there is our formula. We leave off the third

argument because we want to display a blank. The blank function

index is a substitute for an empty cell in Excel or

a null value in a database, close parentheses and Enter. Now let’s alt Tab. I’m going to drag my new

measure down to the values area. And there it is. I can see what did I forget? Number formatting. Alt-Tab, with that

cell selected, I go up, and I’m going to select English. Alt-Tab, and there we go. Now Alt-Tab, let’s

go back over here. We’ll calculate right

below net present value. Internal rate of return,

colon, equal sign, XIRR. The table’s going to

be fm, Tab, comma. It’s the same arguments, fm. And we need the values

which are our cash flows tab, fm, down arrow to

our irregular dates, comma. Now guess. If you put a guess that

you think is close, it can help with the

iterative process that IRR has to go through

as it’s trying to hone in on a value for the IRR. But most the time

you don’t need that. So you can leave

that argument off. That’s the same

over and Excel also. So that’s it. Close parentheses and Enter. I see I have a decimal. I can format this with

a percentage if I want. Let’s do this button right here. And that adds percentage

with two decimals. Alt-Tab, now I drag my internal

rate of return down over here. And just like that,

I have my Pivot table with net present value and

my internal rate of return. You know, I don’t really need

net present value for project, because I have the project

over here, Alt-Tab. Click on the cell. And I’m going to edit this up

in the Formula bar, backspace. And Enter, Alt-Tab. And there we go. Now of course, our table over

here is completely dynamic. If we made a

mistake for example, this cash flow right here was

not 700, it was minus 1,000, I can simply change

it in the table or add new records to

the bottom of the table. Come over here,

Right click, Refresh. And just like that, we get

an updated net present value, internal rate of return. All right. That’s a little bit

about how to use the XNPV function and the XIRR

function over here in DAX. I encourage you, if

you’re interested in different financial

calculations in Excel to check out this play less. All right. We’ll see you next video.

Excellent video, Mike. Loving how DAX is so easy in some cases like these. Thanks a lot.

Thank you Mike for this wonderful episode. I was wondering if we could calculate IRRs of the same cash flow table using Power Query?

Normally we can easily calculate IRR for a single product but it would be great to do the calculation for different products, just you used in this episode.

Thanks for showing that the DAX language has more than just the CALCULATE (and many other) functions for sales reports. A nice change of pace !

this might be my favorite channel on YouTube

Always enjoy your videos. Sensei

What's the difference between Values and Distinct? Can you show an example of that. Thanks.