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

Функции этой группы позволяют находить величины, расчет которых весьма затруднен, если ведется вручную. К ним относятся:

1) общее число периодов постоянных выплат, необходимых для достижения заданного будущего значения; число периодов, через которое начальная сумма займа (вклада) достигнет заданного значения (функция КПЕР);

2) значение постоянной процентной ставки за один период для серии фиксированных периодических платежей; значение ставки процента по вкладу или займу (функция НОРМА).

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

КПЕР (ставка; плт; пс; бс; тип)

Ставка - процентная ставка за период.

Плт - выплата, производимая в каждый период; он не может меняться в течение всего периода выплат.

Пс - текущая стоимость (или общая сумма всех будущих платежей с настоящего момента).

Бс - будущая стоимость (баланс наличности, который должен быть достигнут после последней выплаты).

Тип - число 0 или 1, обозначающее, когда должна производиться выплата.

Задача 1.10

Рассчитать, через сколько лет вклад размером 1 млн. руб. достигнет величины 1 млрд. р., если годовая ставка процента по вкладу - 16,79% и начисление процента производится ежеквартально.

Решение. В соответствии с таблицей, при квартальном начислении процентов размер процента за период равен 16,79%/4.

КПЕР (16,79% / 4„ -1, 1000) = 168 - число кварталов. Число лет составит 168 /4=42.

5. Расчет периодических платежей. Оценка инвестиций в ms Excel

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

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

1. периодические платежи, осуществляемые на основе постоянной процентной ставки и не меняющиеся за все время расчета (функция ПЛТ);

2. платежи по процентам за конкретный период (функция ПРПЛТ);

3. сумму платежей по процентам за несколько периодов, идущих подряд (функция ОБЩПЛАТ);

4. основные платежи по займу (за вычетом процентов) за конкретный период (функция ОСПЛТ);

5. сумму основных платежей за несколько периодов, идущих подряд (функция ОБЩДОХОД).

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

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

Вычисленные платежи включают в себя сумму процентов по непогашенной части займа и основную выплату по займу. Обе величины зависят от номера периода и могут быть рассчитаны при помощи функций ПРПЛТ, ОСПЛТ. Накопленные за несколько периодов величины вычисляют функции ОБЩПЛАТ и ОБЩДОХОД.

Ниже приведена схема погашения займа в 70000 р., выданного сроком на 3 года под 17 % годовых, рассчитанная с помощью финансовых функций EX

CEL.

Таблица 3 - Схема погашения займа

Год	Сумма займа на начало года	Общая сумма платежа	Платежи по процентам	Сумма основного платежа по займу	Сумма займа на конец года
1	70000,00	31680,16	11900,00	19780,16	50219,84
2	50219,84	31680,16	8537,57	23142,78	27077,06
3	27077,06	31680,16	4603,10	27077,06	0
Итого		95040,47	25040,47	70000,00	-

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

ПЛТ (ставка; кпер; пс; бс; тип)

Ставка - процентная ставка по ссуде.

Кпер - общее число выплат по ссуде.

Пс - текущее значение (общая сумма, которую составят будущие платежи, называемая также основной суммой),

Бс - будущая сумма (баланс наличности, которой нужно достичь после последней выплаты). Если бс опущено, оно полагается равным 0 (нулю), т.е. будущая сумма ссуды, например, равна 0.

Тип - число 0 или 1, обозначающее, когда должна производиться выплата.

Задача 2.1

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

Решение. Определим общее число периодов начисления процентов и ставку процента за период по таблице 1. Эти величины составят соответственно 3 * 12 (аргумент кпер) и 12%/12 (аргумент норма). Аргумент тип=0, т.к. по условию это вклады постнумерандо. Рассчитаем величину ежемесячных выплат:

ПЛТ (12%/12,12 * 3„ 4000) = -92,86р.

Задача 2.2

Допустим, банк выдал ссуду 200 млн. р. на 4 года под 18 % годовых. Ссуда выдана в начале года, а погашение начинается в конце года одинаковыми платежами. Определите размер ежегодного погашения ссуды.

Решение. Ежегодные платежи составят:

ПЛТ(18%,4,-200)=74,35 млн.р.

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

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

ПРПЛТ (ставка; период; кпер; пс; бс; тип)

Ставка - процентная ставка за период.

Период - период, для которого требуется найти прибыль; должен находиться в интервале от 1 до кпер.

Кпер - общее число периодов выплат годовой ренты.

Пс - текущая стоимость (общая сумм всех будущих платежей с настоящего момента).

Бс - будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если аргумент бс опущен, то предполагается, что он равен 0 (например, бз для займа равно 0).

Тип - число 0 или 1, обозначающее, когда должна производиться выплата.

Задача 2.3

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

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

ПРПЛТ (10%/12,12 * 3,800) =-6,667 тыс. р.

Задача 2.4

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

Решение. Доход за последний год (шестой период) составил:

ПРПЛТ (17,5 %, 6,6„ 5000) = 664,81 р.

Ежегодно отчислялось ПРПЛТ (17,5 %, 6„ 5000) = -536,27 р.

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

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

Ставка - процентная ставка.

Кол_пер - общее количество периодов выплат.

Нз - текущее значение инвестиции.

Нач_период - номер первого периода, участвующего в вычислениях. Периоды выплат нумеруются начиная с 1.

Кон_период - номер последнего периода, участвующего в вычислениях.

Тип -- определяет, в какой момент производится выплата.

Задача 2.5

Пусть заем под недвижимость сделан на следующих условиях: процентная ставка - 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)

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

ОСПЛТ (ставка; период; кпер; пс; бс;тип)

Ставка - процентная ставка за период.

Период - задает период, значение должно быть в интервале от 1 до кпер.

Кпер - общее число периодов выплат годовой ренты.

Пс - текущее значение s общая сумма, которую составят будущие платежи,

Бс - будущая стоимость или баланс наличности который нужно достичь после последней выплаты. Если бс опущено, оно полагается равным 0 (нулю), то есть будущая стоимость займа, например, равна 0.

Тип - число 0 или 1 обозначающее, когда должна производиться выплата.

Задача 2.6

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

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

31680.16- 11900= 19780.16тыс. р.

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

ОСПЛТ (17 %, 1, 3, 70000) =-19 780,16 р.

ОСПЛТ (17 %, 2, 3, 70000) = -23 142,78 р.

ОСПЛТ (17 %, 3,3, 70000) =-27 077,06р.

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

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

Ставка - процентная ставка.

Кол_пер - общее количество периодов выплат.

Нз - текущее значение инвестиции.

Нач_период - номер первого периода, участвующего в вычислениях. Периоды выплат нумеруются начиная с 1.

Кон_период - номер последнего периода, участвующего в вычислениях.

Тип - определяет, в какой момент производится выплата.

Задача 2.7

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

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

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

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

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

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

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

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

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

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

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

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

Значения - массив или ссылка на ячейки, содержащие числовые величины, для которых вычисляется внутренняя скорость оборота средств.

Предположения - величина, о которой предполагается, что она близка к результату ВСД.

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

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

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

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

В большинстве случаев нет необходимости задавать прогноз для вычислений с помощью функции ВСД. Если прогноз опущен, то он полагается равным 0,1 (10 %).

Если ВСД выдает значение ошибка, или если результат далек от ожидаемого, можно попытаться выполнить вычисления еще раз с другим значением аргумента прогноз.

Задача 2.8

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

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

ВСД (А1:А5) = 9,25%.

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

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

ЧИСТВНДОХ (значения; даты; предп)

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

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

Прогноз - предполагаемое значение результата функции ЧИСТВДОХ.

Задача 2.9

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

Решение. Рассчитаем внутреннюю скорость оборота. Ставка дохода, соответствующая нулевой, будет равна:

ЧИСТВНДОХ (А2:Е2, А1:Е1) = 37,49 %

Этот проект имеет смысл, если рыночная норма дохода меньше, чем вычисленное значение (k< 37,49 %).

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

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

Значения - массив или ссылка на ячейки, содержащие числовые величины. Эти числа представляют ряд денежных выплат (отрицательные значения) и поступлений (положительные значения), происходящие в регулярные периоды времени.

Ставка_финанс - норма прибыли, выплачиваемой за деньги, находящиеся в наличном обороте.

Ставка_реинвест - норма прибыли, получаемой за деньги, находящиеся в наличном обороте при реинвестировании.

Задача 2.10

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

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

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

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

МВСД (В1:В6,10 %, 14 %) = 12,99 %.