Rolling XIRR Calculation

[mjfigg]

Hello there,

I am trying to build a rolling IRR calculation for an investment return report. No mater what I try, I cannot seem to get PBI to return a value for my formula. Can anyone help.

ROLLING IRR = CALCULATE(
XIRR('INVESTMENT RETURNS','INVESTMENT RETURNS'[FREE CASHFLOW FOR PERIOD],'INVESTMENT RETURNS'[DATE])
,FILTER('INVESTMENT RETURNS',EARLIER('INVESTMENT RETURNS'[Index])>='INVESTMENT RETURNS'[Index]))

There are six rows in my test table as follows:

Index	FREE CASHFLOW FOR PERIOD	DATE
42	- 7,531,293.00	30/06/2013
43	859,425.00	30/06/2014
44	901,305.00	30/06/2015
45	951,189.00	30/06/2016
46	7,742,641.00	30/06/2017
47	1,498,289.00	30/06/2018

Any help would be greatly appreciated.

[v-ljerr-msft]

Hi @mjfigg,

Based on my test, you should be able to use the formula below to build a rolling IRR in your scenario. 

ROLLING IRR = 
VAR minIndex =
    CALCULATE ( MIN ( 'INVESTMENT RETURNS'[Index] ), ALL ( 'INVESTMENT RETURNS' ) )
RETURN
    IF (
        'INVESTMENT RETURNS'[Index] > minIndex,
        CALCULATE (
            XIRR (
                'INVESTMENT RETURNS',
                'INVESTMENT RETURNS'[FREE CASHFLOW FOR PERIOD],
                'INVESTMENT RETURNS'[DATE]
            ),
            FILTER (
                'INVESTMENT RETURNS',
                EARLIER ( 'INVESTMENT RETURNS'[Index] ) >= 'INVESTMENT RETURNS'[Index]
            )
        )
    )

Regards

[mjfigg]

Hey there!

Thanks, the code worked great. However, I just realised that I will actually need another filter on the table for company name, as there will be multiple companies that an XIRR will need to be calculated for. See updated data below. Could you still help me?

Name	Index	FREE CASHFLOW FOR PERIOD	DATE
Company A	42	-7,531,293.00	30/06/2013
Company A	43	859,425.00	30/06/2014
Company A	44	901,305.00	30/06/2015
Company A	45	951,189.00	30/06/2016
Company A	46	7,742,641.00	30/06/2017
Company A	47	1,498,289.00	30/06/2018
Company B	48	-8401918	30/06/2014
Company B	49	949747	30/06/2015
Company B	50	942388	30/06/2016
Company B	51	943756	30/06/2017
Company B	52	1049089	30/06/2018

[v-ljerr-msft]

Hi @mjfigg,

Sure! The formula below should work in this new scenario. 

ROLLING IRR = 
VAR minIndex =
    CALCULATE (
        MIN ( 'INVESTMENT RETURNS'[Index] ),
        FILTER (
            ALL ( 'INVESTMENT RETURNS' ),
            'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
        )
    )
RETURN
    IF (
        'INVESTMENT RETURNS'[Index] > minIndex,
        CALCULATE (
            XIRR (
                'INVESTMENT RETURNS',
                'INVESTMENT RETURNS'[FREE CASHFLOW FOR PERIOD],
                'INVESTMENT RETURNS'[DATE]
            ),
            FILTER (
                'INVESTMENT RETURNS',
                EARLIER ( 'INVESTMENT RETURNS'[Index] ) >= 'INVESTMENT RETURNS'[Index]
                    && 'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
            )
        )
    )

Regards

[mjfigg]

Hi @v-ljerr-msft,

Apologies to keep on bothering you... however I cannot seem to get your formula to work on my actual data set. I have copied this below so we can see if you have any issues either. Thanks so much.

MJ

Name	FINANCIAL YEAR	DISTRIBUTIONS	EQUITYtemp	EQUITY BALANCE	ROI	NET ASSET VALUE ON SALE	Index	ACTUAL EQUITY MOVEMENTS	EQUITY REPAYMENT/(INVESTMENT) ON SALE	FREE CASHFLOW FOR PERIOD	DATE
Company A	2012	220,000.00	9629572	9,629,572	2.30%	8,544,134.21	0	-9,629,572	-1,085,438	-9,409,572	30/06/2012
Company A	2013	1,309,598.00		9,629,572	13.60%	8,279,143.54	1	0	-1,350,428	1,309,598	30/06/2013
Company A	2014	1,748,073.00		9,629,572	18.20%	8,250,943.49	2	0	-1,378,629	1,748,073	30/06/2014
Company A	2015	2,865,402.00	############	11,450,400	25.00%	25,753,143.93	3	-1,820,828	14,302,744	1,044,574	30/06/2015
Company A	2016	3,627,947.00		9,629,572	37.70%	26,247,601.43	4	1,820,828	16,618,029	5,448,775	30/06/2016
Company A	2017	3,326,656.00		9,629,572	34.50%	65,531,598.87	5	0	55,902,027	3,326,656	30/06/2017
Company A	2018	4,336,035.00		9,629,572	45.00%	65,463,524.70	6	0	55,833,953	4,336,035	30/06/2018
Company A	2019	4,618,150.00		9,629,572	48.00%	65,473,159.83	7	0	55,843,588	4,618,150	30/06/2019
Company A	2020	5,275,995.00		9,629,572	54.80%	65,471,361.84	8	0	55,841,790	5,275,995	30/06/2020
Company A	2021	5,323,785.00		9,629,572	55.30%	65,471,209.61	9	0	55,841,638	5,323,785	30/06/2021
Company A	2022	4,949,933.00		9,629,572	51.40%	65,471,339.58	10	0	55,841,768	4,949,933	30/06/2022
Company B	2011	-	19/02/9566	2,800,000	0.00%	2,668,345.50	11	-2,800,000	-131,655	-2,800,000	30/06/2011
Company B	2012	266,730.00		2,800,000	9.50%	2,646,045.08	12	0	-153,955	266,730	30/06/2012
Company B	2013	267,272.00		2,800,000	9.50%	2,846,349.35	13	0	46,349	267,272	30/06/2013
Company B	2014	301,676.00		2,800,000	10.80%	2,868,516.18	14	0	68,516	301,676	30/06/2014
Company B	2015	320,209.00		2,800,000	11.40%	3,711,889.98	15	0	911,890	320,209	30/06/2015
Company B	2016	422,263.00		2,800,000	15.10%	3,724,043.17	16	0	924,043	422,263	30/06/2016
Company B	2017	441,096.00		2,800,000	15.80%	3,691,333.20	17	0	891,333	441,096	30/06/2017
Company B	2018	261,140.00	2800000	2,800,000	9.30%	5,966,728.35	18	0	3,166,728	261,140	30/06/2018
Company B	2019	189,619.00	2800000	2,800,000	6.80%	6,156,209.63	19	0	3,356,210	189,619	30/06/2019
Company B	2020	20,898.00	2800000	2,800,000	0.70%	6,306,108.41	20	0	3,506,108	20,898	30/06/2020
Company B	2022	500,000.00	2800000	2,800,000	17.90%	6,242,283.16	21	0	3,442,283	500,000	30/06/2022
Company C	2011	659,079.00	100	100	659079.00%	9,331,458.81	22	-100	9,331,359	658,979	30/06/2011
Company C	2012	540,616.00		100	540616.00%	9,277,099.06	23	0	9,276,999	540,616	30/06/2012
Company C	2013	742,004.00	159820	159,820	464.30%	9,258,662.81	24	-159,720	9,098,843	582,284	30/06/2013
Company C	2014	845,771.00		100	845771.00%	9,265,568.34	25	159,720	9,265,468	1,005,491	30/06/2014
Company C	2015	1,073,678.00		100	1073678.00%	11,398,818.49	26	0	11,398,718	1,073,678	30/06/2015
Company C	2016	1,121,581.00		100	1121581.00%	11,434,088.18	27	0	11,433,988	1,121,581	30/06/2016
Company C	2017	1,316,995.00		100	1316995.00%	11,394,817.59	28	0	11,394,718	1,316,995	30/06/2017
Company C	2018	1,557,526.00	100	100	1557526.50%	11,396,678.18	29	0	11,396,578	1,557,526	30/06/2018
Company D	2013	68,707.00	7600000	7,600,000	0.90%	7,292,257.58	42	-7,600,000	-307,742	-7,531,293	30/06/2013
Company D	2014	859,425.00		7,600,000	11.30%	7,345,734.20	43	0	-254,266	859,425	30/06/2014
Company D	2015	901,305.00		7,600,000	11.90%	7,375,734.52	44	0	-224,265	901,305	30/06/2015
Company D	2016	951,189.00		7,600,000	12.50%	9,588,364.56	45	0	1,988,365	951,189	30/06/2016
Company D	2017	4,428,202.00	4285561	4,285,561	103.30%	3,997,812.72	46	3,314,439	-287,748	7,742,641	30/06/2017
Company D	2018	1,498,289.00		4,285,561	35.00%	0	47	0	-4,285,561	1,498,289	30/06/2018
Company E	2014	273,082.00	8675000	8,675,000	3.10%	8,398,839.45	48	-8,675,000	-276,161	-8,401,918	30/06/2014
Company E	2015	949,747.00		8,675,000	10.90%	8,642,316.32	49	0	-32,684	949,747	30/06/2015
Company E	2016	942,388.00		8,675,000	10.90%	8,846,768.55	50	0	171,769	942,388	30/06/2016
Company E	2017	943,756.00		8,675,000	10.90%	9,870,640.33	51	0	1,195,640	943,756	30/06/2017
Company E	2018	1,049,089.00	8675000	8,675,000	12.10%	12,085,226.38	52	0	3,410,226	1,049,089	30/06/2018
Company E	2019	1,119,677.00	8675000	8,675,000	12.90%	11,969,884.17	53	0	3,294,884	1,119,677	30/06/2019
Company E	2020	625,193.00	8675000	8,675,000	7.20%	11,849,969.14	54	0	3,174,969	625,193	30/06/2020
Company E	2021	871,440.00	8675000	8,675,000	10.00%	11,702,695.72	55	0	3,027,696	871,440	30/06/2021
Company E	2022	1,069,488.00	8675000	8,675,000	12.30%	11,515,814.91	56	0	2,840,815	1,069,488	30/06/2022
Company F	2015	218,605.00	3736500	3,736,500	5.90%	4,251,481.98	57	-3,736,500	514,982	-3,517,895	30/06/2015
Company F	2016	160,000.00		3,736,500	4.30%	4,677,717.90	58	0	941,218	160,000	30/06/2016
Company F	2017	190,000.00		3,736,500	5.10%	4,840,240.82	59	0	1,103,741	190,000	30/06/2017
Company F	2018	-	3736500	3,736,500	0.00%	5,545,139.51	60	0	1,808,640	0	30/06/2018
Company F	2019	253,741.00	3736500	3,736,500	6.80%	5,544,110.50	61	0	1,807,610	253,741	30/06/2019
Company F	2020	578,817.00	3736500	3,736,500	15.50%	5,543,677.06	62	0	1,807,177	578,817	30/06/2020
Company F	2021	606,588.00	3736500	3,736,500	16.20%	5,543,616.03	63	0	1,807,116	606,588	30/06/2021
Company F	2022	640,160.00	3736500	3,736,500	17.10%	5,543,546.62	64	0	1,807,047	640,160	30/06/2022

[v-ljerr-msft]

Hi @mjfigg,

Remarks

The value is calculated as the rate that satisfies the following function:

The series of cash flow values must contain at least one positive number and one negative number.

---

After I a few test, I find the issue is with your data of Company C(there are only positive numbers, but no nagative number).

So you may need to correct your data, or use the the formula below to make it work. 

ROLLING IRR =
VAR minIndex =
    CALCULATE (
        MIN ( 'INVESTMENT RETURNS'[Index] ),
        FILTER (
            ALL ( 'INVESTMENT RETURNS' ),
            'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
        )
    )
VAR firstValue =
    CALCULATE (
        MIN ( 'INVESTMENT RETURNS'[FREE CASHFLOW FOR PERIOD] ),
        FILTER (
            ALL ( 'INVESTMENT RETURNS' ),
            'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
                && 'INVESTMENT RETURNS'[Index] = minIndex
        )
    )
RETURN
    IF (
        'INVESTMENT RETURNS'[Index] > minIndex
            && firstValue < 0,
        CALCULATE (
            XIRR (
                'INVESTMENT RETURNS',
                'INVESTMENT RETURNS'[FREE CASHFLOW FOR PERIOD],
                'INVESTMENT RETURNS'[DATE]
            ),
            FILTER (
                ALL ( 'INVESTMENT RETURNS' ),
                EARLIER ( 'INVESTMENT RETURNS'[Index] ) >= 'INVESTMENT RETURNS'[Index]
                    && 'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
            )
        )
    )

Regards

[mrothschild]

Hi @v-ljerr-msft and @mjfigg 

I evaluated this post, and I think the solution would work for my purposes with one slight tweak that I can't seem to figure out.  My data is in a PBIX table that is CROSSJOINED, so has, out of necessity, blanks or 0 values and I think the XIRR function is having trouble because of the blanks.  

Link to sample excel data: https://www.dropbox.com/s/hc3qx9y9jvs4n4k/Rolling%20XIRR.xlsx?dl=0

Essentially my data looks like this (from Excel and the desired Rolling XIRR column, as calculated in Excel, is shown).  

Asset ID	Period	Cash Flow	Month	Rolling IRR
X	0	-1000	4/30/2019
X	1	130	5/31/2019
X	2	130	6/30/2019	-100.0%
X	3	130	7/31/2019	-99.5%
X	4	130	8/31/2019	-94.7%
X	5	525	9/30/2019	15.0%
X	6		10/31/2019	15.0%
X	7		11/30/2019	15.0%
X	8		12/31/2019	15.0%
X	9		1/31/2020	15.0%
Y	0	-2000	5/31/2019
Y	1	250	6/30/2019
Y	2	250	7/31/2019	-100.0%
Y	3	250	8/31/2019	-99.6%
Y	4	250	9/30/2019	-95.6%
Y	5	250	10/31/2019	-83.2%
Y	6	250	11/30/2019	-61.4%
Y	7	250	12/31/2019	-32.5%
Y	8	250	1/31/2020	0.0%
Y	9	125	2/29/2020	16.6%
Z	0	-1500	6/30/2019
Z	1	175	7/31/2019
Z	2	175	8/31/2019	-100.0%
Z	3	175	9/30/2019	-99.7%
Z	4	175	10/31/2019	-96.6%
Z	5	175	11/30/2019	-86.8%
Z	6	175	12/31/2019	-68.8%
Z	7	550	1/31/2020	17.8%
Z	8		2/29/2020	17.8%
Z	9		3/31/2020	17.8%

[mjfigg]

Hey @mrothschild ,

I managed to get it working for you!

see file here: MJFIGG Updated file

What I did was just use my original code which has two key differences to yours:

• Index - which I have found to be easier when using the EARLIER function, and 
• Free Cashflow Capital - This is an optional column and is not strictly needed. For my purposes it was important because I needed to distinguish between Free Cashflow before Capital and potential capital payments. In your case I have just set this to 0 for all months. Essentially you now have two cashflow columns if you ever need it.

I could rewrite the code to remove the Free Cashflow Capital column, but I preferred to just get it finished for you. 

Hopefully this helps!!

All the best,

MJ

[mrothschild]

Thank you so much!

One question I have is that my actual table, as opposed to the sample data I posted, is calculated using CROSSJOIN, so doesn't actually show up in PowerQuery editor.  As a result, there's no easy way to create the Index column you used.  I'll search the forums for how to create this, but if you have any ideas without using PowerQuery, that would be the final piece.

Regardless, thanks again!

[mjfigg]

@mrothschild 

I updated the file again if you want to download it agin. I had a quick read online and it seems quite difficult to get an index in DAX rather than PowerQuery. I managed to come up with a very rough work around though for your case. Essentially arbitrarily allocating a greatly ascending value to your AssetID then multiplying that number by the period value. The result in this case being that each row has a unique value to it and then the RANK function can order them as an index would. 

This solution works now but may not work for every instance of data that you want to report on . Only other thing I can suggest at the moment is to try and play around with your query so you can manipulate it in powerquery. Let me know if you need some help with that... 

All the best...

[mrothschild]

@mjfigg 

Really appreciate your help.  I tried to start with simple data and was triangulating to a solution as a result, but when I applied to the larger dataset, I'm getting an error that XIRR can't find a solution.  Again, I'm assuming it has to do with blank cells and I'm respectful of your time, so if this is overwhelming or I'm crazy to try to do this in its current form, let me know and I'll try a different path.  

Here's the link to the larger dataset that has your programming contained only changed by my attempt to create the calcualted DAX index.

https://www.dropbox.com/s/lo8m59ach4v3bf8/Rolling%20XIRR%20-%20new%20.pbix?dl=0

[mrothschild]

** Update ** 

I think what you did was this?

zzNew_Portfolio_Outputs[Period] + RANKX(zzNew_Portfolio_Outputs,zzNew_Portfolio_Outputs[Asset ID])

which creates an arbitrary index column.

Apologies and maybe I made a mistake, but the updated file (to me) looks like the original and I don't see a calculated index column or anything resembling RANK or transformation of Asset ID.  Again, error is likely mine, but I think I'm missing something significant.

[mrothschild]

Getting closer, but still have a grouping issue. 

Here's the link to the sampel PBIX file: https://www.dropbox.com/s/gkz6x35x7xg79xq/Rolling%20XIRR.pbix?dl=0

The following code provides a result, and I think it's something close to the Weighted Average XIRR by asset (weighted by Period 0 negative cash flow):  

zzXIRR 1 = 
CALCULATE(
    XIRR(GROUPBY(zzNew_Portfolio_Outputs,zzNew_Portfolio_Outputs[Asset ID],zzNew_Portfolio_Outputs[Cash Flow after Debt Service],zzNew_Portfolio_Outputs[Month]),
        zzNew_Portfolio_Outputs[Cash Flow after Debt Service],zzNew_Portfolio_Outputs[Month]),
        FILTER(zzNew_Portfolio_Outputs,NOT(ISBLANK(zzNew_Portfolio_Outputs[Cash Flow after Debt Service]))),
        FILTER(zzNew_Portfolio_Outputs,zzNew_Portfolio_Outputs[Month]<=
            CALCULATE(
                MAXX(GROUPBY(zzNew_Portfolio_Outputs,zzNew_Portfolio_Outputs[Asset ID],zzNew_Portfolio_Outputs[Month]),zzNew_Portfolio_Outputs[Month]),
                   FILTER(zzNew_Portfolio_Outputs,zzNew_Portfolio_Outputs[Asset ID]=EARLIER(zzNew_Portfolio_Outputs[Asset ID])
                )
            )
        )
        
)

The result from this formula is 16.72% but applies as a column to all three assets.  In excel, the WAVG is 16.65%.  

[mjfigg · Accepted solution]

Hi @v-ljerr-msft,

Thank you very much for your help! 

The formula works great, although I have made a few tweaks to include:

 - a terminal value (from the [equity repayment/(investment) on sale] column) to be calculated at the end of each period and calculated in the XIRR calc (i.e. if I sold the investment now, what would my XIRR be), and

 - a check to ensure that there is at least one positive cashflow (another requirement of the XIRR formula along with the first periof being negative).

see my code as follows fyi.... 

ROLLING XIRR = 
VAR countpos =
    CALCULATE(
        COUNT('INVESTMENT RETURNS'[totalcashflow]),
            FILTER(ALL('INVESTMENT RETURNS'), 'INVESTMENT RETURNS'[totalcashflow]>0 && EARLIER ('INVESTMENT RETURNS'[Index] ) >= 'INVESTMENT RETURNS'[Index] && 'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
              )
           )

VAR minIndex =
    CALCULATE (
        MIN ( 'INVESTMENT RETURNS'[Index] ),
        FILTER (
            ALL ( 'INVESTMENT RETURNS' ),
            'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
        )
    )
VAR firstValue =
    CALCULATE (
        MIN ( 'INVESTMENT RETURNS'[FREE CASHFLOW FOR PERIOD (excl. sale)] ),
        FILTER (
            ALL ( 'INVESTMENT RETURNS' ),
            'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
                && 'INVESTMENT RETURNS'[Index] = minIndex
        )
    )
RETURN
    IF (
        'INVESTMENT RETURNS'[Index] > minIndex
            && firstValue < 0
            && countpos >= 1 ,
            XIRR (
                UNION( 
                    SUMMARIZE( 
                        FILTER(ALL ( 'INVESTMENT RETURNS' ), EARLIER ('INVESTMENT RETURNS'[Index] ) >= 'INVESTMENT RETURNS'[Index] && 'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] ))
                        ,'INVESTMENT RETURNS'[Index],
                        "DATE1",MAX('INVESTMENT RETURNS'[DATE]),
                        "CASHFLOW",MAX('INVESTMENT RETURNS'[FREE CASHFLOW FOR PERIOD (excl. sale)])
                            ),
                    SUMMARIZE(
                        FILTER(ALL ( 'INVESTMENT RETURNS' ), EARLIER ('INVESTMENT RETURNS'[Index] ) = 'INVESTMENT RETURNS'[Index] ),
                        'INVESTMENT RETURNS'[Index],
                        "DATE1",MAX('INVESTMENT RETURNS'[DATE]),
                        "CASHFLOW",MAX('INVESTMENT RETURNS'[EQUITY REPAYMENT/(INVESTMENT) ON SALE])
                            )
                ),
                [CASHFLOW],
                [DATE1]
            )
    )

Thanks again for all your help. Much appreciated.

MJ

[DVA496]

Hi, 

I am trying to get the IRR (rolling or final) depending on a cutoff date which I included as a filter (different table containing all dates of my table). The cutoff date is a measure from a calcualted table which include calendar dates will 2050

S = SELECTEDVALUE(ValuationDate[Dates])

the table looks like this: there could be various assets and various value (entities)

Hope you can help,

Best,

Dyana 

[fatherof4]

Is there a way to modify the code so that it works on Power Pivot as well?

[fatherof4]

We figured it out.  The code needs to be entered as a calculated column not a measure on both Power BI and Power Pivot