Функция ДОХОД
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции ДОХОД в Microsoft Excel.
Описание
Возвращает доходность ценных бумаг, по которым производятся периодические выплаты процентов. Функция ДОХОД используется для вычисления доходности облигаций.
Синтаксис
ДОХОД(дата_согл; дата_вступл_в_силу; ставка; цена; погашение, частота; [базис])
Важно: Даты должны быть введены с использованием функции ДАТА или как результат вычисления других формул и функций. Например, для указания даты 23 мая 2008 г. воспользуйтесь выражением ДАТА(2008,5,23). Если ввести даты как текст, это может привести к возникновению проблем.
Аргументы функции ДОХОД описаны ниже.
- Дата_согл — обязательный аргумент. Дата расчета за ценные бумаги (дата продажи ценных бумаг покупателю, более поздняя, чем дата выпуска).
- Дата_вступл_в_силу — обязательный аргумент. Срок погашения ценных бумаг. Эта дата определяет момент, когда истекает срок действия ценных бумаг.
- Ставка — обязательный аргумент. Годовая процентная ставка для купонов по ценным бумагам.
- Цена — обязательный аргумент. Цена ценных бумаг на 100 рублей номинальной стоимости.
- Погашение — обязательный аргумент. Выкупная стоимость ценных бумаг на 100 рублей номинальной стоимости.
- Частота — обязательный аргумент. Количество выплат по купонам за год. Для ежегодных выплат частота равна 1, для полугодовых — 2, для ежеквартальных — 4.
- Базис — необязательный аргумент. Используемый способ вычисления дня.
Способ вычисления дня
Американский (NASD) 30/360
Замечания
- В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 448 дней.
- Дата соглашения является датой продажи покупателю купона, например облигации. Срок платежа представляет собой дату истечения срока действия купона. Предположим, например, что облигация со сроком действия 30 лет выпущена 1 января 2008 г. и приобретена покупателем через шесть месяцев после выпуска. Датой выпуска будет 1 января 2008 г., датой расчета — 1 июля 2008 г., а срок погашения такой облигации наступит 1 января 2038 г., то есть через 30 лет после даты выпуска.
- Значения аргументов «дата_согл», «дата_вступл_в_силу», «частота» и «базис» усекаются до целых.
- Если дата расчета или дата погашения не является допустимой датой, то доход возвращает #VALUE! значение ошибки #ЗНАЧ!.
- Если ставка < 0, то доход возвращает #NUM! значение ошибки #ЗНАЧ!.
- Если pr ≤ 0 или погашение ≤ 0, то доход возвращает #NUM! значение ошибки #ЗНАЧ!.
- Если частота является любым числом, кроме 1, 2 или 4, то доход возвращает #NUM! значение ошибки #ЗНАЧ!.
- Если базис < 0 или базис >4, то доход возвращает #NUM! значение ошибки #ЗНАЧ!.
- Если «≥», «ДОХОД» возвращает #NUM! значение ошибки #ЗНАЧ!.
- Если только один или менее периодов купона укладываются до даты погашения, функция ДОХОД вычисляется следующим образом: где:
- A = количество дней от начала периода купона до даты расчета (накопленные дни).
- DSR = количество дней от даты расчета до даты погашения.
- E = количество дней в периоде купона.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Как рассчитать доходность инвест-портфеля правильно?
Если вы также, как и я, однажды решили разобраться в том, что подразумевает под собой значение доходности, как понять, какая доходность у вашего портфеля, и как она рассчитывается, то эта статья для вас. В ней я собрал различные методы подсчета, а также обозначил, какой способ кажется мне наиболее правильным.
1.1K показов
1.8K открытийДавайте сначала разберемся, а зачем вообще вам нужно это знать. В первую очередь для того, чтобы оценить эффективность ваших инвестиций, а чтобы это сделать, важно понимать, какие факторы были учтены, а какие проигнорированы при расчете — от этого напрямую зависит значение.
На удивление нет никакого общепринятого стандарта, каждый считает так, как ему вздумается. Кроме того, не существует идеального способа подсчета доходности, поэтому необходимо смириться с определенными допущениями при использовании того или иного метода.
Многие идут легким путем и смотрят аналитику у брокеров, вот только, если разобраться, большинство брокеров учитывают ее не совсем корректно. На мой взгляд, лучшей альтернативой самостоятельному анализу служат платные сервисы аналитики, где представлен более точный результат, но все же я предпочел иной способ. Однако начнём мы не с него, а с самого простого.
Способ при единоразовом внесении средств.
Многие знают, что для того, чтобы посчитать доходность, необходимо результат инвестиций разделить на сумму вложенных средств и перевести полученное значение в проценты.
Этот способ можно использовать, но только, если вы вложились один раз. Таких случаев я знаю крайне мало, так как почти все долгосрочные инвесторы постепенно пополняют свой портфель.
Рассчитывается она следующим образом:
D = (S — Sнач)/Sнач * 100%, где
- D – искомая доходность;
- S – итоговая сумма портфеля;
- Sнач – сумма вложений.
Допустим вы единоразово вложили 100.000 руб., а итоговая сумма вашего портфеля составила 110.000 руб. Тогда мы из итоговой суммы вычитаем сумму вложений, делим на сумму вложений и умножаем на сто : (110.000 — 100.000)/100.000×100 =10%. Получаем доходность за период в размере 10%.
Годовая доходность в данном случае считается так:
D = (S — Sнач)/Sнач * 100%/ Т * 365, где
- Т — кол-во дней, которое сумма лежала на счету.
Делим доходность на количество дней равное тому, сколько сумма внесения работала (допустим 90 дней), и умножаем на количество дней в году: 10/90×365 = 40.55%
Проще говоря, это значение показывает, сколько бы вы заработали, если бы держали средства на протяжении всего года, а не 90 дней, и имели ту же доходность.
Почему этот способ не работает при постепенном пополнении? Потому что здесь не учитываются даты пополнения и довнесения средств на счет. Смотрите, если в начале срока инвестирования, 01.01 числа, вы вложили 100.000 руб. и к концу срока, 30.03 числа, у вас в портфеле 110.000 руб., а 31.03 вы вносите в портфель еще 200.000 руб., то итоговая сумма у вас будет равна 310.000 руб. А так как 200.000 еще не работали у вас на счету и не принесли дохода, то вы получите некорректную доходность. Поэтому данный способ отпадает для всех, кто постоянно пополняет свой портфель, и, если вы считали доходность таким образом, то у меня для вас плохие новости, ваша доходность не отображает реального положения дел.
Способ, который я использую, с учётом даты пополнения.
Изначально я рассчитывал доходность самым популярным способом по XIRR (его разберем следующим), но потом наткнулся на информацию о том, почему он проигрывает этому способу. Изучив его, я пришел к выводу, что это действительно так.
Для расчета потребуется несколько вводных данных: даты пополнений и стоимость портфеля на конечную дату периода. Больше никаких данных не нужно — нам без разницы, когда покупаешь активы, сколько выходит комиссия, и сколько приходит дивидендов.
Напоследок скажу вот что, когда видите, что очередной инвестор пишет о своей доходности, то обязательно спрашивайте, каким методом подсчета он пользуется, так как все способы показывают разные цифры.
Если у вас остались вопросы, то с удовольствием отвечу на них в комментариях. А также можно найти множество полезной информации в моем телеграмм канале, там я делюсь своим опытом инвестирования на зарплату, публикую портфель и комментирую каждый шаг. Переходи по ссылке — LifeStyle Инвестиции.
Расчёт доходности портфеля на основе исторических котировок
Размещённые в настоящем разделе сайта публикации носят исключительно ознакомительный характер, представленная в них информация не является гарантией и/или обещанием эффективности деятельности (доходности вложений) в будущем. Информация в статьях выражает лишь мнение автора (коллектива авторов) по тому или иному вопросу и не может рассматриваться как прямое руководство к действию или как официальная позиция/рекомендация АО «Открытие Брокер». АО «Открытие Брокер» не несёт ответственности за использование информации, содержащейся в публикациях, а также за возможные убытки от любых сделок с активами, совершённых на основании данных, содержащихся в публикациях. 18+
АО «Открытие Брокер» (бренд «Открытие Инвестиции»), лицензия профессионального участника рынка ценных бумаг на осуществление брокерской деятельности № 045-06097-100000, выдана ФКЦБ России 28.06.2002 (без ограничения срока действия).
ООО УК «ОТКРЫТИЕ». Лицензия № 21-000-1-00048 от 11 апреля 2001 г. на осуществление деятельности по управлению инвестиционными фондами, паевыми инвестиционными фондами и негосударственными пенсионными фондами, выданная ФКЦБ России, без ограничения срока действия. Лицензия профессионального участника рынка ценных бумаг №045-07524-001000 от 23 марта 2004 г. на осуществление деятельности по управлению ценными бумагами, выданная ФКЦБ России, без ограничения срока действия.
© «Открытый журнал» 2018 — 2023
Расчет результативности инвестиций в EXCEL
Как быть уверенным, что инвестиции приближают нас к поставленным задачам? В инвестициях практически всегда вместе с любой задачей параллельно следует необходимость «не потерять». Не потерять в мире инвестиций – это значит получать доходность выше инфляции. Переформулировав – портфель должен иметь реальную доходность выше нуля.
При учете результатов инвестиций почти всегда необходимо быть уверенным, что на длинных сроках доходность инвестиционного портфеля выше инфляции. Второй важный элемент — это сравнение доходности с «безрисковыми» инструментами. Инвестор, вкладывая деньги в ценные бумаги, берет на себя дополнительные риски. Подразумевается, что вместе с дополнительными рисками он получает возможность более высокой доходности. Если доходность инвестиций (мы всегда говорим о длинных сроках) ниже, скажем, средней ставки депозита, то зачем брать на себя дополнительные риски?
Есть и другие важные параметры, которые следует учитывать, но все они так или иначе сводятся к необходимости считать доходность. Доходность может быть разной – среднегодовой или накопленной, но считать и понимать эти цифры очень важно для любого инвестора. Без них непонятно, приближают ли нас инвестиции к целям или наоборот – удаляют от них.
Как считать доходность?
Почему большинство инвесторов часто имеют неправильное представление о том, какова настоящая результативность их инвестиций.
Сложность заключается в том, что большинство подходов к расчету доходности подразумевают простую формулу:
А – полученный доход
В – стартовые инвестиции
Представим себе жизненную ситуацию, когда человек в январе инвестировал 10 000 р, а в декабре – 90 000 р. К концу года на инвестиционном счете оказалось 110 000 р (ценные бумаги выросли в цене). Какова доходность инвестиций? Что на что делить? Если мы возьмем доход в 10 000 р и разделим на сумму всех инвестиций – 100 000 р, то получим очень сложно интерпретируемый результат – 10%. Ведь большую часть срока на счете находилось всего 10 000 р, а остаток добавлен только за месяц до конца года …
Или еще более интересный пример. В январе инвестор положил на брокерский счет 100 000 р, а в декабре забрал с него 90 000 р. К концу года на брокерском счете фигурировала сумма 15 000 р. Если просто сложить пополнения и изъятия получится что суммарная инвестиция равна 100 000 – 90 000 = 10 000 р. Разделив доход на суммарные инвестиции, получим слишком оптимистичные 50%. Очевидно, что так делать нельзя …
Более подробно о теме расчетов доходности без пополнений и изъятий читайте в статье: Правильный расчет среднегодовой доходности в инвестициях
IRR или Внутренняя норма доходности (ВНД)
Одним из самых простых и распространенных способов измерить результативность инвестиций является расчет IRR (Internal Rate of Return, Внутренняя норма доходности). IRR – это не совсем доходность. Формально IRR или Внутренняя норма доходности (ВНД) – это процентная ставка, при которой приведённая стоимость денежных поступлений (списаний) равна размеру исходных инвестиций. IRR очень распространен в бизнесе и финансах. При помощи этой величины считается, например, рентабельность проектов в бизнесе. Аналогично считается доходности к погашению для облигаций. IRR можно считать это своего рода стандартом при измерении результативности.
Еще одно важное преимущество – IRR легко считается в EXCEL и других электронных таблицах.
Если IRR меньше ставки по депозитам в Сбербанке, то надо задуматься, все ли нормально с инвестиционной стратегией.
Шаблон для расчета IRR инвестиций в EXCEL
Для быстрого расчета результативности инвестиций предлагаем простой шаблон в EXCEL.
Шаблон считает IRR для каждого из периодов инвестиций, и за последние 6 периодов (колонка «IRR за 6 периодов»). Периоды могут быть произвольными: один месяц, один год. Более того, в калькуляторе используется функция XIRR (ЧИСТВНДОХ), которая умеет считать IRR даже для неравных между собой периодов. Это значит, что в колонке «Дата» можно указывать любую дату, а не только начало месяца или, например, конец года. Удобнее всего вносить новые данные каждый раз, когда пополняется портфель или когда происходит изъятие средств. Для интереса можно вносить новые данные чаще, даже когда нет пополнений портфеля. Например можно указывать даты, когда в размере портфеля происходят какие-то значимые изменения или просто с некоторой заданной регулярностью.
Кроме IRR инвестиционного портфеля в шаблоне можно посмотреть общий прирост портфеля (на сколько размер портфеля отличается от объема инвестированных средств).
Учет результатов инвестиций для сложных портфелей
Важное свойство калькулятора – это возможность измерения результативности инвестиций для широко диверсифицированных портфелей. Часто встречаются ситуации, когда у инвестора несколько брокерских счетов (российский и зарубежный), часть денег размещено в ПИФах через Управляющую компании. Кроме всего, может быть открыт ОМС (Обезличенный металлические счета – используются для покупки драгоценных металлов), куплена недвижимость и тому подобное. В таком случае рассчитать результат инвестиций для итогового портфеля бывает довольно проблематично… Предлагаемый калькулятор поможет справиться с этой задачей. Достаточно регулярно (например, один раз в год) считать суммарный размер всех активов в портфеле и вносить в таблицу пополнения и изъятия.
Расчет доходности к погашению для облигаций
Хотя это и не основная функция калькулятора, но его довольно просто можно использовать для расчета доходности к погашению для облигаций. Доходность к погашению для облигаций определяется именно как IRR всего денежного потока.
Для вычисления доходности к погашению необходимо внести сумму покупки облигации и планируемые поступления в виде дивидендов.
В примере показан прогноз доходности к погашению для облигации с купоном 40 руб (два раза в год) и текущей стоимостью 98% (980 р) и погашением в 2024 году. Предполагается, что облигация держится до погашения. В данном случае имеет релевантность только последнее значение IRR (в момент погашения), так как изменение цены облигации прогнозировать очень сложно. IRR за 6 периодов тоже большого смысла для облигаций не имеет.
Ограничения калькулятора
Калькулятор будет показывать, в том числе, нереализованный доход. Например, если ценная бумага выросла в цене, но еще не продана, то такой доход инвестора называется нереализованным. Поэтому предлагаемый шаблон не может быть использован для расчета налогов (НДФЛ). Нереализованный доход не считается налоговой базой.
Другие финансовые калькуляторы для EXCEL можно найти разделе Калькуляторы.
Файлы для скачивания
Калькулятор результативности инвестиций в EXCEL
Файл: investment_tracker.xlsx
Размер: 48684 байтДля скачивания файлов необходимо зарегистрироваться или авторизоваться