Как создать функцию XIRR на основе уникальных имен и между определенными датами?

Question

Я пытаюсь создать функцию XIRR, которая будет рассчитывать доходность на основе уникальных имен инвестиций и соответствующих денежных потоков между определенными датами. Например, я хочу рассчитать IRR для Инвестиции A в период с 16.01.2008 г. по 20.05.2008 г. со следующими денежными потоками:

Даты инвестиций Суммы A 01/15/18 (55 долл. США) B 01/18/18 (20 долл. США) B 01/19/18 $9 B 04/06/18 $6 A 04/08/18 $24 A 05/20/18 $40 B 21.05.18 $7 23.05.18 $ (5 $)

Начало 16.01.18
Конец 20/20/18

Задача, с которой я сталкиваюсь, - пропускать потоки денежных средств от Инвестиции B и только рассчитывать Инвестиции A. Это упрощенная версия того, что я пытаюсь сделать. Полная версия имеет около 50 названий инвестиций, распределенных по 1000 строкам. Любые предложения помогут. Я пытался использовать Fncs INDEX, MATCH, OFFSET, COUNTIF.

Электронная таблица прилагается для справки.

Я ценю помощь заранее!

Answer 1

Следующая формула массива возвращает XIRR на основе даты инвестирования в E2, даты начала в F2 и даты окончания в G2. Обратите внимание, что формула должна быть подтверждена с помощью CONTROL+SHIFT+ENTER.

=XIRR(INDEX(C:C,N(IF(1,MODE.MULT(IF(A2:A9=E2,IF(B2:B9>=F2,IF(B2:B9<=G2,ROW(C2:C9)*{1,1}))))))),INDEX(B:B,N(IF(1,MODE.MULT(IF(A2:A9=E2,IF(B2:B9>=F2,IF(B2:B9<=G2,ROW(C2:C9)*{1,1}))))))))

Для более ранних версий Excel попробуйте следующую формулу. Обратите внимание, что эту формулу также необходимо подтвердить, нажав CONTROL+SHIFT+ENTER.

=XIRR(N(OFFSET(C2:C9,SMALL(IF(A2:A9=E2,IF(B2:B9>=F2,IF(B2:B9<=G2,ROW(C2:C9)-ROW(C2)))),ROW(INDIRECT("1:"&COUNTIFS(A2:A9,E2,B2:B9,">="&F2,B2:B9,"<="&G2)))),0,1)),N(OFFSET(B2:B9,SMALL(IF(A2:A9=E2,IF(B2:B9>=F2,IF(B2:B9<=G2,ROW(B2:B9)-ROW(B2)))),ROW(INDIRECT("1:"&COUNTIFS(A2:A9,E2,B2:B9,">="&F2,B2:B9,"<="&G2)))),0,1)))