Для расчета внутренней ставки доходности (внутренней нормы доходности, IRR) в Excel используется функция ВСД. Ее особенности, синтаксис, примеры рассмотрим в статье.
Один из методов оценки инвестиционных проектов – внутренняя норма доходности. Расчет в автоматическом режиме можно произвести с помощью функции ВСД в Excel. Она находит внутреннюю ставку доходности для ряда потоков денежных средств. Финансовые показатели должны быть представлены числовыми значениями.
Суммы внутри потоков могут колебаться. Но поступления регулярные (каждый месяц, квартал или год). Это обязательное условие для корректного расчета.
Внутренняя ставка доходности (IRR, внутренняя норма доходности) – процентная ставка инвестиционного проекта, при которой приведенная стоимость денежных потоков равняется нулю. При данной ставке инвестор вернет вложенные первоначально средства. Инвестиции состоят из платежей (суммы со знаком «–») и доходов (со знаком «+»), которые происходят в одинаковые по продолжительности временные промежутки.
Аргументы функции ВСД в Excel:
- Значения. Диапазон ячеек, в которых содержатся числовые выражения денежных средств. Для данных сумм нужно посчитать внутреннюю норму доходности.
- Предположение. Цифра, которая предположительно близка к результату. Аргумент необязательный.
Секреты работы функции ВСД (IRR):
- В диапазоне с денежными суммами должно содержаться хотя бы одно положительное и одно отрицательное значение.
- Для функции ВСД важен порядок выплат или поступлений. То есть денежные потоки должны вводится в таблицу в соответствии со временем их возникновения.
- Текстовые или логические значения, пустые ячейки при расчете игнорируются.
- В программе Excel для подсчета внутренней ставки доходности используется метод итераций (подбора). Формула производит циклические вычисления с того значения, которое указано в аргументе «Предположение». Если аргумент опущен, со значения 0,1 (10%).
При расчете ВСД в Excel может возникнуть ошибка #ЧИСЛО!. Почему? Используя метод итераций при расчете, функция находит результат с точностью 0,00001%. Если после 20 попыток не удается получить результат, ВСД вернет значение ошибки.
Когда функция показывает ошибку #ЧИСЛО!, повторите расчет с другим значением аргумента «Предположение».
Расчет внутренней нормы рентабельности рассмотрим на элементарном примере. Имеются следующие входные данные:
Сумма первоначальной инвестиции – 7000. В течение анализируемого периода было еще две инвестиции – 5040 и 10.
Заходим на вкладку «Формулы». В категории «Финансовые» находим функцию ВСД. Заполняем аргументы.
Значения – диапазон с суммами денежных потоков, по которым необходимо рассчитать внутреннюю норму рентабельности. Предположение – опустим.
Искомая IRR (внутренняя норма доходности) анализируемого проекта – значение 0,209040417. Если перевести десятичное выражение величины в проценты, то получим ставку 20,90%.
В нашем примере расчет ВСД произведен для ежегодных потоков. Если нужно найти IRR для ежемесячных потоков сразу за несколько лет, лучше ввести аргумент «Предположение». Программа может не справиться с расчетом за 20 попыток – появится ошибка #ЧИСЛО!.
Еще один показатель эффективности инвестиционного проекта – NPV (чистый дисконтированный доход). NPV и IRR связаны: IRR определяет ставку дисконтирования, при которой NPV = 0 (то есть затраты на проект равны доходам).
Для расчета NPV в Excel применяется функция ЧПС. Чтобы найти внутреннюю ставку доходности графическим методом, нужно построить график изменения NPV. Для этого в формулу расчета NPV будем подставлять разные значения ставок дисконта.
На основании полученных данных построим график изменения NPV.
Пересечение графика с осью Х (когда чистый дисконтированный доход проекта равняется нулю) дает показатель IRR для данного проекта. Графический метод показал результат ВСД, аналогичный найденному в Excel.
Если значение IRR проекта выше стоимости капитала для предприятия, то данный инвестиционный проект нужно принять.
То есть если ставка кредита меньше внутренней нормы рентабельности, то заемные средства принесут прибыль. Так как в при реализации проекта мы получим больший процент дохода, чем величина капитала.
Вернемся к нашему примеру. Допустим, для запуска проекта брался кредит в банке под 15% годовых. Расчет показал, что внутренняя норма доходности составила 20,9%. На таком проекте можно заработать.
По материалам exceltable.com
IRR, ВНД, ВСД, или внутренняя норма доходности – показатель инвестиционного анализа, который позволяет определить доходность вложения средств и максимально возможную ставку по привлекаемым заемным средствам. Он помогает сравнить несколько проектов и выбрать наиболее удачный вариант для вложений. Поскольку он рассчитывается через NPV, то из 4-х методов удобнее всего использовать автоматизированный – через функцию ВНД табличного редактора Excel.
Как определить оптимальную ставку, по которой можно привлекать кредитные ресурсы для реализации бизнес-плана? Как заранее определить привлекательность инвестиционного проекта? Для этих целей стоит познакомиться с показателем внутренней нормы доходности.
IRR (Internal Rate of Return – внутренняя норма доходности) – это ставка дисконтирования, при которой значение NPV (Net Present Value – чистая приведенная стоимость) равно нулю.
Справка! IRR можно рассматривать двояко:
- с одной стороны, это показатель, который характеризует доходность инвестиционного проекта – чем он выше, тем большей окажется прибыль от реализации проекта;
- с другой – максимальная стоимость капитала, который может быть привлечен для проекта и при использовании окажется безубыточным.
Ставку IRR следует рассчитывать при составлении бизнес-планов и рассмотрении любых вариантов вложения средств.
Важный момент! Если для инвестирования планируется использовать заемные средства, то процентная ставка по ним не должна превышать внутренней нормы рентабельности. В противном случае проект окажется убыточным.
В литературе встречаются и иные названия IRR: внутренняя норма доходности (аббревиатура – ВНД), внутренняя ставка доходности, внутренняя норма рентабельности и др.
Поскольку IRR представляет собой ставку дисконтирования в ситуации, когда NPV равно нулю, то для расчета показателя применяется та же формула, что и для определения чистой приведенной стоимости.
В приведенной формуле присутствуют такие показатели, как:
- CF – суммарный денежный поток за период t;
- t – порядковый номер периода;
- i – ставка дисконтирования денежного потока (ставка приведения);
- IC – сумма первоначальных инвестиций.
Если известно, что NPV равен нулю, то получится сложное уравнение, в котором внутренняя норма доходности должна быть извлечена из-под корня со степенью. В связи с этим IRR невозможно точно рассчитать вручную.
Для расчета можно воспользоваться финансовым калькулятором. Однако даже в этом случае расчеты окажутся громоздкими.
Ранее для расчета внутренней ставки доходности использовали графический метод: рассчитывали для каждого из проектов NPV и строили их линейные графики. В точках пересечения графиков с осью абсцисс (ось Х) и находилось значение IRR. Однако такой метод неточен и носит демонстрационный характер.
Рисунок 1. Пример использования графического метода при расчете IRR
Справка! В финансовой математике применяется метод подбора, который позволяет с использованием логарифмических расчетов выявить зависимость между NPV и ВНД. Такой способ не менее сложен и требует умения проводить действия с логарифмами.
В связи с этим наиболее простым, удобным и точным способом расчета IRR выступает использование финансовой функции ВСД табличного редактора Excel
По материалам moneymakerfactory.ru
Данная группа включает финансовые функции, позволяющие быстро и эффективно осуществить расчет наиболее широко применяемых в мировой практике критериев эффективности инвестиционных проектов:
чистой настоящей величины дохода(net present value – NPV) и
внутренней нормы рентабельности(internal rate of return – IRR).
В табличном процессоре Excel реализованы три встроенные функции для проведения расчета данных критериев:
а) ЧПС() – функция для расчета чистой (приведенной) текущей стоимости капитала;
б) ВСД() – функция для расчета внутренней нормы рентабельности;
в) МВСД() – функция для расчета модифицированной внутренней нормы рентабельности;
Функции для анализа эффективности инвестиционных проектов используют сложные итерационные алгоритмы для реализации дисконтных методов исчисления соответствующих показателей. При этом делаются два основных допущения:
— потоки платежей на конец (начало) каждого периода известны;
— для всего срока проведения операции определена оценка в виде процентной ставки, в соответствии с которой, полученные средства могут быть реинвестированы.
В качестве такой оценки наиболее часто используют: среднюю или предельную стоимость капитала, банковские ставки по долгосрочным кредитам (депозитам), требуемую норму рентабельности и пр.
а) Функция ЧПС( ).
Допустим, Ваша фирма собирается вложить денежные средства в новое оборудование, приобретение, доставка и установка которого оценивается в 100000 тыс. руб. Ожидается, что его эксплуатация обеспечит на протяжении 6 лет получение чистой прибыли в 10000, 25000, 30000, 35000, 40000, 45000 тыс. руб. соответственно. Приемлемая норма рентабельности составляет 10%. Необходимо определить целесообразность осуществления данного проекта, т.е. необходимо определить чистый приведенный доходв конце периода в зависимости от входных параметров.
Для решения задачи выполните следующие действия:
1) Введите исходные данные на рабочий лист. В данном случае диапазон ячеек А9:А15 содержит порядковые номера периодов эксплуатации актива. В ячейке B9 введено значение первоначальных инвестиций (причем в виде отрицательной величины), соответствующее нулевому периоду. В диапазоне B10:B15 содержатся значения ожидаемой чистой прибыли (денежных потоков).
2) Установите курсор в ячейку C9, выполните команду Формулы / Библиотека функций / Вставить функциюили нажмите кнопку 
Строки формул. В категорииФинансовыеиз списка выберите функцию ЧПС( ). Нажмите ОК.
3) Введите необходимые аргументы в соответствии с синтаксисом данной функции:
=ЧПС (ставка; значение1; значение2;…; значениеN),
где: ставка – процентная ставка (норма прибыли или цена капитала);
значение1, значение2,…, значениеN – денежные потоки из N платежей произвольной величины.
Функция ЧПС( ) позволяет определить текущую, т.е. приведенную к настоящему моменту времени, величину потока будущих платежей (present value) с учетом заданной оценки, исходя из следующего соотношения:
где: PV – текущая величина потока;
Pi – сумма платежей за i-й период;
r– процентная ставка (норма дисконтирования);
Следует обратить внимание на то, что данное соотношение не учитывает величину первоначальных инвестиций I, т.е. инвестиций, сделанных на момент времени i=0. Поэтому для определения показателя чистый приведенный доход (net present value)из полученного результата (PV) следует вычесть величину первоначальных вложений I.
Если разность PV – I > 0, то проект возмещает первоначальные затраты, обеспечивает получение прибыли согласно заданному стандарту – процентной ставки r, а также некоторый финансовый резерв, равный: NPV = PV – I. Отрицательная разность показывает убыточность проекта. На практике при проведении расчетов удобно задавать I как отрицательную величину, а NPV рассчитывать по формуле: NPV = I + PV, где I 0, то проект обеспечивает возмещение первоначальных затрат и заданную норму рентабельности, а также дополнительную (сверх установленной нормы) прибыль в размере 26435,16.
Определив показатель PV, не трудно рассчитать еще один важный критерий оценки эффективности инвестиций – индекс рентабельности(PI),используя формулу: PI = PV / I.Для нашего примера он может быть рассчитан следующим образом:
=ЧПС(0,1;10000;25000;30000;35000;40000;45000)/100000 (Результат: 1,26).
б) Другим широко используемым на практике критерием оценки эффективности долгосрочных инвестиций является показатель внутренней нормы рентабельности(internal rate of return – IRR). В экономическом смысле данная величина является процентной ставкой, при которой уровень капитализации регулярного дохода (т.е. будущая стоимость вложений) даст сумму, равную первоначальным инвестициям. Другими словами, это процентная ставка, при которой PV=I, а NPV=0. Таким образом, если величина IRR больше заданной процентной ставки r, то проект следует считать эффективным, т.е. приносящий доход, иначе его следует отклонить, как убыточный.
Критерий внутренней нормы рентабельности предполагает реинвестирование получаемых доходов по ставке IRR.
Для вычисления этого показателя в Excel реализована функция ВСД().
=ВСД(значения;предположение),
где: значения – массив данных или диапазон ячеек, содержащих числовые величины, для которых вычисляется внутренняя скорость оборота денежных средств;
предположение – величина, о которой предполагается, что она близка к результату, возвращаемому функцией ВСД().
Табличный процессор Excel использует метод итераций для вычисления внутренней нормы рентабельности начиная со значения предположение.Функция ВСД() выполняет циклические вычисления пока не возвратит результат с точностью 0,00001 процента. Если функция ВСД() не может получить результат после 20-ти попыток, то возвращается значение ошибки #ЧИСЛО!.
Рассмотрим ее применение в нашей задаче.
Введите в ячейку рабочего листа, например D15, формулу ВСД() в соответствии с ее синтаксисом и нажмите ОК:
=ВСД (начальное значение: конечное значение;
заданная норма рентабельности).
=ВСД(B9:B15;0,01) (Возвращаемый результат: 17%).
Поскольку полученная внутренняя норма рентабельности (17%) выше заданной (10%), то проект следует принять и посчитать его прибыльным.
Показатель IRR, рассчитываемый в процентах, является более удобным критерием в оценке инвестиционной привлекательности проекта и для принятия решений, чем показатель NPV, так как абсолютные величины труднее поддаются интерпретации.
в) Для более корректного учета предположения о реинвестировании денежных средств в Excel реализована функция МВСД( ), вычисляющая модифицированную внутреннюю норму рентабельности (MIRR)для ряда последовательных операций с денежными средствами. Функция МВСД( ) учитывает как стоимость инвестиций, так и доход получаемый от реинвестирования средств.Поэтому данная функция имеет специальный аргумент — предполагаемую ставку реинвестирования.
= МВСД(значения; ставка финанс; ставка реинвест),
где: значения – это массив данных или ссылка на ячейки, содержащие числовые величины. Эти числа представляют ряд денежных выплат (отрицательные значения) и поступлений (положительные значения), происходящие в регулярные периоды времени. Аргумент значения должен содержать, по крайней мере, одно положительное и одно отрицательное значение для того, чтобы можно было вычислить модифицированную внутреннюю скорость оборота (норму рентабельности). В противном случае функция МВСД() возвращает значение ошибки #ДЕЛ/0!;
ставка_финанс– это норма прибыли вложений денежных средств, находящихся в наличном обороте;
ставка_реинвест– это норма прибыли вложений денежных средств, находящихся в наличном обороте при их реинвестировании.
Функция МВСД() использует порядок расположения чисел в аргументе значения для определения порядка выплат и поступлений. Убедитесь, что значения выплат и поступлений введены в нужной последовательности и с правильными знаками (положительные значения для получаемых денег и отрицательные значения для выплачиваемых).
Предположим, что в нашем примере предоставляется реальная возможность реинвестирования получаемых доходов по ставке 12%, т.е. выше заданной (10%).
Для проведения расчета выполните следующие действия:
1) Пусть на рабочем листе (например, диапазон B9:B15) уже введены значения первоначальных инвестиций (ссуд) и потоков платежей (выплат) с соответствующими знаками.
2) Установите курсор в ячейку E10, выполните команду Формулы / Библиотека функций / Вставить функциюили нажмите кнопку Строки формул. В категорииФинансовыеиз списка выберите функцию МВСД( ). Нажмите ОК.
3) Введите необходимые аргументы: в поле аргумента Значения: массив исходных данных, выделив диапазон B9:B10 для расчета модифицированной нормы рентабельности за первый год реализации проекта; введите также значения процентных ставок в соответствующие поля аргументов функции.
Функция, в соответствии с синтаксисом, будет выглядеть следующим образом:
=МВСД(B9:B10;0,1;0,12).
4) 
Нажмите ОК.
В ячейке ввода E10 функция возвратит полученный результат – модифицированную внутреннюю норму рентабельности за первый год, равную минус 90%. Отрицательный знак означает, что получаемая прибыль не обеспечивает окупаемость первоначальных инвестиций к концу первого года.
В конце 4-го года проекта значение модифицированной нормы рентабельности становится положительным и равняется 3%. По завершении всего срока эксплуатации актива, т.е. в конце 6-го года, модифицированная внутренняя норма рентабельности составит 15%.
Полученный результат ниже предыдущего значения (IRR=17%), однако выше заданной (10%), поэтому даже при более пессимистических прогнозах реальных условий, которые могут сложиться на рынке, проект можно считать прибыльным.
Вопросы для самоконтроля
1. Назовите виды финансовых функций.
2. Что такое амортизация и функции, использующиеся для ее вычисления?
3. Чем определяется использование метода равномерного списания и метода быстрого износа активов?
4. На момент ввода актива в эксплуатацию его первоначальная стоимость составила 20000 тыс. руб. Полезный срок эксплуатации оборудования составляет 5 лет. В конце срока эксплуатации данного актива его ликвидационная стоимость предположительно составит 4000 тыс. руб. Определите величину амортизационных отчислений по каждому году, используя метод равномерного списания износа актива.
5. Определите величину амортизационных отчислений по каждому году для этой задачи, используя метод суммы лет.
6. Определите величину амортизационных отчислений по каждому году, используя метод снижающегося остатка.
7. Определите величину амортизационных отчислений по каждому году, используя метод двойного списания.
8. Постройте график амортизации активов по годам.
9. Определите величину амортизационных отчислений при использовании метода двойного процента со снижающегося остатка за период с 3 по 18 месяцы и с 1 по 160 день.
10. Перечислите финансовые функции Excel для анализа обыкновенных аннуитетов.
11. Назовите базовые аргументы финансовых функции Excel для анализа обыкновенных аннуитетов.
12. Фирма решила создать специальный фонд для погашения своих долгосрочных обязательств (кредитов, займов), срок погашения которых наступит, через 6 лет, путем периодического (ежегодного) пополнения депозита в банке. Начальная сумма депозита составляет 15000 тыс. руб. Размер ежегодных платежей – 2000 тыс. руб. Процентная ставка по банковскому депозиту – 12%. Необходимо определить величину фонда к концу 6-го года.
13. Определите число выплат (поступлений) денежных средств, если процентная ставка 0,12, периодический платеж 2000 тыс. руб., начальная и будущая величины потоков платежей составляют соответственно 15000 и 39141 тыс. руб.
14. Определите процентную ставку, если известно, что срок погашения обязательств составляет 6 лет, периодический платеж 1800 тыс. руб., начальная сумма депозита составляет 15000 тыс. руб., будущая стоимость 44215 тыс. руб.
15. Определите величину периодического платежа по ссуде при следующих условиях: величина будущей стоимости вложений 45026 тыс. руб., срок 6 лет, процентная ставка 12%, настоящая стоимость вложений 15000 тыс. руб.
16. Рассчитайте, какую сумму необходимо вложить в банк на депозит, чтобы получить через 6 лет величину вклада 49785 тыс. руб. при ежегодном пополнении вклада на 2000 тыс. руб., если годовая банковская ставка составляет 12%.
17. Какую сумму необходимо вложить в банк при начислении процентов и пополнении вклада по полугодиям?
18. Перечислите финансовые функции Excel для анализа эффективности инвестиционных проектов.
19. Фирма собирается вложить денежные средства в новое оборудование, приобретение, доставка и установка которого оценивается в 130000 тыс. руб. Ожидается, что его эксплуатация обеспечит на протяжении 6 лет получение чистой прибыли в 10000, 25000, 35000, 40000, 45000, 50000 тыс. руб. соответственно. Приемлемая норма рентабельности составляет 10%. Определите приведенную к настоящему моменту времени, величину потока будущих платежей с учетом заданной оценки.
20. Определите для задания 17 чистый приведенный доходв конце периода.
21. Определите для задания 17 индексы рентабельности для каждого года, начиная с 1-го.
22. Рассчитайте для задания 17 внутреннюю норму рентабельности для всех периодов, начиная с 3-го.
23. Рассчитайте для задания 17 модифицированную внутреннюю норму рентабельности (MIRR) для всех периодов, начиная с 1-го.
24. Результаты расчетов в п. 17 – 21 должны выглядеть следующим образом:
25. Сделать общий вывод по инвестиционному проекту.
176.59.110.41 © studopedia.ru Не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования. Есть нарушение авторского права? Напишите нам | Обратная связь.
Отключите adBlock!
и обновите страницу (F5)
очень нужно
По материалам studopedia.ru
Рассчитаем Чистую приведенную стоимость и Внутреннюю норму доходности с помощью формул MS EXCEL.
Начнем с определения, точнее с определений.
Чистой приведённой стоимостью (Net present value, NPV) называют сумму дисконтированных значений потока платежей, приведённых к сегодняшнему дню (взято из Википедии).
Или так: Чистая приведенная стоимость – это Текущая стоимость будущих денежных потоков инвестиционного проекта, рассчитанная с учетом дисконтирования, за вычетом инвестиций (сайт cfin.ru)
Или так: Текущая стоимость ценной бумаги или инвестиционного проекта, определенная путем учета всех текущих и будущих поступлений и расходов при соответствующей ставке процента. (Экономика. Толковыйсловарь. — М.: «ИНФРА—М«, Издательство «ВесьМир«. Дж. Блэк.)
Примечание1. Чистую приведённую стоимость также часто называют Чистой текущей стоимостью, Чистым дисконтированным доходом (ЧДД). Но, т.к. соответствующая функция MS EXCEL называется ЧПС() , то и мы будем придерживаться этой терминологии. Кроме того, термин Чистая Приведённая Стоимость (ЧПС) явно указывает на связь с Приведенной стоимостью.
Для наших целей (расчет в MS EXCEL) определим NPV так:
Чистая приведённая стоимость — это сумма Приведенных стоимостей денежных потоков, представленных в виде платежей произвольной величины, осуществляемых через равные промежутки времени.
Совет: при первом знакомстве с понятием Чистой приведённой стоимости имеет смысл познакомиться с материалами статьи Приведенная стоимость.
Это более формализованное определение без ссылок на проекты, инвестиции и ценные бумаги, т.к. этот метод может применяться для оценки денежных потоков любой природы (хотя, действительно, метод NPV часто применяется для оценки эффективности проектов, в том числе для сравнения проектов с различными денежными потоками).
Также в определении отсутствует понятие дисконтирование, т.к. процедура дисконтирования – это, по сути, вычисление приведенной стоимости по методу сложных процентов.
Как было сказано, в MS EXCEL для вычисления Чистой приведённой стоимости используется функция ЧПС() (английский вариант — NPV()). В ее основе используется формула:
CFn – это денежный поток (денежная сумма) в период n. Всего количество периодов – N. Чтобы показать, является ли денежный поток доходом или расходом (инвестицией), он записывается с определенным знаком (+ для доходов, минус – для расходов). Величина денежного потока в определенные периоды может быть =0, что эквивалентно отсутствию денежного потока в определенный период (см. примечание2 ниже). i – это ставка дисконтирования за период (если задана годовая процентная ставка (пусть 10%), а период равен месяцу, то i = 10%/12).
Примечание2. Т.к. денежный поток может присутствовать не в каждый период, то определение NPV можно уточнить: Чистая приведённая стоимость — это Приведенная стоимость денежных потоков, представленных в виде платежей произвольной величины, осуществляемых через промежутки времени, кратные определенному периоду (месяц, квартал или год). Например, начальные инвестиции были сделаны в 1-м и 2-м квартале (указываются со знаком минус), в 3-м, 4-м и 7-м квартале денежных потоков не было, а в 5-6 и 9-м квартале поступила выручка по проекту (указываются со знаком плюс). Для этого случая NPV считается точно также, как и для регулярных платежей (суммы в 3-м, 4-м и 7-м квартале нужно указать =0).
Если сумма приведенных денежных потоков представляющих собой доходы (те, что со знаком +) больше, чем сумма приведенных денежных потоков представляющих собой инвестиции (расходы, со знаком минус), то NPV >0 (проект/ инвестиция окупается). В противном случае NPV Похожие задачи
По материалам excel2.ru
