Forum Discussion

Frequent Visitor

9 years ago

Solved

Rolling XIRR Calculation

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

mjfigg

9 years ago

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.

v-ljerr-msft

Microsoft Employee

9 years ago

Hi mjfigg,

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

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

Frequent Visitor

9 years ago

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.

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

Microsoft Employee

9 years ago

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

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

mjfigg

Frequent Visitor

9 years ago

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

fatherof4
Frequent Visitor
8 years ago
Is there a way to modify the code so that it works on Power Pivot as well?

mrothschild

Continued Contributor

7 years ago

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
Frequent Visitor
7 years ago
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

Forum Discussion

Rolling XIRR Calculation

Related content

Calculate XIRR

XIRR calculation using DAX

XIRR Calculation Error

Aggregate XIRR Calculation Help

xIRR calculation discrepancy