Доходы и расходы в Excel

Содержание

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

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

Личный бюджет доходов и расходов в Excel

В пакет программ Microsoft Office встроено несколько шаблонов для решения тех или иных задач. Откройте Excel – нажмите кнопку «Файл» — «Создать» — «Образцы шаблонов» — «Личный бюджет на месяц» — ОК.

Если по каким-то причинам у Вас нет данного шаблона или вы не смогли его найти можете .

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

Готовый шаблон семейного бюджета мы можем адаптировать к свои потребностям:

  • добавить/удалить статьи;
  • изменить параметры условного форматирования;
  • названия строк;
  • цвета заливки и т.д.

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



Таблица семейного бюджета в Excel

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

Чтобы детализировать поступления, на отдельном листе создаем таблицу Excel доходов и расходов семьи. Даем ей название.

Обозначаем графы: «Дата», «Статья», «Сумма». Внизу – «Итого». В эту ячейку вбиваем формулу для подсчета суммы.

Возвращаемся в сводную ведомость. Выделяем ячейку, над которой хотим вставить строку. Правая кнопка мыши – «Вставить» — «Строку» — ОК. Подписываем:

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

Переходим на лист с детальным отчетом. И нажимаем итоговую сумму поступлений за месяц. «Ввод»:

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

Детализация расходов бюджета

Деньги тратятся практически каждый день: покупаются продукты питания, топливо, проездные билеты. Чтобы оптимизировать ведение семейного бюджета, рекомендуется сразу же вносить расходы. Потратили – записали.

Для удобства создадим листы детализации по всем статьям расходов. На каждом – таблица Excel расходов семейного бюджета в деталях. «Дата» — «Статья расходов» — «Сумма». Достаточно сделать ее один раз. А потом копировать и вставлять.

Чтобы дать название листу детализации, щелкаем по его обозначению правой кнопкой мыши. «Переименовать».

Не забывайте записывать в строку «Итого» формулу суммы.

Теперь привяжем отчеты с расходами к сводной ведомости. Принцип присоединения тот же.

Старайтесь выделять именно ячейку с итоговой суммой!

Защита данных в ячейке от изменений

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

Как защитить ячейку от изменений в Excel:

  1. От ввода некорректных значений. Данные – проверка данных – параметры. Выбрать тип данных из всплывающего списка. ОК.
  2. Запретить изменение значений в ячейке. Рецензирование – защитить лист. В открывшейся вкладке ввести пароль, который позволит отключить защиту. Во всплывающем списке можно выбрать, что запрещаем, а что разрешаем. В примере мы оставили пользователю возможность выделять ячейки.
  3. Выборочно защитить ячейки. Семейным бюджетом могут пользоваться сразу несколько человек. Для каждого из них можно организовать доступ в определенную область листа. Рецензирование – Разрешить изменение диапазонов. Создать. Вводим имя диапазона, адреса ячеек (к которым будет доступ) и пароль доступа.

Чтобы защитить всю книгу, на вкладке «Рецензирование» нажмите соответствующую кнопку.

Работа с формулами в таблице личных финансов

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

В строке формул выделяем ссылку (относительную), которую необходимо зафиксировать (сделать абсолютной):

Нажимаем F4. Перед именем столбца и именем строки появляется знак $:

Повторное нажатие клавиши F4 приведет к такому виду ссылки: C$17 (смешанная абсолютная ссылка). Закреплена только строка. Столбец может перемещаться. Еще раз нажмем – $C17 (фиксируется столбец). Если ввести $C$17 (абсолютная ссылка) зафиксируются значения относительно строки и столбца.

Чтобы запомнить диапазон, выполняем те же действия: выделяем – F4.

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

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

Срок окупаемости инвестиций (англ. PP, payback period) – это минимальный период времени возврата вложенных средств в инвестиционный проект, бизнес или любую другую инвестицию. Срок окупаемости является ключевым показателем оценки инвестиционной привлекательности бизнес плана, проекта и любого другого объекта инвестирования. Рассмотрим различные показатели срока окупаемости используемые на практике:

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс
  • Срок окупаемости инвестиций (PP).
  • Дисконтированный срок окупаемости инвестиций (DPP).
  • Срок окупаемости инвестиций с учетом ликвидационной стоимости (BO PP).

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

srok-okupaemosti

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

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

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс
Оценка стоимости бизнеса Финансовый анализ по МСФО Финансовый анализ по РСБУ
Расчет NPV, IRR в Excel Оценка акций и облигаций

#1 Срок окупаемости инвестиций (PP). Формула

Срок окупаемости инвестиций. Формула расчета

где:

IC (Invest Capital) – первоначальные инвестиционные затраты в проекте;

CFi (Cash Flow) – денежный поток от проекта в i-й период времени, который представляет собой сумму чистой прибыли и амортизации.

Для расчета денежного потока необходимо воспользоваться следующими формулами:

Формула расчета денежного потока инвестиции

или

cf_2где:

А (Amortization) – амортизация, вид денежного потока, который не является затратами;

NP (Net Profit) – чистая прибыль инвестиционного проекта.

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

Cрок окупаемости инвестиций (PP). Пример расчета в Excel

Рассчитаем срок окупаемости инвестиций в проект с помощью программы Excel. Для этого необходимо определить первоначальные затраты, которые в нашем примере составили 100000 руб., далее необходимо спрогнозировать будущие денежные поступления (CF) и определить с какого периода сумма денежного потока превысит первоначальные инвестиционные затраты. На рисунке ниже показан расчет срока окупаемости проекта. Формула расчета денежного потока нарастающим итогом следующая:

Денежный поток нарастающим итогом (CF) =C6+D5

Срок окупаемости инвестиций (инвестиционного проекта), бизнеса. Пример расчета в Excel

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

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

Основные недостатки использования данного показателя в оценке инвестиций заключаются:

  • Отсутствие дисконтирования денежных потоков бизнес проекта.
  • Не рассматриваются денежные поступления за пределами срока окупаемости.
Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

#2 Дисконтированный срок окупаемости инвестиций (DPP). Формула расчета

Дисконтированный срок окупаемости (англ. DPP, Discounted Payback Period) – период возврата денежных средств с учетом временной стоимости денег (ставки дисконта). Главное отличие от простой формулы срока окупаемости – это дисконтирования денежных потоков и приведение будущих денежных поступлений к текущему времени.

Формула расчета дисконтированного срока окупаемости инвестиции, проекта и бизнеса

где:

DPP (Discounted Payback Period) – дисконтированный срок окупаемости инвестиций;

IC (Invest Capital) – первоначальные инвестиционные затраты в проекте;

CF (Cash Flow) – денежный поток, создаваемый инвестицией;

r – ставка дисконтирования;

n – срок реализации проекта.

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

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

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

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

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

Дисконтированный срок окупаемости инвестиций пример расчета в Excel

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

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

Мастер-класс: «Как рассчитать срок окупаемости для бизнес плана: инструкция”

#3 Срок окупаемости инвестиций с учетом ликвидационной стоимости

Срок окупаемости с учетом ликвидационной стоимости (англ. Bail-Out Payback Period) – представляет собой период возврата денежных средств с учетом остаточной стоимости активов, созданных в инвестиционном проекте. При осуществлении инвестиционного проекта могут создаваться активы, которые могут быть проданы (ликвидированы) в результате этого срок окупаемости проекта существенно сокращается.

pp_5
где:

IC (Invest Capital) – первоначальные инвестиционные затраты в проекте;

RV (Residual Value)­– ликвидационная стоимость активов проекта;

CFi (Cash Flow) – денежный поток от проекта в i-й период времени, который представляет собой сумму чистой прибыли и амортизации.

Ликвидационная стоимость может, как увеличиться в результате создания новых активов, так и уменьшаться за счет износа.

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

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

На рисунке ниже показан расчет периода окупаемости проекта с учетом ликвидационной стоимости. Формула в Excel достаточно простая и имеет вид:

Денежные поступления с ликвидационной стоимостью =C6+E5+D6

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

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

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

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

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

Резюме

Срок окупаемости является важнейшим показателем инвестиционного анализа проектов и бизнеса. Он позволяет определить целесообразность вложения в тот или иной проект. Использование дисконтирования денежных потоков и ликвидационной стоимости активов позволяет инвестору более точно оценить период возврата капитала. Помимо данного коэффициента необходим анализ через другие показатели эффективности: чистой приведенной стоимости (NPV), внутренней нормы доходности (IRR) индекса прибыльности (PI). Кроме точечной оценки необходим анализ динамики денежных потоков и их равномерность.

Задача 1.

Постановка задачи.

Рассматривается возможность приобретения облигаций трех типов, каждая из которых с номиналом в 100 руб. и сроком погашения 9.10.2007 г. Курсовая стоимость этих облигаций на дату 25.07.2005 г. составила соответственно 90, 80 и 85 руб.

Годовая процентная ставка по купонам (размер купонных выплат) составляет:

для первой облигации 8 % при полугодовой периодичности выплат;

для второй облигации – 5 % при ежеквартальной периодичности выплат;

для третьей облигации – 10 % с выплатой 1 раз в год.

Расчеты ведутся в базисе фактический/фактический.

Провести анализ эффективности вложений в покупку этих облигаций, если требуемая норма доходности составляет 15% .

Алгоритм решения задачи.

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

ДОХОД (дата_согл; дата_вступл_в_силу; ставка; цена; погашение; частота; базис)

Для решения задачи построим на листе Excel таблицу, в ячейки которой введем исходные данные и формулы расчета требуемых величин (рис. 4.27).

Выполним также расчет доходности, непосредственно задавая значения аргументов в функции ДОХОД.

Рис. 4.27. Применение функции ДОХОД для оценки доходности облигаций

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

Для облигации первого типа:

=ДОХОД (ДАТА(2005;7;25);ДАТА(2007;10;9);8%;90;100;2;1)= 13,36%

Для облигации второго типа:

=ДОХОД (ДАТА(2005;7;25);ДАТА(2007;10;9);5%;80;100;4;1)= 15,93%

Для облигации третьего типа:

=ДОХОД (ДАТА(2005;7;25);ДАТА(2007;10;9);10%;85;100;1;1)= 18,83%

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

Доходность по второй и третьей облигациям (15,93% и 18,83% соответственно) выше заданной нормы (15%), а по первой облигации (13,36%) – ниже. Следовательно, целесообразно покупать облигации второго и третьего типов.

Задача 2.

Постановка задачи.

Коммерческий банк предлагает свои сберегательные сертификаты номиналом 100 000 руб. сроком на 8 месяцев. Дата соглашения – 10.01.2005 г. Цена продажи составляет 85 000 руб. Способ вычисления дня – фактический/360. Необходимо определить доход за этот период.

Алгоритм решения задачи.

Для вычисления доходности данной финансовой операции, возвращающейся в виде годовой ставки, рассчитанной по простым процентам, используем функцию ИНОРМА, которая задается следующим образом:

ИНОРМА (дата_согл; дата_вступл_в_силу; инвестиция; погашение; базис)

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

Для проверки правильности результата в функцию ИНОРМА введем значения аргументов в непосредственном виде:

Инвестиционная модель в EXCEL с инвестиционным NPV-анализом

на предыдущую страницу — к разработке финансовых моделей в EXCEL

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

• ФИНМОДЕЛЬ ИНВЕСТПРОЕКТА ВЕРСИЯ 2.0 →
→ → →

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

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

ФИНМОДЕЛЬ С ИНВЕСТИЦИОННЫМ АНАЛИЗОМ БИЗНЕС-ПРОЕКТА

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

Для целей настоящей статьи под инвестициями или инвестиционными вложениями мы будем понимать направление капитала в виде денежных средств в производственную инфраструктуру с целью создания в будущем серийного производства нового продукта или расширения производства для создания бОльшего объема продукции, и через продажу которой предполагается возврат вложенных инвестиционных средств. Где в свою очередь в качестве производственной инфраструктуры будем рассматривать все составляющие цикла создания продукта потребления от идей, научных исследований и проектных работ до разработки, строительства, внедрения и запуска мощностей серийного производства и сбыта.
Как обычно, сразу выкладываем для скачивания соответствующую финансовую модель инвестиционного проекта (инвестмодель) в виде EXCEL-файла:

СКАЧАТЬ классическая базовая Инвестиционная Модель файл .xlsx
Внимание! Данная модель разработана в EXCEL-2013. В модели используются выпадающие списки, которые скорее всего не будут работать, если у Вас Excel-2007. Обратитесь к нам и мы поможем Вам настроить модель для корректной работы.

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

Для удобства ознакомления и в качестве примера данная инвестмодель уже наполнена конкретными данными исходных финансово-экономических показателей, которые пользователь вносит для расчета ключевых показателей эффективности инвестиционного проекта. Эта же модель, но только незаполненная, приведена в виде такого же EXCEL-файла для скачивания в конце раздела.
Прежде чем перейти к описанию финмодели инвестиционного проекта, для полноты содержания раздела «управление финансами» мы познакомим с основными широко известными и подробно представленными, например, в Интернете показателями эффективности инвестиционных проектов такими, как NPV, IRR, PP, PI, ROI и т.д., а также коснемся принципов достаточно нового современного подхода к управлению коммерческой структурой, который носит емкую аббревиатуру «EVA» — Economic Value Added; авторами концепции EVAявляются американцы Б.Стюарт и Д.Стерн.
Для любого инвестора при вложении денег в тот или иной проект, инвестиционный проект, естественно важно, чтобы, как минимум, произошел возврат инвестиционных вложений в полном объеме и более того, чтобы возврат этого самого «полного объема» был произведен, как в народе говорят, «с учетом стоимости денег» или, как говорят, специалисты «с учетом уровня потери покупательной способности денежных средств».
Учет покупательной способности денег в инвестиционном анализе происходит путем введения коэффициента дисконтирования, с помощью которого производится дисконтирование будущих финансовых потоков или, что тоже самое, приведение финансового потока будущих периодов к некоторому определенному моменту времени, например, к моменту начала инвестиционного проекта.
Чтобы понять, как работает дисконтирование разберемся сначала с процессом того, как происходит изменение покупательной способности денежных средств. Допустим, Вы ходите в магазин и покупаете систематически за 1000руб. один и тот же набор товаров, составляющих Вашу собственную «потребительскую корзину», т.е. Ваш средний чек на протяжении некоторого периода времени равен 1000руб. Далее пусть также по прошествии еще некоторого периода времени происходят изменения цен на товары Вашей потребительской корзины и Вам приходится покупать тот же набор товаров уже за 1100руб., т.е. на 10% дороже. Тогда если предположить, что у Вас нет 1100руб. на покупку полной корзины, а Вы все также располагаете ровно «той же» тысячью рублей, тогда Вы сможете купить лишь только некоторую долю, обозначим ее через D, от той продуктовой корзины, которую привыкли покупать до повышения цен. И эта доля, как легко посчитать, равна

D = 1 / (1 + 10%).

То есть покупательная способность денег относительно Вашей продуктовой корзины или Ваша личная покупательская способность снизилась на

10% / (1 + 10%) = 9,(09)%.

Получается, что инфляция в размере 10% «съедает» чуть более 9% Вашей продуктовой потребительской корзины. Или обобщая, получаем, что при Вашем финансовом бездействии в течение инфляционного периода инфляция в размере i процентов «съест»

i * D(i) * 100%

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

D(i) = 1 / (1 + i).

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

PV = ∑t=1,2,…,n CFt / (1 + d)t.

Здесь через PV (Present Value) обозначен приведенный к «настоящему» моменту (моменту начала инвестиционного проекта) объем финансового потока инвестпроекта. Через CFt обозначена величина финансового потока периода t в номинале, которая в свою очередь равна разнице между притоком CF+t и оттоком CF-t денежных средств (ДС) за период t при реализации проекта:

CFt = CF+t — CF-t, для каждого t=1,2,…,n.

Наконец через d мы обозначили ставку дисконтирования, выражаемую в процентах, и через n – количество базовых периодов, на которые разделен период реализации (рассмотрения реализации) нашего инвестиционного проекта.
Для простейшей оценки состоятельности или просто окупаемости проекта очевидно, что необходимо произвести сравнение полученной приведенной стоимости PV инвестпроекта с объемом инвестиционных вложений в него, также приведенных к начальной точке проекта – обозначим приведенный инвестиционный поток через PIV или, что тоже самое, необходимо сравнить разницу между PV и PIV с нулем. Эта разница между приведенным (дисконтированным) финансовым потоком проекта и соответствующим инвестиционным потоком обычно обозначается через NPV и называется чистой приведенной стоимостью проекта или чистым дисконтированным доходом проекта, и имеет свое выражение в виде следующей формулы:

NPV = PV — PIV = ∑ t=1,2,…,n CFt / (1 + d)t — ∑ t=1,2,…,n It / (1 + d)t,

где через It обозначен объем инвестиционных средств, которые предполагается вложить в проект за период t.
Таким образом, если NPV > 0, то проект окупается и, как минимум, его можно рассматривать.
На практике ставка дисконтирования может «плавать» год от года. Например, ЦБ может для макроэкономики страны устанавливать стратегическую цель, в соответствии с которой предполагается «борьба с инфляцией» и которая буквально формулируется так: «в течение следующих пяти лет мы планируем добиться снижения инфляции с нынешних 12% до 4% через пять лет, так чтобы в следующем году инфляция составила 10%, через два года 7% и далее в течение очередных трех лет снижение инфляции происходило ежегодно на один процентный пункт до целевого уровня в размере 4%». Стратегия? Да, конечно, стратегия и вполне себе определенная…
В таком случае, обладая, так сказать, сегодня таким знанием странно было бы не учитывать его (знание о стратегии государства) для планирования инвестиционных проектов будущего.
Тогда формула расчета NPV будет иметь следующий вид:

NPV = ∑ t=1,2,…,n CFt / Dt — ∑ t=1,2,…,n It / Dt,

где для каждого t=1,2,…,n через Dt обозначается произведение

Dt = (1 + d1) * (1 + d2) * … * (1 + dt),

где в свою очередь di — ставка дисконтирования i-того периода.
Приведем пару примеров. Пусть «на руках» имеются 10 млн. руб., которые их владелец планирует сохранить для своего однолетнего ребенка для его обучения (поступления, оплаты обучения, проживания и т.п.) в ВУЗе через 15 лет. Допустим в первом случае он (владелец) кладет эти деньги в банк под 9% годовых, а во втором случае – покупает квартиру в Москве для сдачи ее в аренду, денежные средства от которой он также кладет в банк под 9% годовых, постепенно накапливая депозит. Также будем предполагать, что согласно условиям депозитного договора и в том и другом случае период капитализации депозита составляет один год, причем во втором случае владелец собирает в течение года денежные средства, поступающие от сдачи в аренду квартиры и потом общую сумму кладет на депозит в начале очередного года.
Для начала будем считать, что финансовые условия в течение 15 лет не меняются и общая официальная инфляция составляет 10%.
Тогда в первом случае на конец 15-ого года владелец 10 млн. руб. в номинале получит сумму из банка в размере:

10млн.руб. * (1 + 9%)15 = 36,4млн.руб.

Если в качестве коэффициента дисконтирования рассмотреть инфляцию в размере 10% и привести к начальному моменту, полученные через 15 лет 36,4 миллиона, то получим:

36,4млн.руб. / (1 + 10%)15 = 8,7млн.руб.

То есть с точки зрения покупательной способности денежных средств потери составят разницу между 10-тью изначальными миллионами рублей и приведенными к начальному моменту 8,7 млн.руб., равную 1,3млн.руб.
Теперь возвращаемся к сформулированной выше цели вложения денежных средств на 15 лет – сохранение денег для обучения ребенка в ВУЗе. Это значит, что такое вложение является целевым, под покупку конкретного продукта (образовательных услуг) через 15 лет, и поэтому не совсем корректным является применение общего показателя инфляции в качестве ставки дисконтирования. Следовательно необходимо, как минимум, изучить и спрогнозировать средний уровень инфляции в сфере предоставления услуг обучения ВУЗами, и если он окажется ниже 9% (ставки банковского депозита), то вложение очевидно является состоятельным по отношению к выбранной цели.
Причем имеет смысл рассмотреть в том числе и вариант, когда инфляция услуг обучения, которая в нашем случае берется за коэффициент дисконтирования, ниже годовой ставки банковского депозита. В этом случае родитель ребенка получает доход, что немаловажно. Если, например, стоимость услуг обучения в ВУЗе растет год от года в среднем на 7%, то «родитель-инвестор» может рассчитывать на доход, размер которого с точки зрения его приведенной стоимости составит:

(36,4млн.руб. — 10млн.руб. * (1 + 7%)15) / (1 + 10%)15 =
= (36,4млн.руб. – 27,6млн.руб.) / (1 + 10%)15 =
= 8,8млн.руб. / (1 + 10%)15 = 2,1млн.руб.

Здесь мы естественно для дисконтирования применяем общий показатель инфляции (10%), поскольку не знаем на что родитель-инвестор собирается потратить свой дополнительный доход. Заметим, что воспользоваться рассчитанной приведенной суммой в размере 2,1 миллиона в полном объеме наш заботливый инвестор сможет только через 15 лет – как видно из расчетов, тогда это уже будет сумма в номинале в размере 8,8 млн.руб.
Если же наш владелец начального капитала пожелает каждый год выводить свой дополнительный доход при начислении процентов и не оставлять далее на депозите эти 2%, например, будет тратить на свою текущую жизнь, то в таком случае его приведенный к начальному моменту времени дополнительный доход составит 2,3 млн.руб. – предлагаем читателю самому произвести необходимые расчеты, чтобы убедиться в этом. То есть получается, что при таком подходе родитель получит больший доход (с точки зрения стоимости денег) и при этом еще и сможет им воспользоваться в течение 15-ти лет, а не после их прошествия, что на первый взгляд кажется странным и логически неверным, но именно расчеты показывают верность такого заключения.
Теперь обратимся ко второму из рассмотренных вариантов сохранения денежных средств – к инвестпроекту вложения капитала в квартиру с целью ее сдачи в аренду. Допустим за 10 млн. руб. покупается «двушка» в Москве в районе, где ее можно сдавать ежемесячно за 50 тыс. руб., и денежные средства, получаемые в качестве оплаты услуг аренды, накапливаются в течение года и кладутся на депозит со ставкой 9% и с такими же прочими условиями по депозиту, как и выше.
При условии, что купленная квартира будет продана через 15-ть лет, получаем следующую формулу для номинального дохода нашего второго «инвестпроекта»:

I * (1+i1)n + RY * ∑ t=0,1,…,n-1 (1+i2)t * (1+r)n-t-1.

В этой формуле через I обозначен объем начальных инвестиций, равный 10 млн.руб., через i1 и i2обозначены прогнозные уровни инфляции для стоимости квартир и стоимости услуг аренды соответственно в рассматриваемом регионе, через RY обозначен годовой доход от сдачи квартиры, в нашем случае он равен 600 тысяч рублей (50т.р./мес. умножить на 12 месяцев), наконец, через r обозначена годовая ставка по депозиту, равная в нашем случае 9%, и n – период инвестиционного проекта в количестве лет, составляющий 15 лет согласно наших предпосылок.
Теперь если предположить, что в среднем ежегодный прирост стоимости купленной квартиры составит i1=3%, и уровень прироста ставки ее аренды также будет i2=3%, то не сложно посчитать по приведенной формуле, что в конце 15-ого года объем денег на руках инвестора составит ровно те же 36,4 млн. руб. – столько же сколько при вложении начальных 10-ти миллионов на депозит под 9% годовых, см. расчет выше.
Встроив, например, в EXCEL-файл все приведенные здесь формулы можно получить удобный финансовый калькулятор, с помощью которого легко будут просчитываться и сравниваться между собою результаты возможных вариантов вложений денежных средств и пользователь сможет принимать соответствующие решения.
Итак, уже на этом этапе рассмотрения основ NPV-анализа понятно, что вопрос об уровне ставки дисконтирования не такой уж и тривиальный.
В отношении ставки дисконтирования важно то, чтобы «потребности инвестора совпадали с его возможностями», поскольку ставку дисконтирования еще определяют, как уровень средней ежегодной доходности альтернативного инвестиционного проекта или альтернативного вложения капитала. Инвестор конечно же может для себя определять/рассчитывать ставку дисконтирования по какой-либо из известных классических формул, общая суть которых сводится к следующей (формуле):

d = i + r + σ.

Которая «читается» так: ставка дисконтирования равна уровню инфляции i плюс «желаемый» уровень чистой (с учетом инфляции) доходности вложенного капитала r плюс премия за риск σ. И, если опять же обыденно смотреть на эти вещи, то означает это примерно то, что инвестор не желает потерять покупательную способность инвестированных денежных средств в связи с инфляцией i, т.е. необходимо сохранить капитал, и при этом желает получить чистый, очищенный от инфляции доход r, другими словами, успешная реализация инвестиционного проекта должна привести к приросту капитала с точки зрения его реальной рыночной ликвидности, ну и третье слагаемое σ в этой формуле означает, что инвестор не прочь получить еще и «небольшой» бонус или сверхдоход за то, что «отважился на вход» в проект с определенной долей риска, который оценивается в размере σ.
Вполне себе нормальное желание, если не брать в расчет того факта, что с такой «желаемой» доходностью у инвесторов чаще всего нет альтернативных инвестпроектов, поэтому в результате ставку дисконтирования просто-напросто приводят к уровню ставки банковского депозита, усредненной по, например, первой десятке банков и от этого отталкиваются.
Если рассмотреть чистый дисконтированный доход проекта, как функцию от ставки дисконтирования NPV(d)и решить уравнение

NPV(d) = 0,

т.е. найти такой уровень ставки дисконтирования d, при котором приведенный финансовый поток проекта сравняется с приведенными инвестиционными вложениями в него. То мы получим крайне важный показатель, характеризующий эффективность инвестиционного проекта, а именно внутреннюю норму доходностиинвестпроекта, обозначаемую через IRR — Internal Rate of Return, и, как мы уже сказали выше, получаемую из уравнения:

NPV(IRR) = 0.

В общем случае данное уравнение согласно хорошо известной теореме Абеля из курса алгебры, которая говорит о том, что уравнения, начиная с пятой степени, не имеют представления корней в радикалах, нельзя решить так, чтобы прописать единую формулу, например, для расчета IRR в EXCEL в рамках финансовой модели инвестиционного проекта. Поэтому обычно используется итерационный метод дихотомиинахождения приближенного значения внутренней нормы доходности IRR проекта.
Например, в приведенной нами в начале этой страницы финансовой модели инвестиционного проекта в EXCEL внутренняя норма доходности IRR инвестпроекта рассчитывается во вкладке «NPV» в строках с 61ой по 160тую путем применения метода дихотомии с 50-тью итерациями.
Суть метода дихотомии нахождения внутренней нормы доходности IRR состоит в том, чтобы заключить ставку IRR между двумя такими аргументами d0 и d1, что выполняются одновременно два неравенства:

NPV(d0) > 0 и NPV(d1) < 0.

После чего отрезок (d0,d1) делится пополам – обозначим соответствующий аргумент через d2, и в случае если NPV(d2)>0, то в качестве отрезка (d0,d1) при следующей итерации рассматривается отрезок (d2,d1) или если NPV(d2)<0, то в качестве отрезка (d0,d1) при следующей итерации рассматривается отрезок (d0,d2). И так далее, проведя достаточное количество подобных итераций, мы можем получить приближение к значению внутренней нормы доходности IRR с любой наперед заданной точностью.
В случае если внутренняя норма доходности IRR инвестиционного проекта оказалась выше рассматриваемой инвестором, «комфортной» для него ставки дисконтирования dI, т.е.

IRR > dI,

то считается, что проект имеет смысл рассматривать.
Или если анализируются одновременно несколько возможностей инвестирования в различные проекты, то при прочих равных возможностях предпочтение отдается тому инвестпроекту, у которого внутренняя норма доходности выше или, что тоже самое, тому проекту, который выдерживает больший уровень дисконтирования.
В приведенных выше двух примерах при вложении 10-ти миллионов рублей родителем-инвестором предполагалось, что в конце периода инвестирования денежные средства в полном объеме выводились «на руки» либо с банковского депозита, либо при продаже квартиры через 15 лет, так сказать, инвестиционный проект имел некое завершение для инвестора. Но, вообще говоря, можно рассматривать вложения в инвестиционные проекты, при которых не предполагается окончания действия проекта, т.е. проект можно рассматривать, как бесконечно действующий с точки зрения жизни инвестора и в том числе его наследников, что означает предположение о том, что активы в которые вложены инвестиционные денежные средства или соответствующий капитал будут неограниченно долго генерировать постоянный, например, ежегодный доход.
В случае с банковским депозитом этот доход будет ежегодно составлять 900 тыс. руб., при условии что не происходит капитализации по депозиту, если, например, вкладчик выводит проценты на текущие нужды. В случае со сдачей квартиры в аренду ежегодный доход составит 600 тыс. руб., умноженные на инфляционный коэффициент, в нашем случае это 1+i2, где i2 – инфляция арендных ставок на рынке сдачи жилья в рассматриваемом регионе.
Мы же рассмотрим в этой связи вложения в акции на фондовом рынке с целью систематического получения дивидендов, т.е. без цели их перепродажи по более высокой цене. Пусть дивиденды по купленным акциям какого-либо эмитента составляют p рублей, пусть также ежегодно прогнозируется увеличение объема дивидендов по данному эмитенту на i процентов, тогда приведенный финансовый поток при таком инвестиционном вложении будет иметь следующий вид:

∑ t=0,1,…,∞ p * ( 1 + i )t / ( 1 + d )t,

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

q = ( 1 + i ) / ( 1 + d ).

Из математики хорошо известно, что такая сумма имеет конечное значение, если знаменатель qположителен и меньше единицы, т.е. в нашем случае если ставка дисконтирования d больше ожидаемого среднегодового прироста размера дивидендов i.
Предположим, что именно так и есть в рассматриваемом нами случае: 0 < q < 1 или i < d.
Посчитаем чему равна указанная сумма или чему равен в этом примере приведенный финансовый поток неограниченных по сроку дивидендных выплат. Для этого сначала посчитаем чему равна сумма первых n+1членов геометрической прогрессии

Sn = ∑ t=0,1,…,n p * qt.

Имеет место система двух уравнений:

Sn+1 — Sn = p * qn+1,
q * Sn — Sn+1 = -p.

Из первого уравнения получаем

Sn+1 = Sn + p * qn+1.

Подставляем полученное выражение для Sn+1 во второе уравнение и получаем искомую сумму первых n+1членов нашей геометрической прогрессии:

Sn = p * ( qn+1 — 1 ) / ( q – 1 )

или, как чаще она (формула) выглядит:

Sn = p * ( 1 — qn+1 ) / ( 1 — q ).

Теперь устремляя n в бесконечность получаем приведенный финансовый поток PV дивидендных выплат по акциям нашего эмитента:

PV = p / ( 1 — q ),

куда вместо q подставляем выражение ( 1 + i ) / ( 1 + d ) и имеем окончательную формулу приведенного финансового потока для нашего примера:

PV = p * ( 1+ d ) / ( d — i ).

В заключение предположим, что родитель-инвестор из нашего второго примера не собирается продавать через 15-ть лет квартиру, купленную за 10-ть миллионов, да и вообще никогда не собирается продавать, а просто планирует получать арендные платежи с квартиросъемщиков и тратить их на текущую жизнь, т.е. по аналогии с вложениями в акции в качестве дивидендного дохода p он имеет 600 тысяч рублей. Тогда если общая инфляция d, как было рассмотрено выше и которая считалась в качестве ставки дисконтирования, составляет 10%, а инфляция по арендным платежам i предполагается равной 3%, то наш родитель получит от вложения в квартиру сумму равную

PV = p * ( 1+ d ) / ( d — i ) = 600 тыс. руб. * ( 1 + 10% ) / ( 10% — 3% ) = 9 429 тыс. руб.

То есть прогнозный приведенный доход будет вполне себе сравним с изначально вложенными 10-тью миллионами рублей.
Аналогично каждому инвестору приходится рано или поздно принимать решение о том, чтобы остаться в инвестпроекте и получать бессрочный, рассчитываемый по подобным формулам, приведенный дисконтированный доход на вложенный инвестиционный капитал, или выйти из него взяв свою долю здесь и сейчас по текущей рыночной цене.
Вообще говоря, финансовая и инвестиционная математика – дело заманчивое и интересное. Но, как мы видим из примеров этого раздела, помимо всего прочего еще и важное. Приведем еще один простой пример наглядно иллюстрирующий место высшей математики в сфере принятия инвестиционных решений.
Как обычно, сформулируем начальные предпосылки для очередного нашего примера. Допустим Вы решили сажать картошку в Подмосковье, собирать урожай и потом его продавать. Для этого Вам предположим необходимо купить лопату и землю, т.е. произвести инвестиционные вложения во внеоборотные активы для производства картофеля.
Пусть также при выборе лопаты Вам предлагают два экземпляра за одну и ту же цену, отличающиеся между собой только длиной черенка. Тогда естественно Вы выбрать должны более длинный экземпляр при прочих равных условиях. Как в этом случае принять решение без прибегания к математическим расчетам? Очевидно, необходимо приложить обе лопаты к общему началу отсчета кончиками черенков и визуально сравнить их длины, и соответственно принять решение о покупке той лопаты, которая оказалась на Ваш взгляд в буквальном смысле длиннее. Итак, одно инвестиционное решение принято.
Теперь предположим, что Вам за одну и ту же цену предлагают два участка земли, один из которых на юге Подмосковья, а другой – на севере. Понятно, что в этом случае приложить их друг к другу, чтобы увидеть какой больше, а какой меньше, не получится, да и предположим еще, что «на глаз с вертолета» их тоже невозможно оценить, поскольку их границы не квадратные или прямоугольные, а какие-то разнообразно кривые. Естественно для принятия решения без математики здесь уже не обойтись – для принятия решения о покупке, о втором принципиальном инвестиционном вложении средств, Вам необходимо рассчитать площадь каждого участка, т.е. поставить в соответствие каждому объекту вложения некоторое число, а именно число гектаров земли, после чего сравнить эти два числа на предмет больше-меньше.
Если Вы сами не владеете методами расчета площади земли, то Вам придется нанять специального оценщика, который рассчитает площади каждого из участков. Отметим здесь, что за расчет площадей в математике отвечают интегралы, ну а на практике, например, метод триангуляции.
Отлично! Оценщик посчитал и «принес Вам на блюдечке» две цифры, Вы заплатили за участок, который имеет бОльшую площадь, и Ваш проект стартанул.
Здесь-то и возникает главный вопрос: математика-то математикой, а вдруг оценщик ошибся в расчетах или хуже того по каким-либо причинам оказался недобросовестным и специально произвел расчеты так, что по факту меньший участок земли получил в соответствие при расчетах оценщика большую площадь, и Вы в результате приняли неверное решение, а это значит, что, как минимум, Вам придется дольше «отбивать» свои инвестиционные вложения.
Конечно же математика важна, но вопрос доверия при инвестировании не менее важен. Жизнь знает огромное количество самых разных примеров: и когда инвестиционные расчеты заведомо были неверными, а инвестор принимал решение в пользу таких проектов, доверяя представившим эти расчеты инвестиционным аналитикам, после чего «вылетал в трубу», и когда расчеты были верными, а люди представлявшие такие расчеты и желающие запустить свой «супермега» проект были профессионалами своего дела и кристально честными, но инвестор отказывался от вложений, поскольку по причинам известным только ему самому он не доверял этим людям и, тем самым, упускал прекрасную возможность хорошо заработать.
Никакая премия за риск в рамках расчетов ставки дисконтирования не защитит Вас от лопнувшего недобросовестного банка, в который Вы положите деньги в сумме, большей, чем максимальный размер застрахованного вклада по системе страхования вкладов, или от сгоревшей квартиры, которую Вы сдадите недобросовестному квартиросъемщику, или от недобросовестных топ-менеджеров инвестиционного проекта, куда Вы вложили Ваши кровные, откуда, упомянутые топ-менеджеры выведут в оффшоры оборотные средства предприятия, а точнее просто своруют их.
Вопросы доверия находятся в области, так называемых, неявных факторов, изучением нивелирования негативного влияния которых занимается теория управления рисками. И если кратко, то основной вывод этой теории состоит в том, что главным спасением от этих самых неявных факторов или главным спасением от реализации рисков негативного воздействия является выстраивание такой системы принятия решений, при которой своевременно принимаются компетентные и правильные решения, короче говоря, необходимо «держать нос по ветру», а «ухо востро».
Данная тема, равно как управление рисками, так и формирование эффективной системы принятия решений, будет рассмотрена нами подробно в других одноименных разделах сайта, а сейчас мы переходим к изложению методологии финансового моделирования инвестиционного проекта или просто – методологии инвестиционного моделирования, на примере финмодели, EXCEL-файл с которой был выложен для скачивания в начале настоящей статьи и который можно также .
Легенда инвестиционного проекта, моделирование которого представлено в EXCEL-файле, заключается в следующем. Инвестиционные средства вкладываются в создание производства некоторых товаров, в проекте они названы готовой продукцией, прибыль от продажи которых направляется на погашение задолженности производственного предприятия перед инвесторами. Срок инвестирования рассматривается в размере 10-ти лет, расчеты финмодели производятся в ежемесячной динамике, начало периода пользователь может задавать сам из выпадающего списка первых чисел различных годов.
С точки зрения специфики объектов капитальных затрат финмодель различает только то, что на инвестиционные средства приобретается производственная инфраструктура, без уточнения способа приобретения – будь то строительство с нуля или покупка готовых производственных мощностей (зданий, сооружений, оборудования и т.п.), включающая в себя склад сырья и материалов и склад готовой продукции, а также площади для персонала, в том числе офисные для управленческого персонала, т.е. аренды офисов не предполагается, также как не предполагается капитальных вложений в торговую и логистическую инфраструктуру – нет своих магазинов и транспортных средств – логистика на аутсорсинге.
Финмодель позволяет задавать уровень максимальной выработки готовой продукции в месяц, при этом план продаж в количестве готовой продукции задается независимо от максимальной выработки производства. Поэтому в инвестиционной модели предусмотрена возможность расширения или масштабирования производства путем капитальных вложений в дополнительные производственные модули, подобные изначальному, источником финансирования которых также являются инвестиции. То есть предполагается, что если в какой-либо будущий период для выполнения плана продаж необходимо производство продукции в объеме превосходящем максимальную выработку одного производственного модуля, то финмодель автоматически рассчитывает инвестиционные капитальные затраты на создание дополнительных производственных мощностей, тем самым, формируется инвестиционный поток модели.
EXCEL-файл с инвестиционной моделью состоит из 10-ти вкладок, в первой из которых, во вкладке «оглавление» представлен список с указанием содержания оставшихся 9-ти вкладок и гиперссылками на них (в файле финмодели предусмотрена возможность перемещения между вкладками посредством гиперссылок через оглавление), кратко это выглядит так:

content_investmodel.jpg

В листе «условия» пользователь финмодели задает основные исходные условия для моделирования инвестиционного проекта. Начинается все с задания даты старта проекта в ячейке M11 из выпадающего списка дат (набор дат этого списка можно поменять во вкладке «структура», в столбце D). После чего в строках 7, 8 и 9 происходит автоматическая разбивка инвестиционного периода на 10-ть последующих лет.
В строке 13 «условий» задаются общие ежегодные объемы капитальных затрат в тысячах рублей на инфраструктуру одного производственного модуля, с которого проект начинает операционное функционирование.
В строках с 15-той по 48-ую производится ежемесячная разбивка общих сумм капитальных затрат из 13-той строки. Здесь предусмотрены следующие три способа разбивки, выбор которых осуществляется через выпадающий список ячейки M11 со следующими значениями (названия которых можно поменять во вкладке «структура» в столбце G):
— равномерно по году;
— равномерно с…по…;
— вручную.
При выборе способа «равномерно по году» в строках с 35-той по 46-ую общие суммы из 13-той строки просто делятся на 12 для каждого месяца соответствующего года. При выборе способа «равномерно с…по…» в строках 17 и 18 необходимо выбрать из списка номера месяцев начала и окончания капитальных вложений данного года, после чего финмодель автоматически распределит равномерно по выбранным месяцам общие объемы капитальных вложений в блоке строк с 35-ой по 46-ую. Наконец при выборе способа «вручную» Вам будет предложено в строках с 21-ой по 32-ую просто задать процентное распределение по месяцам года общих сумм капзатрат.
В строке 48 задается номер месяца ввода в эксплуатацию объектов капитальных затрат, причем если этот номер не совпадает с номером месяца последних ненулевых ежемесячных объемов капзатрат, то ввод в эксплуатацию будет рассчитан в два этапа: сначала будет введен в эксплуатацию весь объем до указанного месяца, включая его, потом в последний месяц ненулевых объемов будет введен в эксплуатацию оставшийся объем внеоборотных активов.
В ячейке Q50 задается количество лет амортизации производственных объектов.
В строках с 52-ой по 56-тую задаются ограничения на производственные и складские мощности одного производственного модуля: максимальный ежемесячный объем выпуска готовой продукции задается в ячейке Q52, объем собственного склада готовой продукции задается в ячейке Q54, как процент от максимальной ежемесячной выработки, объем собственного склада сырья и материалов – в ячейке Q56 (в удельном пересчете на единицы готовой продукции).
В блоке строк с 58-ой по 77-ую задается план продаж в количестве единиц готовой продукции, как произведение количества потенциальных клиентов/покупателей в регионе продаж (строка 58), прогнозной целевой доли рынка сбыта, которую планирует занять предприятие в регионе, (строка 60) и среднее количество единиц продукции, которое будет покупать клиент, (строка 62).
Операционный цикл нашего предприятия состоит из производственного цикла и торгового цикла, первый из которых начинается покупкой сырья и материалов и заканчивается выпуском готовой продукции и размещением ее на собственный склад. Торговый цикл начинается с размещения готовой продукции на склад предприятия и заканчивается ее продажей клиентам. Соответствующие периоды оборачиваемости или длины указанных циклов задаются в строках 79 и 81.
Такие показатели, как бюджет закупки сырья и материалов, бюджет производства готовой продукции, остатки на конец периода запасов сырья и материалов, а также готовой продукции, рассчитываются в рассматриваемой финансовой модели с точки зрения классического подхода, через коэффициенты оборачиваемости, отталкиваясь от бюджета продаж.
Например, для того чтобы рассчитать план производства готовой продукции за период мы используем формулу:

ГП = ГП1 — ГП0 + S,

где ГП1 и ГП0 – соответственно остатки готовой продукции на конец и начало периода, S – продажи готовой продукции за период.
При этом расчет объема готовой продукции ГП1 на конец периода производится по такой формуле (можно посмотреть в любом учебнике):

ГП1 = — ГП0,

которая вытекает из классического определения коэффициента и периода оборачиваемости запасов готовой продукции, где Поб(ГП) – заданный в строке 79 вкладки «условия» период оборачиваемости готовой продукции.
Естественно задавая объем запасов ГП0 для первого периода нашей инвестмодели равным нулю, мы последовательно применяя вышеприведенные формулы для каждого очередного периода, а в нашем случае это месяцы (см. вкладку «monthly»), получаем объемы производства за период и остатки запасов ГП на конец периода.
Аналогично рассчитываются объемы закупки сырья и материалов за период, а также запасы на конец периодов сырья и материалов через заранее рассчитанный план производства готовой продукции и заданный в строке 81 период оборачиваемости запасов сырья и материалов относительно выпуска готовой продукции.
Далее мы не будем столь подробно излагать методологию технического устройства файла с финмоделью, поскольку считаем, что будет справедливым предложить заинтересованному в этом пользователю оплатить 750 рублей за подробную методологию моделирования. Запрос на получение платной методологии инвестиционного моделирования можно оставить через обратную связь или позвонив нам по телефону, контакты см. в правом верхнем углу страницы.
Заметим лишь то, что формулы с оборачиваемостью запасов и готовой продукции мы применяем в финмодели для продаж выраженных в натуральных величинах, т.е. в количестве единиц готовой продукции, после чего задавая в строках 83, 85 и 104 соответственно плановую себестоимость продукции, прогнозную инфляцию в рамках стоимости сырья и материалов, а также наценку при продажах, получаем бюджет закупок, план производства и остатки запасов сырья и материалов и готовой продукции в деньгах, в нашем случае в тысячах рублей.
Все расчеты инвестиционной модели происходят в ежемесячной детализации во вкладке «monthly», после чего данные поступают в листы нашего EXCEL-файла с итоговыми формами финансовой управленческой отчетности, см. оглавление выше по тексту.
Отчет о прибылях и убытках или как сейчас стало принятым говорить, «отчет о финансовом результате (P&L) в финмодели представлен в двух вариантах, а именно в виде маржинального P&L и функционального P&L – вкладки «PL_m» и «PL_f».
В маржинальном отчете о прибылях и убытках все расходы разделяются на переменные и постоянные, т.е. на те статьи, которые зависят от тех или иных доходных показателей и на статьи, прямо не зависящие от них. С шаблоном/форматом маржинального P&L можно ознакомиться ниже:

report_margin_PL_investproject.jpg

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

report_functional_PL_investproject.jpg

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

report_Cash_Flow_investproject.jpg

Формат баланса мы не будем выкладывать, потому как если читателя уже заинтересовала наша финансовая модель инвестиционного проекта, то наверняка он ее скачает и самостоятельно ознакомится с ним (с форматом/шаблоном баланса).
Мы же обратимся к рассмотрению вкладки «NPV», в которой как раз таки с использованием EXCEL-формул проводится NPV-анализ, где рассчитываются такие показатели, как:
— NPV инвестиционных вложений (Inv);
— NPV потока ДС (CF);
— возврат инвестиций;
— PP — период окупаемости;
— NPV CF после инвестиционного периода;
— PI инвестиционного периода;
— NPV полный;
— PI полный;
— ROI инвестиционного периода;
— ROI полный;
— средняя ставка дисконтирования инвестпроекта;
— IRR — внутренняя норма доходности.
Формат отчета с NPV-анализом инвестпроекта следующий:

report_NPV_analysis_investproject.jpg

Итак, мы рассматриваем вкладку «NPV». В 12-тую строку этой вкладки мы из отчета Cash Flow вкладки «CF» вносим с помощью EXCEL-формул данные о потоке инвестиционных вложений в проект на капитальные затраты для создания того количества производственных модулей, которое необходимо в соответствии с планом продаж готовой продукции.
В строке 14 производится расчет коэффициентов дисконтирования для каждого года на основе данных из строки 188 вкладки «условия», куда пользователь в свою очередь вносит вручную прогнозные ставки дисконтирования финансового потока отдельно для каждого года инвестпроекта, напоминаем, что эти ставки в общем случае могут быть различными от года к году.
Приведенная стоимость инвестиционных вложений рассчитывается в строке 16 путем суммирования последовательных частных от деления для каждого года строки 12 с номинальными объемами инвестиций на соответствующие коэффициенты дисконтирования из строки 14.
Далее в строках 20 и 24 рассчитываются аналогичным образом приведенные стоимости притоков и оттоков денежных средств проекта без учета возврата инвестиционных займов, но с учетом финансовой деятельности предприятия, если топ-менеджменту последнего пришлось привлекать кредиты на пополнение оборотных средств операционной деятельности.
Рассматриваемая модель предполагает, что инвестиции направляются только на производственную инфраструктуру, а на поддержание операционной деятельности в случае возникновения кассовых разрывов топ-менеджмент предприятия должен сам найти возможность привлечения соответствующих кредитов и займов, объемы которых наша финмодель рассчитывает автоматически через модель овердрафтного кредитования и учитывает эти операции в рамках блока с движением денежных средств по финансовой деятельности в отчете Cash Flow.
Другими словами, в строке 26 рассчитывается приведенный финансовый поток по основной и финансовой деятельности или, что тоже самое, финансовый поток по операционной деятельности предприятия, который как раз таки и необходимо сравнить с приведенным потоком инвестиций из строки 16, что и делается в ячейке M29, в которой считается указанная разница. С помощью условного форматирования EXCEL ячейка M29закрашивается в зеленый цвет, если значение в ней больше нуля, т.е. проект состоятелен (приведенный финпоток по операционной деятельности больше приведенного инвестпотока), и окрашивается в красный, если наоборот – проект убыточен при прочих равных условиях с точки зрения инвестора.
В ячейках M31 и M33 рассчитываются значения показателей PI и ROI:
PI — Profitability Index — Индекс прибыльности инвестиций — равен отношению приведенного финансового потока по операционной деятельности к приведенной стоимости инвестиционных вложений – очевидно, что должен быть больше единицы, чтобы проект был предложен к рассмотрению;
ROI — Return On Investment — Рентабельность инвестиций — равен отношению NPV к приведенной стоимости инвестиций.
В строке 35 производится расчет потока возврата инвестиций, в связи с чем в ячейке M39 рассчитывается показатель
PP — Payback Period — Период возврата или окупаемости инвестиций — равен средневзвешенному по суммам возврата количеству лет, по прошествии которых инвестору вернутся все вложенные в проект денежные средства с учетом заданного уровня дисконтирования.
Далее в блоке строк с 41-ой по 53-тью рассчитываются все уже приведенные ранее показатели эффективности инвестиционного проекта для случая, если инвестор решит не выходить из проекта, а оставить его для себя «навсегда», см. соответствующие формулы в первой части этого раздела. Здесь в качестве ежегодного финансового потока на период после 10-ти лет существования проекта рассматривается средний финансовый поток рассчитанный по периоду следующему после периода окупаемости PP.
Наконец в ячейке M56, а на самом деле в блоке строк с 61-ой по 160-тую путем применения метода дихотомии, как мы уже отмечали выше, рассчитывается приближенное значение показателя эффективности инвестпроекта IRR — внутренней нормы доходности. И чтобы его было с чем сравнивать, в случае если от года к году пользователем модели задаются различные ставки дисконтирования, мы приводим в ячейке M56, с расчетом в блоке строк со 161-ой по 260-тую среднюю единую для каждого года ставку дисконтирования инвестиционного проекта.
В заключение отметим, что за скобки представленного в настоящей статье материала мы осознанно выносим обсуждение таких важнейших понятий, как EBITDA и EVA, а также рыночная стоимость чистых активов, философия которых формирует адекватное понимание того, что такое эффективное управление с точки зрения системы принятия инвестиционных, да и не только, решений. Также отметим, что взгляд на управление через эти понятия подобен, например, системе управления, называемой «бережливое производство», которая сформировалась в Японии. В данном случае речь идет не просто о формулах, через которые формально дается представление обывателю об этих показателях и связанных с ними понятиях, речь идет о том, как в реальной жизни предприятия внедрить соответствующие высоко эффективные системы управления, что является базовой отправной точкой для любого инвестора при формировании команды топ-менеджеров для управления проектом. Указанный материал мы разместим в ближайшем будущем в разделе, посвященном законам эффективного управления. На следующую страницу.

Незаполненные инвестиционные модели

СКАЧАТЬ Незаполненная финансовая модель инвестиционного проекта, NPV-анализ файл .xlsx
Внимание! Данная модель разработана в EXCEL-2013. В модели используются выпадающие списки, которые скорее всего не будут работать, если у Вас Excel-2007. Обратитесь к нам и мы поможем Вам настроить модель для корректной работы.

Расчет NPV в Excel. Расчет IRR, срока окупаемости простого и дисконтированного в экселе

Расчет чистого дисконтированного дохода NPV, также называемого ЧДД, несложен, но трудоемок, если считать его вручную.

Мы уже рассматривали пример расчета NPV и IRR по формулам. Там же были приведены формулы всех перечисленных показателей и их расчеты ручным методом .

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

Здесь вы можете бесплатно скачать таблицу Excel для расчета NPV, внутренней нормы доходности (IRR), сроков окупаемости простого и дисконтированного. Мы приведем таблицу для расчета NPV за 25 лет или меньший срок, в таблицу только стоит вставить значения предполагаемого размера инвестиций, размер ставки дисконтирования и величину годовых денежных потоков. И NPV рассчитается автоматически.

Вот эта таблица. Пароль к файлу: goodstudents.ru

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

Теперь давайте поговорим, как воспользоваться данной таблицей для расчета ЧДД, ВНД, срока окупаемости. В ней уже приведен пример расчета NPV.

Пример

Если вам нужно рассчитать NPV за 5 лет. Вам известна ставка дисконтирования 30% (т.е. 0,3). Известны денежные потоки по годам:

100 т.р.

200 т.р.

300 т.р.

350 т.р.

400 т.р.

Размер инвестиций 500 т.р.

В таблице экселя исправим значение ставки дисконтирования на 0,3 (2я строка сверху), исправим значение инвестиций (5я строка, 3й столбец) на 500.

Сотрем денежные потоки и их итог за 25 лет. (также сотрем строки чистых денежных потоков с 6го по 25й год и значение NPV для лишних лет). Вставим известные нам значения за 5 лет. Получим следующие данные.

0,3

ставка дисконтирования

Годы

Сумма инвестиций, тыс. руб

Денежные потоки, тыс. руб(CF)

Чистые денежные потоки, тыс. руб.

Чистый дисконтировнный доход, тыс. руб. (NPV)

1

500,00

100,00

76,92

-423,08

2

200,00

118,34

-304,73

3

300,00

136,55

-168,18

4

350,00

122,54

-45,64

5

400,00

107,73

62,09

Итого

500,00

1350,00

562,09

62,09

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

Теперь давайте разберемся как посчитать IRR с помощью экселя на конкретном примере. В Ms Excel есть функция, которая называется «подбор параметра». В 2003 экселе эта функция расположена в сервис-> подбор параметра.

Мы уже говорили ранее, что IRR – это такая ставка дисконтирования, при которой NPV равен нулю.

Нажимаем в экселе сервис-> подбор параметра, открывается окошко,

Расчет NPV в Excel

Мы знаем, что ЧДД =0, выбираем значение ячейки с ЧДД за 5й год, присваиваем ему значение 0, изменяя значение ячейки, в которой расположена ставка дисконтирования. После расчета получим.

Расчет IRR (ВНД) в Excel

Итак, NPV равен нулю при ставке дисконтирования равной 35,02%. Т.е. ВНД внутренняя норма доходности (IRR)=35,02%.

Теперь рассчитаем значение срока окупаемости простого и дисконтированного с помощью данной таблицы Эксель.

Срок окупаемости простой:

Мы видим по таблице, что у нас инвестиции 500 т.р. За 2 года мы получим доход 300 т.р. За 3 года получим 600 т.р. Значит срок окупаемости простой будет более 2 и менее 3х лет.

В ячейке F32 (32 строка файла экселя) нажимаем F2 и исправляем, вместо «1+» у нас будет «2+», меняем 1 на 2, и преобразуем формулу следующим образом, вместо » =1+(-(D5-C5)/D6)» у нас будет «=2+(-((D5+D6)-C5)/D7)», другими словами, мы к 2м полным годам прибавили долг по инвестициям на конец второго года, деленный на денежный поток за третий год. Получим 2,66 года.

Срок окупаемости дисконтированный пример расчета:

NPV переходит с минуса на плюс с 4го на 5й год, значит срок окупаемости с учетом дисконтирования будет более 4х и менее 5 лет.

В ячейке F33 (33 строка файла экселя) нажимаем F2 и исправляем, вместо «2+» у нас будет «4+», меняем 2 на 4, и преобразуем формулу следующим образом, вместо «=2+(-F6/E7)» у нас будет «=4+(-F8/E9))», другими словами, мы к четырем полным годам прибавили отношение последнего отрицательного NPV к чистому денежному потоку в следующем году (4+-(-45,64/107,73).

Получим 4,42 года – срок окупаемости с учетом дисконта.

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

Данный пример предназначен для практических занятий. к.э.н., доцент Одинцова Е.В.