Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))
Please help.
Hi @jamessun1000 - I've just encountered this very same issue myself. Did you ever manage to resolve this?
I have the same question. Thanks in advance!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |