Расчет доходности с учетом ввода 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 года.

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

Источник

Excel для финансиста

Поиск на сайте

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

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

Показатели инвестиционного проекта

Для ответа на вышеприведённые вопросы используют следующие показатели эффективности инвестиционного проекта:

• срок окупаемости проекта (обычно в месяцах)
• чистая приведённая стоимость (net present value, NPV)
• внутренняя норма доходности (IRR).

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

Недостаток этого показателя – игнорирование факта изменения стоимости денег во времени (дисконтирования). Дисконтирование — это приведение будущих денежных потоков к текущему периоду с учетом изменения стоимости денег с течением времени. Дисконтирование производится путём умножения значений будущих потоков на понижающий коэффициент:

Кд = 1 / (1 + Ставка дисконтирования)^Номер периода

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

• стоимостью привлекаемого капитала инвестора;
• прогнозной инфляцией;
• премией за риск проекта.

Коэффициент дисконтирования используется для расчёта показателя Чистая приведённая стоимость (net present value -NPV), который по сути является совокупным дисконтированным денежным потоком. Проект считается экономически выгодным, если его NPV не отрицательна. Нулевое значение NPV говорит о том, что проект принесет прибыль, достаточную для выплаты процентов по привлечённому капиталу с учётом инфляции. Чем выше NPV проекта, тем он привлекательнее (при учете рисков).

Для того чтобы получить более универсальную оценку привлекательности инвестиционного проекта, можно рассчитать третий показатель: внутреннюю норму доходности (IRR) – значение ставки дисконтирования, при которой NPV равен нулю (то есть проект отобъёт вложенные в него средства). Считается, что проект приемлем, если расчётное значение IRR больше ставки дисконтирования. Кроме того, этот показатель удобно использовать при сравнении альтернативных инвестиционных проектов: для каждого рассчитывается показатель IRR и предпочтение отдаётся проекту с наибольшим IRR.

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

Скачайте файл с примером pokazateli-investproekta, ознакомьтесь с заданием. Первый шаг инвестиционного планирования – составление прогноза денежных потоков.

Прогнозирование денежного потока в Excel

Заполните таблицу «Денежные потоки»:

• в ячейку В9 введите значение первоначальных инвестиций,
• в ячейку В10 — формулу «=B8-B9»
• в ячейку С8 введите сумму поступлений в первый год,
• в D8 – формулу «=C8*1,3»,
• в С9 — «=C8*0,8»,
• протяните формулу из ячейки D8 вправо до 2019 года, рассчитайте итоговое значение;
• протяните вправо формулы из ячеек С9 и В10,
• протяните формулу из ячейки G8 на две ячейки вниз.
• В ячейку В11 формулу «=B10», в ячейку С11 формулу =B11+C10, протяните ячейку С11 вправо до F11, сверьте значение в ячейке F11 cо значением в G10.

Теперь рассчитаны денежные потоки, в том числе нарастающим итогом.

Срок окупаемости в Excel: пример расчёта

Для расчёта срока окупаемости в примере Excel введите в ячейку В17 формулу «=СЧЁТЕСЛ�?(B11:G11;» —>

Источник

Считаем доходность инвестиций в портфеле + готовая 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 с уже готовыми формулами, перечисленными в статье. Подставляете свои данные. Считаете прибыль. Радуйтесь (или огорчайтесь) полученной доходности.

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

Источник