cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
klcwgs Frequent Visitor
Frequent Visitor

Create a measure to calculate IRR live

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Create a measure to calculate IRR live

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. Smiley Happy

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

 

Regards

2 REPLIES 2
klcwgs Frequent Visitor
Frequent Visitor

Help! Create measure to calculate IRR live

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 Super Contributor
Super Contributor

Re: Create a measure to calculate IRR live

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. Smiley Happy

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

 

Regards