Создание диапазона Excel из непересекающихся ячеек

Question

У меня есть таблица с моими инвестиционными счетами, показывающая за каждый месяц депозит или снятие средств, которые произошли в этом месяце, и остаток на конец месяца.

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

Deposit 100
Withdraw 10

Покажет -90% доходности. Однако, если у меня все еще есть 95 долларов на счету, реальная доходность составляет +5%.

Моя таблица выглядит примерно так:

Deposits  Balance
100       100
-10       95
0         103
0         98
100       215

Справа можно добавить столбец "Норма прибыли" с формулой "=IRR($A$2:A3)"

Однако, опять же, это дает мне неточные данные, потому что функция IRR предполагает, что конечный баланс равен нулю и принимает только один диапазон. Есть ли способ сделать что-то вроде этого:

=IRR(ConcatenateRange($A$2:A3, B3))

... так что он видит окончательный вывод средств, равный моему текущему балансу?

Answer 1

Я думаю, что вы, вероятно, застряли с обходным путем, который вы определили - показать конечный баланс как (условный) вывод в конце месяца (или периода) - и депозит конечного остатка предыдущего месяца в качестве (условный) депозит в начало месяца. К сожалению, я думаю, что вы делаете еще одну незначительную ошибку: IRR предполагает движение денежных средств в четные периоды времени - я не вижу, чтобы у вас это было, если только "0" не поддерживает четные периоды (еженедельно?). Чтобы рассчитать доходность, если денежные потоки не происходят через регулярные промежутки времени (особенно если вы используете это в течение более длительных периодов времени, например, года или более), вам придется использовать функцию XIRR, которая принимает два диапазона значений - дату сделка и денежный поток.

* Обновить *

Я ЗНАЛ, что работал над чем-то подобным раньше - просто потребовалось время, чтобы найти это! Посмотрите на эту ветку от Mr Excel. Вывод состоит в том, что вы загружаете денежные потоки и даты, включая конечное значение баланса и дату, в два массива, а затем оцените XIRR этих двух массивов. Сообщение с UDF для этого примерно третье в списке. Непосредственно в предыдущем посте утверждается, что IRR будет принимать непостоянный диапазон - я никогда не проверял это, но это стоит попробовать!