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.