Использование XIRR с расчетными датами в Excel

Question

Я хочу найти внутреннюю норму доходности следующих данных:

Я попробовал целый набор формул в R4C2, введенных как в виде стандартных формул, так и в виде формул массива, и, похоже, ничего не работает. Нужно ли указывать даты в ячейках электронной таблицы, или можно ли поместить даты в формулу в качестве расчета? Вот пример некоторых вещей, которые я пробовал:

=XIRR(R[-3]C:R[-2]C,   {Today();Today()+30*R[-1]C})

=XIRR({R[-3]C;R[-2]C}, {Today();Today()+30*R[-1]C})

Опять же, я попытался ввести их с помощью клавиши Enter и Ctrl+Shift+Enter. Это всегда говорит мне, что есть проблема с формулой. Можно ли это сделать с помощью формулы, или мне нужно поместить рассчитанные даты в соседний диапазон ячеек?

Answer 1

Ваша конструкция { value, value } называется константой массива.  Как следует из его названия и как обсуждалось (хотя и не ясно) во введении констант массива в Excel, компоненты константы массива должны быть (скалярными) константами.  Так, например, вы можете ввести

={5, 12}

в ячейке (или использовать {5,12} как часть формулы), но даже

={5, 4+8}

не является действительным.  Очевидно, что Today() и Today()+30*R[-1]C} не являются константами.

Так что да, к сожалению, похоже, что вы должны поместить массив дат в ячейки.  Конечно, как это обычно бывает для ячеек «вспомогательного» типа, они могут находиться в любом месте рабочей книги ¹ и могут быть скрыты.

Пока у вас есть место для ввода длинной формулы, вы можете сделать это правильно.  Для некоторой даты D (которая может быть Today() , константой, ячейкой или формулой),

D+30*R3C2

D плюс 180 дней (для R3C2 = 6).  Вы действительно должны использовать

DATE(YEAR(D), MONTH(D)+R3C2, DAY(D))

чтобы получить D плюс 6 месяцев.
________________
¹ … как отметил Рон Розенфельд в комментарии.