Формула выдаёт #value!, в то время как все отдельные части формулы работают

[AlekseyBu]

Пользуюсь функцией XIRR()
Формула которую хочу посчитать
=XIRR(IF(ROW($C$7:$C$1001) = 7,D2,IF(ROW($C$7:$C$1001) < 1001,IF($B$7:$B$1001-D1 > 0, $C$7:$C$1001, ""),-$B$2)),IF(ROW($C$7:$C$1001) = 7,D1,IF(ROW($C$7:$C$1001) < 1001,IF($B$7:$B$1001-D1 > 0,$B$7:$B$1001, ""),$B$1)))

Идея следующая, Нужно следить за своей эффективностью инвестирования.
Соответсвенно хочу следить за коэффициентом XIRR. Для этого каждый новый год, скажем 31 декабря. Эмулируется новый портфель. Чья начальная стоимость равна стоимости портфеля на конец очередного года, а все последующие пополнения и успехи этого нового портфеля совпадают с портфелем.

Файл прикладываю.

B7:B1000  -- даты транзакций ввода-вывода средств
C7:C1000 -- суммы соответсвующих транзакций
B2 -- текущаю цена портфеля на последнюю дату, а B1 -- сама дата.
C1:C2 и B1:B2 -- даты и стоимости портфелей на даты конца года, т.е. те даты, когда запоминается портфель.

D7:D1001 -- расчиатанная дата, второй аргумент функции XIRR
IF(ROW($C$7:$C$1001) = 7,D1,IF(ROW($C$7:$C$1001) < 1001,IF($B$7:$B$1001-D1 > 0,$B$7:$B$1001, ""),$B$1))
E7:E1001 -- расчитанные транзакции, первый аргумент функции XIRR
IF(ROW($C$7:$C$1001) = 7,D2,IF(ROW($C$7:$C$1001) < 1001,IF($B$7:$B$1001-D1 > 0, $C$7:$C$1001, ""),-$B$2))

Как мы видим, столбцы D и Е посчитанны верно.
G6 -- функция XIRR посчитанная на этих столюцах. Как видим, она также посчитанна верно.

ПОЧЕМУ не работает   начальная формула для XIRR?

Спасибо!

[rami]

ПОЧЕМУ не работает   начальная формула для XIRR?

Это должна быть формула массива (нужно установить "птичку"), тогда получится результат 8,53%

[AlekseyBu]

ПОЧЕМУ не работает   начальная формула для XIRR?

Это должна быть формула массива (нужно установить "птичку"), тогда получится результат 8,53%

А ларчик просто открывался. Спасибо! Удивительно, что C3 посчиталась без галочки.

[rami]

Удивительно, что C3 посчиталась без галочки.

Нет, не удивительно, в ячейке C3 нет конструкции $B$7:$B$1001-D1 (от диапазона отнимается одна ячейка, а это возможно в формуле массива)

[AlekseyBu]

Удивительно, что C3 посчиталась без галочки.

Нет, не удивительно, в ячейке C3 нет конструкции $B$7:$B$1001-D1 (от диапазона отнимается одна ячейка, а это возможно в формуле массива)

Действительно. Спасибо!

[economist]

Следует помнить что все функции вида =IRR() рассчитываются итерационно (по-сути - в режиме циклической ссылки).  И даже если все правильно с формулой - если сходимость модели "неуверенная" и сама модель сложна - итерации могут не дать результата и привести к ошибке #value/#ЗНАЧ.

Такое часто бывает если график инвестирования неровный (или, скажем, в бизнес-плане - "рваная" модель капвложений и выхода на полную мощность, плюс сезонность, короче все недетерминированно).

Помогает при этом увеличение числа итераций до 200-500 или загрубление точности. За это отвечает Сервис-Параметры-...Calc-Вычисления-Итерации...