cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
BINewbie_KJ Frequent Visitor
Frequent Visitor

Help with Investment Returns and IRR

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
1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Help with Investment Returns and IRR

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




4 REPLIES 4
OwenAuger Super Contributor
Super Contributor

Re: Help with Investment Returns and IRR

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




BINewbie_KJ Frequent Visitor
Frequent Visitor

Re: Help with Investment Returns and IRR

Thank you Owen for offering help. Data pasted below.

 

EntityIDPERIODActivity 
100003201801-10583.7 
100003201802-8236.2 
100003201803-6599.17 
100003201804-10579.6 
100003201805-10581.1 
100003201806-6599.26 
100003201807-11115.3 
100003201808-10022 
100003201809-1623.42 
100003201810-10110.1 
100003201811-25276.4 
100003201812-10590.9 
100003201901-11161.9 
100003201902-10050.6 
100003201903-5466.06 
100003201904-10620.4 
100003201905-8868.55 
100003201906-5499.66 
100003201907-11176.2 
100006201801178835.5 
100006201802272612.5 
100006201803173155.4 
100006201804263643.2 
10000620180582247.28 
10000620180657220.69 
100006201807192903.3 
100006201808266491.4 
100006201809164037.9 
100006201810230024.2 
100006201811449372.7 
100006201812-112039 
100006201901312232.2 
100006201902310847.7 
100006201903-38932.1 
100006201904369918.6 
100006201905240592.6 
100006201906243543.6 
100006201907122274.9 
100009201801-2820.9 
100009201802197959.4 
10000920180316869.86 
100009201804-10271.9 
100009201805200801.4 
10000920180612622.41 
100009201807-233864 
100009201808199656.5 
100009201809-3904.6 
100009201810-8701.49 
100009201811186740 
100009201812347490.2 
1000092019012395.42 
1000092019022239.15 
10000920190391332.08 
1000092019047468.26 
10000920190592987.3 
100009201906-11486.9 
10000920190714606.47 
10001120180168.02 
1000112018021540.28 
10001120180356.69 
1000112018042271.87 

 

@OwenAuger 

BINewbie_KJ Frequent Visitor
Frequent Visitor

Re: Help with Investment Returns and IRR

@OwenAuger 

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

OwenAuger Super Contributor
Super Contributor

Re: Help with Investment Returns and IRR

@BINewbie_KJ 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 256 members 2,634 guests
Please welcome our newest community members: