Calculate Year-on-Year Return using XIRR function and non-contiguous data

Question

I have input data as below wherein "Investment" was made on "Date" in Column-A and "Return" (Column-C) is as of today.

Date        Investment  Return
03-Jan-2011 -2000.00    5467.78  
03-Oct-2011 -2000.00    6021.89  
01-Nov-2011 -2000.00    5814.14  
01-Dec-2011 -2000.00    6304.06  
02-Jan-2012 -2000.00    6722.25  
01-Feb-2012 -2000.00    6038.32  
01-Mar-2012 -2000.00    5857.22  
02-Apr-2012 -2000.00    5751.47  
02-May-2012 -2000.00    5719.95  
01-Jun-2012 -2000.00    5977.00  
01-Aug-2012 -2000.00    5763.80  
03-Sep-2012 -2000.00    5670.38  
01-Oct-2012 -2000.00    5207.01  
01-Nov-2012 -2000.00    5277.69  
03-Dec-2012 -2000.00    4848.90 

I want to use XIRR formula to calculate year-on-year return as of today. The result should look something like this

Column-A    Column-B  
2011        14.1%  
2012        14.5%  
2013        13.8%  
....  
....

Effectively, I have the outflow (negative values) in sequence, but I am not able to add inflow (as positive value) as last row for each year.

I tried the below for years 2011 and 2012 but apparently non-contiguous values are not supported in XIRR

Year 2011 =XIRR({B2:B5;sum(C2:C5)},{A2:A5;today()})

Year 2012 =XIRR({B2:B16;sum(C2:C16)},{A2:A16;today()})

Request your help on the same.

Thanks, Kingshuk.

Answer 1

If you have 2011 entered in E2, use CHOOSE in an array formula to combine the return and investment arrays (commit with ctrl+shift+enter):

=XIRR(OFFSET($A$1,MATCH(E2,YEAR($A$2:$A$16),0),1,SUM(IF(YEAR($A$2:$A$16)=E2,1)),2),CHOOSE({1,2},OFFSET($A$1,MATCH(E2,YEAR($A$2:$A$16),0),0,SUM(IF(YEAR($A$2:$A$16)=E2,1))),TODAY()))

EDIT The above formula assumes your data are sorted by year.

Answer 2

Found this works with different data areas

XIRR((VSTACK(B2:B16,sum(c2:c16)),VSTACK(A2:A16,today()))

This works with arrays, filters as well.