Excel для расчета доходности портфеля

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

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

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

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

Общее определение доходности

Классический портфель инвестиций состоит из ценных бумаг разной доходности различных эмитентов. Это позволяет грамотно диверсифицировать риски при каких-либо изменениях. Но для получения максимальной прибыли важно определить лучший доход по инвестиционному портфелю, который и будет формировать портфель (см. Модели формирования портфеля инвестиций. Грамотный подход – удачные вложения).

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

• Рост доходности сопровождается ростом рисков.
• Сумма доходов напрямую влияет на уровень доходности. Минимальный доход можно получить без какого-либо риска, если сумма вложений высока.

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

• часть вложений может быть высокорискованной, следовательно, с высокой доходностью;
• часть активов – с низким уровнем риска, то есть – малодоходными.

Способы минимизации риска
Это поможет получать прибыль. Возможные убытки высокорискованных ценных бумаг могут быть покрыты прибылью от низкодоходных ценных бумаг.

Важно! Как правило, инвестиционный портфель состоит из 8-25 всевозможных активов. Нет необходимости в сильной диверсификации, так как это не даст эффективно управлять портфелем. Большой объем инвестиций разной направленности повысит издержки на содержание и усложнит контроль.

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

• доходность, которую хотелось бы получить;
• уровень налоговой базы на доходы.

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

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

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

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

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

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

Пример.

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

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

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

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

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

Определение доходности по теории портфельных инвестиций

Теория предполагает, что прибыльность ценной бумаги – случайный показатель. На распределение этих показателей (величин) распространяется Гауссовский закон.

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

Существуют следующие подходы к распределению вероятностей:

В первом случае инвестор сам строит возможные пути развития в течение инвестиционного периода, определяет вероятность результатов и потенциальную доходность бумаг. Этот подход имеет преимущество из-за возможности оценки будущих (желаемых) показателей доходности. Но это очень сложно сделать, поэтому он не получил широкого распространения.

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

Для получения информации по распределению надо лишь найти значения этих величин за определенный отрезок времени в прошлом и построить их распределение.

Динамичный или дисконтированный метод

Как следует из названия, этот метод определяет время от вложения до возврата средств с учётом дисконтирования. Речь о таком моменте времени, когда чистая текущая стоимость становится неотрицательной и такой и остаётся дальше.

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

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

Далее разберём возможные формулы расчёта

Формула расчета

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

Если внутренняя цена превышает рыночную, актив считается недооцененным. Рекомендуется к приобретению, так как ожидается рост. В противном случае бумагу лучше реализовать ввиду ее переоценки.

Вариант 1

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

Прибыль по портфелю = (Цена цб на дату расчета — Цена цб на дату приобретения)/(Цена цб на дату приобретения), Где цб – ценная бумага.

Для наглядности рассмотрим пример. Выбираем между портфелями А и С, инвестиции в каждый составляют 100 000 руб. Спустя год, цена портфеля А составила 108 000 руб., активы С были равны 135 000 руб.

То есть, прибыльность равна:

• А: 8 % годовых ((108 тыс. – 100 тыс.) / 100 тыс.);
• С: 35 % годовых ((135 тыс. – 100 тыс.) / 100 тыс.).

Вариант 2

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

Величину ожидаемой доходности можно рассчитать по формуле: R портфеля, % = R1 × W1 + R2 × W2 + … + Rn × Wn , где Rn — предполагаемая доходность i-й акции; Wn — доля i-й акции в портфеле.

Еще пример. В портфель входят акции:

• А доходностью 10 % годовых;
• С прибыльностью 25 %.

Рассмотрим таблицу ниже.

Таблица – Прибыль инвестиционных портфелей:

Актив	Процент каждой акции в портфеле
Портфель 1	Портфель 2	Портфель 3
А	70	50	30
С	30	50	70
R портфеля, %	14,5	17,5	20,5

Рассчитаем доходность, исходя из формулы:

• портфель 1: предполагаемая доходность портфеля = 0,1*0,7 + 0,25*0,3 = 14,5 %;
• портфель 2: предполагаемая доходность портфеля = 0,1*0,5 + 0,25*0,5 = 17,5 %;
• портфель 3: предполагаемая доходность портфеля = 0,1*0,3 + 0,25*0,7 = 20,5 %.

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

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Рассмотрим пример оценки дисконтированного срока окупаемости инвестиций для бизнес-плана. Первоначальные инвестиции составили 100000 руб., денежный поток изменялся ежемесячно и отражен в столбце «С». Ставка дисконтирования была взята равной 10%. Для расчета дисконтированного денежного потока воспользуемся следующей формулой:

Дисконтированный денежный поток =C7/(1+$C$3)^A7

Денежные поступления нарастающим итогом =E7+D8

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

Проект окупится на 5 месяц, в котором денежные поступления составят 100860 руб.

Коротко о рисках

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

Важно! Безрисковая доходность рассчитывается по ставкам облигаций государства. На Западе по государственным облигациям получают порядка 5 % годовых, в РФ – 8-10 %.

Все инвесторы работают при полной неопределенности. Определение дохода инвестиционного портфеля основывается лишь на их предположениях, знаниях, опыте. Риск – это вероятность какого-либо события. Дать ему оценку – означает понять степень того, будет это событие или нет.

Риск при портфельных инвестициях бывает:

• систематический (рыночный, неспецифический или недиверсифицируемый);
• несистематический (нерыночный, специфический или диверсифицируемый).

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

Важно! Для снижения влияния диверсифицируемого риска к нулю на рынках развитых стран общие инвестиции формируются из 30-40 инструментов. Развивающие рынки имеют сильную волатильность, поэтому эту цифру необходимо увеличить.

Несистематический риск зависит от особенностей каждого инструмента. Его снижают диверсификацией.

Инфографика: Срок окупаемости инвестиций (PP, DPP, BO DPP)

★ Инвестиционная оценка в Excel. Расчет NPV, IRR, DPP, PI за 5 минут

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

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Как рассчитать простым способом

Формула, к которой прибегают для расчёта простым способом вычисления коэффициента срока окупаемости выглядит так:

СРОК ОКУПАЕМОСТИ = РАЗМЕР ВЛОЖЕНИЙ / ЧИСТАЯ ГОДОВАЯ ПРИБЫЛЬ

Принимаем во внимание, что РР — срок окупаемости выраженный в годах.

К0 — сумма вложенных средств.

ПЧсг — Чистая прибыль в среднем за год.

Пример.

Вам предлагают инвестировать в проект сумму в размере 150 тысяч рублей. И говорят, что проект будет приносить в среднем 50 тысяч рублей в год чистой прибыли.

Путём простейших вычислений получаем срок окупаемости равный трём годам (мы поделили 150 000 на 50 000).

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

Пример 2:

К уже имеющимся условиям прибавим следующий факт. Во время осуществления проекта, каждый год будет тратиться порядка 20 тысяч рублей на всевозможные издержки. То есть мы уже можем получить значение ПЧсг — вычтя из 50 тысяч рублей (чистой прибыли за год) 20 тысяч рублей (расход за год).

А значит, формула у нас будет выглядеть следующим образом:

РР (срок окупаемости) = 150 000 (вложения) / 30 (среднегодовая чистая прибыль). Итог — 5 лет.

Пример показателен. Ведь стоило нам учесть среднегодовые издержки, как мы увидели, что срок окупаемости увеличился аж на два года (а это гораздо ближе к реальности).

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

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

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

Находим количество месяцев, которое потребуется для прихода к полноценной окупаемости.

Рассмотрим этот вариант дальше.

Пример 3.

Условия похожи. В проект нужно вложить 150 тысяч рублей. Планируется, что в течение первого года доход будет 30 тысяч рублей. В течение второго — 50 тысяч. В течение третьего — 40 тысяч рублей. А в четвёртом — 60 тысяч.

Вычисляем доход за три года — 30+50+40 = 120 тысяч рублей.

За 4 года сумму прибыли станет 180 тысяч рублей.

А учитывая, что вложили мы 150 тысяч, то видно, что срок окупаемости наступит где-то между третьим и четвёртым годом действия проекта. Но нам нужны подробности.

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

150 000 (вложения) — 120 000 (доход за 3 года) = 30 000 рублей.

Приступаем к третьему этапу. Нам потребуется найти дробную часть для четвёртого года. Покрыть остаётся 30 тысяч, а доход за этот год будет 60 тысяч. Значит мы 30 000 делим на 60 000 и получаем 0,5 (в годах).

Получатся, что с учётом неравномерного притока денег по периодам (но равномерного — по месяцам внутри периода), наши вложенные 150 тысяч рублей окупятся за три с половиной года (3 + 0,5 = 3,5).

Дисконтированный период: плюсы и недостатки

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

Дисконтирование даёт специалисту более гибкую оценку окупаемости и КПД инвестпроекта, благодаря чему можно приблизительно оценить все существующие риски. Если страна не может похвастаться стабильной экономико-налоговой и политситуацией, тогда применение этого показателя будет способствовать минимизации финрисков окупаемости.

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

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

Иногда дисконтированный возврат финансов оценивается как неудовлетворительный. В частности: окупаемость по DPP не включает объём и вектор финвложений по срокам. Результат расчётов совершенно не зависит от распределения финансового потока. Именно этот нюанс играет важную роль для специалистов, которые занимают оценкой эффективности компании.

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

Ключевые особенности

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

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

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

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

Дополнительные материалы на нашем сайте: «Закон о защите прав потребителей возврат товара».

Источник