Расчет недооценки акций по интегральной методике. Пример в Excel-таблице

Теперь, после вычисления нормализованной прибыли по МНК, рассчитаем коэффициент недооценки акций эмитента К недо.

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

Формула коэффициента недооценки акций K недо:

• E norm – нормализованная чистая прибыль компании на текущую дату.

• P – текущая рыночная капитализация эмитента, рассчитанная суммарно по всем типам акций (обыкновенным и привилегированным).

• КБД10 – актуальная доходность 10-летних государственных облигаций (ОФЗ), взятая с графика Кривой бескупонной доходности.

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

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

Формула исторической премии за риск для конкретного года y:

• Премия y — надбавка за риск в проверяемом году y.

• E y — чистая прибыль компании за исторический год y.

• P y — рыночная капитализация компании в историческом году y.

• КБД 10,y — значение десятилетней доходности ОФЗ на дату закрытия годового отчета в историческом году y (взятое из архива на сайте Банка России или Мосбиржи).

Полученные за 7 лет значения Премия y суммируются и делятся на 7. Итоговая Премия за риск (уже как единая константа) подставляется в знаменатель нашей главной формулы для расчета коэффициента недооценки K недо.

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

В общих настройках Excel-модели жестко задается минимальный порог средней премии (в диапазоне от 2% до 5%). Стоит учитывать, что этот порог зависит от текущей доходности 10-летних ОФЗ и в норме стремится примерно к половине от этого значения. Чем выше безрисковая ставка на рынке, тем большую премию должен требовать инвестор за принятие на себя корпоративных рисков акций.

В нашем Excel документе на вкладке «Общие настройки» минимальная премия за риск принята равной 3%.

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

1. На вкладке «общие данные» добавляем исторические значения доходности десятилетних гособлигаций.

Найти эти данные можно в любом поисковике по запросу «график КБД». В выдаче открываем сайт московской биржи (moex.com). Листаем чуть ниже самого графика и находим таблицу параметров. 

Чтобы взять точные данные для каждого архивного года, вводим в календаре дату 31 декабря. Если торгов в этот день не было и данные отсутствуют, выбираем по порядку предыдущие даты — 30 или 29 декабря.

Например, для конца 2025 года значение доходности десятилетних облигаций составило 14.44%.

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

В таблице расчета эмитента добавлено две строки:

• «Рыночная капитализация, млрд ₽» — строка по годам (в блоке исторических данных), куда вручную вносятся архивные значения на конец каждого года. Здесь добавляем данные за семилетний период. Остальные годы, по которым нет годовых отчетов, должны остаться пустыми.

• «Текущая капитализация, млрд ₽» — ячейка, в которую вносится свежая рыночная капитализация компании на текущую дату отчета.

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

• Находим эмитента по названию, например, «Татнефть» или по тикеру «TATN», переходим во вкладку «Финансовая отчетность» и выбираем «Рыночные данные»

• Исторические значения за годы, по которым уже вышли закрытые годовые отчеты, вручную переносятся в Excel-таблицу.

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

Информация об интерфейсе платформы financemarker.ru актуальна на дату написания этого материала.

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

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

2.    Значение текущей капитализации должно быть актуальным, то есть пересчитанным в соответствии с последними биржевыми ценами акций.

Вставка формулы в документ Excel

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

Вспомним нашу главную формулу:

В расчетной модели заложим следующую логику вычисления:

КБД 10 — текущая доходность десятилетних облигаций по кривой бескупонной доходности. Заполняется отдельной ячейкой на вкладке Excel-документа «общие данные».

Enorm / P — отношение текущей нормализованной прибыли к текущей капитализации. В Excel-таблице расчета эмитента обозначено как «Enorm/P, Текущая доходность по прибыли» (на рисунке ниже).

💡 Справочно: Доходность по прибыли (англ. earnings yield) — доход, который получил бы акционер при распределении всей прибыли компании в виде дивидендов. Этот показатель позволяет инвестору напрямую сравнивать эффективность акций с доходностью низкорисковых защитных инструментов.

Премия за риск рассчитывается по каждому историческому году как Нормализованная прибыль / Рыночная капитализация, затем из полученного значения вычитается десятилетняя доходность КБД за этот же год. Затем вычисляется среднее арифметическое значение за все годы исторического периода. В Excel-таблице расчета эмитента эта ячейка называется «Историческая премия за риск, %» (на рисунке ниже).

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

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

Вывод

• Доходность по прибыли (Enorm/P) позволяет напрямую сопоставлять инвестиционную эффективность акций с доходностью низкорисковых защитных инструментов.

• Excel-модель дает быстрое сравнение акции компаний внутри одного сектора с помощью коэффициента недооценки K недо.

База: 

Выбор акций по коэффициенту недооценки с применением ИИ-запросов

Нормализация прибыли компаний по МНК в Excel

Критерии выбора акций, защита, ребалансировка – интегральный метод инвестирования

Инструмент: калькулятор ребалансировки портфеля