Forum Discussion
IRR v.s. XIRR
Hi Our business uses a Software package that reports IRR on IRR (rather than XIRR formula). When I source the cash flow data and create charts / tables etc the XIRR output in Power BI does not...
OwenAuger
Super User
Super User
Since XIRR treats 1 year as 365 days, you can replicate the results of the Excel IRR function by constructing a table of dates that are at 365 day intervals.
The exact formula depends on your tables etc, but you can use a structure like this to generate the table of "dates":
VAR FirstDate = <first date>
VAR NumDates = <num dates>
VAR DateTableForIRR =
GENERATESERIES ( FirstDate, FirstDate + ( NumDates - 1 ) * 365, 365 )
// Then use this table within the DAX XIRR function
I am by no means a power user! I have been using for ~1-2months and I am not familiar with VAR (assuming this is entered in via Query Editor rather than DAX formula).
An example of my data set is:
| Project Start Date | Period | PeriodDate | Project ID | EquityCashFlow |
| 01-07-18 | 0 | 15-07-18 | 205 | (1,099,275) |
| 01-07-18 | 1 | 15-08-18 | 205 | (58,785) |
| 01-07-18 | 2 | 15-09-18 | 205 | (58,785) |
| 01-07-18 | 3 | 15-10-18 | 205 | (58,785) |
| 01-07-18 | 4 | 15-11-18 | 205 | (1,546,285) |
| 01-07-18 | 5 | 15-12-18 | 205 | (96,285) |
| 01-07-18 | 6 | 15-01-19 | 205 | (96,285) |
| 01-07-18 | 7 | 15-02-19 | 205 | (96,285) |
| 01-07-18 | 8 | 15-03-19 | 205 | 2,000,000 |
| 01-07-18 | 9 | 15-04-19 | 205 | 2,000,000 |
| 01-07-18 | 10 | 15-05-19 | 205 | 2,000,000 |
What are my next steps?
- OwenAugerThanks for posting the sample data 🙂
Actually my above code snippet is DAX, as I am thinking of replicating the behaviour of IRR with the DAX XIRR function, by controlling the dates provided to XIRR.
Could you show me the IRR value you expect for Project 205 from the table of cashflows?
The critical thing to work out is the fractional number of years since time zero to assign to each cashflow.