Need help with IRR calculation

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

Need Help

Anonymous

4 years ago

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)