Need help with IRR calculation

[Anonymous]

Hi,

I realize there's no IRR function like in excel, but there is the XIRR function. I have a series of periodic cashflows for a group of people with the date as the column name. I'd ultimately like to be able to group the cash flows based on characteristics of the people to get IRRs for the aggregate group. Example screenshot of what my data (highlighted yellow) looks like, using excel, and the irr solutions I'm seeking (not highlighted) for individual and aggregated (sumif in excel) and I used the IRR function rather than XIRR function (which I will need to modify slightly to get annualized IRR eventually: (1+[IRRCalc])^4-1).

 My full data set has many more cashflows and a few more columns I'll use as filters eventually. And about 10k people.

Is there a good way to get a dataset like this set up in Power BI?

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Power Query – Check four columns – Unpivot Column.

2. Create calculated column.

Date =
DATE(
    LEFT('Table2'[Attribute],4),
SWITCH(TRUE(),
    RIGHT('Table2'[Attribute],1)="1",3,
    RIGHT('Table2'[Attribute],1)="2",6,
    RIGHT('Table2'[Attribute],1)="3",9,
    RIGHT('Table2'[Attribute],1)="4",12)
    ,1)

3. Create calculated table.

Flase1 =
SUMMARIZE(
    'Table2','Table2'[Gender],'Table2'[Date],  "Sum",SUMX(FILTER(ALL('Table2'),'Table2'[Gender]=EARLIER('Table2'[Gender])&&'Table2'[Date]=EARLIER('Table2'[Date])),[Value]))

Flase2 =
SUMMARIZE(
    'Table2','Table2'[Type],'Table2'[Date],    "Sum",SUMX(FILTER(ALL('Table2'),'Table2'[Type]=EARLIER('Table2'[Type])&&'Table2'[Date]=EARLIER('Table2'[Date])),[Value]))

Flase3 =
UNION('Flase1','Flase2')

Flase4 =
var _table1=
SUMMARIZE(
   'Flase3',
    [Gender],
    "Date","IRRCalc",
    "Sum",
    XIRR(FILTER(ALL(Flase3),'Flase3'[Gender]=EARLIER('Flase3'[Gender])),[Sum],[Date]))
return
UNION('Flase3',_table1)

4. Result:

If you need pbix, please click here.

Need help with IRR calculation.pbix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[terrabyss]

Thanks Liu Yang; found the explanation very helpful. Could you reupload your .pbix please?

[lbendlin]

- unpivot your snapshots

- convert quarters into dates (first day of quarter or last day of quarter)

Then you can apply XIRR.

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523