topic Re: IRR or XIRR in DAX Commands and Tips

IRR or XIRR

garzajnc24 — Wed, 09 Oct 2024 23:03:41 GMT

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
)

Re: IRR or XIRR

OwenAuger — Thu, 10 Oct 2024 00:08:52 GMT

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)?

Re: IRR or XIRR

garzajnc24 — Thu, 10 Oct 2024 01:20:12 GMT

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.

Re: IRR or XIRR

garzjnc — Thu, 10 Oct 2024 11:44:44 GMT

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

Re: IRR or XIRR

Anonymous — Wed, 23 Oct 2024 08:54:55 GMT

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