Расчет доходности инвестиций в excel с учетом ввода и вывода
Пару лет назад у меня был счет в ВТБ. У них есть приложение «Мои инвестиции», которым я пользовался. В какой-то момент, после нового обновления появился раздел с аналитикой портфеля. Там можно было узнать доходность за год/месяц и тд.
Поначалу все работало нормально, но стоило мне внести средства и купить новые акции, как вся статистика по доходности полетела. Приложение выдавало совершенно несопоставимые с реальностью цифры.
И тогда я озадачился тем, как посчитать реальную доходность своего портфеля с учетом регулярных пополнений.
Сейчас ВТБ все исправили и доходность считается адекватно. Возможно, у других брокеров проблема осталась, и эта статья поможет вам с ней разобраться. К тому же, если у вас несколько счетов/видов инвестиций, вы сможете посчитать суммарную доходность.
Если вы положили на счет 100 тыс. р., инвестировали их и благополучно забыли на год, то посчитать доходность не составит труда. Предположим, к концу года у вас на счете образовалось 120 тыс. руб., тогда годовая доходность составит:
120/100-1=0,2 или же 20%
Проблемы начинаются, стоит только немного усложнить этот пример. Предположим, прошел не год, а 8 мес. В таком случае, 20% — это прибыль за 8 месяцев. Но общепринято высчитывать именно годовую доходность, чтобы было проще сравнить с тем же банковским вкладом. Для этого нужно провести дополнительные расчеты. Есть два варианта:
А что делать, если инвестор периодически пополняет счет или снимает средства?
На помощь нам приходит функция Excel XIIR или в русской версии ЧИСТВНДОХ.
Функция очень простая в использовании, но сложная для понимания. ЧИСТВНДОХ рассчитывает IRR, внутреннюю норму доходности при нерегулярных денежных потоках. Этот показатель часто используется при оценке привлекательности инвестиционных проектов. IRR — такая ставка дисконтирования, при которой совокупный денежный поток проекта равен нулю. Не буду вдаваться в подробности оценки проектов, сейчас не об этом. Как мы можем применить ЧИСТВНДОХ для расчета доходности нашего портфеля?
Для этого нам понадобятся вводные данные, а именно: сумма на начало периода, сумма на конец периода и суммы ввода/вывода средств с датами. Эти данные можно найти в брокерском отчете или отчете о движении денежных средств.
Ниже приведен пример. Сумма на начало периода — 100 тыс. руб. Сумма на счете на конец периода — 170 тыс. руб. Конечная сумма и вывод средств выписываются со знаком «-», начальная сумма и пополнения счета со знаком «+».
Знаки можно расставить наоборот, итоговый результат не изменится. Тут уже кому как удобнее.
В этой статье описаны синтаксис формулы и использование функции ЧИСТВНДОХ в Microsoft Excel.
Описание
Возвращает внутреннюю ставку доходности для графика денежных потоков, которые не обязательно носят периодический характер. Чтобы рассчитать внутреннюю ставку доходности для ряда периодических денежных потоков, следует использовать функцию ВСД.
Синтаксис
Аргументы функции ЧИСТВНДОХ описаны ниже.
Значения Обязательный. Ряд денежных потоков, соответствующий графику платежей, приведенному в аргументе "даты". Первый платеж является необязательным и соответствует затратам или выплате в начале инвестиции. Если первое значение является затратами или выплатой, оно должно быть отрицательным. Все последующие выплаты дисконтируются на основе 365-дневного года. Ряд значений должен содержать по крайней мере одно положительное и одно отрицательное значение.
Даты Обязательный. График дат платежей, который соответствует платежам для денежных потоков. Даты могут быть в любом порядке. Дата должна быть введена с использованием функции ДАТА либо как результат других формул или функций. Например, для указания даты 23 мая 2008 г. воспользуйтесь выражением ДАТА(2008,5,23). Если ввести даты как текст, это может привести к возникновению проблем. .
Предп Необязательный. Величина, предположительно близкая к результату ЧИСТВНДОХ.
Замечания
В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 448 дней.
Числа в аргументе "даты" усекаются до целых.
В большинстве случаев задавать аргумент "предп" для функции ЧИСТВНДОХ не требуется. Если этот аргумент опущен, то он полагается равным 0,1 (10 процентов).
Функция ЧИСТВНДОХ тесно связана с функцией ЧИСТНЗ. Ставка доходности, вычисляемая функцией ЧИСТВНДОХ — это процентная ставка, соответствующая ЧИСТНЗ = 0.
di = дата i-й (последней) выплаты;
d1 = дата 0-й выплаты (начальная дата);
Pi = сумма i-й (последней) выплаты.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Достаточно частый вопрос о том, как вести учет доходности своих портфелей в экселе. За 4 года я выделил для себя 2 наиболее удобных способа. Автоматизированный учет на сторонних ресурсах (вроде Интелинвест) сегодня разбирать не будем.
Способ 1. Ежемесячный учет доходности.
Это самый первый метод, к которому я пришел. Здесь все просто, каждый месяц вы учитываете то, сколько денег было в портфеле на начало месяца, сколько вы довнесли или сняли за этот период и сколько осталось на конец месяца.
Пример:
1 ноября в портфеле было активов общей стоимостью 95 000 рублей.
За месяц ничего не снимали и не пополняли.
30 ноября в портфеле активы стоили 100 000 рублей.
Доходность за ноябрь = (100 000 — 95 000) / 95 0000 * 100% = 5,3%
1 декабря сумма активов в портфеле была 100 000 рублей.
10 декабря вы довнесли 50 000 рублей.
31 декабря в портфеле было 153 000 рублей.
Доходность за декабрь = (153 000 — 100 000 — 50 000) / 100 000 * 100% = 3%, таким образом, все довнесения и снятия влияют только на доходность одного месяца.
1 января сумма активов равна 153 000 рублей… и т.д.
В конце года я просто суммирую все месячные доходности и получаю примерную картину динамики доходности за весь год.
Способ 2. Функция Excel ЧИСТВНДОХ()
Эта функция возвращает внутреннюю ставку доходности для графика денежных потоков, которые не обязательно носят периодический характер. Проще говоря, эта функция сама учитывает даты и суммы взносов и выводов средств, а так же считает доходность в зависимости от срока. В отличие от ежемесячного учета, здесь нет необходимости вписывать данные по тем месяцам, когда не было операций ввода/вывода средств.
Главное помнить одно простое правило, все пополнения счета идут со знаком (-) минус, все выводы средств и конечный результат со знаком плюс.
Пример функции выглядит так: =ЧИСТВНДОХ(диапазон сумм; диапазон дат).
Всем успешных инвестиций!
Следить за всеми моими обзорами можете здесь: Telegram, Смартлаб, Вконтакте
Многие инвесторы часто вносят в свой инвестиционный портфель дополнительные средства, докупают активы или продают часть активов и выводят деньги. И считают доходность по обычной формуле, и думают, что все делают правильно. На самом деле они делают неправильно и только вводят себя в заблуждение. В этой статье я расскажу как нужно считать доходность инвестиций, если вы вносили и выводили деньги со своего счета.
Для этого рассчитывают результат инвестирования с учетом вводов/выводов средств и делят его на средневзвешенную по времени величину вложенных средств. Данный метод расчета очень подробно описан на сайте УК Арсагера, поэтому здесь я его описывать не буду. Те, кто читал их статью, знают, что этот метод очень трудоемкий, все приходится считать вручную. Если вы много раз вводили и выводили деньги, то формула расчета доходности портфеля будет ооочень длинной, легко запутаться и сделать ошибку. Поэтому я объясню, как очень просто посчитать доходность инвестиций в Excel.
Как считать доходность инвестиций в Excel
- Инвестор купил акций на сумму 1000 рублей.
- Через 3 месяца он купил еще акций на 500 рублей.
- Еще через 4 месяца он продал часть акций на сумму 300 рублей.
- Через год после первоначального приобретения, стоимость акций составила 1300 рублей.
Доходность портфеля составила 8,004% годовых.
Введем эти данные в Excel. В первой колонке указываем суммы, во второй даты.
- В первой строчке указываем начальную сумму инвестиций 1000 рублей и дату инвестирования, к примеру 01.01.2014.
- Во второй строчке указываем ввод средств 500 рублей и дату 01.03.2014.
- В третьей строчке указываем вывод средств со знаком минус -300 и дату 01.04.2014.
- В четвертой строчке указываем стоимость портфеля на конец года со знаком минус -1300 и дату конец года 31.12.2014.
Если бы мы считали по простой формуле, то получили бы результат (1300-1200)/1200=8,3%. Вроде бы разница небольшая, но в других примерах разница может составить несколько процентов.
Функцию в ячейку так же можно вписать руками. Для этого в пустой ячейке впишите текст: =ЧИСТВНДОХ(A1:A4;B1:B4), номера ячеек укажите свои.
Расчет доходности инвестиционного портфеля за год
Следующий способ будет полезен тем, кому надо рассчитать доходность своего инвестиционного портфеля за год. Например, вы инвестируете 5 лет, тогда с помощью этого способа вы сможете рассчитать свои результаты в каждом году. Этот способ я нашел здесь. Возьмем пример из статьи:
Рыночная стоимость портфеля на 31 декабря 2004 года: 10000$
20 марта 2005 года: внесение 1000$
25 июня 2005 года: изъятие 500$
1 октября 2005 года: внесение 1000$
Рыночная стоимость портфеля на 31 декабря 2005 года: 12000$
Вносим данные в Excel:
Формулы расчетов ниже:
Таким образом можно рассчитать доходность вашего инвестиционного портфеля за год, если известны его рыночная стоимость на начало и конец года и движение денежных средств по датам.
Читайте также: