Рассчитать годовой доход, используя функцию XIRR и несмежные данные

Question

У меня есть входные данные, как показано ниже, в которых "Инвестиции" были сделаны на "Дата" в столбце А, а "Возврат" (столбец С) - на сегодняшний день.

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 

Я хочу использовать формулу XIRR для расчета годовой доходности на сегодняшний день. Результат должен выглядеть примерно так

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

По сути, у меня есть отток (отрицательные значения) в последовательности, но я не могу добавить приток (как положительное значение) в качестве последней строки для каждого года.

Я пробовал ниже для 2011 и 2012 годов, но, очевидно, несмежные значения не поддерживаются в XIRR

2011 год =XIRR({B2: ​​B5; сумма (C2:C5)},{A2:A5; сегодня ()})

2012 год =XIRR({B2: ​​B16; сумма (C2:C16)},{A2:A16; сегодня ()})

Запросите вашу помощь по тому же.

Спасибо, Кингшук.

Answer 1

Если вы в 2011 году вошли в E2использовать CHOOSE в формуле массива для объединения массивов возврата и инвестиций (передайте с помощью 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()))

РЕДАКТИРОВАТЬ Приведенная выше формула предполагает, что ваши данные отсортированы по годам.