Таблица для расчета доходности проекта

Анализ инвестиционного проекта в Excel скачать

Любая инвестиция нуждается в тщательных расчетах. Иначе инвестор рискует потерять вложенные средства.

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

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

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

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

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

• постоянными (нельзя рассчитать на единицу товара);
• переменными (можно рассчитать на единицу товара).

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

Составляем таблицу с постоянными затратами:

* Статьи расходов индивидуальны. Но принцип составления — понятен.

По такому же принципу составляем отдельно таблицу с переменными затратами:

Для нахождения цены продажи использовали формулу: =B4*(1+C4/100).

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

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

Для подсчета выручки использовалась формула: =СУММПРОИЗВ(B3:B6;Лист2!$D$4:$D$7). Где первый массив – объемы продаж; второй массив – цены реализации.

Выручка минус переменные затраты: =B7-СУММПРОИЗВ(B3:B6;Лист2!$B$4:$B$7).

Прибыль до уплаты налогов: =B8-Лист1!$B$14 (выручка без переменных и постоянных затрат).

Налоги ЕНВД: =Лист1!A10*1800*0,15*3 (1800 – базовая доходность по виду деятельности, 3 – количество месяцев, С12 – площадь помещения).

Чистая прибыль: прибыль – налоги.

Оценка инвестиционного проекта в Excel

Рассчитывают 4 основных показателя:

• чистый приведенный эффект (ЧПЭ, NPV);
• индекс рентабельности инвестиций (ИРИ, PI);
• внутреннюю норму доходности (ВНД, IRR);
• дисконтированный срок окупаемости (ДСО, DPP).

Для примера возьмем следующий вариант инвестиций:

Сначала дисконтируем каждый положительный элемент денежного потока.

Создадим новый столбец. Введем формулу вида: = положительный элемент денежного потока / (1 + ставка дисконтирования)^ степень, равная периоду.

Теперь рассчитаем чистый приведенный эффект:

1. С помощью функции СУММ.
2. С помощью встроенной функции ЧПС.
   Чтобы получить чистый приведенный эффект, складываем результат функции с суммой инвестиций.
   Цифры совпали:

Найдем индекс рентабельности инвестиций. Для этого нужно разделить чистую приведенную стоимость (ЧПС) на объем инвестированных средств (со знаком «+»):

Посчитаем IRR инвестиционного проекта в Excel. Напомним формулу:

ВНД = ΣДП_t/ (1 + ВНР) t = И.

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

ΣДП_t / (1 + ВНР) t – И = 0,

Воспользуемся инструментом «Анализ «Что-Если»»:

1. Ставим курсор в ячейку со значением чистого приведенного эффекта. Выбираем «Данные»-«Анализ Что-Если»-«Подбор параметра».
2. В открывшемся окне в строке «Значение» вводим 0 (чистый приведенный эффект должен равняться 0). В поле «Изменяя значение ячейки» ссылаемся на ставку дисконтирования. Нужно изменить ее так, чтобы соблюдалось приведенное выше равенство.
3. Нажимаем ОК.

Ставка дисконтирования равняется 0,41. Следовательно, внутренняя норма доходности составила 41%.

Моделирование рисков инвестиционных проектов в Excel

Используем метод имитационного моделирования Монте-Карло. Задача – воспроизвести развитие бизнеса на основе результатов анализа известных элементов и взаимосвязей между ними.

Продемонстрируем моделирование рисков на простейшем примере. Составим условный шаблон с данными:

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

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

Заполняем параметры следующим образом:

Нам нужно смоделировать ситуацию на основе распределений разного типа.

Для генерации количества пользователей воспользуемся функцией СЛУЧМЕЖДУ. Нижняя граница (при самом плохом варианте событий) – 1 пользователь. Верхняя граница (при самом хорошем варианте развития бизнеса) – 50 покупателей услуги.

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

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

Программа выдает результат (по столбцу «Коэффициент эффективности»):

Можно делать выводы и принимать окончательное решение.

Источник

Таблица для учета инвестиций

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

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

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

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

Таблица считает сумму дивидендного дохода в год и средний в месяц, в рублях и долларах отдельно + конвертация долларов по курсу в рублях и общий итог ДД в месяц.

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

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

Действую так: Купил акцию — добавил строчку в соответствующий сектор. Указываю эмитента, сектор, количество купленных бумаг, брокера, валюту акции, сумму покупки и планируемый дивиденд на одну акцию. Формулы просчитывают все остальное.

Если акция уже была — просто изменил количество акций в строчке. Автоматически просчитывается чистая ДД (за вычетом налога) на то количество акций, которое я указал. Чистая ДД прибавляется в итоговую сумму заработка за год. Если это доллары — они конвертируются в рубли по курсу 75 рублей за доллар и добавляются к сумму заработка за год.

В комплекте к таблице идут принципы инвестирования, которым я следую. Например, доля одного эмитента не может быть более 5% от портфеля, а доля одного сектора не может быть более 15% от портфеля. Покупки совершаются в три этапа: 30% + 30% + 40% в зависимости от степени падения бумаги. По некоторым эмитентам использую так называемую «демо покупку»: когда бумага на хаях, и я захожу на одну акцию, чисто чтобы за ней следить и так далее. В совокупности таблица и принципы отлично дисциплинируют.

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

Сделки я совершаю один-два раза в месяц, каждую фиксирую в таблице. Занимает это около 10 минут.

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

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник