IRR v.s. XIRR

[jamessun1000]

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 match our external software. 

At the moment I have a measure calculating the XIRR of cash flows applied to a Clustered Bar Chart (dependant on the filter I apply).

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. 

[OwenAuger]

@jamessun1000

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

[jamessun1000]

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?

[OwenAuger]

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.

[Greg_Deckler]

Hmm,

Well, here is an article from @ImkeF on goal seeking with XIRR that might help if you have the manual formula for IRR.  https://www.thebiccountant.com/2017/05/23/goal-seeking-and-xirr-in-powerbi-and-powerquery/

Also, here is an Idea you can vote for:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19469422-create-an-irr-function