Овчаренко_4

3.1.5.2. Расчет платежей по процентам. Функция ПЛПРОЦ

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

Синтаксис ПЛПРОЦ(норма, период, кпер, те, бс, тип).

Функция предназначена для следующих расчетов.

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

ПЛПРОЦ(норма, период, кпер, те),

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

2. Допустим, необходимо вычислить доход, который приносят постоянные периодические выплаты за конкретный период. Этот доход представляет собой сумму процентов, начисленных на накопленную (с процентами) к данному моменту совокупную величину вложений. Расчет ведется по формуле:

ПЛПРОЦ(норма, период, кпер, , бс, тип).

Примеры.

Задача 1.

Вычислите платежи по процентам за первый месяц от трехгодичного займа в 800 тыс. руб. из расчета 10% годовых.

Решение.

Определяем число периодов и ставку за период: норма= 10%/12, кпер = 12 • 3. Расчет производим за первый период:

ПЛПРОЦ(10%/12,1,12-3,800) = -6.667 тыс. руб.

Задача 2.

Предположим, что за счет ежегодных отчислений в течение 6 лет был сформирован фонд в 5 000 тыс. руб. Определим, какой доход приносили вложения владельцу за последний год, если годовая ставка составляла 17.5%:

Решение.

Доход за последний год (6 период) составил ПЛПРОЦ(17.5%,6,6„5000) = 664.81 тыс. руб.

Ежегодно отчислялось ППЛАТ(17.5%,6„5000) = -536.27 тыс. руб.

Задача 3.

Рассчитаем колонку "Платежи по процентам" таблицы 3.4.

Решение.

Расчет производится следующим образом. Платежи по процентам за первый год 70000•0.17 = 11900 тыс. руб. Из суммы займа на начало года вычитаем общую сумму платежа, получаем сумму займа на конец 1-го года и начало 2-го: 70000•31680.16=50219.84. По этой сумме начисляем 17% за второй год: 50219.84 • 0.17 = 8537.57. Аналогично получаем сумму процентов за третий год: 27077.06'0.17 = 4603.1. Расчет в EXCEL имеет вид:

ПЛПРОЦ(17%,1,3,-70000) =11900 тыс. руб. ПЛПРОЦ(17%,2,3,-70000) = 8537.57 тыс. руб. ПЛПРОЦ(17%,3,3,-70000) = 4603.10 тыс. руб.

3.1.5.3. Расчет суммы платежей по процентам по займу. Функция ОБЩПЛАТ

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

Синтаксис ОБЩПЛАТ(ставк, кол_пер, нз, нач_период, он_период, тип).

Примеры.

Задача 1.

Пусть заем под недвижимость сделан на следующих условиях: процентная ставка — 9% годовых; срок — 30 лет, размер ссуды — 125 000 тыс. руб., проценты начисляются ежемесячно. Найти сумму выплат по процентам за 2-й год и за 1-й месяц займа.

Решение.

Определяем по таблице 3.3 общее число выплат и процентную ставку за период. Эти величины равны 30 • 12 и 9%/12 соответственно. Второй год платежей — это периоды от 13 по 24. Общая выплата по процентам за второй год составит

ОБЩПЛАТ(9%/12,30 • 12,125000,13,24,0) = -11135.23 тыс. руб.

Одна выплата за первый месяц составит ОБЩПЛАТ(9%/12,30-12,125000,1,1,0) = -937.5 тыс. руб.

Это же значение будет получено при расчете по формуле ПЛПРОЦ(9%/12, 1, 30-12, 125000).

3.1.5.4. Расчет основных платежей по займу. Функция ОСНПЛАТ

Функция вычисляет величину основного платежа (выплаты задолженности) по займу, который погашается равными платежами в конце или начале каждого расчетного периода, на указанный период.

Синтаксис ОСНПЛАТ(норма, период, клер, те, бс, тип).

Пример.

Задача 1.

Рассчитаем при помощи ОСНПЛАТ колонку "Сумма основного платежа по займу" таблицы 3.4.

Решение.

Сумма основного платежа по займу (выплата задолженности) получается как разность между фиксированной периодической выплатой и процентами по непогашенной части займа: например, для первого года

31680.16-11900 = 19780.16 тыс. руб.

Размер основных выплат по займу при помощи функции ОСНПЛАТ EXCEL определяется так:

ОСНПЛАТ(17%, 1, 3, 70000) = -19 780.16 руб. ОСНПЛАТ(17%, 2, 3, 70000) = -23 142.78 руб. ОСНПЛАТ(17%, З, 3, 70000) = -27 077.06 руб.

3.1.5.5. Расчет суммы основных выплат по займу. Функция ОБШДОХОД

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

Синтаксис ОБЩДОХОД(ставка, кол_пер, нз, нач_период,

кон_период, тип).

Пример.

Задача 1.

Допустим, выдана ссуда размером 1000 тыс. руб. сроком на 6 лет под 15% годовых; проценты начисляются ежеквартально. Определим величину основных выплат за 5-й год.

Решение.

Определяем по таблице 3.3 ставку процента за расчетный период (15%/4) и общее число расчетных периодов (6'4). Периоды с 17 по 20 составляют пятый год. Если ссуда погашается равными платежами в конце каждого расчетного периода, то размер выплаты задолженности за пятый год составит:

ОБЩДОХОД(15%/4,6•4,1000,17,20,0) = -201.43 тыс. руб.

Задания для расчетов.

1.Рассчитайте таблицу погашения займа размером 300 млн. руб., выданного на полгода под 20% годовых, если проценты начисляются ежемесячно. Используйте функции ППЛАТ, ПЛПРОЦ, ОСНПЛАТ.

3.2. Определение скорости оборота инвестиций

EXCEL содержит функции, позволяющие рассчитать:

1)внутреннюю скорость оборота для ряда последовательных периодических поступлений и выплат переменной величины (функция ВНДОХ);

2)внутреннюю скорость оборота для ряда нерегулярных поступлений

ивыплат переменной величины (функция ЧИСТВНДОХ);

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

Функции ВНДОХ и ЧИСТВНДОХ вычисляют итеративным методом норму дисконтирования R, при которой чистая текущая стоимость (NPV) равна 0. Если известна рыночная норма дохода k, то вычисленное значение можно использовать в качестве оценки целесообразности принятия того или иного проекта вложения средств.

Проект принимается, если R > к и отвергается, если R < к. Основанием для такого решения является то, что при R < k ожидаемых доходов от проекта оказывается недостаточно .для покрытия всех финансовых платежей, и принятие такого проекта оказывается экономически нецелесообразным. Соответственно, при R > k инвестор за счет доходов от проекта сможет не только выполнить все финансовые обязательства, но и получить дополнительную прибыль. Очевидно, что такой проект экономически целесообразен, и его следует принять.

3.2.1. Функция ВНДОХ

Функция вычисляет внутреннюю скорость оборота инвестиции (внутреннюю норму доходности) для ряда периодических выплат и поступлений переменной величины. Значение функции вычисляется из формулы (3.10) для NPV = 0:

0 =∑n	valuei	(3.12),
	i
i =1 (1 + R)

где п — количество выплат и поступлений; value, — значения выплат и поступлений;

R — внутренняя скорость оборота.

Синтаксис ВНДОХ(значения, предположение).

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

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

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

Функции НПЗ и ВНДОХ взаимосвязаны: НПЗ(ВНДОХ(В1:В6),В1:В6) = 0 для одинаковых значений выплат и

поступлений, находящихся в ячейках В1:В6.

Примеры.

Задача 1.

Предположим, затраты по проекту составят 500 млн. руб. Ожидаемые доходы составят 50 млн. руб., 100 млн. руб., 300 млн. руб., 200 млн. руб. в течение последующих 4 лет. Оценим экономическую целесообразность проекта по скорости оборота инвестиции, если рыночная норма дохода 12%.

Решение.

Пусть ячейки А1:А5 содержат значения -500, 50, 100, 300, 200. Внутренняя скорость оборота инвестиции составит

ВНДОХ(А1:А5) = 9.25%.

Это меньше, чем рыночная норма, поэтому проект должен быть отвергнут.

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

Рис. 3.1. Схема денежных потоков.

Задача 2.

Ожидается, что доходы по проекту в течение последующих 4 лет составят: 50000 тыс. руб., 100000 тыс. руб., 300000 тыс. руб., 200000 тыс.

руб. Определим, какими должны быть первоначальные затраты, чтобы обеспечить скорость оборота 10%.

Решение.

Для решения такой задачи необходимо использовать аппарат подбора параметра пакета EXCEL, вызываемый командой меню СЕРВИС, Подбор параметра, так как отсутствует соответствующая финансовая функция

EXCEL.

Пусть ячейки А2:А5 содержат значения 50000, 100000, 300000, 200000. Поместим в ячейку А1 предполагаемое значение затрат -300000. В ячейку В1 введем функцию ВНДОХ(А1:А5). Установив курсор в ячейку В1, выбираем в меню EXCEL команду Сервис, Подбор параметра и заполняем диалоговое окно следующим образом:

В результате в ячейке А1 будет получено значение затрат по проекту:

А1 = -489339.

Задания для расчетов.

1.Рассчитайте внутреннюю скорость оборота инвестиции, если затраты по проекту составили 200 млн. руб., а ожидаемые доходы в последующие 5 лет составят соответственно: 40, 60, 80, 90 и 100 млн. руб. Оцените экономическую эффективность проекта, если рыночная норма дохода составляет 10%. Ответ: 21% > 10% , поэтому проект экономически целесообразен.

2.Определите, какими должны быть первоначальные затраты по проекту, чтобы обеспечить следующие доходы: 2, 5, 6, 8 и 10 млн. руб. при норме дохода по проекту 9%. Используйте аппарат Подбора параметров.

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

3.2.2. Функция ЧИСТВНДОХ

Функция вычисляет внутреннюю скорость оборота для ряда нерегулярных поступлений и выплат переменной величины. Значение, вычисленное функцией ЧИСТВНДОХ, — это процентная ставка, соответствующая чистой текущей стоимости, равной нулю XNPV = 0

(формула (3.11)):

n	valuei
0 = ∑				(3.13),
		di −d1
i=1 (1 + R)
		365

где п — количество выплат и поступлений; di — дата i-й операции;

d1 — дата 1-й операции (начальная дата); valuei — сумма i-й операции;

R — внутренняя скорость оборота

Синтаксис ЧИСТВНДОХ({сумма0; сумма1 ;...;cyммaN}, {дата1;

дата2;...;датаN}}, предп).

Метод вычисления тот же, что и для функции ВНДОХ. Функции ЧИСТВНДОХ и ЧИСТНЗ взаимосвязаны : для одинаковых значений поступлений (выплат) и дат ЧИСТНЗ (ЧИСТВНДОХ (...),...) = 0

Примеры.

Задача 1.

Рассмотрим данные задачи для функции ЧИСТНЗ. Определим, при каких рыночных условиях этот проект будет экономически целесообразен.

Решение.

Рассчитаем внутреннюю скорость оборота. Ставка дохода, соответствующая нулевой XNPV, будет равна ЧИС-ТВНДОХ(А2:Е2, А1:Е1) = 37.49%. Этот проект имеет смысл, если рыночная норма дохода меньше, чем вычисленное значение (k < 37.49%).

3.2.3. Функция МВСД

Функция возвращает модифицированную внутреннюю скорость оборота средств для ряда периодических поступлении и выплат переменной

величины. При этом учитывается как стоимость инвестиции, так и доход, получаемый от реинвестирования. формула для вычисления функции МВСД имеет вид:

n

1

p

n−1

− ∑

valuei

(1 + r)

n

i

i =1 (1 + r)

−1

n

m

(1 + f )

(3.14),

∑

valuei

(1 + f )

i

i =1

где п

— количество выплат и поступлений;

valuep

— положительные значения (поступления);

valuem

— отрицательные значения (выплаты);

f — норма прибыли, выплачиваемой за деньги, находящиеся в

обороте;

r — норма прибыли, получаемой за деньги в обороте при реинвестировании.

Синтаксис МВСД(значения, финансовая_норма, реинвест_норма).

Аргумент значения должен содержать по крайней мере одно положительное и одно отрицательное значение для того, чтобы можно было вычислить модифицированную внутреннюю скорость оборота. В противном случае функция МВСД возвращает значение ошибки #ДЕЛ/0!

Примеры.

Задача 1.

Предположим, пять лет назад была взята ссуда в размере 1 млрд. руб. под 10% годовых для финансирования проекта, прибыль по которому за эти годы составила: 100, 270, 450, 340 и 300 млн. руб. Эти деньги были реинвестированы под 12% годовых. Найти модифицированную внутреннюю скорость оборота инвестиции.

Решение

Пусть на рабочем листе заем введен как -1000 в ячейку В1, и в ячейки В2:В6 введены значения прибыли за каждый год. Тогда модифицированная внутренняя скорость оборота за пять лет вычисляется следующим образом

МВСД(В1.В6,10%,12%) = 1225%.

Модифицированная внутренняя скорость оборота за пять лет, если бы ставка реинвестирования составляла 14%, вычисляется следующим образом

МВСД(В1 В6,10%,14%) = 1299%.

3.3. Оценка инвестиций на основе Таблицы подстановки

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

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

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

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

Далее технология построения и использования Таблицы подстановки EXCEL описана более подробно.

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

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

Для решения задачи целесообразно воспользоваться Таблицей подстановки EXCEL. Предварительно необходимо подготовить исходные данные на рабочем листе EXCEL, как показано на рис. 3.2.