Including a non-adjacent cell in a Range (series) in the XIRR formula

Question

I am using the XIRR formula as follows:

=XIRR(E$11:E17,B$11:B17)

but need to add an additional non-adjacent cell to the Range of values and dates.

My first attempt:

=XIRR((E$11:E17,H17),(B$11:B17,B17))

resulted in #VALUE

I have attempted to do it using the CHOOSE function as follows:

=XIRR(CHOOSE({1,2},E$11:E17,H17),CHOOSE({1,2},B$11:B17,B17))

But this is not working to produce the correct results.

I cannot figure out how to add one cell onto the end of the range. The following did work to give correct results but isn't going to work for me, as I need to use a range and individual cell, not all individual cells

=XIRR(CHOOSE({1,2,3},E11,E12,H13),CHOOSE({1,2,3},B11,B12,B13))

Thanks for your help.

Chara

+1 as you clearly have had a good look already. For multi-column choices the OFFSET workaround won't be applicable (it works well for dis continuous ranges in a single range or column). For the same reason the CHOOSE workaround is cumbersome. If H11:16 are blank, then this will work =XIRR(CHOOSE({1,2},E$11:E17,H11:H17),CHOOSE({1,2},B$11:B17,B$11:B17)) as the ranges for CHOOSE are now similarly sized – Swarthy

@brettdj. Unfortunately, those values aren't blank. – Chara

I think you are stymied then .... I could write a UDF, but think it probably makes more sense for you to reorganise your data? I will ask the formula wizard, Barry Houdini to take a look. – Swarthy

For now I did the math myself and used a VBA to run solver a billion times, but it's not the optimal solution. Thanks for your help. – Chara

Answer 1

You could try something similar to this:

=XIRR(IF(1-FREQUENCY(9^9,B11:B17),E11:E17,H17),IF(1-FREQUENCY(9^9,B11:B17),B11:B17,B17))

Supen

I tried this out and it produces the correct result +1. I need to apply this to an entire column of different shape arrays [which looks like it works as is]. Can you please explain why it works and specifically how you transformed the array with the Frequency formula. Thanks – Chara

Note: I had to put ARRAYFORMULA() around each of the IF statements to get this work. – Cerated

Answer 2

I figured out how this works and thought I would share for anyone who comes across this.

The trick is that the FREQUENCY function returns an array that has one more element than the input array. I'll spare the whole explanation of that function here as the help file does a good job, but as it is implemented in this case it is returning an array like {0;0;0;1}. When the 1-{} operation is performed, we are left with the array {1;1;1;0}.

That array is now input to the IF function along with an array of values (the contiguous cells) to evaluate to for elements equal to 1 (in the array from above) and a single value (the outlier cell) to evaluate to for elements equal to 0. Thus, producing the desired array to be used as input for the XIRR formula.

Notes: The FREQUENCY function does not have to use one of the value arrays as input. It only needs an array of numeric values one element smaller than the desired output array. You could create a hidden column off to the side full of 0s and use it as needed repeatedly throughout the sheet. If using 0s, the first value in the FREQUENCY function can be any value greater than 0. For example, 1 makes it easy to read. 9^9 was used as an arbitrarily large value.

You could repeat this process to build an array of discrete cells from all over a sheet.

Pylle

Sounds like this could be a useful answer - but can you include the actual formula? – Allanallana

This was a response to lori_m's response above mine to explain the formula: "=XIRR(IF(1-FREQUENCY(9^9,B11:B17),E11:E17,H17),IF(1-FREQUENCY(9^9,B11:B17),B11:B17,B17))". – Pylle

Answer 3

I have tried first

=XIRR(IF(1-FREQUENCY(9^9,B11:B17),E11:E17,H17),IF(1-FREQUENCY(9^9,B11:B17),B11:B17,B17)) as suggested by lori_m and it is returning last element only.

Then I used it with ARRAYFORMULA a suggested by Dominic

=XIRR(ARRAYFORMULA(IF(1-FREQUENCY(9^9,B11:B17),E11:E17,H17)),ARRAYFORMULA(IF(1-FREQUENCY(9^9,B11:B17),B11:B17,B17))).

Now it's producing enter image description herecorrect result. This is really helpful in calculating running daily XIRR in trading account. I have added my result, Maybe it will help someone that how to do it.

Sheeran