IRR or XIRR

[garzajnc24]

Hello all,

Trying to figure out how to calculate XIRR or IRR for both of these tables but getting an error that it cannot find any values. Any tips on handling this. Thanks in advance. 

first table: IRR = XIRR(CF_TABLE, CF_TABLE[Valuation], CF_TABLE[EntryDate], .10, 0)

second table: Stakeholder_IRR =
XIRR(
'CAP Table'[Investment Amount], 
'CAP Table'[EntryDate], .10, 0
)

[garzjnc]

I figured out what I was doing wrong here. Thank you.

[Anonymous]

Hi @garzajnc24 ,

Thanks for your feedback. If your problem has been solved, please mark it as the correct solution, and point out if the problem persists.

Best regards,

Adamk Kong

[OwenAuger]

Hi @garzajnc24 

IRR cannot be computed if all values are the same sign. This is because there is no finite discount rate that would produce a zero NPV (see here for example).

Stepping back a bit, for IRR to make sense, you would need to define "cash inflows" and "cash outflows" based on your data for an IRR calculation to make sense (these can be notional inflows/outflows if necessary).

Looking at the tables you posted, did you want the IRR based on investment to date (outflow) and valuation at a point in time (inflow)?

[garzajnc24]

Thank you for your response.

I've been tasked to recreate formulas in an Excel spreadsheet in PowerBI. The actual dates don't matter so much as the periods. 

Their definition of valuation is EBITDA multiple x EBITDA. Easy to accomplish. 

Valuation = CF_TABLE[EBITDA] * CF_TABLE[ebitda_multiple]

their example:

Second, they want IRR and MOIC for each period as a predictor (if in this period they exit what was the valuation, IRR, MOIC at that time) 

MOIC = valuation / initial investment

IRR = IRR(C2:D3) for period 1

          IRR(C5:36) for period 2 etc..

3. They want a stakeholder Cap table. But I'll just focus on the main table for now.