Примеры практического расчета показателей эффективности инвестиций в MS Excel – Проектные методологии управления. Agile и Scrum

Примеры практического расчета показателей эффективности инвестиций в MS Excel - Проектные методологии управления. Agile и Scrum Архив процентов по вкладам

Внутренняя норма доходности на excel

Внутреннюю норму доходности можно довольно легко рассчитать при помощи встроенной финансовой функции ВСД (IRR) в MS Exel.

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

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

Синтаксис функции ВСД:

ВСД(Значения;Предположение)

где
Значения — это массив или ссылка на ячейки, содержащие числа, для которых требуется подсчитать внутреннюю ставку доходности, учитывая требования указанные выше;
Предположение — это величина, о которой предполагается, что она близка к результату ВСД:

  • Microsoft Excel использует метод итераций для вычисления ВСД. Начиная со значения Предположение, функция ВСД выполняет циклические вычисления, пока не получит результат с точностью 0,00001 процента. Если функция ВСД не может получить результат после 20 попыток, то выдается значение ошибки #ЧИСЛО!.
  • В большинстве случаев нет необходимости задавать Предположение для вычислений с помощью функции ВСД. Если Предположение опущено, то оно полагается равным 0,1 (10 процентов).
  • Если ВСД возвращает значение ошибки #ЧИСЛО! или если результат далек от ожидаемого, можно попытаться выполнить вычисления еще раз с другим значением аргумента Предположение.

Пример расчёта внутренней ставки доходности (на основе данных о денежных потоках по трём проектам, которые рассматривались выше):

Примеры практического расчета показателей эффективности инвестиций в ms excel

ЗАДАЧА 1

Определить экономическую целесообразность проекта в том случае, если первоначально необходимо вложить 12 000 000 руб., в конце второго года провести дополнительную инвестицию в размере 9 500 000 руб., а в конце третьего года — 5 000 000 руб. (рис. 6.1).

Е

F

3

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

12%

4

Инвестиции и поступления

5

Первоначальные затраты, руб

-12 000 000

б

1 ГОД

7

2 год

-9 500 000

8

3 год

-5 000 000

9

4 год

7 000 000

10

5 год

8 900 000

И

6 год

12 790 000

12

7 год

14 870 000

13

Величина ЧПС

11 572 727

14

NPV=4nC-l

-427 273

,5

Проект экономически нецелесообразен

Задача 1

Рис. 6.1. Задача 1

Финансовые поступления от проекта начинаются в конце четвертого года и за последующие четыре года составят 7 000 000, 8 900 000, 12 790 000 и 14 870 000 руб. соответственно. Ставка дисконтирования составляет 12%. Построить также график инвестиций и поступлений.

Ответ: Проект экономически нецелесообразен. Величина NPV = = -427 273 руб.

ЗАДАЧА 2

Определить экономическую целесообразность проекта. Первоначально необходимо вложить 10 000 000 руб., в конце второго года необходима дополнительная инвестиция в размере 3 500 000 руб., а в конце пятого года — инвестиция в размере 4 000 000 руб.

Финансовые поступления от проекта начинаются в конце третьего года и за 7 лет составят по 5 500 000 руб. ежегодно. Норма дисконтирования составляет 15% (рис. 6.2).

Ответ: Проект экономически целесообразен. Величина NPV = = 2 667 103 руб.

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

Для расчета этого показателя используется формула

ПТ PV пт ЧПС РІ =– или РІ =—-

‘о ‘о

А

в

С

29

Ставка

15,00%

30

Инвестиции

Поступления

Сальдо

31

Первоначальные затраты, Iq. руб

-10 000 000

32

1 год

33

2 год

•3 500 000

-3 500 000

34

Згод

5 500 000

5 500 000

35

4 год

5 500 000

5 500 000

36

5 год

-4 000 000

5 500 000

1 500 000

37

6 год

5 500 000

5 500 000

38

7 год

5 500 000

5 500 000

39

8 год

5 500 000

5 500 000

40

9 год

5 500 000

5 500 000

41

Величина ЧПС

12 667 103

Проект экономически целесообразен

42

NPV=4nC-l

2 667 103

Рис. 6.2. Задача 2

В том случае, если величина критерия РІ > 1, современная стоимость денежных потоков превышает величину первоначальных инвестиций, обеспечивая тем самым наличие положительной величины NPV. При этом норма доходности превышает заданную, и проект отличается экономической целесообразностью.

При РІ = 0 величина NPV = 0. В этом случае инвестиции не приносят дополнительной прибыли, а поступления от проекта только покрывают произведенные затраты.

В том случае, если РІ < 1, проект не обеспечивает заданного уровня рентабельности и его следует отклонить.

Читайте также:  Вклад «Сберегательный сертификат» Сбербанка России - действие предложения завершено 24.08.2018

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

‘о

ЗАДАЧА 3

Определить индекс рентабельности инвестиции первоначальным размером 100 млн руб., если ожидаемые ежеквартальные доходы составят соответственно 10, 35, 60, 76,8 и 98 млн руб. Издержки привлечения капитала на данном этапе равняются 21,5% годовых. Сделать вывод о целесообразности проекта.

Ответ: Р1 = 2,3. Проект выгоден.

ЗАДАЧА4

Рассчитать индекс рентабельности коммерческого проекта, затраты по которому в начале его реализации составят 100 млн руб., а предполагаемые доходы за второй и третий год реализации проекта — 140 и 170 млн руб. Норма дисконтирования — 20% годовых. Сделать вывод о целесообразности проекта.

Ответ: Р1= 1,96. Проект экономически целесообразен.

ЗАДАЧА5

Определить индекс рентабельности инвестиции размером 200 млн руб., если ожидаемые ежемесячные доходы за первые пять месяцев составят соответственно 20, 40, 50, 80 и 100 млн руб.

Издержки привлечения капитала равняются 13,5% годовых. Сделать вывод о целесообразности проекта (рис. 6.3).

Ответ: Р1= 1,39. Проект экономически целесообразен.

ЗАДАЧА 6

Определить чистую текущую стоимость проекта на 01.01.2021г., затраты по которому на 20.12.2021 г. составят 100 млн руб., а поступления натри даты 2021 г. представлены в таблице. Ставка банка 12%.

Ответ: 3,77 млн руб.

ЗАДАЧА7

Определите, какие поступления должны были быть 01.09.2005 г. с тем, чтобы доход от реализации проекта составил 1 млн руб.

В табл, на рис. 6.4 приведены данные о поступлении денежных средств от реализации проекта, в который в конце декабря 2004 г. было вложено 10 млн руб. Ставка дисконтирования 10% (рис. 6.4).

Доход от реализации это все поступления минус все затраты. Для решения использовать финансовую функцию и Подбор параметра (рис. 6.5 и 6.6).

Ответ: 10 млн руб.

139

Е

F

2

Задача 3

3

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

21,5%

4

Первоначальные затраты ь млн руб.

100

5

1 квартал

10

6

2 квартал

35

7

3 квартал

60

8

4 квартал

77

9

5 квартал

98

10

Величина ЧПС

230

11

Индекс рентабельности,

Р1=ЧПС/ 1

2,30

-12-

Проект экономически целесообразен

N

2

Задача 4

3

Ставка

20%

4

Первоначальные затраты млн руб.

100

5

1Г0Д

6

2 год

140

7

3 год

170

8

Величина ЧПС

196

9

Индекс рентабельности, Р1=ЧПС/ 1

1,96

10

Проект экономически целесообразен

Рис. 6.3. Задачи 3, 4, 5

Е

F

16

Задача 5

17

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

13,50%

18

Первоначальные затраты млн руб.

200

19

1 месяц

20

20

2 месяц

40

21

3 месяц

50

22

4 месяц

80

23

5 месяц

100

24

Величина ЧПС

278

25

Индекс рентабельности,

Р1=ЧПС/ 1

1,39

26

Проект экономически целесообразен

6.2. Примеры практического расчета показателей эффективности инвестиций в MS Excel

Задача 6

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

12,00%

Дата

Затраты

01.012021

20.122021

-ню

01.03.2021

18

15.04.2021

40

30.062021

51

Вел им ина ЧИСТНЗ

3,77

Задача 7

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

10,00%

Дата

Затраты

30.12.2004

-10,00

01.03.2005

2,00

01.06.2005

-3,00

01.09.2005

1,00

01.12.2005

2,00

Величина ЧИСТНЗ

Доход от реализации

Рис. 6.4. Задачи 6 и 7

Графическое окно Подбор параметра

Рис. 6.5. Графическое окно Подбор параметра

Е

F

15

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

10,00%

16

Дата

Затраты

17

30.12.2004

-10,00

18

01.03.2005

2,00

19

01.06.2005

-3,00

20

01.09.2005

1,00

21

01.12.2005

2,00

22

Величина ЧИСТНЗ

-8,14

23

Доход от реализации

-8,00

Е

F

15

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

10,00%

16

Дата

Затраты

17

30.12.2004

-10,00

18

01.03.2005

2,00

19

01.06.2005

-3,00

20

01.09.2005

10,00

21

01.12.2005

2,00

22

Величина ЧИСТНЗ

0,30

23

Доход от реализации

1,00

Рис. 6.6. Результат до применения (а) и после применения (б) Подбора параметра

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

IRR = г, при котором NPV=f(r) = 0.

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

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

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

В аргументе значение должно содержаться по крайней мере одно положительное и одно отрицательное значения.

Читайте также:  Как открыть счет в польском банке: правила для иностранцев

Начиная со значения предположения, функция ВСД выполняет циклические вычисления до получения результата с точностью 0,00001%, если этот результат не получается за 20 попыток, то возвращается значение ошибки #ЧИСЛО.

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

ЧПС (ВСД(значения); значения) = 0

Внутренняя ставка доходности инвестиций ВСД (IRR) — та ставка дисконта, при которой чистый приведенный доход равен нулю (NPV = 0), т.е. дисконтированные доходы от проекта равны инвестиционным затратам.

Внутренняя ставка доходности инвестиций ВСД (IRR) — та ставка дисконта, при которой чистый приведенный доход равен нулю (NPV = 0), т.е. дисконтированные доходы от проекта равны инвестиционным затратам.

Дисконтированные доходы

Примеры практического расчета показателей эффективности инвестиций в MS Excel - Проектные методологии управления. Agile и Scrum

ВСД

ЗАДАЧА 8

Определить экономическую целесообразность проекта по внутренней скорости оборота IRR. Затраты по проекту равны 700 млн руб.

Предполагаемые доходы в течение последующих шести лет составляют 100, 135, 195, 255, 350 и 200 млн руб. соответственно. Рыночная норма дохода — 19% годовых.

Используя логическую функцию ЕСЛИ, можно ответить на вопрос о целесообразности проекта.

Ответ: ВСД = 16,05% < 19%. Значит, проект нецелесообразен.

ЗАДАЧА 9

Рассчитать внутреннюю ставку доходности инвестиции ВСД, если первоначальные затраты по проекту составили 600 млн руб., а ожидаемые доходы за последующие шесть лет составят 140, 170, 280, 350, 300, 50 млн руб. Определите экономическую эффективность проекта, если рыночная норма дохода составляет 20% (рис. 6.7).

Ответ: ВСД (IRR) = 26,27%, что больше чем 20%, поэтому проект экономически целесообразен.

Задача 8

Год

Затраты, млн руб.

Рыночная ставка дохода

Инвестиция

-700

19%

Доходы

1

100

2

135

3

195

4

255

5

350

Внутренняя ставка доходности BCA(IRR)

6

200

16,05%

Проект нецелесообразен

Задача 9

Год

Затраты, млн руб.

Рыночная ставка дохода

Инв

-600

20%

Дох

1

140

2

170

3

280

4

350

5

300

Внутренняя ставка доходности BCfl(IRR)

6

50

26,27%

Проект целесообразен

Рис. 6.7. Задачи 8 и 9

ЗАДАЧА 10

Определите, какими должны быть первоначальные затраты по проекту, для того чтобы при внутренней норме дохода 10% обеспечить следующие поступления в течение 4-х лет: 700 000, 500 000, 300 000, 200 000 руб. (рис. 6.8). Использовать финансовую функцию и Подбор параметра.

Ответ: -1 млн 412 тыс. руб.

Примеры практического расчета показателей эффективности инвестиций в MS Excel - Проектные методологии управления. Agile и Scrum

До применения Подбора параметра

Е

F

G

Н

16

Год

Затраты,тыс. руб.

Рыночная ставка дохода

17

Инвестиция

-1

19%

18

Доходы

1

700

19

2

500

20

а

апо

Внутренняя ставка доходности BCfl(IRR)

21

4

200

69971%

После применения Подбора параметра

Е

F

G

Н

16

Год

Затраты,тыс. руб.

Рыночная ставка дохода

17

Инвестиция

-1412

19%

18

Доходы

1

700

19

2

500

20

3

300

Внутренняя ставка доходности ВСД (1RR)

21

4

200

10%

6)

Рис. 6.8. Подбор параметра до его применения (а) и после применения (б)

ЗАДАЧА 11

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

Фирма, предлагая внедрение новой технологии, намерена инвестировать в нее 01.01.2021 г. сумму 10 млн руб. При этом предполагается, что ее внедрение даст доходы:

3 марта 2021 г. — 2,75 млн руб., 30 октября 2021 г. — 4,25 млн руб., 15 февраля 2021 г. — 3,25 млн руб., 1 апреля 2021 г. — 2,75 млн руб.

Рыночная ставка доходности составляет 15% (рис. 6.9).

Ответ: внутренняя ставка доходности (ЧИСТВНДОХ) = 37% > 15%. Проект целесообразен.

Задача 11

Рыночная ставка

15,00%

Дата

Затраты, млн руб.

01.01.2021

-10,00

03.03.2021

2,75

30.10.2021

4,25

15.02.2021

3,25

01.04.2021

2,75

ЧИСТВНДОХ

37%

Рис. 6.9. Задача 11

Срок окупаемости — период окупаемости инвестиций (Payback Period, РР) — время, которое требуется, чтобы инвестиция обеспечила достаточные поступления денег для возмещения инвестиционных расходов. Вместе с чистым дисконтированным доходом (NPV* и внутренней ставкой доходности (IRR) используется как инструмент оценки инвестиций.

Общая формула для расчета срока окупаемости инвестиций:

п

TL,K = п, при котором ^CFt > ;

z=l

где Г. к — срок окупаемости инвестиций; п — число периодов: CF, — і іри і ок денежных средств в период /; Ift величина исходных инвестиций в нулевой период.

Схема расчета срока окупаемости следующая:

  • 1. Рассчитать дисконтированный денежный поток доходов по проекту исходя из ставки дисконта и периода возникновения доходов.
  • 2. Рассчитать накопленный дисконтированный денежный поток как алгебраическую сумму затрат и потока доходов по проекту.
  • 3. Накопленный дисконтированный денежный поток рассчитывается до получения первой положительной величины.
  • 4. Уточняется срок окупаемости по формуле.

ЗАДАЧА 12

Рассчитать срок окупаемости проекта, для которого размер инвестиций составляет 1 млн руб., а денежные поступления в течение пяти лет соответственно 200, 300, 500, 400, 100 тыс. руб.; ставка дисконтирования 15%.

Читайте также:  Процент в банке вклад

Формулы в ячейках:

  • — в ячейке 14 имеем = ЧПС(Н6; 13)
  • — в ячейке J4 имеем = ЧПС(Н6; 13: J3)
  • — в ячейке К4 имеем = ЧПС(Н6; 13:КЗ)
  • — в ячейке L4 имеем = ЧПС(Н6; 13: L3)
  • — в ячейке М4 имеем = ЧПС(Н6; 13:МЗ)

В ячейке 15 имеем = 14 $Н$4. Далее растянуть вправо до ячейки М5 (рис. 6.10).

В ячейке М7 имеем = L2 — L5/(—L5 М5). Данная формула позволяет рассчитать период окупаемости.

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

G

н

1

J

К

L

М

2

Период

1

2

3

4

5

3

Денежный поток, тыс.руб.

-1 000,00

200

300

500

400

100

4

Дисконтированные денежные поступления, они же приведенная стоимость (они же ЧПС=Р/), ты с. руб.

-1 000,00

173,91

400,76

729,51

958,22

1 007,93

5

Накопленный денежный поток (он же чистый дисконтированный доход, он же NPV=PV-IO), тыс.руб.

-1 000,00

-826,09

-599,24

-270,49

-41,78

7,93

6

Ставка дисконтирования, %

15%

7

Период окупаемости, лет

4,84

8

Вывод: инвестиции полностью окупаются в интервале между 4-м и 5-м годом.

Рис. 6.10. Расчет периода окупаемости

В данном случае это момент времени между 4-м и 5-м годом, а не в конце 3-го, как могло показаться.

Рис. 6.11 иллюстрирует процесс расчета периода окупаемости.

В рассматриваемом случае Период окупаемости = 4 — 41,78/(—41,78 7,93) = 4,84 года.

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

Определение периода окупаемости путем линейной экстраполяции

Рис. 6.11. Определение периода окупаемости путем линейной экстраполяции:

Н — это предшествующий год; Л — последующий год на интервале изменения знака NPV

Чистый приведенный доход (NPV)

ю -200

J -400

Л

-600

> z -800

1-41;

те]

___I “7 QQ 1__

|/,Э э |р

1

2

3

4

5

-?-Ряд!

-1000,00

-826,09

-599,24

-270,49

-41,78

7,93

Время, годы

Рис. 6.12. График изменения NPVво времени

ЗАДАЧА 13

Определить период окупаемости инвестиции, ставка дисконтирования 8%.

Размер инвестиции — 115 000 руб.

Доходы от инвестиций: в первый год: — 32 000 руб.; во второй год — 41 000 руб.; в третий год — 43 750 руб., в четвертый год — 38 250 руб.

Ответ: 3,55 года.

ЗАДАЧА 14

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

Норма дисконтирования — 20%. Потоки платежей по проекту представлены в таблице.

Проект целесообразен, если Чистый дисконтированный доход больше 0, а Внутренняя ставка доходности больше Рыночной ставки дисконтирования. Рассчитать также модифицированную внутреннюю ставку доходности (рис. 6.13).

Ответ: показатели экономической эффективности проекта составляют: АРИ ЧИСТНЗ) = — 838 546,05 руб. < 0; Внутренняя ставка доходности (ЧИСТВНДОХ) = 18,14% < 20%.

На основе этих показателей делается вывод — проект экономически нецелесообразен.

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

20,00%

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

Даты платежей

Суммы платежей, руб.

1

12.05.2000

-20 000 000

2

12.05.2001

2 000 000

3

12.05.2002

12 000 000

4

12.05.2003

7 810 000

5

12.05.2004

5 880 000

6

12.05.2005

4 500 000

Сумма:

12 190 000

Величина ЧИСТНЗ = NPV

Проект нецелесообразен

-838 546,05

Внутренняя ставка доходности ЧИСТВНДОХ

18,14%

Рис. 6.13. Оценка эффективности проекта по двум параметрам

ЗАДАЧА 15

Определить, какой из инвестиционных проектов (А или В), рассчитанных на шесть лет, более выгоден. Норма дисконтирования составляет 15%. Потоки платежей по проектам представлены на рис. 6.14.

Ответ:

Первоначальная инвестиция для обоих проектов одинакова (по 10 млн руб.), суммарные поступления (номинально, без учета моментов времени) по проекту В больше (19 млн), чем по проекту А (16 млн). Однако по проекту А большие поступления приходят раньше, чем по проекту в.

Показатели экономической эффективности проекта А составляют: ЛРК(ЧИСТНЗ) = 1 739 206 руб.; Внутренняя ставка доходности = = 24,8%.

Показатели экономической эффективности проекта В составляют: АРИЧИСТНЗ) = 400 200 руб.; Внутренняя ставка доходности = 16,2%.

Ставка дисконтирования 15,00%

Дата платежа

Сумма платежа, руб.

Сумма платежа, руб.

Проект А

ПроектВ

16.01.2000

-10 000 000

-10 000 000

1

16.01.2001

6 500 000

3 000 000

2

16.01.2002

5 000 000

3 000 000

3

16.01.2004

3 000 000

3 500 000

4

16.01.2006

1 000 000

4 000 000

5

16.01.2008

500 000

5 500 000

Сумма:

6 000 000

9 000 000

Величина ЧИСТНЗ = NPV

1 739 206

400 200

Внутренняя ставка доходности, % чиствндох

24,8

16,2

Рис. 6.14. Оценка эффективности двух проектов и выбор лучшего

Первый проект экономически более выгоден.

Приложение

Оцените статью
Adblock
detector