Таблица для учета акций и дивидендов эксель

Главное в статье: "Таблица для учета акций и дивидендов эксель" с выводами и комментариями специалистов. Если требуется уточнение какого-то вопроса или актуализация данных на 2020 год, то обращайтесь к дежурному консультанту.

Как посчитать доходность инвестиционного портфеля

Расчёт доходности портфеля необходим инвестору, чтобы понять, насколько эффективно работают его деньги. Мы разобрались, как считать доходность: какие формулы использовать, как это делать в Excel и какой сервис посчитает всё за вас.

Что такое доходность и прибыль

Доходность показывает, насколько эффективно инвестор вкладывает свои деньги. Рассчитывается в процентах за определенный период, чаще за год.

Например, доходность активов инвестора — +3% годовых. Это значит, что на каждую вложенную в активы 1000 рублей приходится 30 рублей прибыли в год. Такой результат ниже инфляции, поэтому со временем деньги инвестора будут дешеветь.

Оценив доходность, можно понять, стоит ли что-то менять в своей инвестиционной стратегии.

Прибыль инвестора — это положительная разница между начальной суммой инвестирования и суммой на брокерском счёте после начисления купонов и дивидендов за вычетом налогов и комиссии брокера.

  1. Инвестор купил акции на 100 000 рублей.
  2. Через год они стоят 110 000 рублей. Прибыль с роста курса акций — 10 000 рублей, доходность — 10% годовых.
  3. За этот год инвестор также получил дивиденды 5000 рублей.
  4. При выводе денег со счёта он заплатил налог 13% с 10 000 рублей прибыли и оплатил брокерскую комиссию 0,3% от суммы сделки (у каждого брокера комиссия своя).
  5. Чистая прибыль составила 13 400 рублей при доходности 10%.

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

Прибыль облигаций складывается из купонного дохода, разницы курсов при покупке и погашении, налогов и комиссий. НДФЛ 13% для резидентов и 30% для нерезидентов облагается доход от курсовой разницы. С купонами сложнее:

  • в ОФЗ и муниципальных облигациях они освобождены от налогов;
  • не облагаются НДФЛ корпоративные бумаги, выпущенные после 1 января 2017 года;
  • купоны корпоративных бумаг, выпущенных до 1 января 2017 года, облагаются налогом 13%;
  • если купонная ставка новых облигаций превышает ключевую ставку ЦБ РФ более, чем на 5%, с разницы между ними инвестор заплатит налог 35%, например, если ставка ЦБ РФ 6,5%, а купон облигации 13% относительно номинала, инвестор заплатит 35% с 1,5% (разница между купоном и ставкой ЦБ +5%).

Инвестиции в ПИФы не приносят дивидендов или купонов. Доход зависит от разницы между ценой покупки и продажи пая. Издержки инвестора — налог на прибыль и комиссия управляющей компании: надбавка за покупку паёв, скидка за погашение, вознаграждение за управление.

Последние новости

Как считать доходность инвестиций в Excel

В Excel для расчета доходности инвестиций с учетом ввода/вывода денег используется функция ЧИСТВНДОХ (XIRR) — это функция, которая возвращает внутреннюю ставку доходности для графика денежных потоков, которые не обязательно носят периодический характер. Как ей пользоваться? Возьмем пример из статьи Арсагеры:

  1. Инвестор купил акций на сумму 1000 рублей.
  2. Через 3 месяца он купил еще акций на 500 рублей.
  3. Еще через 4 месяца он продал часть акций на сумму 300 рублей.
  4. Через год после первоначального приобретения, стоимость акций составила 1300 рублей.

Доходность портфеля составила 8,004% годовых.

Введем эти данные в Excel. В первой колонке указываем суммы, во второй даты.

  • В первой строчке указываем начальную сумму инвестиций 1000 рублей и дату инвестирования, к примеру 01.01.2014.
  • Во второй строчке указываем ввод средств 500 рублей и дату 01.03.2014.
  • В третьей строчке указываем вывод средств со знаком минус -300 и дату 01.04.2014.
  • В четвертой строчке указываем стоимость портфеля на конец года со знаком минус -1300 и дату конец года 31.12.2014.

Теперь выбираем какую-нибудь пустую ячейку и жмем кнопку fx (вставить функцию). Находим функцию ЧИСТВНДОХ. Вводим значения ячеек. В строке «Значения» выбираем ячейки с суммами, в строке «Даты» — ячейки с датами.

Жмем ОК, получаем доходность — 8,009% годовых.

Если бы мы считали по простой формуле, то получили бы результат (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:

Формулы расчетов ниже:

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

Инвестиционный проект в Excel c примерами для расчетов

Для привлечения и вложения средств в какое-либо дело инвестору необходимо тщательно изучить внешний и внутренний рынок.

На основании полученных данных составить смету проекта, инвестиционный план, спрогнозировать выручку, сформировать отчет о движении денежных средств. Наиболее полно всю нужную информацию можно представить в виде финансовой модели.

Читайте так же:  Открыть ооо в беларуси пошаговая инструкция

Годовая доходность в процентах

Более правильно оценить прибыль вложений можно с помощью годовой доходности.

Если простыми словами, то годовая доходность показывает, сколько зарабатывает инвестор на каждый вложенный рубль за одинаковый период времени.

Общепринятая оценка «одинакового периода времени» — это один год. Все проценты доходности полученные за разные промежутки времени сводятся к годовой ставке доходности.

Как это выглядит на практике?

Вложили деньги в акции Сбербанка — 30 000 рублей. И в акции Газпрома — 50 000 рублей

Через полгода, после роста котировок Сбербанка, продали все за 36 тысяч рублей.

Газпром вы держали ровно год и скинули бумаги за 65 тысяч.

Итог: На Сбербанке вы заработали за полгода 6 тысяч. На Газпроме 15 тысяч, но за целый год.

Если считать по общей доходности, то чистая прибыль в процентах составила бы:

  • Сбербанк — 6 тысяч или 20%;
  • Газпром — 15 тысяч или 30%.

Для правильной оценки эффективности инвестиций нужно все перевести в годовые проценты:

Доходность (% годовых) = (прибыль в % * 365 дней) / срок инвестиций в днях.

Доходность Сбербанка = 20% х 365 дня / 180 дней = 40% годовых

Доходность Газпрома = 30% х 365 / 365 = 30% годовых.

Более выгодными оказались инвестиции в акции Сбербанка.

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

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

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

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

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

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

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

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

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

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

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

Результатом применения функции является определение прибыльности облигаций. Программа может выдать не результат, а знак ошибки #ЧИСЛО! Это происходит в нескольких случаях:

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



Рекомендованные новости

Сбербанк будет куплен в два подхода

Электроэнергетический сектор вышел в лидеры роста с начала года

Связанные одной целью

Как потерять 99,98% капитала на ETF

Рубль резко снизился из-за возвращения страхов по поводу коронавируса

Куда направят средства от продажи акций Сбербанка

Ход торгов. Энергетики не хотят останавливаться и идут против рынка

Менеджмент CISCO разочаровал инвесторов

Адрес для вопросов и предложений по сайту: [email protected]

Copyright © 2008–2020. ООО «Компания БКС» . г. Москва, Проспект Мира, д. 69, стр. 1
Все права защищены. Любое использование материалов сайта без разрешения запрещено.
Лицензия на осуществление брокерской деятельности № 154-04434-100000 , выдана ФКЦБ РФ 10.01.2001 г.

Данные являются биржевой информацией, обладателем (собственником) которой является ПАО Московская Биржа. Распространение, трансляция или иное предоставление биржевой информации третьим лицам возможно исключительно в порядке и на условиях, предусмотренных порядком использования биржевой информации, предоставляемой ОАО Московская Биржа. ООО «Компания Брокеркредитсервис» , лицензия № 154-04434-100000 от 10.01.2001 на осуществление брокерской деятельности. Выдана ФСФР. Без ограничения срока действия.

* Материалы, представленные в данном разделе, не являются индивидуальными инвестиционными рекомендациями. Финансовые инструменты либо операции, упомянутые в данном разделе, могут не подходить Вам, не соответствовать Вашему инвестиционному профилю, финансовому положению, опыту инвестиций, знаниям, инвестиционным целям, отношению к риску и доходности. Определение соответствия финансового инструмента либо операции инвестиционным целям, инвестиционному горизонту и толерантности к риску является задачей инвестора. ООО «Компания БКС» не несет ответственности за возможные убытки инвестора в случае совершения операций, либо инвестирования в финансовые инструменты, упомянутые в данном разделе.

Приведенная информация и мнения составлены на основе публичных источников, которые признаны надежными, однако за достоверность предоставленной информации ООО «Компания БКС» ответственности не несёт. Приведенная информация и мнения формируются различными экспертами, в том числе независимыми, и мнение по одной и той же ситуации может кардинально различаться даже среди экспертов БКС. Принимая во внимание вышесказанное, не следует полагаться исключительно на представленные материалы в ущерб проведению независимого анализа. ООО «Компания БКС» и её аффилированные лица и сотрудники не несут ответственности за использование данной информации, за прямой или косвенный ущерб, наступивший вследствие использования данной информации, а также за ее достоверность.

Читайте так же:  Основания для обжалования решения арбитражного суда

Считаем доходность инвестиций в портфеле + готовая Excel таблица с формулами

Для оценки эффективности вложений нужно обязательно знать, какую доходность они принесут (или принесли). А если таких вложений много? Их нужно как то сравнить. Чтобы понять — что было более выгодным. И вообще, как можно рассчитать доходность портфеля имея различные вложения (вклады в банке, облигации, акции и прочее). На разные суммы и различные сроки?

Например, что более выгодно? Вложить 57 тысяч на 3 месяца и заработать 3 тысячи. Или инвестировать 75 000 на восемь месяцев и получить 5500?

Как узнать процент эффективной доходности портфеля, если в течении года было постоянное снятие и внесение средств?

Как рассчитать доходность

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

Прибыль / Конечная стоимость × 100%.

Например, инвестор купил акции за 1000 рублей, продал за 1500. Прибыль составила 500 рублей. Доходность: 500 / 1000 × 100% = 50%.

На практике формула почти бесполезна. Она не учитывает срок инвестирования, операции с активами, внесение и выводы средств. Для определения доходности портфеля используются формулы посложнее.

Когда в портфеле несколько активов. Допустим, портфель инвестора состоит из акций трёх компаний, купленных год назад:

  • в акции компании A вложено 20 000 рублей — они принесли 10% прибыли;
  • в акции компании B вложено 50 000 рублей — они подешевели на 5%;
  • акции компании C, купленные за 70 000 рублей, принесли 25%.

Доходность портфеля измеряется как средневзвешенная сумма доходностей входящих в него бумаг. Для расчёта определяем вес акций каждой компании в портфеле по формуле:

инвестиции в акции одной компании / общая сумма вложений × 100%.

Общая сумма вложений в нашем примере — 140 000 рублей. Считаем:

  • вес акций компании А: 20 000 / 140 000 × 100% = 14%;
  • вес акций компании B: 50 000 / 140 000 × 100% = 36%;
  • вес акций компании C: 70 000 / 140 000 × 100% = 50%.

Для определения общей доходности портфеля прибыль каждой акции умножается на её вес, переведённый в десятичную дробь (14% → 0,14), после чего все произведения суммируются:

(10 × 0,14) + (−5 × 0,36) + (25 × 0,50) = 12,1%

Если инструменты докупались в разное время. Сначала определяется изменение каждого актива в процентах годовых, после чего рассчитывается стоимость портфеля. Воспользуемся Excel, чтобы упростить подсчёты.

Допустим, вы купили акции на 50 000 рублей. Далее в течение года пять раз докупали активы ещё на 5000 рублей, один раз вывели 7000 досрочно и один раз получили дивиденды 3000 рублей. За год стоимость портфеля выросла до 90 000 рублей. Какая годовая доходность?

Открываем таблицу и пишем:

  • в первом столбце — дату денежных движений в формате дд.мм.гггг;
  • во втором — сумму покупок, дивидендов, вывода.
  • в третьем — комментируем, какие изменения произошли в портфеле (это необязательно, но удобно);
  • в последней нижней строке указываем текущую дату, а в столбце с суммой отнимаем общую стоимость портфеля со знаком «минус».

В свободную ячейку вставляем формулу:

Переменные значения в скобках указывайте в соответствии с вашими данными.

Формула ЧИСТВНДОХ рассчитывает годовую доходность с учётом сложного процента. Эксель автоматически рассчитает годовую доходность портфеля. Результат в процентах появится в ячейке, где вставлена формула. В примере это 14,2% годовых.

Как считать в Excel доходность портфеля с учётом всех финансовых операций.

Наши друзья из Арсагеры тоже рассказали, как посчитать доходность вручную. Там много формул, и они сложнее — будьте готовы к долгим расчётам.

Расчет доходности в Excel

Есть более простой вариант расчета процентов в таблице Эксель. Нам поможет формула ЧИСТВНДОХ.

Все что нам нужно знать — это даты и суммы движений средств.

Как заполнить таблицу?

Нам нужны 2 колонки по движению денежных средств:

  1. сумма входящих и выходящих потоков
  2. Даты движений.

Все поступления на счет должны быть со знаком плюс. Снятия и прочие расходы обязательно со знаком минус. Конечная финальная сумма (на момент которой подсчитывается доходность) на счете тоже со знаком минус.

Вот как это выглядит на примере:

Как это сделать в Excel?

Вносим в таблицу собственные значения (по аналогии с примером выше).

Вызываем функцию ЧИСТВНДОХ.

В поля «Значение» и «Даты» вносим наши условия как на картинке ниже. Просто выделяя правой кнопкой мыши необходимый диапазон.

Саму формулу еще нужно умножить на 100. Дабы привести к более привычному нам виду. По умолчанию показывается не в процентах, в доле от единицы. В нашем случае — 0,16.

По ссылке, есть файл Excel с уже готовыми формулами, перечисленными в статье. Подставляете свои данные. Считаете прибыль. Радуйтесь (или огорчайтесь) полученной доходности.

За обновлениями в этой и других статьях теперь можно следить на Telegram-канале: @vsedengy.

Большая таблица дивидендов

Представленная на этой странице информация позволит легко найти дивиденды, которые выплачивались по акциям крупнейших российских компаний. Таблица содержит дивидендную историю 100 акций, а так же позволяет визуально оценить динамику выплат. В самом низу представлены средние ежегодные темпы роста за 15, 10 и 5 лет. С помощью этих данных можно найти перспективные и стабильные дивидендные акции для инвестирования, отличающиеся стабильными и высокими темпами роста.

Если вы заметили ошибку или считаете, что какой-то акции не хватает, напишите в комментариях.

Читайте так же:  Аудиторская проверка кредитных учреждений

комментариев 35

  • Комментарии 35
  • Пингбэки 0

А эти темпы роста в чем и как? Еще бы добавить табличку с данными в процентном выражении. Может будет полезен еще вот этот сайт. Сохранил на одном из вебинаров Открытия. //cfocom.ru/issuers/

Это средние годовые темпы роста в процентах за 15, 10 и 5 лет.

Может лучше указывать не размер дивидендов в рублях, а, например, в процентах от стоимости акции на момент отсечки?
И столбцы отсортировать по алфавиту…

Пока особого смысла для себя в этом не вижу. Это потребует слишком много времени, рыночные цены меняются каждый день. К тому же уже есть сайт, где можно отслеживать текущую дивидендную доходность //www.dohod.ru/ik/analytics/dividend/
Цель данной таблицы — оценить общую динамику дивидендов и их темпы роста.

Спасибо за ссылочку!

Таблица правильная. Доходность от дивидендов величина относительная и зависит от стоимости приобретния. Автору респект за проделанную работу!
Да и сортировка правильная Кто когда начал платить того и вперед.

Почти правильная — кто когда начал платить того и вперёд Вот и будут видны «аристократы» российского фондового рынка

Мой любимый энергосбыт пропущен — Нижегородский, пока до него не добрались загребущие ручки ТНС энерго был лидером дивидедных сезон по методике расчёта дивиденды к цене закрытия

Алексей, большое спасибо за таблицу! Забрал ее себе. На досуге отсортировал убрав лишнее. В итоге остались около 30 которые имеют долгую дивидендную доходность (без перерывов).

Видео (кликните для воспроизведения).

Не за что. Рад, что она вам пригодилась.

Спасибо огромное за проделанную работу — очень полезная табличка!

Спасибо! То что нужно! Забрал себе.

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

Интуитивно понятна таблица для новичка. Большое спасибо за доходчивую информацию! За проделанный труд! Это ценная таблица!

Пример функции ДОХОД для анализа доходности инвестиций в Excel

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

Доходность с учетом движения средств

А как вывести общий результат, например за год?

Складывать все доходности не очень удобно и трудоемко.

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

Пример. На начало года инвестор обладал капиталом в 200 тысяч рублей. За счет выгодных инвестиций, через год его портфель оценивался в 240 тысяч.

Чистая прибыль 40 000 рублей или 20% годовых.

В этой методике расчета доходности есть один существенный минус, который будет искажать реальные цифры. А по простому, делать их совсем не правильными.

За рассматриваемый период не учитываются возможные движения средств по счету или портфелю.

Что это за движения:

  • ввод-вывод средств;
  • получения прибыли «извне». Например, купонный доход по облигациям или дивиденды по акциям.

Из примера выше. Если за месяц до окончания годового периода инвестор вносил дополнительные 40 тысяч рублей. Как это скажется на результате? В абсолютных цифрах мы также имеем +40 тысяч прибыли или 20% годовых. Но по факту результата ноль.

Другой вариант. Через 1 месяц инвестор не внес, а снял 40 тысяч. В итоге почти целый год он оперировал суммой на 20% меньше первоначальной. И все равно заработал 40 тысяч прибыли.

Или в течении года выплачивались дивиденды, купоны. Были постоянное внесение и вывод средств со счета. Как тогда? Как определить реальную доходность?

Для расчета есть специальная формула расчета процентов в зависимости от даты и суммы движения средств. Но думаю большинство (наверняка все) не будут ее пользоваться. Она слишком сложная и громоздкая. Даже ее приводить здесь не буду.)))

Считаем прибыль

Самая простая и базовая формула для определения «выгодности» вложений.

Разность между конечной суммой и начальной образует чистую прибыль.

Чтобы вывести в процентном соотношении воспользуйтесь формулой:

Доходность = (чистая прибыль) / сумму вложения * 100%.

Пример.

Купили акции Газпрома на 10 000 рублей. Через год все продали за 13 000 тысяч.

Чистая прибыль составила 3 тысячи рублей (13 000 — 10 000).

Доходность вложений 30% (3 000 / 10 000) * 100%).

В этой формуле есть один существенный недостаток. Она позволяет рассчитать только абсолютную доходность. Без привязки к периоду, за который она была получена.

Мы могли заработать 30% за 1 год. А могли бы и за 5 лет.

Расчет экономической эффективности инвестиционного проекта в Excel

Для оценки эффективности инвестиций применяются две группы методов:

  • статистические (PP, ARR);
  • динамические (NPV, IRR, PI, DPP).

Коэффициент PP (период окупаемости) показывает временной отрезок, за который окупятся первоначальные вложения в проект (когда вернутся инвестированные деньги).

Экономическая формула расчета срока окупаемости:

где IC – первоначальные вложения инвестора (все издержки),

CF – денежный поток, или чистая прибыль (за определенный период).

Расчет окупаемости инвестиционного проекта в Excel:

  1. Составим таблицу с исходными данными. Стоимость первоначальных инвестиций – 160000 рублей. Ежемесячно поступает 56000 рублей. Для расчета денежного потока нарастающим итогом была использована формула: =C4+$C$2.
  2. Рассчитаем срок окупаемости инвестированных средств. Использовали формулу: =B4/C2 (сумма первоначальных инвестиций / сумма ежемесячных поступлений).

Так как у нас дискретный период, то срок окупаемости составит 3 месяца.

Данная формула позволяет быстро найти показатель срока окупаемости проекта. Но использовать ее крайне сложно, т.к. ежемесячные денежные поступления в реальной жизни редко являются равными суммами. Более того, не учитывается инфляция. Поэтому показатель применяется вкупе с другими критериями оценки эффективности.

Читайте так же:  Что нужно для открытия цветочного бизнеса пошагово

Рентабельность инвестиций

ARR, ROI – коэффициенты рентабельности, показывающие прибыльность проекта без учета дисконтирования.

где CFср. – средний показатель чистой прибыли за определенный период;

IC – первоначальные вложения инвестора.

Пример расчета в Excel:

  1. Изменим входные данные. Первоначальные вложения в размере 160 000 рублей вносятся только один раз, на старте проекта. Ежемесячные платежи – разные суммы.
  2. Рассчитаем средние поступления по месяцам и найдем рентабельность проекта. Используем формулу: =СРЗНАЧ(C23:C32)/B23. Формат ячейки с результатом процентный.

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

Примеры инвестиционне6ого проекта с расчетами в Excel:

Статистические методы не учитывают дисконтирование. Зато позволяют быстро и просто найти необходимые показатели.

Как правильно рассчитать доходность портфеля

Приближается к завершению очередной календарный год. Актуальным становится вопрос оценки инвестиционных успехов. Расчет доходности портфеля за определенный период — задача простая, но имеет ряд особенностей. Рассмотрим основные моменты, которые стоит знать частному инвестору.

Полученный от инвестиций доход принято измерять в процентах, так как абсолютный размер прибыли напрямую зависит от размера вложенного капитала. Для этого размер прибыли необходимо разделить на начальную сумму инвестиций и умножить на 100%. Например, если вы вложили 100 руб. и заработали от этой инвестиции 8 руб., то доход составит 8/100*100% = 8%.

Пусть эти 8% были заработаны инвестором за 10 месяцев. Вместо инвестиционного портфеля инвестор мог разместить средства на депозите по ставке 8% годовых на 1 год или вложить в альтернативный проект, который сулил 15% за 18 месяцев. Эффективно ли распорядился деньгами инвестор?

В финансовой сфере принят единый стандарт, позволяющий сравнивать различные варианты вложений. По этому стандарту доходность оценивается в процентах годовых. Чтобы привести доход за любой период к годовой ставке необходимо разделить его на срок инвестиций в днях и умножить на 365 (или 366, если год високосный).

В нашем примере 8% были заработаны за 10 месяцев или 304 дня. Тогда годовая доходность составит 8%/304 * 365 = 9,6% годовых. Доходность депозита уже измеряется в процентах годовых и составляет 8%. Доходность альтернативного проекта будет равна 19%/517 * 365 = 10,6% годовых. Делаем вывод, что портфель инвестора уже опережает по доходности депозит, но пока отстает от альтернативного проекта.

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

В рассматриваемом примере годовая доходность портфеля инвестора, рассчитанная по формуле, составит: (1+8%) ^ (365/304) — 1 = 9,68%.

Среднегодовая доходность и формула CAGR

Если срок инвестиций составляет несколько лет, инвестору важно понимать значение среднегодовой доходности своих инвестиций. Инвестор может сравнить какие варианты активов наиболее эффективны для его целей — вложения в акции или другие финансовые инструменты, например, облигации или депозиты.

Самый легкий способ — рассчитать среднее арифметическое, то есть сложить доходности за все годы и разделить на количество лет. Если разброс значений невелик, результат такого расчета близок к истине. Но такой подход все-таки не вполне корректен и может ввести инвестора в заблуждение.

Применив значение средней доходности ко всему сроку инвестиций мы должны получить тот же размер капитала, что и при использовании исходных значений. Проверим, что в случае среднего арифметического это не так:

Итоговый размер капитала при использовании средней ставки составит 1891,9 тыс. руб. против фактических 1839,4 тыс. руб. Это значит, что среднее арифметическое не подходит для оценки среднего темпа роста капитала.

Корректная оценка среднегодовой доходности проводится по формуле среднего геометрического. В Excel к ней можно обратиться по названию СРГЕОМ(), перечислив в скобках значения доходностей за все годы. При этом к каждой доходности необходимо прибавлять единицу, а из итогового результата — вычитать единицу. В противном случае формула выдаст ошибку.

Для тех, кто будет рассчитывать доходность без использования Excel или хочет лучше разобраться с логикой среднего геометрического, приведем математическую формулу, где буквой r обозначена доходность за каждый год, а буквой n – число лет:

Если данные по доходности за каждый год отсутствуют, но известны стартовая и итоговая суммы каптала, можно использовать формулу CAGR (Compound Annual Growth Rate):


Доходность портфеля с переменной суммой инвестиций

Формулу CAGR может быть использована в ситуации, когда сумма инвестиций была внесена один раз на старте, и инвестор не совершал более никаких движений по счету. На практике — это редкая ситуация. Обычно инвестор вносит или снимает различные суммы со счета в процессе инвестиций. В таком случае возникнет резонный вопрос каким образом рассчитывать доходность?

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

В качестве аргументов функция принимает два массива: массив значений денежных потоков (вводов/выводов средств) и массив дат, в которые эти потоки были получены (со знаком плюс) или уплачены (со знаком минус).

Рассмотрим пример. Допустим, 1 февраля 2019 г. инвестор вложил 1 млн руб., затем 1 июня внес еще 600 тыс. руб. и 1 сентября того же года снял 400 тыс. руб. По состоянию на 1 ноября портфель стоит 1,37 млн руб. Рассчитаем доходность портфеля инвестора.

Вводим данные в таблицу Excel. Ввод средств — со знаком минус, вывод средств и финальную сумму — со знаком плюс. Далее применяем функцию ЧИСТВНДОХ() следующим образом:

Читайте так же:  Ефрсфдюл сведения о результатах обязательного аудита

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

Таким образом, получается 18,7%/365*273 = 14%, именно столько заработал инвестор за 9 месяцев по отношению к среднему размеру капитала в рассматриваемом временном промежутке.

БКС Брокер

Где вести портфель

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

Самые информативные сторонние сервисы, известные нам — Wealth Adviser и Smart-lab. Что-то подобное предлагают Investing, Google Finance и другие, но они показывают только изменение цены актива в портфеле за текущий день.

Wealth Adviser показывает полное состояние портфеля: стоимость, доходность в процентах годовых, прибыль. Smart-lab показывает прибыль или убыток в валюте и изменение капитала в процентах, но не учитывает доходность в процентах годовых.

Как работать с сервисами ведения портфелей на примере Wealth Adviser:

  1. зарегистрироваться;
  2. в разделе «Состав» личного кабинета добавить активы: акции, облигации, металлы, ETF и другие;
  3. в появившемся списке бумаг поочередно ввести дату покупки, сумму и цену;
  4. система рассчитает прибыль (или убыток) и доходность.

Сводная информация по портфелю в Wealth Adviser.

Как рассчитать доходность инвестиций с учетом ввода/вывода средств?

Автор: Алексей Мартынов · Опубликовано 05.06.2015 · Обновлено 23.11.2018

Многие инвесторы часто вносят в свой инвестиционный портфель дополнительные средства, докупают активы или продают часть активов и выводят деньги. И считают доходность по обычной формуле, и думают, что все делают правильно. На самом деле они делают неправильно и только вводят себя в заблуждение. В этой статье я расскажу как нужно считать доходность инвестиций, если вы вносили и выводили деньги со своего счета.

Для этого рассчитывают результат инвестирования с учетом вводов/выводов средств и делят его на средневзвешенную по времени величину вложенных средств. Данный метод расчета очень подробно описан на сайте УК Арсагера, поэтому здесь я его описывать не буду. Те, кто читал их статью, знают, что этот метод очень трудоемкий, все приходится считать вручную. Если вы много раз вводили и выводили деньги, то формула расчета доходности портфеля будет ооочень длинной, легко запутаться и сделать ошибку. Поэтому я объясню, как очень просто посчитать доходность инвестиций в Excel.

пополни брокерский счёт без комиссии

  • С карты любого банка
  • Прямо на сайте
  • Без комиссии

Финансовая модель инвестиционного проекта в Excel

Составляется на прогнозируемый период окупаемости.

  • описание макроэкономического окружения (темпы инфляции, проценты по налогам и сборам, требуемая норма доходности);
  • прогнозируемый объем продаж;
  • прогнозируемые затраты на привлечение и обучение персонала, аренду площадей, закупку сырья и материалов и т.п.;
  • анализ оборотного капитала, активов и основных средств;
  • источники финансирования;
  • анализ рисков;
  • прогнозные отчеты (окупаемость, ликвидность, платежеспособность, финансовая устойчивость и т.д.).

Чтобы проект вызывал доверие, все данные должны быть подтверждены. Если у предприятия несколько статей доходов, то прогноз составляется отдельно по каждой.

Финансовая модель – это план снижения рисков при инвестировании. Детализация и реалистичность – обязательные условия. При составлении проекта в программе Microsoft Excel соблюдают правила:

  • исходные данные, расчеты и результаты находятся на разных листах;
  • структура расчетов логичная и «прозрачная» (никаких скрытых формул, ячеек, цикличных ссылок, ограниченное количество имен массивов);
  • столбцы соответствуют друг другу;
  • в одной строке – однотипные формулы.



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

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

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

  1. Облигации были проданы 15 февраля 2008 года.
  2. Срок погашения – 15 ноября 2016.
  3. Ставка по купонам определена 5,75%.
  4. Цена составляет 95.04 номиналов.
  5. Выкупная стоимость — 100 номиналов.
  6. Проценты выплачиваются раз в полгода.
  7. Значение базиса – 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 удалось всего за несколько минут установить, что самыми прибыльными инвестициями являются облигации из второго примера.

Источники

Таблица для учета акций и дивидендов эксель
Оценка 5 проголосовавших: 1

ОСТАВЬТЕ ОТВЕТ

Please enter your comment!
Please enter your name here