Forum Discussion
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)
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.