XIRR with a dynamic range

[DWig]

Hello all, I'm back.

I am trying to calculate the XIRR for a series of projects, and the timing of cash flows changes on them. However, the cash flows will always be in row 48, and the months will always be in row 5. The ending period (which can extend in XIRR past the last cash flow, so it doesn't have to be dynamic) is always in column HQ.

The correct XIRR formula is as follows:

=XIRR(EK48:$HQ$48,EK5:$HQ$5)

I can get a dynamic beginning cash flow value as follows:
=ADDRESS(5,COLUMN(INDEX(J48:HQ48,MATCH(TRUE,INDEX(J48:HQ48<>0,0),0))))

This gives me the value $EK$5, which is correct.

The dynamic beginning month value would be as follows:
=ADDRESS(48,COLUMN(INDEX(J48:HQ48,MATCH(TRUE,INDEX(J48:HQ48<>0,0),0))))

This gives me the value $EK$48, which is correct.

However, trying to add them together as follows:

=XIRR(ADDRESS(48,COLUMN(INDEX(J48:HQ48,MATCH(TRUE,INDEX(J48:HQ48<>0,0),0)))):$HQ$48,ADDRESS(5,COLUMN(INDEX(J48:HQ48,MATCH(TRUE,INDEX(J48:HQ48<>0,0),0)))):$HQ$5)

Gives me the following error message and doesn't allow the formula to be confirmed:
"The formula you typed contains an error.
-For information about fixing common problems, click Help.
-To get assistance in entering a function, click Function Wizard (Formulas tab, Function Library group).
-If you are no trying to enter a formula, avoid using an equal sign (=) or minus sign(-), or precede it with a single quotation mark (')."


I've tried a couple different methods of concatenation and quotation marks with limited success. For example:
=XIRR(ADDRESS(48,COLUMN(INDEX(J48:HQ48,MATCH(TRUE,INDEX(J48:HQ48<>0,0),0))))&":$HQ$48",ADDRESS(5,COLUMN(INDEX(J48:HQ48,MATCH(TRUE,INDEX(J48:HQ48<>0,0),0))))&":$HQ$5")
Allows the formula to be confirmed. However, the result is a #VALUE! error. When I show the calculation steps, it shows the following:

"XIRR("$EK$48:$HQ$48","$EK$5:$HQ$5")
The next evaluation will result in an error."

I'm not really sure where the problem is. I also tried removing the absolute references in the "address" functions with the same error, but didn't include it for the sake of brevity. Any guidance would be very much appreciated.

Thanks,
DWig

 

[DWig]

Bump

 

[DWig]

Bump

 

[DWig]

Bump

 

[Andrew Poulsom]

Try (untested):

=XIRR(INDIRECT(ADDRESS(48,COLUMN(INDEX(J48:HQ48,MATCH(TRUE,INDEX(J48:HQ48<>0,0),0))))&":$HQ$48"),INDIRECT(ADDRESS(5,COLUMN(INDEX(J48:HQ48,MATCH(TRUE,INDEX(J48:HQ48<>0,0),0))))&":$HQ$5"))

 

[DWig]

That worked like a charm. Thanks a bunch! So any time you're making an equation part of a cell reference, you have to use "indirect"?

 

[Andrew Poulsom]

No, INDIRECT converts the text into a reference.

 

[circledchicken]

Hi,

For an alternative way to write your formula perhaps try something like this:

In A1 (using CTRL-SHIFT-ENTER rather than just ENTER):

=MATCH(TRUE, J48:HQ48 <> "", 0)

In A2 (with just ENTER):

=XIRR(
    INDEX(J48:HQ48, A1):$HQ$48,
    INDEX(J5:HQ5, A1):$HQ$5)

• This uses the colon range reference operator.
• You can change A1 to avoid CTRL-SHIFT-ENTER as your original version does but it may perform a little less efficiently.
• You could also incorporate the formula from A1 into a single formula, but again it may be a little less efficient (and a bit harder to read).