cancel
Showing results for
Did you mean:
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-Raw Period End-Raw Period-Raw NetCashFlowBeforeInterest- Raw EquityCashFlow- Raw XIRR-Raw 1 31-07-20 0:00 1 -20000 -20000 14.0% 1 31-08-20 0:00 2 400000 500000 14.0% 2 30-09-20 0:00 1 -30000 -30000 10.0% 2 31-10-20 0:00 2 200000 250000 10.0% CashFlowID-Sum XIRR-Sum 1 14.0% 2 10.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]))

3 REPLIES 3
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.
Frequent Visitor

## Re: XIRR Function - CALC Filter Value

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)

Frequent Visitor

## Re: XIRR Function - CALC Filter Value

@v-lili6-msft

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

 NetCashFlowBeforeInterest PeriodDate CashFlowID CorporateTax Period End Period XIRR (250,057) 15-05-17 145 0 31-05-17 0 - 15-07-17 146 0 31-07-17 0 (348,306) 15-06-17 145 0 30-06-17 1 (250,010) 15-08-17 146 0 31-08-17 1 (7,759,974) 15-07-17 145 0 31-07-17 2 (3,001,440) 15-09-17 146 0 30-09-17 2 (151,709) 15-08-17 145 0 31-08-17 3 (3,850) 15-10-17 146 0 31-10-17 3 (142,823) 15-09-17 145 0 30-09-17 4 (180,968) 15-11-17 146 0 30-11-17 4 (157,490) 15-10-17 145 0 31-10-17 5 (50,553) 15-12-17 146 0 31-12-17 5 (155,955) 15-11-17 145 0 30-11-17 6 (18,794) 15-01-18 146 0 31-01-18 6 (621,471) 15-12-17 145 0 31-12-17 7 (23,427) 15-02-18 146 0 28-02-18 7 (235,838) 15-01-18 145 0 31-01-18 8 (43,211) 15-03-18 146 0 31-03-18 8 (138,249) 15-02-18 145 0 28-02-18 9 (35,640) 15-04-18 146 0 30-04-18 9 36,216 15-03-18 145 0 31-03-18 10 51,713 15-05-18 146 0 31-05-18 10 (56,211) 15-04-18 145 0 30-04-18 11 - 15-06-18 146 0 30-06-18 11 (605,218) 15-05-18 145 0 31-05-18 12 (54,664) 15-07-18 146 0 31-07-18 12 (456,878) 15-06-18 145 0 30-06-18 13 (94,088) 15-08-18 146 0 31-08-18 13 (701,458) 15-07-18 145 0 31-07-18 14 (52,726) 15-09-18 146 0 30-09-18 14 (941,692) 15-08-18 145 0 31-08-18 15 (8,138,447) 15-10-18 146 0 31-10-18 15 (1,153,876) 15-09-18 145 0 30-09-18 16 (624,164) 15-11-18 146 0 30-11-18 16 (5,862,441) 15-10-18 145 0 31-10-18 17 (600,640) 15-12-18 146 0 31-12-18 17 (5,425,137) 15-11-18 145 0 30-11-18 18 (499,039) 15-01-19 146 0 31-01-19 18 (39,483,915) 15-12-18 145 0 31-12-18 19 (612,957) 15-02-19 146 0 28-02-19 19 (729,184) 15-01-19 145 0 31-01-19 20 (469,591) 15-03-19 146 0 31-03-19 20 (708,178) 15-02-19 145 0 28-02-19 21 (1,966,873) 15-04-19 146 0 30-04-19 21 (669,823) 15-03-19 145 0 31-03-19 22 (119,278) 15-05-19 146 0 31-05-19 22 (79,249,307) 15-04-19 145 0 30-04-19 23 (1,833) 15-06-19 146 0 30-06-19 23 (2,011,479) 15-05-19 145 0 31-05-19 24 38,316 15-07-19 146 0 31-07-19 24 (1,477,223) 15-06-19 145 0 30-06-19 25 19,431,443 15-08-19 146 0 31-08-19 25 (1,496,638) 15-07-19 145 0 31-07-19 26 - 15-09-19 146 0 30-09-19 26 (1,851,760) 15-08-19 145 0 31-08-19 27 30,284 15-10-19 146 0 31-10-19 27 (2,045,514) 15-09-19 145 0 30-09-19 28 (1,190) 15-11-19 146 0 30-11-19 28 (2,052,810) 15-10-19 145 0 31-10-19 29 - 15-12-19 146 0 31-12-19 29 (2,552,881) 15-11-19 145 0 30-11-19 30 (170) 15-01-20 146 0 31-01-20 30 (2,534,006) 15-12-19 145 0 31-12-19 31 (1,190) 15-02-20 146 0 29-02-20 31 (2,502,405) 15-01-20 145 0 31-01-20 32 - 15-03-20 146 0 31-03-20 32 (2,377,231) 15-02-20 145 0 29-02-20 33 (170) 15-04-20 146 0 30-04-20 33 (1,980,918) 15-03-20 145 0 31-03-20 34 (1,190) 15-05-20 146 0 31-05-20 34 (7,662,700) 15-04-20 145 0 30-04-20 35 - 15-06-20 146 0 30-06-20 35 116,230,935 15-05-20 145 0 31-05-20 36 (170) 15-07-20 146 0 31-07-20 36 (1,587,657) 15-06-20 145 0 30-06-20 37 (2,891) 15-08-20 146 0 31-08-20 37 (1,475,437) 15-07-20 145 0 31-07-20 38 - 15-09-20 146 0 30-09-20 38 (1,967,288) 15-08-20 145 0 31-08-20 39 (1,232) 15-10-20 146 0 31-10-20 39 (2,233,785) 15-09-20 145 0 30-09-20 40 (1,190) 15-11-20 146 0 30-11-20 40 (2,562,115) 15-10-20 145 0 31-10-20 41 - 15-12-20 146 0 31-12-20 41 (2,716,351) 15-11-20 145 0 30-11-20 42 (170) 15-01-21 146 0 31-01-21 42 (1,892,247) 15-12-20 145 0 31-12-20 43 (1,190) 15-02-21 146 0 28-02-21 43 (13,432,056) 15-01-21 145 0 31-01-21 44 1,807,504 15-03-21 146 0 31-03-21 44 131,964,060 15-02-21 145 0 28-02-21 45

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.