Excel Magic Trick 1430: DAX Functions XNPV & XIRR for Irregular Cash Flow Net Present Value & IRR

Excel Magic Trick 1430: DAX Functions XNPV & XIRR for Irregular Cash Flow Net Present Value & IRR


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.

6 comments

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *