Forum Discussion

BINewbie_KJ's avatar
BINewbie_KJ
Frequent Visitor
7 years ago
Solved

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
Need Help
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    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

4 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    7 years ago

    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's avatar
      BINewbie_KJ
      Frequent Visitor
      7 years ago

      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