1. IRR and payback period in Power BI
Welcome back! In this screencast, we will practice capital budgeting concepts with the internal rate of return and payback period while continuing to work on our dashboard for Projects A and B.
First, let's start with finding the internal rate of return. We'll create a new measure called “Project IRR” using XIRR.
The syntax for XIRR is very simple, as it only needs the table being referenced, the cash flows as values, and dates of those cash flows to calculate the IRR.
Guess is your guess of what IRR might be, but it’s totally optional, and really not needed.
The alternative result is what will be returned if there are any errors, so just put zero.
Once that’s loaded, we’ll format IRR as a percentage with two decimals.
We’ll create a visual with Project IRR in a column chart split by Project. Now it’s easy to see that Project A has a higher internal rate of return.
We want to zoom in on this, so we can make the range of the y-axis smaller by changing the range between point 95 and point 13.
Now let’s analyze the payback period. This requires a bit of DAX. Watch closely!
Remember that the payback period is just the period in which the cumulative cash flows breach zero.
So the first thing we need to do is create a running total of cash flows. We can easily do this with a quick measure.
We’ll select the running total calculation. Then we want to choose Cash flow as our base value, and Date as the field. Now we’ll let that load and rename it Cumulative cash flow.
Let’s put this into our matrix and compare it against cash flows.
We can see that the break-even dates are year 2025 for Project A, and 2026 for Project B because the cumulative cash flow is finally positive, but we need Power BI to see this too.
So, let’s create a new measure called “Break-even date”. Using CALCULATE, we need to find the first date that cumulative cash flow is not negative. So we’ll use MIN, then Date. We can FILTER for all dates where cumulative cash flow is greater or equal to zero.
Let’s put this into the Project metrics card and format it as a date.
And there it is! We have a nice-looking dashboard.
Now that you’ve seen it, it's your turn to try.
2. Let's practice!