Reply
garzajnc24
Frequent Visitor

IRR or XIRR

 

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
)
 

 

garzajnc24_2-1728514834302.png

 

4 REPLIES 4
garzjnc
Frequent Visitor

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

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
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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:

garzajnc24_0-1728521968474.png

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..

garzajnc24_1-1728522114775.png

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

 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)