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

    Отношение выгоды/затраты (benefit to cost ratio, B/Cratio)

    Отношение прибыли/затраты или прибыли/издержки (benefit to cost ratio) является частным от деления дисконтированного потока выгод на дисконтированный поток затрат и рассчитывается по формуле:

     

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

    Отношение выгоды/затраты показывает, насколько  можно увеличить затраты и  при этом проект останется прибыльным предприятием. Т.е. если B/Cratio = 2, то это означает, что если более, чем вдвое увеличить затраты на проект, то он станет убыточным.

    Варианты  решений по проекту, в зависимости  от значений B/Cratio:

Варианты  значений показателя	Характеристика  проекта	Варианты решений
B/Cratio<1	проект является убыточным	необходимо  отказаться от проекта или изменить параметры проекта и повторно осуществить расчет
B/Cratio=0	проект на грани  убыточности
B/Cratio>1	проект прибыльный	следует принять  проект и продолжить анализ и оценку проекта

    Внутренняя норма доходности (internal rate of return, IRR)

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

    IRR = r, при котором NPV = 0

    Можно утверждать .что эта точка имеет конкретный экономический смысл дисконтированной «точки безубыточности» и называется внутренней нормой рентабельности – внутренней нормой доходности или прибыльности. Этот критерий позволяет инвестору данного проекта оценить целесообразность вложения средств. Если банковская учетная ставка больше IRR, то, по-видимому, положив деньги в банк, инвестор сможет получить большую выгоду.

    Если  нет возможности использовать для  расчета IRR специального финансового калькулятора, то можно применить формулу, для чего необходимо выбрать два значения коэффициента дисконтирования r1<r2 таким образом, чтобы в интервале (r1,r2) функция NPV = f(r) меняла свое значение с положительного на отрицательное. 

     

    Точность  вычислений по формуле обратно пропорциональна  длине интервала (r1,r2).

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

    Если  капиталовложения осуществляются только за счет привлечения средств, причем кредит получен по ставке i, то разность( IRR-i) показывает эффект инвестиционной деятельности. При IRR<i возврат вложенных средств невозможен.

    Варианты  принятия решений по проекту в  зависимости от значений IRR и r: 
 
 
 
 
 
 

Варианты  значений показателя	Характеристика  проекта	Варианты реений
IRR<r	проект является убыточным	необходимо  отказаться от проекта или изменить параметры проекта и повторно осуществить расчет
IRR=r	проект на грани  убыточности
IRR>r	проект прибыльный	Следует принять  проект и продолжить анализ и оценку проекта

 

    При одинаковых ставках дисконтирования  r предпочтение отдается проекту с большим IRR. В случае, если ставки дисконтирования r для проектов различны, то более эффективным признается проект, у которого больше разность IRR – r.

    Период  окупаемости (payback period, PBP)

    PBP = n, при котором:

      или (как частный случай): =  

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

Применение  методов инвестиционных расчетов на практике 

Задача №1. Анализ проекта.

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

	1 год	2 год	3 год	4 год
B (доходы), млн.рублей		2	5	7
C (затраты), млн. рублей	10

 

Ставка дисконтирования: r = 12%

Необходимо рассчитать основные критерии оценки проекта: NPV, PI, B/C, IRR и период окупаемости (PBP).

1. Открываем новый документ MS Excel и сохраняем его как task1.xls.
2. Вводим исходные данные и готовим для дальнейших расчетов соответствующие ячейки электронной таблицы, как показано на рис.1.1. В ячейку B4 вносим затраты в первый год, в ячейки C3, D3, E3 записываем доходы во 2-ой, 3-ий, 4-ый года соответственно; в ячейке B6 указываем ставку дисконтирования.

Рис. 1.1. Ввод исходных данных для задачи №1

3. Рассчитываем величину B-C (доход минус затраты) для каждого года.

- для этого  в ячейке B5 указываем соответствующую формулу: = B3 – B4;

- копируем  ячейку B5 в ячейки C5, D5, E5;

- в итоге  должны получиться результаты, указанные  на рис.1.2.

Рис.1.2. Расчет B – C для задачи №1

4. Рассчитаем NPV.
1. В ячейку B13 вставляем функцию ЧПС.

- Активируем  ячейку B13. Нажимаем кнопку «Вставка» в верхнем меню окна. В появившемся дополнительном меню выбираем строку «Функция». Появляется окно «Мастер функций» (рис.1.3). В строке «Категория» выбираем «Финансовые». Из предлагаемых ниже функций фибираем ЧПС и нажимаем кнопку «ОК» внизу окна «Мастер функций». Если в в версии MS Excel нет финансовых функций, то надо перейти к шагу 4.2.

Рис.1.3. Расчет NPV для задачи №1: вставка функции ЧПС

- В появившемся  окне «Аргументы функции» в  предлагаемых строках Ставка, Значение 1, Значение 2, Значение 3, Значение 4 указываем  ссылки на соответствующие ячейки, как показано на рис.1.4. и нажимаем  кнопку «ОК» в окне «Аргументы  функции».

Рис.1.4. Расчет NPV для задачи №1: аргументы функции ЧПС

- В ячейке  появится число 0,67, соответствующее  значению NPV для данного проекта. Переходим к шагу 5.

4.2.  Рассчитаем  дисконтированные значения величины  B – C в каждый год проекта.

- В ячейке  B11 вводим формулу, соответствующую формуле:

= B5/(1+$B$6)^B2

- Копируем  ячейку B11 в ячейки C11, D11, E11.

Рис.1.5. Изменение  формата ячеек

4.3.Рассчитаем  значение NPV, указав в ячейке B13 формулу, соответствующую формуле: = СУММ(B11:E11).

5.  Расссчитаем B/Cratio.

- Сначала необходимо  рассчитать сумму дисконтированных  величин доходов и затрат за  все годы реализации проекта.  Вводим в ячейку B9 формулу, соответствующую формуле (1): =B3(1+$B$6)^B2.

- Затем копируем  ячейку B9 в ячейки C9, D9, E9, B10, C10, D10, E10. Таким образом мы рассчитаем дисконтированные величины доходов и расходов за каждый год.

- Рассчитаем  сумму дисконтированных величин  доходов, указав в ячейке F9 формулу:

= СУММ(B9:E9).

- Рассчитаем  сумму дисконтированных величин  расходов, указав в ячейке F10 формулу:

= СУММ(B10:E10).

- Рассчитаем  B/Cratio, указав в ячейке B17 формулу, соответствующую формуле:

=F9/F10.

6. Рассчитаем PI. Для этого в ячейку B15 вводим формулу: =B13/B10, соответствующую формуле.
7. Рассчитаем IRR.
1. Активируем ячейку B19. Нажимаем кнопку «Вставка» в верхнем меню окна. В появившемся дополнительном меню вибираем строку «Функция». Появляется окно «Мастер функций». В строке «Категория» выбираем «Финансовые». Из предлагаемых ниже функций выбирем ВСД и нажимаем кнопку «ОК» внизу окна «Мастер функций». Если в версии MS Exel нет финансовых функций, то надо перейти к шагу 7.2.

- В появившемся  окне «Аргументы функции» в  предлагаемой строке «Значения»  указываем ссылку на соответствующие  значения (B5:E5), как показано на рис.1.6. и нажимаем кнопку «ОК» в окне «Аргументы функции».

Рис.1.6. Расчет IRR для задачи №1: вставка функции ВСД

- В ячейке  появится число 15,62, соответсвующее значению IRR для данного проекта. Переходим к шагу 8.

2. Если в версии MS Excel отсутствует финансовая функция ВСД, необходимо рассчитать IRR по формуле.

- Для этого  сначала необходимо найти ставку  дисконтирования r1, при которой NPV>0 и ставку дисконтирования r2, при которой NPV<0, при этом чем меньше разница между r1 и r2, тем точнее получится значение IRR. Подставляем в ячейку B6 различные значаения r, начиная с 12%, увеличивая каждый раз ее на 1%, и анализируем значение NPV в ячейке B13. Чем больше r, тем меньше NPV. При r=16% NPV становится отрицательным. Значит значение IRR лежит в интервале 15%<IRR<16%.

- Чтобы рассчитать  более точную IRR, меняем в ячейке B6 значение r, начиная с 15,1%, увеличивая его каждый раз на 0,1%. При r=15,7% NPV становится отрицательным.

- В ячейку  B22 заносим значение r1=15,6%, а в ячейку B24 – значение NPV1 при r1=15,6% (рис.1.7). В ячейку B23 заносим значение r2=15,7%, а в ячейку B25 – значение NPV2 при r2=15,7 (рис.1.7).

- В ячейке  B19 указываем формулу: =B21+B23/(B23-B24)*(B22-B21)

Рис.1.7. Расчет IRR для задачи №1 
 
 

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

- Подготовим  ячейки A26:E28 для расчета накопленных доходов и затрат, как показано на рис.1.8.

Рис.4.8. Подготовка к расчету накопленных доходов  и затрат для задачи №1 

- В ячейку  B27 вносим формулу: =B9

- В ячейку  C27 вносим формулу: =B27+C9

- Копируем  ячейку C27 в ячейки D27, E27

- Копируем  ячейки B27:E27 в ячейки B28:E28

- Получаем  результаты, как на рис.1.9.

Рис.1.9. Расчет накопленных доходов и затрат для задачи №1 

- Выделяем  ячейки A27:E28 и нажимаем кнопку мастера диаграмм на стандартной панели верхнего меню окна MS Excel. В открывшемся окне мастера диаграмм указываем тип диаграммы: График, и нажимаем кнопку «Далее»; в открывающихся друг за другом окнах мастера диаграмм при желании или необходимости вносим соответствующие корректировки, либо просто нажимаем кнопку «Далее». В последнем окне нажимаем кнопку «Готово». В результате получаем график, как на рис.1.10.