Problem with XIRR formula using CHOOSE function with array to return non-continguous values and dates

[ReformedCounsel]

Hi all, stuck on a problem and haven't been able to find an answer elsewhere.

I'm using excel 2007 on Windows 7.

I'm trying to use XIRR on a non-continguous list of dates and values to get returns on a stock portfolio. A simplified version of my sheet is as follows. In A1:A20, I have dates for trades and dividends over financial years 2011-13. In B1:B20, I have corresponding values for trades and dividends over the same time period. Rows 1-5 are FY11, 6-13 are FY12 and 14-20 are in FY13. I also have the end of the financial year dates for those years in C1:C3, the corresponding value of the holding in D1:D3 as at the end of each financial year and in E1:E3, a copy of D1:D3 multiplied by -1. I want to be able to keep the end of financial year data separate from the list of actual transactions.

I want to automatically calculate the returns of the stock from each of the financial years, as well as a total annualised return. So for example, to find the return during FY12, I have used the following formula:

=XIRR(CHOOSE({1,2,3},D1,B6:B13,E2),CHOOSE({1,2,3},C1,A6:A13,C2))

However, instead of returning values in the order of
D1,B6,B7,B8,B9,B10,B11,B12,B13,E2, it returns values in the order of D1,B6,E2,D1,B7,E2,D1,B8,E2... etc.

I can only seem to get the formula to work if the CHOOSE function is refercing arrays of the same length. Is there any way around this? What am I doing wrong?

 

[Domenic]

I'd suggest keeping the data together. Otherwise, try...

=XIRR(N(OFFSET(D1,{0;1;5;6;7;8;9;10;11;12},{0;1;-2;-2;-2;-2;-2;-2;-2;-2})),N(OFFSET(C1,{0;1;5;6;7;8;9;10;11;12},{0;0;-2;-2;-2;-2;-2;-2;-2;-2})))

I'm assuming that the reference to E2 is correct and that it shouldn't be D2.

 

[ReformedCounsel]

Thanks for looking at this Domenic,

Yes E2 reference is correct as the values in column E are negatives of column D (XIRR requires ending with the negative of current value).

Your solution would get the correct output, but I'd like to use dynamic named ranges for the transactions in column B, so that I don't have to manually work out how many entries exist in each financial year and then manually input the offset details... That's why I was using the CHOOSE function but I don't understand why it's returning multiple instances of the single cell references (i.e. D1, E2, C1, C2 in my example above).

Any thoughts on why CHOOSE is returning that result?

One workaround I could use is to give each end of financial year date and value a column of its own and then refer to them as ranges the same size as the transactions range in column D (incorporating the single entry and then the rest of the rows blank). That way instead of repeating the value many times it just returns it once and then a lot of zeros which don't affect the XIRR result. Seems inelegant though!

 

[Aladin Akyurek]

CHOOSE can append single cells. When you try to append a single cell to a multi-cell/column range, you get every cell paired the the single cell.

CHOOSE({1,2},C1,L2)

CHOOSE({1,2},C1:C4,Y2:Y5)

will deliver the intended results. But:

CHOOSE({1,2},E1,F2:F3)

will yield something like:

{4,0.6;4,0.5}

where E1 = 4 and F1:F2 0.6 and 0.5.

 

[Domenic]

=XIRR(CHOOSE({1,2,3},D1,B6:B13,E2),CHOOSE({1,2,3},C1,A6:A13,C2))

How about using a custom function? If so, based on the above, first define your dynamic ranges as follows...

Name: DatesDynRng

Refers to: =$A$6:INDEX($A$6:INDEX($A:$A,ROWS($A:$A)),MATCH(9.99999999999999E+307,$A$6:INDEX($A:$A,ROWS($A:$A)),1))

Name: ValuesDynRng

Refers to: =$B$6:INDEX($B$6:INDEX($B:$B,ROWS($B:$B)),MATCH(9.99999999999999E+307,$B$6:INDEX($B:$B,ROWS($B:$B)),1))

Then place the following code in a standard module (Alt+F11 > Insert > Module > Copy/Paste > Alt+Q)...

Option Explicit
Function MyXIRR(rValues As Range, rDates As Range)
    Dim rCell As Range
    Dim MyValues() As Double
    Dim MyDates() As Date
    Dim ValCnt As Long
    Dim DateCnt As Long
    Dim Cnt As Long
    ValCnt = rValues.Cells.Count
    ReDim MyValues(1 To ValCnt)
    Cnt = 0
    For Each rCell In rValues
        Cnt = Cnt + 1
        MyValues(Cnt) = rCell.Value
    Next rCell
    DateCnt = rDates.Cells.Count
    ReDim MyDates(1 To DateCnt)
    Cnt = 0
    For Each rCell In rDates
        Cnt = Cnt + 1
        MyDates(Cnt) = rCell.Value
    Next rCell
    MyXIRR = Application.Xirr(MyValues, MyDates)
End Function

Now you can use the custom function in your worksheet as follows...

=MyXIRR((D1,ValuesDynRng,E2),(C1,DatesDynRng,C2))

 

[Aladin Akyurek]

Another option is to invoke ARRAYUNION...

=XIRR(ARRAYUNIPON(D1,B6:B13,E2),ARRAYUNION(C1,A6:A13,C2))

You might need TRANSPOSE for the terms though.

 

[ReformedCounsel]

Aladin, Domenic,

Thanks so much for your attention to my problem. Ultimately I've used Aladin's ARRAYUNION solution (found the code here), and Domenic's instructions for how to turn that code into a function above (I hadn't used VBA before, but seems to be working just fine).

XIRR is working how I need it to now. Thanks!