IRR v.s. XIRR

Question

Hi&nbsp;&nbsp;Our business uses a Software package that reports IRR on IRR (rather than XIRR formula). When I&nbsp;source the cash flow data and create charts / tables etc the XIRR output in Power BI does not match our external software.&nbsp;&nbsp;At the moment I have a measure calculating the XIRR of&nbsp;cash flows applied to a Clustered Bar Chart (dependant on the filter I apply).&nbsp;Is there a way to calculate the IRR rather than the XIRR? There is no IRR formula, but I was wondering whether I could apply the manual mathamatical formula and use a goal seek type approach to ensure the NPV = 0. Any other ideas would be much appreciated.&nbsp;&nbsp;

greg_deckler · Answer

Hmm,
&nbsp;
Well, here is an article from&nbsp;ImkeF on goal seeking with XIRR that might help if you have the manual formula for IRR. &nbsp;https://www.thebiccountant.com/2017/05/23/goal-seeking-and-xirr-in-powerbi-and-powerquery/
&nbsp;
Also, here is an Idea you can vote for:
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19469422-create-an-irr-function
&nbsp;
&nbsp;

owenauger · Answer

jamessun1000&nbsp;Since XIRR&nbsp;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.&nbsp;The exact formula depends on&nbsp;your tables&nbsp;etc, but you can use a structure like this to generate the table of "dates":VAR FirstDate = &lt;first date&gt;
VAR NumDates = &lt;num dates&gt;
VAR DateTableForIRR =
    GENERATESERIES ( FirstDate, FirstDate + ( NumDates - 1 ) * 365, 365 )// Then use this table within the DAX XIRR function

jamessun1000 · Answer

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&nbsp;in via Query Editor rather than DAX formula).&nbsp;&nbsp;An example of my data set is:&nbsp;Project Start DatePeriodPeriodDateProject ID&nbsp;EquityCashFlow&nbsp;01-07-18015-07-18205&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (1,099,275)01-07-18115-08-18205&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (58,785)01-07-18215-09-18205&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (58,785)01-07-18315-10-18205&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (58,785)01-07-18415-11-18205&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (1,546,285)01-07-18515-12-18205&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (96,285)01-07-18615-01-19205&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (96,285)01-07-18715-02-19205&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (96,285)01-07-18815-03-19205&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,000,00001-07-18915-04-19205&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,000,00001-07-181015-05-19205&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,000,000&nbsp;What are my next steps?

owenauger · Answer

Thanks 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.

Forum Discussion

IRR v.s. XIRR

4 Replies

Related content

Data Replication BC OnPrem v.s BC Cloud

Calculate XIRR

Calculating Weighted IRR using XIRR properly

XIRR calculation using DAX

XIRR Calculation Error

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