Как посчитать доход в excel

Обновлено: 08.07.2024

Функция ДОХОД возвращает доходность ценных бумаг, по которым производятся периодические выплаты процентов. Она используется для вычисления доходности облигаций.

Аргументы функции ДОХОД:

Дата_согл — обязательный аргумент. Дата расчета за ценные бумаги (дата продажи ценных бумаг покупателю, более поздняя, чем дата выпуска).

Дата_вступл_в_силу — обязательный аргумент. Срок погашения ценных бумаг. Эта дата определяет момент, когда истекает срок действия ценных бумаг.

Ставка — обязательный аргумент. Годовая процентная ставка для купонов по ценным бумагам.

Цена — обязательный аргумент. Цена ценных бумаг на 100 рублей номинальной стоимости.

Погашение — обязательный аргумент. Выкупная стоимость ценных бумаг на 100 рублей номинальной стоимости.

Частота — обязательный аргумент. Количество выплат по купонам за год. Для ежегодных выплат частота равна 1, для полугодовых — 2, для ежеквартальных — 4.

Базис — необязательный аргумент. Используемый способ вычисления дня.

Для аргумента Базис предусмотрено 5 видов значений:

0 или опущен	Американский (NASD) 30/360. В соответствии с этим методом предполагается, что каждый месяц содержит по 30 дней, а каждый год – по 360 дней. Метод основывается на стандарте Национальной ассоциации фондовых дилеров США (NASD) и предполагает внесение особых поправок в даты, выпадающие на конец месяца.
1	Фактический/фактический. В соответствии с этим методом предполагается вычисление реального количества дней между любыми двумя датами и использование фактического количества дней для всех промежуточных лет при расчетах. Этот метод используется в США при ведении расчетов, связанных с выпуском и обслуживанием Казначейских векселей и облигаций. Кроме того, он оптимален для прочих нефинансовых расчетов.
2	Фактический/360. В соответствии с этим методом предполагается вычисление реального количества дней между двумя заданными датами, но при этом считается, что каждый год содержит по 360 дней.
3	Фактический/365. В соответствии с этим методом предполагается вычисление реального количества дней между двумя заданными датами, но при этом считается, что каждый год содержит по 365 дней.
4	Европейский 30/360. Аналогичен методу 0, при расчетах полагается, что каждый месяц содержит по 30 дней, а каждый год – по 360. Однако поправки в даты, выпадающие на конец месяца, вносятся в соответствии с Европейскими финансовыми конвенциями.

Замечания по использованию функции ДОХОД:

A = количество дней от начала периода купона до даты расчета (накопленные дни).
DSR = количество дней от даты расчета до даты погашения.
E = количество дней в периоде купона.

Пример использования функции

Рассмотрим пример использования функции ДОХОД на примере расчета доходности по облигации СберБ БО3R.

Данные по облигации:

Краткое наименование ценной бумаги	СберБ БО3R
ISIN	RU000A0ZYBS1
Дата погашения	08.12.2020
Дата следующей выплаты	12.04.2019
Номинал, руб	1000
Доходность купона % годовых от номинала	8.00%
Купон, руб	39.89
Длительность купона, дни	182

Для расчета доходности будем исходить из предположения, что планируем купить ценную бумагу 15 февраля 2019 года по цене 100,26% к номиналу.

Заполним таблицу следующим образом:
=ДОХОД(B2;B3;B4;B5;B6;B7;B8)

В данном примере доходность составляет 7,83% годовых.

Рассмотренная формула, дает общее понимание доходности по облигациям. В случае, если процентная ставка по купонам меняется, либо производится периодическое погашение номинала (например еврооблигация RUS-30 или облигация СтаврКрай2) рассматриваемая формула не подходит. На практике, по моему мнению, если рассматривается небольшое количество бумаг для расчета доходности лучше опираться от конкретных планируемых сумм и дат.

Управление личными финансами может быть сложной задачей, особенно если вам нужно планировать свои платежи и сбережения. Excel формулы и шаблоны бюджетов помогут вам вычислить будущую стоимость своих задолженности и инвестиций, что упростит расчет времени, необходимого для достижения целей. Используйте следующие функции:

ПЛТ: возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и процентной ставки.

КПЕР: возвращает количество периодов выплаты для инвестиции на основе регулярных постоянных выплат и постоянной процентной ставки.

ПВ: возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на данный момент равноценна ряду будущих выплат.

БС: возвращает будущую стоимость инвестиции при условии периодических равных платежей и постоянной процентной ставки.

Расчет ежемесячных платежей для погашения задолженности по кредитной карте

Предположим, остаток к оплате составляет 5400 долларов США под 17% годовых. Пока задолженность не будет погашена полностью, вы не сможете рассчитываться картой за покупки.

С помощью функции ПЛТ(ставка;КПЕР;ПС)

=ПЛТ(17%/12;2*12;5400)

получаем ежемесячный платеж в размере 266,99 долларов США, который позволит погасить задолженность за два года.

Аргумент "ставка" — это процентная ставка на период погашения кредита. Например, в данной формуле ставка 17% годовых делится на 12 — количество месяцев в году.

Аргумент КПЕР 2*12 — это общее количество периодов выплат по кредиту.

Аргумент ПС или приведенной стоимости составляет 5400 долларов США.

Расчет ежемесячных платежей по ипотеке

Представьте дом стоимостью 180 000 долларов США под 5% годовых на 30 лет.

С помощью функции ПЛТ(ставка;КПЕР;ПС)

=ПЛТ(5%/12;30*12;180000)

получена сумма ежемесячного платежа (без учета страховки и налогов) в размере 966,28 долларов США.

Аргумент "ставка" составляет 5%, разделенных на 12 месяцев в году.

Аргумент КПЕР составляет 30*12 для ипотечного кредита сроком на 30 лет с 12 ежемесячными платежами, оплачиваемыми в течение года.

Аргумент ПС составляет 180 000 (нынешняя величина кредита).

Расчет суммы ежемесячных сбережений, необходимой для отпуска

Необходимо собрать деньги на отпуск стоимостью 8500 долларов США за три года. Процентная ставка сбережений составляет 1,5%.

С помощью функции ПЛТ(ставка;КПЕР;ПС;БС)

получаем, что чтобы собрать 8500 долларов США за три года, необходимо откладывать по 230,99 долларов США ежемесячно.

Аргумент "ставка" составляет 1,5%, разделенных на 12 месяцев — количество месяцев в году.

Аргумент КПЕР составляет 3*12 для двенадцати ежемесячных платежей за три года.

Аргумент ПС (приведенная стоимость) составляет 0, поскольку отсчет начинается с нуля.

Аргумент БС (будущая стоимость), которую необходимо достичь, составляет 8500 долларов США.

Теперь допустим, вы хотите собрать 8500 долларов США на отпуск за три года, и вам интересно, какую сумму необходимо положить на счет, чтобы ежемесячный взнос составлял 175,00 долларов США. Функция ПС рассчитает размер начального депозита, который позволит собрать желаемую сумму.

С помощью функции ПС(ставка;КПЕР;ПЛТ;БС)

мы узнаем, что необходим начальный депозит в размере 1969,62 долларов США, чтобы можно было откладывать по 175,00 долларов США в месяц и собрать 8500 долларов США за три года.

Аргумент "Ставка" составляет 1,5%/12.

Аргумент КПЕР составляет 3*12 (или двенадцать ежемесячных платежей за три года).

Аргумент ПЛТ составляет -175 (необходимо откладывать по 175 долларов США в месяц).

Аргумент БС (будущая стоимость) составляет 8500.

Расчет срока погашения потребительского кредита

Представьте, что вы взяли потребительский кредит на сумму 2500 долларов США и согласились выплачивать по 150 долларов США ежемесячно под 3% годовых.

С помощью функции КПЕР(ставка;ПЛТ;ПС)

=КПЕР(3%/12;-150;2500)

выясняем, что для погашения кредита необходимо 17 месяцев и несколько дней.

Аргумент "Ставка" составляет 3%/12 ежемесячных платежей за год.

Аргумент ПЛТ составляет -150.

Аргумент ПС (приведенная стоимость) составляет 2500.

Расчет суммы первого взноса

Скажем, вы хотите приобрести автомобиль стоимостью 19 000 долларов США под 2,9 % годовых за три года. Вы хотите, чтобы ежемесячные платежи были на уровне 3500 долларов США в месяц, поэтому вам нужно выяснить сумму своего взноса. В этой формуле результатом функции ПС является сумма займа, которая затем вычитается из цены покупки, чтобы получить первый взнос.

С помощью функции ПС(ставка;КПЕР;ПЛТ)

= 19000-ПС(2,9%/12; 3*12;-350)

выясняем, что первый взнос должен составлять 6946,48 долларов США.

Сначала в формуле указывается цена покупки в размере 19 000 долларов США. Результат функции ПС будет вычтен из цены покупки.

Аргумент "Ставка" составляет 2,9%, разделенных на 12.

Аргумент КПЕР составляет 3*12 (или двенадцать ежемесячных платежей за три года).

Аргумент ПЛТ составляет -350 (необходимо будет выплачивать по 350 долларов США в месяц).

Оценка динамики увеличения сбережений

Начиная с 500 долларов США на счету, сколько можно собрать за 10 месяцев, если класть на депозит по 200 долларов США в месяц под 1,5% годовых?

Финансовые показатели облигаций могут быстро рассчитываться в Excel. Для этого используется функция ДОХОД. С ее помощью вычисляется доходность ценных бумаг, по которым периодически выплачиваются проценты (дивиденды).

Как работает функция ДОХОД в Excel?

Облигации – это финансовый инструмент. Предприятие, выпустившее облигации, выступает в роли заемщика. Нуждаясь в привлечении средств, оно выпускает и выбрасывает на рынок такие ценные бумаги. Покупатель облигаций, фактически, является заимодавцем. Ставка купона – это процент, который получает покупатель облигации за то, что предоставил свои средства в пользование.

Как рассчитать доходность облигаций в Excel? Для решения данной задачи используется функция ДОХОД. Как и любая другая функция, ее синтаксис состоит из имени и аргументов. В строке формул сначала ставится знак равенства, после прописывается имя функции, а затем заполняем ее аргументы входящими значениями всех необходимых показателей.

Всего аргументов у сложной функции 7. Из них 6 обязательных для заполнения:

Дата_согл. Расчетный день, когда облигация продана. Дата, когда долговые обязательства переданы покупателю. Не стоит путать ее с датой выпуска облигации.
Дата_вступл_в_силу. День, когда заем возвращается покупателю. Это срок погашения облигации, когда истекает срок ее действия.
Ставка. Годовая ставка процентов по облигации. Процент, который получает покупатель ценных бумаг.
Цена. Показатель определяет цену облигации на 100 рублей номинала.
Погашение. Стоимость, по которой выкупаются ценные бумаги.
Частота. Число выплат по облигации в год. Может быть равна 1, 2 или 4.
Базис. Этот аргумент не является обязательным. Поэтому он заключается в квадратные скобки. Он определяет способ вычисления дней.

Существует 5 видов базиса:

- 0 – Американский 30/360;
- 1 – факт/факт;
- 2 – факт/360;
- 3 – факт/365;
- 4 – Европейский 30/360.

Аргументы всегда берутся в круглые скобки. Все они перечисляются по порядку. Между ними ставится знак «;».

Синтаксис функции прописывается следующим образом:

=ДОХОД(дата_согл; дата_вступл_в_силу; ставка; цена; погашение; частота; [базис])

Обязательно учитывать то, что все даты в Excel записываются в виде последовательных чисел. Недопустимо использование текстового формата. За 1 принято 1 января 1900 года. Все последующие даты просто прибавляют число прошедших дней. Поэтому все значения рассчитываются с помощью функции «ДАТА».

введены даты без использования функции «ДАТА»;
значение ставки меньше нуля;
указана цена меньше или равная нулю;
погашение указано меньше или равно нулю;
базис меньше нуля или больше четырех.

Примеры использования функции ДОХОД для анализа облигаций

Для вычисления доходности ценных бумаг нужно ввести в первый столбик таблицы все аргументы функции.

Пример 1. Необходимо вычислить доходность облигаций при следующих условиях:

Облигации были проданы 15 февраля 2008 года.
Срок погашения – 15 ноября 2016.
Ставка по купонам определена 5,75%.
Цена составляет 95.04 номиналов.
Выкупная стоимость - 100 номиналов.
Проценты выплачиваются раз в полгода.
Значение базиса – 0 (30/360).

Последовательно вводите все значения заполняя таблицу с данными:

В ячейку B9 вводите данные для аргументов функции ДОХОД. Формула будет выглядеть так:

Доходность облигаций составляет 4,26%.

Можно вводить формулу с клавиатуры или указывать соответствующие ячейки мышью. При этом прописать имя функции нужно вручную. Затем откройте скобку и последовательно через «пробел» укажите все ячейки, которые должны быть учтены.

Пример 2. Предприятие продало облигации 14 июля 2015. Установлен небольшой срок их погашения 14 июля 2017. Предлагается ставка 6%. Цена бумаг 97,4 номинальных значений. Стоимость для выкупа – 107 номиналов. Выплата процентов проводится 4 раза в год. Базис 2 – (48/360).

Для вычисления прибыльности облигации введите данные в таблицу по принципу, который стал понятен из примера 1:

В итоговой ячейке B9 приписываем функцию:

Это обозначает, что расчет доходности к погашению облигаций составляет (6,16% х 2года)=12,32%.

Пример 3. Ценные бумаги проданы 2 августа 2004. Погашение произошло 13 мая 2018. Купонная ставка по ним – 5,3%. Цена бумаг – 93 номинала. Стоимость для выкупа – 104 номинала. Проценты начисляются раз в год. Базис – 0 (30/360).

По аналогии составляем таблицу, используя функцию =ДОХОД():

Прибыльность акций оценена в 3,52%.

С помощью программы Excel удалось всего за несколько минут установить, что самыми прибыльными инвестициями являются облигации из второго примера.

Функция ЧИСТВНДОХ предназначена для расчета внутренней ставки доходности по денежным потокам, носящим непериодический характер, и возвращает соответствующее значение в процентах. Для расчета аналогичной ставки по периодическим платежам в Excel предусмотрена функция ВСД.

Расчет рентабельности инвестиций по денежным потокам в Excel

Примеры использования функции ЧИСТВНДОХ в Excel.

Пример 1. Инвестор получил кредит в размере 100000 руб. под 13% годовых и вложил средства в проект, финансовые потоки которого занесены в таблицу Excel. Определить, является ли его вложение прибыльным.

Вид таблицы данных:

Рассчитаем внутреннюю ставку доходности по формуле:

B3:B12 – диапазон ячеек со значениями финансовых выплат. Первое значение соответствует затратам (вложение 100000 рублей в инвестиционный проект), поэтому указано в виде отрицательного числа.
A3:A12 – диапазон ячеек с датами, соответствующие моментам совершения выплат (введены с использованием функции ДАТА).

Данный инвестиционный проект является убыточным, поскольку кредит был получен по ставке 13%.

Расчет внутренней ставки доходности облигаций в Excel

Пример 2. В таблице Excel внесены данные об операциях с облигацией (покупка, выплата купонов, погашение). Выплаты производились несистематично. Определить прибыль и внутреннюю ставку доходности облигации за указанный период.

Вид таблицы данных:

Для определения прибыли используем формулу:

То есть, определяем разницу между суммарными выплатами по купонам + погашением и расходами на приобретение облигации:

Для определения ставки доходности используем формулу:

На практике подобные расчеты имеют смысл для достаточно больших периодов времени, поскольку на рынке зачастую возникают кризисные явления. Оптимальный период расчета доходности инвестиционного портфеля с использованием функции ЧИСТВНДОХ – от 5 лет и более.

Читайте также: