XIRR for sorted dates URGENT

[Anonymous]

Greetings! I have a table called Transactions with 3 columns: ProductID, Date and Cash Flow. I used Power Query to sort by Date before importing it into the data model, but the sort order is not maintained. I am aware this has to do with storage optimization and such. However, I believe it is affecting my XIRR measure. The measure is defined as IRR = XIRR(Transactions, Transactions[Cash Flow], Transactions[Date]. I am using a Power Pivot table where the row context is ProductID and I need to add IRR as a value to the table (so that there is a separate IRR for each ProductID). But when I try to add the IRR measure to the value field I get the infamous "cannot find a solution" error. Is there any way to create a table sorted by date ascending within the IRR measure? When I calculate a single IRR for the entire columns in Excel and sort the Date column, the calculation is successful, so I know the cash flows are not the issue, bu tI believe the date order is.

[Anonymous]

Hi @OwenAuger 

Yes I am using Power Pivot in Excel. I have read that XIRR is not affected by order, but I strongly believe this is false because I have tested by running XIRR on an Excel table of dates and amounts and when I change the sort order of the table, the IRR either changes or cannot find a solution. Does this maybe have to do with the first value and date not being the earliest value and date? 

I have got data that I can post but I'm not sure how to upload the file...

[OwenAuger]

Thanks for the info 🙂

To share the data, you can use Google Drive, OneDrive, DropBox, or similar and share a link.

(Only Super Users can directly attach files to posts.)

The Excel XIRR function is certainly affected by the order of cells and may return an error if the dates are not in ascending order (from the documentation it appears that at the very least the earliest date must appear first).

However the DAX XIRR function (to my knowledge) automatically orders the dates behind the scenes, so doesn't rely on any existing ordering of the table. This means that any errors should be due to something else.

If you are able to post some data, I can try to help diagnose.

Regards

[Anonymous]

Thanks @OwenAuger. Here is a Dropbox link:

https://www.dropbox.com/scl/fi/k7zn8vwrqlq3lnaeifscv/IRRSample.xlsx?dl=0&rlkey=2tqa3psh070g6ukc06nyw...

You can see the yellwo highligted cell is a standard Excel IRR that finds a solution. If you sort the table by date ascending, the solution changes. I have loaded the table into the data model and created an XIRR measure called Annualized Return. In the measure pane of the data model, the measure finds a solution, but when I try and add it to the PivotTable with ProductID in the Rows field, I get the error. My intended use is to calculate an IRR for each ProductID using a PivotTable.

[OwenAuger]

Hi again @Anonymous 

Thanks for sharing the Excel workbook!

I've had a look and have attached my solution to this post.

My solution is to update your Annualized Return measure to this:

Annualized Return:=
VAR IRR_Try =
    XIRR ( Table1, Table1[Cash Flow], Table1[Date] )
RETURN
    IF (
        ISERROR ( IRR_Try ),
        BLANK (),
        XIRR ( Table1, Table1[Cash Flow], Table1[Date] )
        // Note: We cannot use IRR_Try here in Power Pivot as this does not handle the error
        // Instead, we have to recalculate the XIRR expression.
    )

The short explanation is that we need to handle any errors returned by XIRR. The above measure:

1. Tries to calculate the XIRR expression (storing it in a variable IRR_Try)
2. If it returns an error, return BLANK() instead.
3. If it does not return an error, we return the original XIRR expression.

Note that we cannot use the variable IRR_Try as the 3rd argument of IF as this oddly results in an error being returned due to the way this version of the engine handles the IF function.

If you don't want to replace errors with BLANK(), you could instead return any value you like, including a text value.

By using BLANK(), errors are effectively hidden, and you will only see the valid IRR values in the PivotTable.

If you were using the current version of Power BI, you could write this measure more simply:

Annualized Return = 
XIRR ( Table1, Table1[Cash Flow], Table1[Date], , BLANK() )

The reason that XIRR can't find a solution for some ProductIDs (e.g. 1 to 8 ) is that those ProductIDs have either only positive or only negative cashflows. We need at least one positive and one negative cashflow as a minimum requirement to calculate IRR.

Hope this helps!

Regards,

[Anonymous]

Thanks again @OwenAuger ! You've been a big help. But the response you've provided has given me a different issue. In my full version of the file, each line item belongs to a portfolio. So imagine there is another column called PortfolioID. I am using a slicer to slice the PivotTable by PortfolioID, and I need it to only display the products relevant to that portfolio, and for the IRR to be calculated at the portfolio level (not for the overall product). But now after I use the IRR function you provided, my slicer is always displaying all the products, but returns BLANK (or as I've replaced it with "N/A") for any IRRs outside of the slicer context. Any idea why this might be the case? Would it still be an issue if I move to Power BI and use the simpler formula you provided?

[OwenAuger]

@Anonymous 

Glad I've been able to help a bit!

Interesting...I'm having trouble replicating your current issue.

I created an arbitrary PortfolioID column with 10 ProductIDs per PortfolioID. When I filter on a particular PortfolioID, the PivotTable shows only the ProductIDs that you would expect.

Also just confirming the exact requirement: you want to filter by PortfolioID and display the IRR at ProductID level?

I've attached my file as an example, but could you attach a sanitised file demonstrating the issue?

[OwenAuger]

Hi there @Anonymous 

There is no ability to specify a sort order for the rows of a table passes to XIRR. Also, the ordering of the rows of a table loaded to the data model does not in itself affect the outcome of XIRR.

XIRR will throw the error you've described if it can't find a solution for at least one of the rows of the table or the total.

You could try filtering on different products to find the culprit in the current dataset.

I would also suggest adding some error-handling to the measure.

Could you post some sanitised data that exhibits the error?

Also, just to confirm, are you using the Power Pivot data model in Excel?

regards

[Anonymous]

Hi @OwenAuger . Sorry for the late response. I have't had the chance to sanitise the dataset yet. I'd like to get you a fairly complete version of my dataset (obviously falsified but complete in terms of dimensions) so that you can get the full picture. I have moved over to Power BI and am now using that exclusively, though Excel is still the source file. I did try the IRR formula you had mentioned previously for the latest version of Power BI / DAX (which I am running), but I had the same issue. Just wanted to let you know I'm still here!😂 I'll post some more info when I find the time, hopefully soon.