cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jamessun1000 Frequent Visitor
Frequent Visitor

XIRR Function - CALC Filter Value

Hi 

 

I have a data table with raw information. This table lists all project infomration required to calculate XIRR. 

 

This includes cash flows, date information and is listed in one consolidated table (i.e. there are multiple Projects referred to as Cashflow ID's within the one table). 

 

How do I calculate the XIRR on the NetCashFlowBeforeInterest cash flows specific to the one particular CashFlowID?

 

CashFlowID-RawPeriod End-RawPeriod-RawNetCashFlowBeforeInterest- RawEquityCashFlow- RawXIRR-Raw
131-07-20 0:001-20000-2000014.0%
131-08-20 0:00240000050000014.0%
230-09-20 0:001-30000-3000010.0%
231-10-20 0:00220000025000010.0%
      
CashFlowID-SumXIRR-Sum    
114.0%    
210.0%    

 

 (Raw Data table and summary table presented above). 

 

Ideally I would like the XIRR value returned within the table (as above) or calculated in a seperate table linking to the cash flows. 

 

 

At the moment I am using the following:

XIRR - Raw = CALCULATE(XIRR(Raw Data Table,Raw Table[NetCashFlowBeforeInterest],Raw Table'[PeriodDate]),FILTER(Raw Table,Sum Table[CasfhlowID-Sum]=Raw Table[CashFlowID]))

 

 

Please help.

3 REPLIES 3
Community Support Team
Community Support Team

Re: XIRR Function - CALC Filter Value

Can you show me the formula that you get the result (14.0%,14.0%,10.0%,10.0%) in the raw data table.
I do a measure but the result is different from your "XIRR - Raw" . So please show me more details for me.
thank you .
 
 
Best Regards,
Lin
Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jamessun1000 Frequent Visitor
Frequent Visitor

Re: XIRR Function - CALC Filter Value

Hi @v-lili6-msft

 

The 14% and 10% is there for illustration only to differentiate betwen detailed / raw data and the summary table. i.e. the 14% and 10% represent two unique projects IRR with the cash flows, dates and project identifier all in a single table (Rather than a pivot column).  

 

I cannot get the XIRR formula to work when there are multiple project cash flows in the one column and I attempt to add in a filter. If i remove the filter and just do XIRR (TABLE,Values,Dates) it is one IRR value for every single row (as it thinks its one continuos cash flow)

 

 

jamessun1000 Frequent Visitor
Frequent Visitor

Re: XIRR Function - CALC Filter Value

@v-lili6-msft

 

An example of the data I am looking at is as follows: 

 

 NetCashFlowBeforeInterest PeriodDateCashFlowIDCorporateTaxPeriod EndPeriodXIRR
                             (250,057)15-05-17145031-05-170 
                                           -  15-07-17146031-07-170 
                             (348,306)15-06-17145030-06-171 
                             (250,010)15-08-17146031-08-171 
                         (7,759,974)15-07-17145031-07-172 
                         (3,001,440)15-09-17146030-09-172 
                             (151,709)15-08-17145031-08-173 
                                  (3,850)15-10-17146031-10-173 
                             (142,823)15-09-17145030-09-174 
                             (180,968)15-11-17146030-11-174 
                             (157,490)15-10-17145031-10-175 
                               (50,553)15-12-17146031-12-175 
                             (155,955)15-11-17145030-11-176 
                               (18,794)15-01-18146031-01-186 
                             (621,471)15-12-17145031-12-177 
                               (23,427)15-02-18146028-02-187 
                             (235,838)15-01-18145031-01-188 
                               (43,211)15-03-18146031-03-188 
                             (138,249)15-02-18145028-02-189 
                               (35,640)15-04-18146030-04-189 
                                  36,21615-03-18145031-03-1810 
                                  51,71315-05-18146031-05-1810 
                               (56,211)15-04-18145030-04-1811 
                                           -  15-06-18146030-06-1811 
                             (605,218)15-05-18145031-05-1812 
                               (54,664)15-07-18146031-07-1812 
                             (456,878)15-06-18145030-06-1813 
                               (94,088)15-08-18146031-08-1813 
                             (701,458)15-07-18145031-07-1814 
                               (52,726)15-09-18146030-09-1814 
                             (941,692)15-08-18145031-08-1815 
                         (8,138,447)15-10-18146031-10-1815 
                         (1,153,876)15-09-18145030-09-1816 
                             (624,164)15-11-18146030-11-1816 
                         (5,862,441)15-10-18145031-10-1817 
                             (600,640)15-12-18146031-12-1817 
                         (5,425,137)15-11-18145030-11-1818 
                             (499,039)15-01-19146031-01-1918 
                       (39,483,915)15-12-18145031-12-1819 
                             (612,957)15-02-19146028-02-1919 
                             (729,184)15-01-19145031-01-1920 
                             (469,591)15-03-19146031-03-1920 
                             (708,178)15-02-19145028-02-1921 
                         (1,966,873)15-04-19146030-04-1921 
                             (669,823)15-03-19145031-03-1922 
                             (119,278)15-05-19146031-05-1922 
                       (79,249,307)15-04-19145030-04-1923 
                                  (1,833)15-06-19146030-06-1923 
                         (2,011,479)15-05-19145031-05-1924 
                                  38,31615-07-19146031-07-1924 
                         (1,477,223)15-06-19145030-06-1925 
                         19,431,44315-08-19146031-08-1925 
                         (1,496,638)15-07-19145031-07-1926 
                                           -  15-09-19146030-09-1926 
                         (1,851,760)15-08-19145031-08-1927 
                                  30,28415-10-19146031-10-1927 
                         (2,045,514)15-09-19145030-09-1928 
                                  (1,190)15-11-19146030-11-1928 
                         (2,052,810)15-10-19145031-10-1929 
                                           -  15-12-19146031-12-1929 
                         (2,552,881)15-11-19145030-11-1930 
                                     (170)15-01-20146031-01-2030 
                         (2,534,006)15-12-19145031-12-1931 
                                  (1,190)15-02-20146029-02-2031 
                         (2,502,405)15-01-20145031-01-2032 
                                           -  15-03-20146031-03-2032 
                         (2,377,231)15-02-20145029-02-2033 
                                     (170)15-04-20146030-04-2033 
                         (1,980,918)15-03-20145031-03-2034 
                                  (1,190)15-05-20146031-05-2034 
                         (7,662,700)15-04-20145030-04-2035 
                                           -  15-06-20146030-06-2035 
                       116,230,93515-05-20145031-05-2036 
                                     (170)15-07-20146031-07-2036 
                         (1,587,657)15-06-20145030-06-2037 
                                  (2,891)15-08-20146031-08-2037 
                         (1,475,437)15-07-20145031-07-2038 
                                           -  15-09-20146030-09-2038 
                         (1,967,288)15-08-20145031-08-2039 
                                  (1,232)15-10-20146031-10-2039 
                         (2,233,785)15-09-20145030-09-2040 
                                  (1,190)15-11-20146030-11-2040 
                         (2,562,115)15-10-20145031-10-2041 
                                           -  15-12-20146031-12-2041 
                         (2,716,351)15-11-20145030-11-2042 
                                     (170)15-01-21146031-01-2142 
                         (1,892,247)15-12-20145031-12-2043 
                                  (1,190)15-02-21146028-02-2143 
                       (13,432,056)15-01-21145031-01-2144 
                            1,807,50415-03-21146031-03-2144 
                       131,964,06015-02-21145028-02-2145 
       

 

This represents two unique projects (Project ID 145 and 146). How do I calculate the IRR of each Project to be presented in the above table or calculated in another table on a project by project basis. 


I do not want a rolling XIRR as I can see this in other sections of the forum.