2014-02-05
8577
Расчет периодических платежей. Оценка инвестиций в MS Excel
Функции EXCEL позволяют вычислять следующие величины, связанные с периодическими выплатами:
1) периодические платежи, осуществляемые на основе постоянной процентной ставки и не меняющиеся за все время расчета (функция ПЛТ);
2) платежи по процентам за конкретный период (функция ПРПЛТ);
3) сумму платежей по процентам за несколько периодов, идущих подряд (функция ОБЩПЛАТ);
4) основные платежи по займу (за вычетом процентов) за конкретный период (функция ОСПЛТ);
5) сумму основных платежей за несколько периодов, идущих подряд (функция ОБЩДОХОД).
Все эти величины вычисляются, например, при расчете схемы равномерного погашения займа. Допустим, что заем погашается одинаковыми платежами в конце каждого расчетного периода. Будущая стоимость этих платежей будет равна сумме займа с начисленными процентами к концу последнего расчетного периода, если в нем предполагается полное погашение займа.
Текущая стоимость выплат по займу должна равняться настоящей сумме займа. Если известна сумма займа, ставка процента, срок, на который выдан заем, то можно рассчитать сумму постоянных периодических платежей, необходимых для равномерного погашения займа с помощью функции ПЛТ.
Вычисленные платежи включают в себя сумму процентов по непогашенной части займа и основную выплату по займу. Обе величины зависят от номера периода и могут быть рассчитаны при помощи функций ПРПЛТ, ОСПЛТ. Накопленные за несколько периодов величины вычисляют функции ОБЩПЛАТ и ОБЩДОХОД.
Ниже приведена схема погашения займа в 70000 р., выданного сроком на 3 года под 17 % годовых, рассчитанная с помощью финансовых функций EX
CEL.
Таблица 3 - Схема погашения займа
| Год | Сумма займа на начало года | Общая сумма платежа | Платежи по процентам | Сумма основного платежа по займу | Сумма займа на конец года |
| 70000,00 | 31680,16 | 11900,00 | 19780,16 | 50219,84 | |
| 50219,84 | 31680,16 | 8537,57 | 23142,78 | 27077,06 | |
| 27077,06 | 31680,16 | 4603,10 | 27077,06 | ||
| Итого | 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 тыс р
