xIRR on yearly hierarchical data - how to?

[sashasneg3]

Hi all,

I would really appreciate any advice on this. 

I have a 3 level hierarchy (Scenario - Intervention - Location), I have a column with years, I have a column with investments (in different years there's an investment happening and after that there are annual returns, all in one column). 

I want to make a matrix table visual with 3 level hierarchy and an IRR column with values. As everyone knows, SUM of IRRs for all locations for 1 scenario and 1 initiative won't give me the real total IRR in this case. There needs to be a measure. 

Obviously, the usual formula does not work for me (xIRR). I tried calculate IRR with ALLEXCEPT but did not get the result. Any help would be appreciated... I feel like this formula should be a monster one and I just don't have knowledge about how to build it 😞 

My data looks like this: 

Per each scenario (I have 3) there are several interventions (3-5), for each intervention there are 10 locations. Years are between 2019 and 2030 but investments happen on different years per intervention. 

Scenario	Intervention	Location	Year	Investment
1	A	Q	2015	-10000
1	A	Q	2016	2000
1	A	Q	2017	2500
1	B	W	2016	-20000
1	B	W	2017	4500
2	C	R	2018	-4000
2	C	R	2019	3000
...	...	...	...	...

Thanks a lot,

Aleksandra

[v-danhe-msft]

Hi @sashasneg3 ,

From your description, I could not understand an IRR column with values

Could you please offer me more information about how the logic to calcualte this column?

And could you please post your desired result if possible?

Regards,

Daniel He

[sashasneg3]

@v-danhe-msft 

Hi Daniel, 

Sorry for my language 🙂 I meant IRR column with ... IRR numbers. Here's a desired result: 

A Matrix: 

Hierarchy (Scenario - Int - Location)    |  IRR 

Scenario 1                                               14%

               Intervention A                          10%

                              Location Q                -0.3%

                              Location W                 1%

Scenario 2 ....                                             ....

The logic is :  IRR =  xIRR(TableName, [Investment], [Year]) but because of the hierarchy and maybe because my dates are years only - the formula does not work.

Thanks for any help,

Aleksandra