Create a measure to calculate IRR live

[klcwgs]

Hi

I have some difficulties calculating IRR using XIRR in PowerBI and was hoping to get some help here.

1. I have a table ("CF") with 3 columns:

Investment, Amount, Date.

I want to create a measure that calculate the IRR for each investment. when i use XIRR(CF,CF[CF_amount],CF[CF_Date],-0.1), PowerBI says an unexpected exception occurred.

2. If I have another table ("Inv_Details") with 2 columns:

Investment, Description

And I want to create a table of IRR for each investment by loading the related cashflows from the "CF" table, below doesn't seem to work? Basically I want to filter CF to get the relevant cashflows for an investment and then calculate IRR from those cashflows and dates.

XIRR(FILTER(CF,CF[Investment]=Inv_Details[Investment]),CF[CF_amount],CF[CF_Date])

Thanks for your help!

Best regards,

Chris

[v-ljerr-msft · Accepted solution]

Hi @klcwgs,

Is there any relationship between your "CF" table and ""Inv_Details"" table? If no, you may need to create one between them with CF[Investment] and Inv_Details[Investment] column first.

Then you can try using the formula below to add a new calculate column in "Inv_Details" table to calculate IRR for each investment by loading the related cashflows from the "CF" table.

Column = XIRR ( RELATEDTABLE ( CF ), CF[CF_amount], CF[CF_Date] )

Regards

[klcwgs]

Hi

I have some difficulties calculating IRR using XIRR in PowerBI and was hoping to get some help here.

1. I have a table ("CF") with 3 columns:

Investment, Amount, Date.

I want to create a measure that calculate the IRR for each investment. when i use XIRR(CF,CF[CF_amount],CF[CF_Date],-0.1), PowerBI says an unexpected exception occurred.

2. If I have another table ("Inv_Details") with 2 columns:

Investment, Description

And I want to create a table of IRR for each investment by loading the related cashflows from the "CF" table, below doesn't seem to work? Basically I want to filter CF to get the relevant cashflows for an investment and then calculate IRR from those cashflows and dates.

XIRR(FILTER(CF,CF[Investment]=Inv_Details[Investment]),CF[CF_amount],CF[CF_Date])

Thanks for your help!

Best regards,

Chris