Help with Investment Returns and IRR

[BINewbie_KJ]

Hello,
I am hoping to figure out how to return an XIRR on each Investment Entity I own.  I have tried the simple "IF" EntityID is equal, then XIRR function and get the

IF(

Selectedvalue('Entity Info'[EntityID]) =SelectedValue(FMVNOI[EntityID]),

xirr(FMVNOI,FMVNOI[Annual Activity],.2),0)

Error: There isn't a valid result for the XIRR function for the given values.

I have also tried something I found on the Help site, it's returning a value but I can't scale it across the dataset and the value looks incorrect.

IRR =

VAR First_CashFlow_Date =

CALCULATE( MIN ( FMVNOI[Date] ), FMVNOI[EntityID] = "MF1106" )

VAR Last_CashFlow_Date =

CALCULATE( MAX ( FMVNOI[Date] ), FMVNOI[EntityID] = "MF1106" )

VAR CashflowTable =

ADDCOLUMNS (

SUMMARIZE (

FMVNOI,

FMVNOI[Date],

FMVNOI[EntityID]

),

"Cashflow",

VAR OriginalValue = CALCULATE ( SUM ( FMVNOI[Annual Activity] ) )

VAR CashflowMultiplier =

SWITCH (

TRUE (),

FMVNOI[EntityID] = "MF1106" && FMVNOI[Date] = First_CashFlow_Date, -1, 

FMVNOI[EntityID] = "MF1106" && FMVNOI[Date] <> Last_CashFlow_Date, 0, -

)

RETURN

OriginalValue * CashflowMultiplier

)

RETURN

XIRR ( CashflowTable, [CashFlow] , FMVNOI[Date] )

I am a novice at programming, so any help would be greatly appreciated!! 

Table:
EntityID, Date, Activity

@XIRR

[OwenAuger · Accepted solution]

Hi @BINewbie_KJ 

Could you post a sample of your data?

The arguments for the XIRR function are:

1. Table (to iterate over)
2. Values
3. Dates
4. Guess (optional)

Your initial formula didn't seem to have a Dates argument.

The Values and Dates arguments are evaluated in the row context of the first Table argument, and can either be column references or other expressions.

The longer IRR expression you posted I believe was from a question a while ago where cashflows that needed their sign reversed under certain conditions, so may or may not apply in your situation.

Regards,

Owen

[BINewbie_KJ]

Thank you Owen for offering help. Data pasted below.

EntityID	PERIOD	Activity
100003	201801	-10583.7
100003	201802	-8236.2
100003	201803	-6599.17
100003	201804	-10579.6
100003	201805	-10581.1
100003	201806	-6599.26
100003	201807	-11115.3
100003	201808	-10022
100003	201809	-1623.42
100003	201810	-10110.1
100003	201811	-25276.4
100003	201812	-10590.9
100003	201901	-11161.9
100003	201902	-10050.6
100003	201903	-5466.06
100003	201904	-10620.4
100003	201905	-8868.55
100003	201906	-5499.66
100003	201907	-11176.2
100006	201801	178835.5
100006	201802	272612.5
100006	201803	173155.4
100006	201804	263643.2
100006	201805	82247.28
100006	201806	57220.69
100006	201807	192903.3
100006	201808	266491.4
100006	201809	164037.9
100006	201810	230024.2
100006	201811	449372.7
100006	201812	-112039
100006	201901	312232.2
100006	201902	310847.7
100006	201903	-38932.1
100006	201904	369918.6
100006	201905	240592.6
100006	201906	243543.6
100006	201907	122274.9
100009	201801	-2820.9
100009	201802	197959.4
100009	201803	16869.86
100009	201804	-10271.9
100009	201805	200801.4
100009	201806	12622.41
100009	201807	-233864
100009	201808	199656.5
100009	201809	-3904.6
100009	201810	-8701.49
100009	201811	186740
100009	201812	347490.2
100009	201901	2395.42
100009	201902	2239.15
100009	201903	91332.08
100009	201904	7468.26
100009	201905	92987.3
100009	201906	-11486.9
100009	201907	14606.47
100011	201801	68.02
100011	201802	1540.28
100011	201803	56.69
100011	201804	2271.87

@OwenAuger 

[BINewbie_KJ]

@OwenAuger 

I looked up an old post of yours and figured it out! Thank you vm!!

[OwenAuger]

@BINewbie_KJ 

Glad you solved it 🙂 Glancing at your data my main observation is that the PERIOD values would need to be converted to dates.