Таблица эффективности инвестиций как составить таблицу

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

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

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

Составляется на прогнозируемый период окупаемости.

Основные компоненты:

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

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

Финансовая модель – это план снижения рисков при инвестировании. Детализация и реалистичность – обязательные условия. При составлении проекта в программе Microsoft Excel соблюдают правила:

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



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

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

• статистические (PP, ARR);
• динамические (NPV, IRR, PI, DPP).

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

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

Экономическая формула расчета срока окупаемости:

где IC – первоначальные вложения инвестора (все издержки),

CF – денежный поток, или чистая прибыль (за определенный период).

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

1. Составим таблицу с исходными данными. Стоимость первоначальных инвестиций – 160000 рублей. Ежемесячно поступает 56000 рублей. Для расчета денежного потока нарастающим итогом была использована формула: =C4+$C$2.



2. Рассчитаем срок окупаемости инвестированных средств. Использовали формулу: =B4/C2 (сумма первоначальных инвестиций / сумма ежемесячных поступлений).

Так как у нас дискретный период, то срок окупаемости составит 3 месяца.

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

Рентабельность инвестиций

ARR, ROI – коэффициенты рентабельности, показывающие прибыльность проекта без учета дисконтирования.

Формула расчета:

где CFср. – средний показатель чистой прибыли за определенный период;

IC – первоначальные вложения инвестора.

Пример расчета в Excel:

1. Изменим входные данные. Первоначальные вложения в размере 160 000 рублей вносятся только один раз, на старте проекта. Ежемесячные платежи – разные суммы.



2. Рассчитаем средние поступления по месяцам и найдем рентабельность проекта. Используем формулу: =СРЗНАЧ(C23:C32)/B23. Формат ячейки с результатом процентный.

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

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

• скачать полный инвестиционный проект
• скачать сокращенный вариант в Excel

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

Этот текст написан в Сообществе, в нем сохранены авторский стиль и орфография

В первые месяцы инвестирования — начало 2020 года — я просматривал разные сервисы по учёту инвестиций. Некоторые нравились, некоторые — не очень. Одновременно с этим я вёл учёт расходов семьи. И заметил, что довольно много небольших расходов в разных категориях. Добавлять ещё одну категорию — оплата сервиса учёта — совсем не хотелось. Вроде копейки, но на эти «копейки» можно купить какую-нибудь акцию. А за год — 12 акций. Если каждый год такие 12 акций будут приносить прибыль, то почему я должен тратить эту сумму на сделанные кем-то таблички?!

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

Разобью функции по вкладкам.

Портфель. У меня открыто два счёта у одного брокера — обычный брокерский и ИИС. Поэтому в первой вкладке два портфеля. Цены подтягиваются с сайта Мосбиржи, раньше пользовался функцией Googlefinance. В самом низу вкладки подсчитаны суммы по категориям эмитентов, чтобы понимать диверсифицированность суммарного портфеля. На момент написания статьи в лидерах категорий у меня «нефть и газ», «химия» и «энергетика». Стоимость активов подтягивается сама из формулы суммы с двух счетов. Я заполняю только среднюю цену входа, беру из приложения, и количество акций.

Во вкладке «Вложения 2021» я ежедневно обновляю данные по доходности портфеля, при вычислении её стандартной формулой «сумма вложений/текущую стоимость портфеля». По этим данным строится график.

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

«Дивиденды» — моя любимая вкладка. Очень приятно наблюдать за их увеличением, в том числе средним значением за последние 12 месяцев. На момент написания этого текста мой пассивный доход в среднем составляет более 3000 рублей в месяц. Даже подсчитана средняя сумма дивидендов за всё время инвестирования — 22 месяца, включая первые месяцы с полным отсутствием дивидендных выплат.

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

Идеи. Название говорит само за себя, можно и без пояснений.

Сравнение. По этой таблице я выбираю наиболее привлекательные акции, голосуя за них по разным критериям. Для иностранных акций данные нахожу на Finasquare, для российских — на Смартлабе. Наиболее привлекательному показателю ставлю единичку, наименее привлекательному — 0, иногда 0,5. И затем смотрю, какая компания набрала больше баллов.

План. После первого года инвестирования стало интересно, когда при текущих условиях я приду к сумме в 10 000 000 рублей. Тут можно играться с процентной ставкой и суммами ежегодных вложений.

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

В идеале было бы интересно из этой таблицы создать более презентабельный вид при помощи какого-нибудь Python или другого языка. Но поскольку таблица создавалась исключительно для себя, да и знаний у меня на это нет, она остаётся такой, какая есть в данный момент. Иногда я что-то в неё добавляю, иногда убираю. Посмотрим, как она будет выглядеть через несколько лет.

Ссылка на таблицу

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

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

У вас наверняка возник вопрос: Если все так просто, то зачем же она нужна?

А я вам отвечу: Эта таблица может дать вам гораздо больше, чем вы думаете. Она превращает ваши мечты в реальность, позволяет объективно взглянуть на конкретно вашу финансовую ситуацию, продумать шаги для достижения именно ваших целей и наконец начать предпринимать что-то для получения результата. И если вы как и я всегда хотели стать как Уоррен Баффет, как Скрудж Макдак , как сын маминой подруги и не испытывать проблем с деньгами, то рекомендую вам дочитать статью до конца.

Эта таблица поспособствует тому, что вы наглядно пропишите и учтете:

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

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

Теперь предлагаю перейти к заполнению. В таблице указан не мой план, которому я следую, а просто пример. Вы можете изменять исходные данные. Жми на ссылку и следуй шаг за шагом:

Шаг 1

Заполняем доходы и расходы (последние столбцы). Здесь все просто. Там где у вас указаны месяца вы пишите ваш планируемый доход и расход в месяц, там где начинаются года, то умножаете эти данные на 12. У меня для примера указан доход на начальном этапе 35 тысяч, расход 25 тысяч, а после я их увеличиваю с течением времени. Чем выше у вас доход и меньше расход, тем больше вы сможете выделить под инвестиции и соответственно получить в результате капитал либо больше, либо быстрее (в зависимости от целей). Но тут главное не перестараться, в свой первый раз я заложил слишком маленькие расходы и потом корректировал.

Шаг 2

После того, как вы внесли доходы и расходы таблица рассчитает вам прибыль(самый последний столбец), то есть средства, которые вы можете пустить на инвестиции, либо оставить на счету. Исходя из этой суммы необходимо решить, какую долю вы хотели бы выделить для создания инвест портфеля. У меня указано значение 80% от прибыли, но оно может быть абсолютно любым. Чем выше доля вложений, которую вы готовы инвестировать, тем лучше будет результат. Это значение необходимо внести в ячейку с названием «Капитал» над графой «ВЗНОСЫ».

Шаг 3

Заполняем распределение капитала между различными видами активов. В таблице учитываются низкорисковые активы, среднерисковые и высокорисковые. В зависимости от уровня доходности (чем она выше, тем выше риски) необходимо распределить доли, согласно которым будет распределяться «Капитал» по этим активам. Чтобы было понятнее приведу пример. У меня в таблице, в ячейке «Консервативный», над графой «ВЗНОСЫ» указана цифра 5%, это значит, что одна двадцатая от выделенного мной капитала(на предыдущем шаге) пойдет на низкорисковые активы. Аналогичным образом, нужно будет выделить долю для ячейки «Умеренный»(среднерисковые) и «Агрессивный»(высокорисковые). В сумме эти доли должны составлять 100%. В этом плане это 5%+30%+65%.Стоит понимать, что риск влияет на доходность. Не нужно бояться этого слова ,но и пренебрегать им не стоит. Чем выше будет агрессивная доля в портфеле тем, быстрее вы получите нужный результат. Обращаю внимание, что заполнить нужно только ячейку с процентом, в самой таблице расчет идет автоматически.

Шаг 4

Переходим к соседней графе-«НАКОПЛЕНИЯ». Это и есть наш портфель. Сюда подтягиваются значения из колонки «ВЗНОСЫ» и учитывается доходность портфеля. По умолчанию у меня указаны актуальные проценты доходности для долгосрочного инвестирования, поэтому стоит их менять только если ситуация на рынке меняется, сейчас можно этого не делать. В этой части таблицы находится самое интересное-столбец, который выделен голубым под названием «Капитал». В нем указана сумма активов на вашем портфеле накопленная к определенному временному периоду. Следует понимать, что на протяжении всего периода инвестирования вы реинвестируете средства и не снимаете их с брокерского счета, таким образом работает сложный процент и вы получаете десятикратный результат. Для того чтобы у вас не было соблазна воспользоваться средствами на брокерском счету, понадобится финансовая подушка.

Шаг 5

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

Шаг 6

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

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

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

Рассмотрим 6 методов оценки эффективности инвестиций, дам различные формулы расчета инвестиционных коэффициентов, методику (алгоритм) расчета их в Excel. Данные методы будут полезны инвесторам, финансовым аналитикам, бизнес-аналитикам и экономистам. Сразу следует отметить, что под инвестициями будем понимать различные инвестиционные проекты, объекты вложения и активы. То есть данные методы могут быть широко использованы в оценочной деятельности любого предприятия/компании.

Все методы оценки эффективности инвестиционных проектов можно разделить на две большие группы:

• Статистические методы оценки инвестиционных проектов
  • Срок окупаемости инвестиционного проекта (PP, Payback Period)
  • Рентабельность инвестиционного проекта (ARR, Accounting Rate of Return)
• Динамические методы оценки инвестиционных проектов
  • Чистый дисконтированный доход (NPV, Net Present Value)
  • Внутренняя норма прибыл (IRR, Internal Rate of Return)
  • Индекс прибыльности (PI, Profitability index)
  • Дисконтированный срок окупаемости (DPP, Discounted Payback Period)

Статистические методы оценки эффективности инвестиций/инвестиционных проектов

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

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

Срок окупаемости инвестиций (Payback Period)

Срок окупаемости инвестиций или инвестиционного проекта (англ. Payback Period, PP, период окупаемости) – данный коэффициент показывает период, за который окупятся первоначальные инвестиции (затраты) в инвестиционный проект. Экономический смысл данного показателя заключается в том, что бы показать срок, за который инвестор вернет обратно свои вложенные деньги (капитал).

Формула расчета срока окупаемости инвестиций (инвестиционного проекта)

где:

IC  (Invest Capital) – инвестиционный капитал, первоначальные затраты инвестора в объект вложения. В формуле в иностранной практике иногда используют понятие не инвестиционный капитал, а затраты на капитал (Cost of Capital, CC), что по сути несет аналогичный смысл;

CF (Cash Flow) – денежный поток, который создается объектом инвестиций. Под денежным потоком иногда в формулах подразумевают чистую прибыль (NP, Net Profit).

Формулу расчета срока/период окупаемости можно расписать по-другому, такой вариант тоже часто встречается в отечественной литературе по финансам:

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

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

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

Денежный поток нарастающим итогом рассчитывается в колонке С, C7=C6+$C$3

Если рассчитать срок окупаемости по формуле, то получится следующее:

Так как мы у нас период дискретный, то необходимо округлить этот период до 6 месяцев.

Направления использования срока окупаемости инвестиций (инвестиционных проектов)

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

Достоинства и недостатки показателя срока окупаемости инвестиций

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

Коэффициент рентабельности инвестиций (Accounting Rate of Return)

Коэффициент рентабельности инвестиций или инвестиционного проекта (англ. Accounting Rate of Return, ARR, ROI, учетная норма прибыли, рентабельность инвестиций) – показатель отражающий прибыльность объекта инвестиций без учета дисконтирования.

Формула расчета коэффициента рентабельности инвестиций

где:

CFср – средний денежный поток (чистая прибыль) объекта инвестиций за рассматриваемый период (месяц, год);

IC (Invest Capital) – инвестиционный капитал, первоначальные затраты инвестора в объект вложения.

Существует также следующая разновидность формулы рентабельности инвестиций отражающая случай, когда в объект/проект в течение рассматриваемого периода вносят дополнительные инвестиционные вложения. Поэтому берется средняя стоимость капитала за период. Формула  при этом имеет вид:

где:

IC₀, IC₁ – стоимость инвестиций (затраченного капитала) на начало и конец отчетного периода.

Пример расчета коэффициента рентабельности инвестиций (инвестиционного проекта) в Excel

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

В14=СРЗНАЧ(C5:C12)/B5

Цели использования коэффициента рентабельности инвестиционного проекта

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

Достоинства и недостатки коэффициента рентабельности инвестиций

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

Динамические методы оценки эффективности инвестиций/инвестиционных проектов. Как рассчитать эффективность проекта

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

Чистый дисконтированный доход (Net Present Value)

Чистый дисконтированный доход  (англ. Net Present Value, NPV, чистая текущая стоимость, чистый приведенный доход, текущая стоимость) – показатель, отражающий изменение денежных потоков и показывает разность между дисконтированными денежными доходами и расходами.

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

Формула чистого дисконтированного дохода

где:

NPV – чистый дисконтированный доход проекта;

CF_t – денежный поток в период времени t;

CF₀ – денежный поток в первоначальный момент. Первоначальный денежный поток равняется инвестиционному капиталу (CF₀ = IC);

r – ставка дисконтирования (барьерная ставка).

Оценка проекта на основе критерия NPV

Критерий оценки NPV	Заключение по проекту
NPV<0	Инвестиционный проект, имеющий отрицательное значение NPV следует исключить из рассмотрения
NPV=0	Инвестиционный проект обеспечит уровень безубыточности, когда все доходы равны расходам
NPV>0	Инвестиционный проект привлекателен для вложения
NPV1>NPV2*	Сравнение NPV одного проекта с NPV* другого, показывает большую инвестиционную привлекательность первого

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

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

Вариант №1

Итак, разберем алгоритм последовательного расчета всех показателей NPV.

1. Расчет денежного потока по годам: E7=C7-D7
2. Дисконтирование денежного потока по времени: F7=E7/(1+$C$3)^A7
3. Суммирование всех дисконтированных денежных поступлений по инвестиционному проекту и вычитание первоначальных капитальных затрат: F16 =СУММ(F7:F15)-B6

Вариант №2

Расчет с использованием встроенной формулы ЧПС. Следует заметить, что необходимо вычесть первоначальные капитальные затраты (В6).

=ЧПС($C$3;E7;E8;E9;E10;E11;E12;E13;E14;E15)-B6

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

Мастер-класс: «Как рассчитать чистый дисконтированный доход для бизнес плана»

Внутренняя норма прибыли инвестиционного проекта

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

Формула расчета внутренней нормы прибыли инвестиционного проекта

где:

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

IRR – внутренняя норма прибыли;

CF₀ – денежный поток в первоначальный момент. В первом периоде, как правило, денежный поток равняется инвестиционному капиталу (CF₀ = IC).

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

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

E16 =ВСД(E6:E15)

Достоинства и недостатки  внутренней нормы доходности IRR

+ ) возможность сравнения инвестиционных проектов между собой, имеющих разный горизонт инвестирования;

+ )возможность сравнения не только проектов, но и альтернативных инвестиций, например банковский вклад. Если IRR проекта составляет 25%, а банковский вклад равен 15%, то проект более инвестиционно привлекателен.

+ ) экспресс-оценка проекта на его целесообразность дальнейшего развития.

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

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

— ) не отражен абсолютный рост стоимости инвестиционного проекта;

— ) денежные потоки часто имеют не систематическую структуру, что затрудняет правильный расчет данного показателя.

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

Индекс прибыльности инвестиций (Profitability index)

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

Формула расчета индекса прибыльности инвестиций

где:

NPV – чистый дисконтированный доход;

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

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

IC – вложенный (затраченный) инвестиционный капитал.

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

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

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

Оценка проекта на основе критерия PI

Критерий оценки PI	Заключение по выбору проекта
PI<1	Инвестиционный проект не сможет возместить в полном размере вложенные в него капитальные затраты
PI=1	Инвестиционный проект имеет доходность равную выбранной ставки дисконтирования
PI>1	Проект инвестиционно привлекателен для вложения, так как сможет обеспечить дополнительную отдачу капитала.
PI1>PI2*	Сравнение между собой инвестиционной привлекательности нескольких проектов. Первый проект имеет большую рентабельность, поэтому второй будет отброшен

Пример расчета индекса прибыльности проекта в Excel

Рассмотрим на примере вычисление индекса прибыльности. На рисунке ниже показан расчет PI в ячейке F18.

1. Расчет столбца F – Дисконтированного денежного потока =E7/(1+$C$3)^A7
2. Расчет чистого дисконтированного дохода NPV в ячейке F16 =СУММ(F7:F15)-B6
3. Оценка прибыльности инвестиций в ячейке F18 =F16/B6

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

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

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

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

где:

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

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

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

t –период оценки получаемого денежного потока.

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

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

1. Рассчитать дисконтированный денежный поток в колонке D =C7/(1+$C$3)^A7
2. Посчитать нарастающим итогом прирост капитала в колоне E =E7+D8
3. Оценить в какой период вложенные инвестиции (IC) окупились полностью.

Как мы видим окупаемость всех затрат дисконтированным денежным потоком произошла на 6 месяц. Чем меньше период окупаемости инвестиционного проекта, тем более привлекательны данные проекты.

Достоинства и недостатки дисконтированного срока окупаемости

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

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

Выбор инвестиции на основе коэффициентов оценки эффективности

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

Показатель	Критерий выбора инвестиции
Статистические методы оценки эффективности инвестиций
Период окупаемости	PP -> min
Рентабельность инвестиционного капитала	ARR>0
Динамические методы оценки эффективности инвестиций
Чистый дисконтированный доход	NPV>0
Внутренняя норма прибыли	IRR>WACC
Индекс прибыльности	PI>1
Дисконтированный период окупаемости	DPP -> min

 Резюме

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

Автор: к.э.н. Жданов Иван Юрьевич   

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

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

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

Итак, поехали!

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

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

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

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

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

Пример.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Формула:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Удачных инвестиций!