Произведем расчет процентов по кредиту.

КРЕДИТ взят в 2009г. 334875 руб. на 24 месяца(2 года) под 18 % годовых.

В ячейке В34(В35) находится ссылка на ячейку(=В16), в которой рассчитано значение заемного капитала (кредита). В диапазон ячеек В37:В59 вносятся формулы - ссылки на ячейки Е36:Е58, в которых рассчитывается остаток задолженности по кредиту. Потому что задолженность по кредиту, начиная со второго месяца начинает умень шаться на сумму выплаченных по кредиту процентов.

Сумма процентов по кредиту рассчитывается в ячейках D36:D59 по формуле =B36*$B$7/12, которая копируется на весь диапазон ячеек(копируется маркером заполнения ). В этой формуле мы сумму кредита 334875 руб * на среднегодовую ставку по банковскому кредиту 18%(0,18) и делим на 12 месяцев(один год). Так как ставка по кредиту в течении 24 месяцев не изменяется по ячейка $B$7фиксируется(абсолютная ссылка). Далее формула копируется на весь диапазон ячеек.

Остаток задолженности по кредиту рассчитывается в ячейках Е36:Е58 по формуле =B36-C36, которая копируется на весь диапазон ячеек.

Так как по условию задачи выплата процентов и основного долга осуществляется ежемесячно, и основной долг выплачивается равными долями, рассчитаем столбец Выплата основного долга, в ячейку С36 записываем формулу =$B$35/24 (сумма кредита/ на количество месяцев кредитования). В нашем случае 334875 руб / 24 месяца, получим 13953,13 руб – сумма выплата основного долга. Так как выплачивается равными долями , формула =$B$35/24 не изменяется на всем диапазоне ячеек С36:С59.

Результаты всех вычислений представлены ниже:

Расчет показателей эффективности проекта

Для расчета показателей эффективности необходимо привести все данные к одному базисному году.

Примем за такой год - год инвестирования.

Для нахождения коэффициентов дисконтирования необходимо определить норму дисконта (q):

q=W*Sk*(1-T)/P*Sp, где

Назначение	Обозначение	Ячейка в таблице Excel	Значение
Доля капитала, взятого в кредит	W	В16	334875 руб
Процент по кредиту	Sk	В7	18%
Ставка налога на прибыль	Т	В6	20%
Доля капитала в привилегированных акциях	Р	В17	252625 руб
Ставка дивиденда по акциям	Sp	В5	4%

Таким образом, норма дисконта q рассчитывается в ячейке В63 по формуле =B16/(B16+B17)*B7*(1-B6)+B17/(B16+B17)*B5 и имеет значение q=0,099.

Коэффициент дисконтирования (kd):

kd=(1+q)^-t где t - это положение текущего года по отношению к базисному

Коэффициент дисконтирования kd рассчитывается в ячейках В64:G64 по формуле =(1+$B$63)^(B3-$B$3),которая копируется на весь диапазон ячеек.

Результаты расчетов q и kd приведены ниже:

С учетом соответствующих коэффициентов дисконтирования можно найти следующие показатели эффективности проекта:

1. Чистая дисконтированная стоимость

NPV= ∑NCFi*kdi-∑ICi*kdi

NCF - чистый денежный поток

IC - инвестиции (капитальные вложения)

Если данные показатель > 0, то данный проект эффективен, т.к. дисконтированные доходы по проекту превышают расходы.

Рассчитывая чистую дисконтированную стоимость учтем, что значения NCF находится в ячейке B27, а значения IC – в ячейках C27:G27. Чистая дисконтированная стоимость рассчитывается в ячейке В71 по формуле =СУММПРОИЗВ(B69:G69;B64:G64)-B70*B64. В ячейку С71 заносится логическая формула для определения эффективности проекта по показателю NPV, =ЕСЛИ(B71>0;"проект эффективен";"проект НЕ эффективен")

Результаты расчетов приведены ниже:

2. Внутренняя норма доходности (IRR)

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

Для нахождения показателя воспользуемся равенством:

NPV= ∑NCFi*kdi-∑ICi*kdi

Подберем соответствующие значения q` при котором NPV`>0 и стремится к 0, и q``при котором NPV``<0 и стремится к 0. Причем q`от q`` отличается на 1 разрядную единицу.

Проект эффективен, если IRR>q

Подбор произведем на Листе 1(2)

В результате получим:

3. Коэффициент рентабельности (BCR)

Если данный показатель > 1, то данный проект эффективен, т.к. дисконтированные доходы по проекту превышают расходы.

Коэффициент рентабельности (BCR) рассчитывается в ячейке В73 по формуле =СУММПРОИЗВ(B69:G69;B64:G64)/СУММПРОИЗВ(B64;B70)

Результаты вычислений представлены ниже.

4. Срок окупаемости (PBP)

Показатель показывает - за какой срок окупится данный проект. Значение показателя определяется суммой нарастающих итогов денежных потоков до того момента, когда NPV=0

Т.е.

год	формула расчета по годам
0	(NCF0-IC0)*kd0
1	(NCF0-IC0)*kd0 + (NCF1-IC1)*kd1
2	(NCF0-IC0)*kd0 + (NCF1-IC1)*kd1 + (NCF2-IC2)*kd2
3	(NCF0-IC0)*kd0 + (NCF1-IC1)*kd1 + (NCF2-IC2)*kd2 + (NCF3-IC3)*kd3
4	(NCF0-IC0)*kd0 + (NCF1-IC1)*kd1 + (NCF2-IC2)*kd2 + (NCF3-IC3)*kd3 + (NCF4-IC4)*kd4
5	(NCF0-IC0)*kd0 + (NCF1-IC1)*kd1 + (NCF2-IC2)*kd2 + (NCF3-IC3)*kd3 + (NCF4-IC4)*kd4 + (NCF5-IC5)*kd5

Срок окупаемости (PBP) рассчитываем в ячейке В76 по формуле =СУММПРОИЗВ($B$75:B75;$B$64:B64). Формула копируется на диапазон ячеек В76:G76.

Результат вычислений PBP

По рассчитанным данным строится график с отображением срока окупаемости проекта.

Расчет (подбор параметра) – npv’

Расчет (подбор параметра) – q’

13