Доходность портфеля формула excel

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

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

Загрузка исторических котировок

Первым делом необходимо получить данные, на основании которых, мы сможем провести наш анализ. Для примера, проведем анализ на основании бумаг 3 компаний: Сбербанк, Яндекс и ММК. Для загрузки котировок я использую сайт finam.ru, однако это не единственный ресурс, вы также можете воспользоваться ru.investing.com, moex.com, finam.ru и многими другими ресурсами.

Далее вы выбирайте интересующую вас бумагу, на рисунке 1 это акции Яндекса. Задаете интересующий вас исторический период в окошке “Интервал и периодичность”. Для корректного анализа лучше брать максимально возможный исторический период. Для примера, я взял период равный 1 году. И выбрал интервал равный 1 дню. Также меняем формат на .csv, для использования в Excel и выбираем формат записи, необходимый для анализа, в данном случае я взял DATE, TIME, OPEN, HIGH, LOW, CLOSE, VOL.

Повторяем данную операцию для Сбербанка и ММК. И таким образом получаем 3 файла формата .cvs. Теперь открываем файлы и переносим их в один Excel. LДалее удаляем столбцы TIME, OPEN, HIGH, LOW для каждой из компаний и получаем данные, соответствующие рисунку 2.

В нашей табличке ровно 251 значение по каждой из компаний. Внимание! Важно, чтобы для каждой рассматриваемой вами компании количество строк совпадало.

Следующий шаг – расчет доходности по бумаге. Для каждой из компаний, справа от колонки VOL создаем новую колонку доходность, как на рисунке 3. В ячейке D4 вводим формулу, рассчитывающую доходность бумаги за день:

=(B4-B3)/B3

В нашем случае получаем значение равное -0,7%. Далее протягиваем формулу вниз для каждой из строчек. И проделываем данную операцию для двух оставшихся компаний.

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

=СРЗНАЧ(D4:D253)*251

и получаем значение 7,66%. Формула означает, что мы берем среднее значение за рассмотренный период и умножаем на количество событий в этот отрезок времени, в нашем случае 251. Аналогичную операцию проводим для оставшихся компани 1.

Теперь, когда мы посчитали их годовые доходности, можно посмотреть на доходность портфеля за этот период. Для этого, определим, какой вес активы имеет в портфеле. Для примера я возьму веса: Яндекс – 40%, Сбербанк – 30%, ММК – 30%. Теперь посчитаем портфельную доходность, как на рисунке 5. Для этого мы используем следующую формулу:

=B259*C259+B260*C260+B261*C261

и получаем 39,62%. Формула означает, что мы доходность каждой из бумаг умножили на ее вес в нашем портфеле.

Источник

Как рассчитать доходность портфеля + Excel с готовыми формулами

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

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

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

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

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

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

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

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

Пример.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

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

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

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

Два вида доходности: TWR и MWR

Представим такую ситуацию. Мы инвестировали в рынок 100 долларов, которые за год выросли на 20%. Соответственно, у нас на счету оказалось $120. Довольные результатом, мы вносим теперь 1000 долларов. Однако рынок ведет себя иначе: на следующий год он падает на 5%. Следовательно, наша конечная сумма оказывается равна 1120 × (1 – 0.05) = $1064. А как подсчитать полученную доходность?

В этом случае есть два способа:

• TWR (Time Weighted Return / доходность, взвешенная по времени)
• MWR (Money Weighted Retutn / доходность, взвешенная по деньгам)

Разберемся с ними отдельно.

1. TWR

Считается более традиционной, хотя это можно оспорить. Для ее расчета инвестиционный процесс разбивается на интервалы, в которых происходят ввод или вывод средств, таким образом нивелируя их влияние на результат. В нашем случае инвестиции разбиты на два равных годовых отрезка (число лет =2):

Фактически TWR отражает доходность неизменной суммы инвестора с начала инвестиций без пополнений и снятий. Но насколько логичен этот результат у нас? На первом отрезке мы заработали $20, однако на втором потеряли 1064 – 1120 = $56. Т.е. фактически у нас убыток, вызванный многократным увеличением вклада при последующем снижении рынка, а TWR считает нам плюс, игнорируя абсолютное значение взноса.

2. MWR

Учесть этот недостаток TWR может доходность, взвешенная по деньгам. Она отражает движение денежных средств и может быть посчитана при помощи функции ВСД в Excel. Для этого нам нужны вносимые средства со знаком «минус» и конечный результат на счете. Для случая выше расчет следующий:

Итого, MWR = – √1 + (3.01%/100%) = –1.49%. Корень означает степень ½, где 2 — число лет инвестиций. Как видно, доходность по деньгам более адекватно описывает поведение нашего реального счета, хотя для ее вычисления мы прописываем только ввод средств и конечный результат. MWR и TWR встречаются в аналитике зарубежных брокеров и в сервисах расчета инвестиционных портфелей.

CAGR (Compound Annual Growth Rate) в данном случае включает в результат все вносимые инвестором средства (ежегодно $1000), не отделяя их от рыночной доходности – поэтому результат получается заметно лучше.

Итоги

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

Учет инвестиций с помощью ЧИСТВНДОХ

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

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

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

В2:B5 — диапазон ячеек со значениями введенных или выведенных средств

С2:С5 — диапазон ячеек с датами, когда происходил ввод или вывод

Умножение на 100 позволяет получить результат в процентах. Для примера выше средняя годовая доходность будет ≈ 22.1%.

🔔 При инвестициях меньше года подобный расчет является некорректным. Кроме того, поскольку на рынке периодически возникают пузыри (доткомов на рынке США в 1995-2000 годах или российских активов на росте нефти в 2000-2008), а также бывают затяжные кризисы (обычно именно после пузырей — например Великая Депрессия 30-х или Япония после 1990 года), то для более адекватной оценки портфеля гораздо лучше подходят временные интервалы в несколько лет, чем за 1-2 года.

В заключении пример доходности инвестиционного портфеля одного моего клиента:

Источник