☰

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

tag-icon • tag-icon microsoft-excel
Создание диапазона Excel из непересекающихся ячеек

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

Я хочу использовать функцию 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))

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

решение1

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

* Обновлять *

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

Связанный контент