Овчаренко_4

Рис. 3.2. Подготовка исходных данных для использования Таблицы подстановки.

Для заполнения таблицы необходимо выполнить следующую последовательность действии.

1. Ввести в ячейку D7 формулу для расчета периодических постоянных выплат по займу при условии, что он полностью погашается в течение срока займа,

=ППЛАТ(С4/12,СЗ • 12,С2)

2.Выделить диапазон ячеек, содержащий исходные значения процентных ставок и формулу для расчета — C7:D13. Исходные данные в нашем примере расположены в столбце С8:С13, поэтому результаты подстановки также будут расположены в столбце (D8.D13).

3.В меню Данные выберите команду Таблица подстановки. На экране появится диалоговое окно Таблицы подстановки (рис 3 3).

Рис. 3.3. Диалоговое окно команды Таблица подстановки.

Это окно используется для задания рабочей ячейки, на которую ссылается формула расчета. В нашем примере это ячейка С4, которую необходимо указать в поле Подставлять значения по строкам в

диалогового окна в абсолютных координатах.

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

5. При нажатии кнопки OK EXCEL заполнит столбец результатов, как показано на рисунке 3.4.

Рис. 3.4. Ежемесячные выплаты по займу для разных процентных ставок.

Обратите внимание, что полученные периодические выплаты имеют отрицательный знак, так как сумма займа в функции ППЛАТ была введена как положительное значение

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

Например, в нашем примере для расчета платежей по процентам за первый период для каждого значения процентной ставки в ячейку Е7 необходимо ввести формулу

=ПЛПРОЦ(С4/12,1,СЗ • 12,С2),

и повторить все шаги, как описано выше. Результаты расчета приведены на рис 3.5.

Рис. 3.5. Ежемесячные выплаты и платежи по процентам за первый месяц для разных процентных ставок.

Для расчета выплат по процентам для остальных периодов (со 2 по 36) необходимо подставить формулы в следующие ячейки справа от последней.

Полученная таблица будет автоматически пересчитана при изменении суммы и срока займа, то есть при внесении изменений в ячейки С2 и СЗ.

3.3.2. Построение Таблицы подстановки для двух переменных

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

Предположим, необходимо найти ежемесячные выплаты по займу размером 300 млн. руб. для различных сроков погашения и процентных ставок.

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

1.Ввести первое множество входных значений (процентные ставки) в столбец, например, в ячейки В8:В13.

2.Ввести второе множество входных значений (сроки погашения) в строку, расположенную выше и правее на одну ячейку от начала первого диапазона. В нашем примере это ячейки C7:F7.

3.Ввести формулу для расчета на пересечении строки и столбца, содержащих два множества входных значений, то есть в ячейку В7. Если исходные данные введены на рабочем листе EXCEL гак, как показано на рис. 3.6, то формула для расчета постоянных периодических выплат по займу при полном его погашении в течение срока займа выглядит следующим образом:

=ППЛАТ(С4/12,СЗ • 12.С2).

Результат подготовки таблицы подстановки с двумя переменными представлен на рис.3.6.

Рис. 3.6. Подготовка Таблицы подстановки с двумя переменными.

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

4 Далее необходимо выделить диапазон таблицы данных, включающий все входные значения и формулу расчета, то есть В7:F13

5 Выбрать в меню Данные команду Таблица подстановки и

заполнить диалоговое окно, как показано на рис 3.7.

Рис. 3.7. Диалоговое окно Таблицы подстановки для двух переменных.

6. При нажатии кнопки OK EXCEL выполнит расчет таблицы подстановки Результаты расчета приведены на рис 3.8.

Рис. 3.8. Результат расчета Таблицы подстановки с двумя переменными.

При изменении суммы займа EXCEL автоматически пере считает всю таблицу

3.33. Оценка эффективности инвестиций на основе Таблицы подстановки и функции НПЗ

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

Поскольку расчет чистой текущей стоимости связан с дисконтированием, то наиболее важным моментом здесь является выбор ставки процента, по которой производится дисконтирование Существуют различные методы определения нормы дисконтирования Поэтому при оценке эффективности капиталовложений важно проанализировать влияние различных процентных ставок на чистую текущую стоимость проекта В EXCEL такой расчет можно осуществить при помощи Таблицы подстановки и функции НПЗ Рассмотрим следующий пример

Предположим, что в конце года капиталовложения по проекту составят около 1280 млн. руб. Ожидается, что за последующие 3 года проект принесет следующие доходы 420, 490, 550 590 млн. руб. Рассчитаем чистую текущую стоимость проекта для различных норм дисконтирования и объемов капиталовложений

На рабочем листе EXCEL исходные данные представим следующим образом (рис. 3.9.).

В ячейку D3 поместим предполагаемую величину начальных затрат по проекту (1280 млн. руб.) со знаком «минус», поскольку этот поток движется противоположно ожидаемым доходам Это значение необходимо включить в список аргументов функции НПЗ, так как чистая текущая стоимость рассчитывается на начало года, а капиталовложения, по условию задачи, будут осуществлены в конце года В ячейки С9 F9 поместим различные объемы капиталовложений, как показано на рис. 3.9. Для расчета чистой текущей стоимости возьмем значения процентных ставок 13%, 138%, 15% В ячейку В9 с помощью Мастера функции поместим формулу для расчета:

=HП3(D2,D3,D4,D5,D6,D7)

Рис. 3.9. Исходные данные задачи.

Для построения Таблицы подстановки необходимо выделить диапазон ячеек B9F12, в меню Данные выбрать команду Таблица подстановки, а диалоговое окно заполнить следующим образом:

Рис. 3.10. Заполнение диалогового окна для решения задачи.

Результаты расчета приведены на рис 3.11 Полученные значения можно представить на графике (рис. 3.12).

Рис. 3.11. Результаты расчета

Рис. 3.12. Чистая текущая стоимость проекта для различных объемов капиталовложений и процентных ставок.

Очевидно, что максимальная величина чистой текущей стоимости достигается при минимальных капиталовложениях и минимальной ставке дисконтирования Анализируя полученные результаты, можно отметить, что некоторые варианты дают практически одинаковую величину чистой текущей стоимости, например, при капиталовложениях 1310 млн. руб и норме дисконтирования 13.8% достигается та же величина NPV, что и при инвестициях размером 1270 млн. руб. и ставке 15%.

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

3.4. Функции EXCEL для расчета амортизации

В EXCEL имеется группа функций для определения амортизации имущества различными методами. Из них наибольшее использование имеют функции АМР, АМГД, ДДОБ, ДОБ и ПДОБ. Эти функции позволяют рассчитывать амортизационные отчисления следующими методами:

1)равномерным (функция АМР);

2)методом уменьшающегося остатка (функция ДДОБ);

3)методом суммы чисел (функция АМГД);

4)методом постоянного учета амортизации с использованием функции ДОБ.

Кроме того, можно рассчитать сумму амортизационных отчислений за несколько идущих подряд периодов амортизации при помощи функции ПДОБ, если для расчета амортизационных отчислений за каждый период используется метод уменьшающегося остатка.

Общие аргументы функций представлены в таблице 3.5.

В качестве примера рассмотрим следующую задачу.

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

Таблица 3.5. Аргументы функций EXCEL для расчета

		амортизации.
	Аргумент	Значение аргумента
		Логическое значение, определяющее, следует ли
		переключаться на равномерный метод в случае,
	без_переключений	когда амортизируемая стоимость превышает
		накопленную сумму амортизации, по умолчанию
		равное 0 (переключаться на равномерный метод)
	время амортизации	Срок эксплуатации имущества (число периодов
	время полн аморт
	время эксплуатации	амортизации)
	жизнь
	кон_период	Конечный период для вычисления суммы
		накопленной амортизации
	коэффициент	Коэффициент ускоренной амортизации, по
		умолчанию равный 2
	месяц	Число месяцев в первом году эксплуатации
		имущества, по умолчанию равный 12
	нач_период	Начальный период для вычисления суммы
		накопленной амортизации
	Остаточная_стоимость
	ост_стоим	Остаточная стоимость имущества в конце срока
		эксплуатации
	ликвидная стоимость
	период	Период, для которого требуется вычислить
		амортизацию
	стоимость	Первоначальная стоимость имущества
	ликв_стоимость