В предыдущей статье вы могли узнать, что доходность – один из показателей успешности, эффективности инвестиций.
В это статье я хочу предложить вам готовую таблицу, с помощью которой вы сможете самостоятельно определять доходность ваших инвестиций.
Я использую метод, при котором инвестиции рассматриваются как банковский вклад. При этом методе, ежемесячно рассчитывается доход, который получил бы в виде процентов по вкладу в банке, если бы положил свои деньги в банк под процент, равный искомой доходности. Ежемесячный доход прибавляется к вкладу, то есть капитализируется или реинвестируется. При этом датой открытия вклада считается дата первого перечисления денег на брокерский счет. Все последующие перечисления на брокерский счет учитываются как пополнение вклада. Путем подбора нахожу доходность вклада, при котором доход будет равен доходу от инвестиций.
Таблица для расчета
Скачать таблицу можно по ссылке.
Таблица содержит две вкладки: одна – для того, чтобы вы могли ею пользоваться, вторая – для примера, на котором я буду объяснять, как работать с таблицей.
Для начала работы необходимо в ячейку "В3" ввести текущую дату, а в ячейку "В5" – текущую стоимость вашего портфеля.
В примере дата – 15.07.2004, сумма – 237 438,42.
Далее необходимо заполнить столбцы "С" и "D".
В ячейку "С2" вносится дата первого перечисления на брокерский счет. При этом число месяца должно быть от 1 до 28, иначе таблица будет работать некорректно. Если на брокерский счет вы перевели 29-го, 30-го или 31-го числа месяца, то в таком случае следует поставить 28-е число месяца или 1-е число следующего месяца. То есть, если вы перечислили на брокерский счет, например, 30 августа, то следует указать 28 августа или 1 сентября. Далее даты в столбце "С" формируются автоматически по формуле. В ячейку "D2" вносится сумма перечисления на брокерский счет.
В примере дата – 01.01.2001, сумма – 10 000.
Чтобы учесть дополнительное перечисление на брокерский счет, нужно в ячейку столбца "С" внести дату, когда было перечисление. Дата должна быть указана в такой ячейке, чтобы разница с предыдущей датой была меньше месяца. В примере дата 15.03.2001 (ячейка "С5") стоит после 01.03.2001 (ячейка "С4"). То есть, изначально в ячейке "С5" стояла формула, потом в нее просто ввели нужную дату, а в ячейке "С6" автоматически по формуле обновилась дата. В соответствующей ячейке "D" вносится сумма. В примере в ячейку "D5" – 15 000.
Вводить новые даты нужно, начиная с самой ранней, то есть сверху вниз. И так далее, пока вы не дойдете до последней даты, когда вы перечисляли деньги на брокерский счет.
Столбцы "E" и "F" служат для технических целей, на них не стоит обращать внимание.
Далее переходите в ячейку "В6" и используя возможности excel (Данные -> Работа с данными -> Анализ «что если» -> Подбор параметра) находите значение ячейки "В2" (Доходность).
Либо методом перебора, меняете значение в ячейке "В2" до тех пор, пока в ячейке "В6" не окажется «0,00»
Значение в ячейке "В2" и есть ваша доходность, выраженная в процентах годовых. В примере доходность 20,00% годовых.
Надо понимать, что таким образом находится средняя доходность за все время инвестирования. Чтобы найти доходность за текущий год, нужно в ячейку "С2" ввести первое января текущего года, в ячейку "D2" – стоимость портфеля на начало года. Дальнейшие действия те же.
Возможен случай, когда доход по ценным бумагам, дивиденды по акциям или купонные выплаты по облигациям, реинвестируется не полностью, а частично расходуется на потребление. Тогда сумму дохода, потраченную на потребление, следует прибавить к стоимости портфеля и в ячейку В5 вводить сумму = текущая стоимость портфеля + потраченный доход.
Как еще можно использовать таблицу?
В предыдущей статье, я указывал, что существует две цели определения доходности: сравнение и прогноз.
С помощью этой таблицы также можно спрогнозировать, когда стоимость портфеля достигнет определенной стоимости при текущей доходности. То есть сначала, определяете текущую доходность, выполняя описанные выше действия, потом делаете прогноз.
Для этого, после того как определили текущую доходность, нужно в ячейку "В5" ввести интересующую стоимость. Далее перейти в ячейку "В6" и используя возможности excel (Данные -> Работа с данными -> Анализ «что если» -> Подбор параметра) находите значение ячейки "В3" (Дата расчета). Либо методом перебора, меняете значение в ячейке "В3" до тех пор, пока в ячейке "В6" не окажется «0,00».
В примере, определили дату, на которую стоимость портфеля будет равна 1 миллиону. Получилось 12.10.2011.
Надо, понимать, что это ориентировочная дата, которая в действительности может быть совсем другой – как более ранней, так и более поздней. Прогноз использует текущую доходность, которая с течением времени может колебаться, что будет сказываться на скорости роста вашего портфеля и дату достижения определенной стоимости. Также приведенный выше пример прогноза не учитывает дополнительные инвестиции, которые существенно влияют на рост портфеля. Дополнительные инвестиции можно учесть, делая все тоже самое, что и при определении текущей доходности. Только, вместо реальных дополнительных перечислений на брокерский счет, нужно указывать предполагаемые в будущем перечисления. Не смотря на все это, прогноз позволяет получить представление о будущих перспективах инвестиций.
С помощью таблицы также можно спрогнозировать стоимость портфеля на определенную дату при текущей доходности. Для этого нужно сначала определить текущую доходность, потом в ячейке "В3" указать интересующую дату – через год, два, пять или десять лет. Но здесь будут справедливы те же оговорки, что и выше. Приводить пример этого расчета или расчета с дополнительными будущими перечислениями не стану. Думаю, что если вы поняли все предыдущее, то сможете самостоятельно выполнить эти расчеты. Если же у вас что-то не получается или не до конца понятно, как работать с таблицей, задавайте вопросы в комментариях – я постараюсь помочь вам разобраться.
- UPD: 23.08.2020 обнаружил ошибку в формуле расчета стоимости портфеля (ячейка В4) - сумма определялась некорректно, в случае, если день месяца в ячейке В3 (дата расчета) совпадала с днем месяца в ячейке С2 (первая дата в расчете). То есть, если первая дата была, например, 15.03.2015, а дата расчета 15-е число любого месяца, то расчет стоимости портфеля выполнялся неверно. Ссылку на таблицу обновил, теперь можно скачать с правильным расчетом. Если вы уже начали пользоваться таблицей и вам не хочется переписывать данные в новую таблицу, просто в ячейку В4 введите формулу "=ЕСЛИ(ДЕНЬ(B3)=ДЕНЬ(C2);ВПР(B3;C:F;4;ЛОЖЬ);ВПР(B3;C:F;4;ЛОЖЬ)+ВПР(B3;C:F;3;ЛОЖЬ))"
- UPD2: 15.10.2020 обнаружил ошибку в формуле автоматической простановки даты в столбце С - если дата расчета (ячейка В3) начиналась с числа больше числа первой даты (ячейка С2), а предыдущая дата была датой пополнения, то в соответствующей ячейке столбца С устанавливалась дата из ячейки В3, что приводит к искажению расчета. То есть, если первая дата была, например, 10.03.2015, а очередное пополнение было 29.09.2020, то при постановке даты расчета (ячейка В3) 15.10.2020 в ячейке после даты 29.09.2020 устанавливалась дата 15.10.2020, тогда как должна была быть сначала дата 10.10.2020 и после нее 15.10.2020. Ссылку на таблицу обновил, теперь можно скачать с правильным расчетом. Если вы уже начали пользоваться таблицей и вам не хочется переписывать данные в новую таблицу, то в ячейку С3 введите формулу
"=ЕСЛИ($B$3<ДАТАМЕС(ДАТА(ГОД(C2);МЕСЯЦ(C2);ДЕНЬ($C$2));1);$B$3;ЕСЛИ(ДЕНЬ(C2)>=ДЕНЬ($C$2);ДАТАМЕС(ДАТА(ГОД(C2);МЕСЯЦ(C2);ДЕНЬ($C$2));1); ДАТА(ГОД(C2);МЕСЯЦ(C2);ДЕНЬ($C$2))))"
После скопируйте ячейку С3 (именно ячейку, а не формулу) и вставьте во все ячейки столбца С, где дата рассчитывается автоматически, то есть там, где не было пополнения брокерского счета. - UPD2: 10.07.2021 обнаружил ошибку в формуле автоматической простановки даты в столбце С - если дата пополнения (ячейка Сn, где n - номер ячейки, соответствующей дате пополнения) начиналась с числа меньше числа первой даты (ячейка С2), то в соответствующей ячейке столбца С устанавливалась дата из ячейки В3, что приводит к искажению расчета. То есть, если первая дата была, например, 10.03.2015, а очередное пополнение было 03.06.2020, то при постановке даты расчета (ячейка В3) 09.07.2020 в ячейке после даты 03.06.2020 устанавливалась дата 09.07.2020, тогда как должна была быть сначала дата 10.06.2020 и после нее 09.07.2020. Ссылку на таблицу обновил, теперь можно скачать с правильным расчетом. Если вы уже начали пользоваться таблицей и вам не хочется переписывать данные в новую таблицу, то в ячейку С3 введите формулу "=ЕСЛИ(ДЕНЬ(C2)>=ДЕНЬ($C$2);ЕСЛИ($B$3<ДАТАМЕС(ДАТА(ГОД(C2);МЕСЯЦ(C2);ДЕНЬ($C$2));1);$B$3;ЕСЛИ(ДЕНЬ(C2)>=ДЕНЬ($C$2);ДАТАМЕС(ДАТА(ГОД(C2);МЕСЯЦ(C2);ДЕНЬ($C$2));1); ДАТА(ГОД(C2);МЕСЯЦ(C2);ДЕНЬ($C$2))));ЕСЛИ($B$3<ДАТА(ГОД(C2);МЕСЯЦ(C2);ДЕНЬ($C$2));$B$3;ДАТА(ГОД(C2);МЕСЯЦ(C2);ДЕНЬ($C$2))))" После скопируйте ячейку С3 (именно ячейку, а не формулу) и вставьте во все ячейки столбца С, где дата рассчитывается автоматически, то есть там, где не было пополнения брокерского счета
