Расчет ставки доходности денежных потоков в Excel ЧИСТВНДОХ

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

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

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

Любые изменения в статьи доходов и расходов Вы можете или внести сами, или обратиться за помощью к эксперту по Excel.

Так выглядит таблица учета и анализа денежного потока: Скачать бесплатно

Расчет простой доходности облигации к погашению

2017-05-25_18-54-44

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

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

В данном примере разберём расчёт простой доходности облигации к погашению на примере одной из популярных государственных облигаций (ОФЗ) ОФЗ-26218-ПД. Расчёт других облигаций (муниципальных и корпоративных) ничем принципиально не отличается от расчёта доходности ОФЗ.

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

Для расчёта необходимо знать следующие данные:

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

Всю эту информацию можно найти на специализированных сайтах типа rusbonds.ru, cbonds.info, bonds.finam.ru и многих других.

Итак, расчёт простой доходности облигации к погашению ОФЗ-26218-ПД. Это облигация подходит для простого расчёта, так как есть постоянная доходность (размер купона не меняется) и нет амортизации (то есть номинал неизменен). Для случаев амортизируемых облигаций и переменных купонов лучше использовать другие способы, которые рассмотрим отдельно.

2017-05-25_18-27-52

В первых строках размещена общая информация об облигации. В ячейке С7 рассчитывается срок до погашения простой формулой «=C6-СЕГОДНЯ()«.

Обратите внимание, что пример сохранён 25.05.2017, в другой день все расчёты будут иными.

В строке 11 рассчитывается число купонов для погашения формулой «=ОКРУГЛВНИЗ(C7/365;0)*C10+1«: вычисляем количество полных лет до погашения, умножаем на количество купонов в год и добавляем 1 (так как последний купон выплачивается в момент погашения).

В строке 13 рассчитывается накопленный купонный доход (НКД) формулой «=(СЕГОДНЯ()-C12)/365*C9*C5«: он зависит от текущей даты, даты последней выплаты купона, купонной доходности и номинала.

С помощью рассчитанного НКД и цены, по которой облигация торгуется на рынке («чистой цены»), рассчитывается так называемая «грязная цена» — стоимость, которую необходимо заплатить за покупку облигации (без учёта брокерских комиссий). Это сумма инвестиций.

В строках 17-20 рассчитывается доход от инвестирования в облигацию ОФЗ, равный доходу от погашения облигации плюс купонный доход за всё время инвестирования.

В строке 22 рассчитана прибыль, равная разности дохода и сумме инвестиций. В строке 23 — она же в процентном виде. Видим, что за время инвестирования сумма инвестиций более чем удвоилась, но срок очень долгий (14 лет) и простая доходность к погашению составила всего лишь 7,35%.

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

Скачать пример расчёта простой доходности облигации ОФЗ к погашению : doh_obl_prost

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

доходность портфеля

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

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

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

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

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

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

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

1. TWR

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

TWR расчет

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

2. MWR

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

функция ВСД для MWR

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

Доходность по времени и по деньгам

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

Итоги

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

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

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

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

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

Читайте также:  Примеры формулы ГПР для выборки по строкам с условием в Excel

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

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

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

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

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

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

Рассмотрим пример расчета внутренней нормы доходности на примере с помощью Excel, разберем два способа построения с помощью функции и с помощью надстройки «Поиск решений».

Пример расчета IRR в Excel c помощью встроенной функции

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

Внутренняя норма доходности (E16) =ВСД(E6:E15)

Внутренняя норма доходности. Расчет в Excel

Внутренняя норма доходности. Расчет в Excel по встроенной формуле

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

Внутренняя норма доходности (IRR). Формула и пример расчета в Excel ★ Программа InvestRatio – расчет всех инвестиционных коэффициентов в Excel за 5 минут
(расчет коэффициентов Шарпа, Сортино, Трейнора, Калмара, Модильянки бета, VaR)
+ прогнозирование движения курса

Пример расчета IRR через надстройку «Поиск решений»

Второй вариант расчета подразумевает использование надстройки «Поиск решений» для поиска оптимального значения ставки дисконтирования для NPV=0. Для этого необходимо рассчитать чистый дисконтированный доход (NPV).

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

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

Чистый дисконтированный доход (NPV) =СУММ(F7:F15)-B6

На рисунке ниже показан первоначальный вид для расчета IRR. Можно заметить, что ставка дисконтирования, используемая для расчета NPV, ссылается на ячейку, в которой нет данных (она принимается равной 0).

Внутренняя норма рентабельности IRR. Расчет в Excel

Внутренняя норма доходности (IRR) и NPV. Расчет в Excel в помощью надстройки

Сейчас наша задача состоит в том, чтобы отыскать на основе оптимизации с помощью надстройки «Поиск решений», то значение ставки дисконтирования (IRR) при котором NPV проекта будет равен нулю. Для этого открываем в главном меню раздел «Данные» и в нем «Поиск решений».

Пример использования надстройки "Поиск решений" в Excel в инвестиционном анализе

При нажатии в появившемся окне заполняем строки «Установить целевую ячейку» – это формула расчета NPV, далее выбираем значение данной ячейки равной 0. Изменяемый параметр будет ячейка со значением внутренней нормы доходности (IRR). На рисунке ниже показан пример расчета с помощью надстройки «Поиск решений».

Внутренняя норма прибыльности в Excel

Поиск значения IRR для NPV=0

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

Внутренняя норма прибыльности в Excel. Пример расчета

Результат расчета внутренней нормы доходности (IRR)

Расчет ставки доходности денежных потоков в Excel ЧИСТВНДОХ

IRR

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

Для расчета, выполняем следующее:

  1. В выбранную ячейку вставляем формулу =ЧИСТНВДОХ(), нажимаем fx.

1

2

2. В поле аргумента «Значения» указываем диапазон, содержащий значения денежных потоков.

3

3. В поле аргумента «Даты» указываем диапазон, содержащий даты поступления потоков.

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

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)

Методы инвестиционного анализа, использующие дисконтированные денежные потоки

Следует заметить, что дисконтированный денежный поток (DCF) в своей формуле расчета сильно походит на чистый дисконтированный доход (NPV). Главное отличие заключается во включении первоначальных инвестиционных затрат в формулу NPV. Дисконтированный денежный поток (DCF) используется во многих методах оценки эффективности инвестиционных проектов. Из-за того, что данные методы используют дисконтирование денежных потоков, их называют динамическими.

  • Динамические методы оценки инвестиционных проектов
    • Чистый дисконтированный доход (NPV,NetPresentValue)
    • Внутренняя норма прибыли (IRR, Internal Rate of Return)
    • Индекс прибыльности (PI, Profitability index)
    • Эквивалент ежегодной ренты (NUS, Net Uniform Series)
    • Чистая норма доходности (NRR, Net Rate of Return)
    • Чистая будущая стоимость (NFV,NetFutureValue)
    • Дисконтированный срок окупаемости (DPP,DiscountedPayback Period)

    Более подробно узнать про методы расчета эффективности инвестиционных проектов вы можете в статье «6 методов оценки эффективности инвестиций в Excel. Пример расчета NPV, PP, DPP, IRR, ARR, PI». Помимо только дисконтирования денежных потоков существую более сложные методы, которые в дополнение учитывают реинвестирование денежных платежей.

    Финансовый анализ инвестиционного проекта. Расчет показателей NPV и IRR в Excel

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

    • ЧДД или чистый дисконтированный доход от инвестиционного проекта (NPV)
    • Внутренняя норма доходности (IRR)

    Рассмотрим эти два показателя подробнее и рассчитаем пример работы с ними в Excel.
    Net Present Value (NPV, чистый дисконтированный доход) – один из самых распространенных показателей эффективности инвестиционного проекта. Это разность между дисконтированными по времени поступлениями от проекта и инвестиционными затратами на него.

    Метод определения NPV:

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


    Где:
    CF – денежный поток;
    r – ставка дисконта.

    • Сравниваем текущую стоимость инвестиций (наши затраты) в проект (Io) с текущей стоимостью доходов (PV). Разница между ними будет чистый дисконтированный доход – NPV.

    NPV=PV-Io (1)
    NPV – показывает инвестору доход или убыток от вложений средств в проект по сравнению с доходом от хранения денег в банке. Если NPV больше 0, то инвестиции принесут больше дохода, нежели чем аналогичный вклад в банке.
    Формула 1 модифицируется если инвестиционные вложения в проект осуществляются в несколько этапов (периодов).

    Где:
    CF – денежный поток;
    I – сумма инвестиционных вложений в проект в t-ом периоде;
    r – ставка дисконтирования;
    n – количество периодов.

    Internal Rate of Return (Внутренняя норма доходности, IRR) – определяет ставку дисконтирования при которой инвестиции равны 0 (NPV=0), или другими словами затраты на проект равны его доходам.

    IRR = r, при которой NPV = f(r) = 0, находим из формулы:

    Где:
    CF – денежный поток;
    I – сумма инвестиционных вложений в проект в t-ом периоде;
    n – количество периодов.

    Этот показатель показывает норму доходности или возможные затраты при вложении денежных средств в проект (в процентах).

    Пример определения NPV в Excel

    В MS Excel 2010 для расчета NPV используется функция =ЧПС().
    Найдем чистый дисконтированный доход (NPV) проекта, требующего вложений инвестиций на 90 тыс. руб., и денежный поток которого распределен по времени рис 1. , и ставка дисконта равна 10%.

    Рассчитаем показатель NPV по формуле excel:
    =ЧПС(D3;C3;C4:C11)
    Где
    D3 – ставка дисконта
    C3 – вложения в 0 периоде (наши инвестиционные затраты в проект)
    C4:C11 – денежный поток проекта за 8 периодов

    В итоге показатель чистого дисконтированного дохода равен 51,07 >0, это говорит о том, что

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

    Доходность вложения в проект равна 38%.

    В завершение картинка финансового анализа проекта целиком.

    Расчёт эффективной доходности облигации к погашению

    2017-05-25_18-56-59

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

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

    Самый простой инструмент — функция ДОХОД, у неё 7 аргументов:

    • дата покупки облигации;
    • дата погашения;
    • процентная ставка купонных выплат в %;
    • текущая цена покупки («грязная» цена), приведённая к 100% от номинала (если облигация сейчас стоит 1078 руб. с учётом НКД, делим на 10, чтобы получилось 107,8);
    • цена погашения (обычно равна номиналу, если нет амортизации или оферты с особыми условиями);
    • базис — способ вычисления дня. В формуле использован фактический базис (один день равен календарному), подробнее о вариантах базиса смотрите в справке Excel.

    Скачайте файл с примером: doh_obl_eff. В ячейке С24 рассчитана доходность облигации ОФЗ-26218-ПД при покупке на дату 25.05.2018. Формула имеет вид: «=ДОХОД(СЕГОДНЯ();C6;C9;C15/10;100;2;1)»:

    2017-05-25_23-30-37

    (Исходные данные для расчёта можно взять на специализированных сайтах типа rusbonds.ru, cbonds.info, bonds.finam.ru и многих других).

    Как видим, эффективная доходность выше простой, как и ожидалось.

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

    В файле примера справа создана таблица финансовых потоков. Первая строчка в ней — операция покупки облигации по «грязной» цене, сумма обязательно должна быть отрицательным значением. Далее перечислены операции по выплате купонов, данные также скопированы с сайта rusbonds, за исключением уже выплаченных купонов. Если по облигации предусмотрена амортизация (частичное погашение номинала), то эти операции также нужно будет отразить в таблице. Самая последняя операция — погашение облигации. Ещё ниже рассчитана прибыль как сумма всех финансовых потоков (платежей и поступлений).

    В ячейке G37 рассчитана эффективная доходность облигации к погашению с помощью функции ЧИСТВНДОХ. Как видим, формула имеет очень простой вид: «=ЧИСТВНДОХ(G4:G34;F4:F34;8%)» В ней три аргумента: диапазон дат операций, суммы операций и третий необязательный аргумент — ориентировочное значение ответа, для облегчения работы алгоритма Excel.

    расчет эффективной доходности облигации двумя методами

    расчет эффективной доходности облигации двумя методами

    Скачать пример расчёта эффективной доходности облигации ОФЗ: doh_obl_eff

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

    Конец года. Пора считать доходность! Главное знать как.

    Еще

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

    Итак, простые кейсы.

    1. Сумма на начало года 1 миллион рублей. Сумма на конец года 1,12 миллиона рублей. Пополнений и снятий не было. Всё просто (1,12/1)-1=12% годовых заработал наш инвестор.
    2. Что если мы хотим посчитать среднегодовую доходность в диапазоне нескольких лет? Инвестор в начале 2007 года вложил 1 миллион рублей, в конце 2019 года у него на счету 3,5 миллиона рублей. (3,5/1)-1=250% за 13 лет. И тут инвестору может показаться, что среднегодовая доходность должна считаться так 250%/13=19,23%. Это не совсем корректно. Так как в этом случае высчитывается простой процент, а когда речь идет о промежутке более года, как правило, считают сложные проценты.

    Здесь на помощь инвестору придет метод CAGR (англ. Compound annual growth rate), если по-русски «совокупный годовой темп роста». Считается он просто, отношение конечного результата к начальному вложению (3,5/1=3,5) возводится в степень 1/n, где n – это количество периодов (2019-2007=12). Правильный ответ на этот кейс выглядит так: (3,5/1)^(1/12)-1=11,004% годовых.

    Всё можно посчитать в Excel или Google Sheets. Пример таблицы найдете тут.
    Конец года. Пора считать доходность! Главное знать как.

    Убедиться в правильности расчета можно обратным счетом. Например, 1 миллион +11,004%= 1 110 041 рублей + 11,004%=…. и так все периоды. Результатом будет 3,5 млн рублей.
    Конец года. Пора считать доходность! Главное знать как.

    Данный метод расчёта широко применяется при сравнении темпов роста в фундаментальном анализе, тут вы можете найти темпы роста:

    Теперь кейсы посложнее.

    3. Как посчитать годовую доходность, если внутри года были пополнения и даже снятия? В этом случае нам поможет метод расчета по средневзвешенным активам. Суть метода заключается в том, что необходимо посчитать какие средние активы (под активами здесь понимаются только внесенные средства без роста курсовой или дивидендной стоимости) были по году и уже от них считать доходность. Например, первые 100 дней у вас был 1 миллион на счете, потом вы внесли еще 0,5 млн и еще 100 дней у вас было 1,5 (1+0,5) млн, далее вы сняли 0,2 млн и оставшиеся 165 дней было 1,3 (1,5-0,2) млн, какие средние активы были у вас? ((1*100)+(1,5*100)+(1,3*165))/365= 1,273 млн ваших активов работало на счёте.
    Рассмотрим конкретный пример:
    Конец года. Пора считать доходность! Главное знать как.

    В столбце Е указаны длины периодов в днях, а в столбце F капитал (активы), которые были внесены и работали данный отрезок времени. Далее мы перемножаем все длины периодов на капитал в работе и полученную сумму делим на 365 дней (разница дней между 01.01.2019 и 01.01.2020). Получаем средние активы в работе по году – 1 139 205 рублей. (данную операцию можно сделать с помощью функции СУММПРОИЗВ)

    Далее нам нужно посчитать валовый доход. Валовый доход это ни что иное как разница между конечным результатом (общая сумма на счете на конец года 1 500 000 р) и суммой всех снятий и внесений на счет, также начальная сумма капитала является внесением. То есть, если сложить все внесения и снятия со счета (столбец С), то получим 244 000 рублей, а также у нас был начальный капитал в 1 000 000 рублей (ячейка D3), то расчет валовой доходности будет выглядеть так 1 500 000 – 244 000 – 1 000 000 = 256 000 рублей.

    Дальше всё просто, валовый доход делим на средние активы 256 000 / 1 139 205 = 22,47% годовых получил наш инвестор.

    Частозадаваемые вопросы по данному расчету:

    — что если у меня период не год, а меньше, например 234 дня? Ответ: В этом случае средние активы считались бы не через деление на 365, а через деление на 234. А годовая доходность считалась бы так «валовый доход/средние активы/234*365»

    — Мне на счёт приходили дивиденды и выплаты по купонам, как их учесть в расчете? Ответ: Если вы пришедшие дивиденды и купоны реинвестировали обратно в свой портфель, то можете никак их не учитывать, так как они учтены в конечной сумме средств на счету (в нашем случае 1 500 000 р). Если хотите убедиться в том, что это правильно, то можете отражать приход дивидендов как вывод и этой же датой эту же сумму как ввод. Получите тот же результат. Но помните, если вы забираете дивиденды с оборота (тратите их на свои нужды), то нужно отражать данную операцию как вывод средств.

    — Как мне посчитать дивидендную доходность портфеля? Ответ: Самый простой способ – поделить полученные дивиденды на сумму средних активов. Предположим в нашем варианте мы получили 85 000 рублей дивидендами. 85 000 / 1 139 205 = 7,46% годовых – дивидендная доходность нашего портфеля. Не обманывайте себя, считайте чистый дивидендный доход, то есть сумму дивидендов берите очищенную от налогов. У данного способа есть определенные недостатки, но он подойдет в 99% случаев для портфельного инвестора.

    — Как мне учесть транзакционные издержки в данном расчете (комиссии брокеру, депозитарные комиссии)? Ответ: Если вы берете конечную сумму (в нашем случае 1 500 000 рублей) к расчету, то вы уже очистили свой доход от транзакционных издержек, так как брокер их вычитает из суммы ваших средств. Если вы, наоборот, хотите посчитать долю своих транзакционных издержек, то вам необходимо сумму издержек поделить на средние активы. Допустим по нашему счету, мы потратили 1 159 рублей на комиссии. 1 159/1 139 205 = 0,102% средств ушло на комиссии.

    — А можно как-то проще посчитать? Этот расчет слишком сложен для меня! Ответ: Конечно, можете применить 4 вариант расчет, который описан ниже.
    4. Следующий вариант расчета подойдет тем, кто хочет упростить третий вариант, либо тем, кто хочет посчитать среднегодовую доходность за несколько лет, учитывая снятия и внесения средств.

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

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

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

    Внимательный читатель заметит, что доходность в 3 и 4 расчетах отличается на 0,09%. В функции есть ЧИСТВНДОХ есть некие математические нюансы, связанные с длиной срока и сложными процентами. Думаю, никому не принципиально 0,1-0,2%.

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

    Вот пример расчета за 5 лет.
    Конец года. Пора считать доходность! Главное знать как.

    Если у вас не было пополнений и снятий – для вас 1 и 2 вариант оптимален. Если вы считаете доходность за 1 год со снятиями и внесениями – 3 вариант ваш. Если вам нужно посчитать доходность со снятиями и внесениями за несколько лет – 4 вариант к вашим услугам.

    Методы учета доходности портфеля

    Еще

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

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

    Пример:
    1 ноября в портфеле было активов общей стоимостью 95 000 рублей.
    За месяц ничего не снимали и не пополняли.
    30 ноября в портфеле активы стоили 100 000 рублей.
    Доходность за ноябрь = (100 000 — 95 000) / 95 0000 * 100% = 5,3%

    1 декабря сумма активов в портфеле была 100 000 рублей.
    10 декабря вы довнесли 50 000 рублей.
    31 декабря в портфеле было 153 000 рублей.
    Доходность за декабрь = (153 000 — 100 000 — 50 000) / 100 000 * 100% = 3%, таким образом, все довнесения и снятия влияют только на доходность одного месяца.

    1 января сумма активов равна 153 000 рублей… и т.д.
    В конце года я просто суммирую все месячные доходности и получаю примерную картину динамики доходности за весь год.

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

    Главное помнить одно простое правило, все пополнения счета идут со знаком (-) минус, все выводы средств и конечный результат со знаком плюс.
    Пример функции выглядит так: =ЧИСТВНДОХ(диапазон сумм; диапазон дат).

    Методы учета доходности портфеля

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

    Всем успешных инвестиций!

    Следить за всеми моими обзорами можете здесь: Telegram, Смартлаб, Вконтакте

    Калькулятор доходности к погашению в EXCEL

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

    Калькулятор доходности к погашению облигации в EXCEL

    В примере использованы данные еврооблигации Московского кредитного банка со сроком погашения в 2024 году (тикер: CBOM-24).

    В EXCEL для этого существует довольно удобная функция XIRR (ЧИСТВНДОХ), которая позволяет быстро и просто считать доходность к погашению. Функция использует две колонки данных: колонка «Даты» и колонка «Денежный поток».

    Доходность к погашению (Yield to maturity, YTM) – это IRR (ВНД) денежного потока инвестора, покупающего облигацию. При этом предполагается, что облигация держится до погашения.

    Кроме доходности к погашению калькулятор считает:

    • Купонную доходность
    • Доходность при погашении (ценовая доходность)
    • Модифицированную доходность (сумма купонной доходности и ценовой доходностей)
    • Дюрацию
    • Модифицированную дюрацию

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

    Npv Формула Расчет Пример Excel Скачать

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

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

    Поэтому прибыльность данного проекта сомнительна. Графический метод расчета IRR в Excel. Значение IRR можно найти графическим способом, построив график зависимости чистой приведенной стоимости NPV от ставки дисконтирования.

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

    Решение для интерактивной (нажал-отредактировал) отправки электронных писем и печатных форм через почтовый клиент (Thunderbird, Outlook) находящийся на локальном компьютере, из конфигурации 1С, развернутой под удаленным рабочим столом (RDP, remote-app). Подходит также для локального развертывания 1С. Представлен пример быстрой интеграции с конфигурациями "Управление торговлей 10.3", "Управление производственным предприятием 1.3", "Комплексная автоматизация 1.1", "Бухгалтерия предприятия 2.0".

    1 стартмани

    21.09.2018 29064 22 stvorl 0

    Решение для интерактивной (нажал-отредактировал) отправки электронных писем и печатных форм через почтовый клиент (Thunderbird, Outlook) находящийся на локальном компьютере, из конфигурации 1С, развернутой под удаленным рабочим столом (RDP, remote-app). Подходит также для локального развертывания 1С. Представлен пример быстрой интеграции с конфигурациями "Управление торговлей 10.3", "Управление производственным предприятием 1.3", "Комплексная автоматизация 1.1", "Бухгалтерия предприятия 2.0".

    1 стартмани

    21.09.2018 29064 22 stvorl 0

    Расчет дисконтированной стоимости (PV) и чистой дисконтированной стоимости (NPV) в Excel.

    Оба понятия из заголовка этого раздела, дисконтированная (приведенная) стоимость, ПС (presentvalue, или PV), и чистая дисконтированная (приведенная) стоимость, ЧПС (netpresentvalue, или NPV), обозначают текущую стоимость ожидаемых в будущем денежных поступлений.

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

    Можно вычислить ценность инве­стиции путем дисконтирования денежных поступлений от нее с использованием альтернативной стоимости в качестве ставки дисконтирования.

    Формула расчета в Excel дисконтированной (приведенной) стоимости (PV) = ЧПС(C1;B5:B9)

    Приведенная стоимость (ПС) в объеме 379,08 долл. и есть текущая стоимость инвестиции.

    Предположим, что данная инвестиция продавалась бы за 400 долл. Очевидно, она не стоила бы запрашиваемой цены, поскольку — при условии альтернатив­ного дохода (учетной ставки) в размере 10% — реальная стоимость этого капи­таловложения составляла бы только 379,08 долл. Здесь как раз уместно ввести понятие чистой приведенной стоимости (ЧПС). Обозначая символом r учетную ставку для данной инвестиции, получаем следующую формулу NPV:


    Где СFt – денежное поступление от инвестиции в момент t; CF –поток средств (поступление) на текущий момент.

    Формула расчета в Excel чистой дисконтированной (приведенной) стоимости (NPV) = ЧПС(C1;B6:B10)+B5

    Терминология Excel, касающаяся дисконтируемых потоков денежных средств, несколько отличается от стандартной финансовой терминологии. В Excel сокращение МУР (ЧПС) обозначает приведенную стоимость (а не чи­стую приведенную стоимость) серии денежных поступлений.

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

Ссылка на основную публикацию