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.