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

Автор AlekseyBu, 17 октября 2016, 14:27

0 Пользователи и 1 гость просматривают эту тему.

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

Цитата: AlekseyBu от 17 октября 2016, 12:27ПОЧЕМУ не работает   начальная формула для XIRR?
Это должна быть формула массива (нужно установить "птичку"), тогда получится результат 8,53%

AlekseyBu

Цитата: rami от 17 октября 2016, 14:47
Цитата: AlekseyBu от 17 октября 2016, 12:27ПОЧЕМУ не работает   начальная формула для XIRR?
Это должна быть формула массива (нужно установить "птичку"), тогда получится результат 8,53%

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

rami

Цитата: AlekseyBu от 17 октября 2016, 12:58Удивительно, что C3 посчиталась без галочки.
Нет, не удивительно, в ячейке C3 нет конструкции $B$7:$B$1001-D1 (от диапазона отнимается одна ячейка, а это возможно в формуле массива)

AlekseyBu

Цитата: rami от 17 октября 2016, 15:05
Цитата: AlekseyBu от 17 октября 2016, 12:58Удивительно, что C3 посчиталась без галочки.
Нет, не удивительно, в ячейке C3 нет конструкции $B$7:$B$1001-D1 (от диапазона отнимается одна ячейка, а это возможно в формуле массива)

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

economist

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

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

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

Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...