XIRR from a table of cash flows

[Richml]

Hi

I want to add a column that has the IRR for a project.

I have two tables:

1 - the Projects

2 - the Cash Flows

Tables linked 1-* on Project

Result is not filtering by row context - Help please!

Richard

[Martin_D]

This could be the easiest fix:

IRR = XIRR(FILTER('Cash Flows', 'Cash Flows'[Project] = 'Projects'[Project]), 'Cash Flows'[Cashflow], 'Cash Flows'[Cashflow date])

[Richml]

Thanks @Martin_D - this gave an error message "A result for XIRR could not be determined because the Values and Dates arguements are empty" 

[Martin_D]

Can you provide the file, pls.?

[Richml]

@Martin_D - Here you go.

https://drive.google.com/file/d/1f0k1nDl1zoehrCEu7HpR0qdx_C-01xfK/view?usp=sharing 

[Martin_D · Accepted solution]

The error was caused by project 3 for which there is no data in your Cash Flows table. This formula checks for this condition and works with your data:

IRR = 
IF (
    'Projects'[Project] IN VALUES ( 'Cash Flows'[Project] ),
    XIRR(FILTER('Cash Flows', 'Cash Flows'[Project] = 'Projects'[Project]), 'Cash Flows'[Cashflow], 'Cash Flows'[Cashflow date])
)

[Richml]

Thanks @Martin_D !