XIRR formula is not working

Here is my formula

=XIRR(CONCATENATE("d119:"&ADDRESS(119,AT1+10)),CONCATENATE("d49:"&ADDRESS(49,AT1+10)),0.5) = #VALUE!

But if I just hardcode the range it works

=XIRR(D119:Z119,D49:Z49,0.5) = 10.20%

If I highlight the concatenate formula and press F9, it shows exactly the correct range

Then I tried "evaluate formula"

Here is how it looks before it gives error

XIRR("d119:z119","d49:z49",0.5)

is quotation marks causing the problem here?



so what is the problem here?

I think you need to use INDIRECT

Formula:

Please Login or Register  to view this content.

Thanks Alkey

I tried that before but it did not work. Result is #N/A

When I highlight your entire concatenate formula and press F9, it does not yield date range which it supposed to in order for XIRR to work

Instead it shows the actual numbers in chosen cells which XIRR can not work on. It needs date range like d119:z119

d119:z119 refers to cash flows and d49:z49 refers to years

I think that the CONCATENATE() function should be inside of the INDIRECT() function
...INDIRECT(CONCATENATE(...))