Разработки планов погашения кредитов

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

Основная задача планирования поступлений (выплат) по кредитам сводится к исчислению составных элементов платежей и распределению их во времени.

Для этих целей в ППП EXCEL реализована специальная группа функций, формат которых приведен в табл. 2.4.

Таблица 2.4

Функции для разработки планов погашения кредитов

Наименование функции

Формат функции

англоязычная версия

русская

версия

IPMT

ПЛПРТ

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

РРМТ

осплт

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

CUMIPMT

ОБЩПЛАТ

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

CUMPRINC

ОБЩДОХОД

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

К уже известным вам аргументам финансовых функций добавляются:

  • нз — начальное значение (текущая сумма);
  • кол_пер — номер периода выплаты;
  • нач_период — номер периода первого платежа;
  • кон_период — номер периода последнего платежа. Рассмотрим механизм работы функций на следующем примере.

Пример 2.16

Банком выдан кредит в 10 000,00 на 5 лет под 15 % годовых, начисляемых 1 раз в конце каждого периода. По условиям договора кредит должен быть погашен равными долями в течение указанного срока, выплачиваемыми в конце каждого периода. Разработать план погашения кредита для банка.

Прежде всего, необходимо определить величину периодического платежа. Для этого воспользуемся уже знакомой нам функцией ППЛАТ(). Будущая величина сделки на данном этапе анализа нам пока неизвестна. Однако она не является обязательным аргументом, поэтому мы можем ее опустить.

Периодический платеж по данной операции составит:

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

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

Для осуществления подобных расчетов применяются функции ПЛПРТО и ОСПЛТ(), позволяющие выделить для заданного периода из платежа его процентную и основную части.

Функция ПЛПРТ(ставка; кол_пер; кпер; пс; бс; [тип])

Функция ПЛПРТО выделяет из периодического платежа его процентную часть.

Определим процентную часть платежа на первый период для рассматриваемого примера:

Функция ОСПЛТ(ставка; кол_пер; кпер; пс; бс; [тип])

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

Определим основную часть платежа:

Нетрудно заметить, что:

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

Две оставшиеся функции этой группы — ОБЩПЛАТО и ОБЩДО- ХОД() — предназначены для вычисления накопленных процентов и суммы погашенного долга между любыми двумя периодами выплат.

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

Функция ОБЩПЛАТ(ставка; кол_пер; нз; нач_период; кон_пе- риод; [тип])

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

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

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

Воспользуемся этими функциями для проверки итоговых результатов (т. е. за 5 лет) по примеру 2.16.

Как следует из проведенных расчетов, сумма полученных величин равна общей сумме, выплаченной по данному займу:

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

Сформируем шаблон для разработки планов погашения кредитов (рис. 2.12).

Шаблон для разработки планов погашения кредитов

Рис. 2.12. Шаблон для разработки планов погашения кредитов

Первая часть этого шаблона будет предназначена для ввода условий, на основании которых получен (выдан) кредит, т. е. для задания величин PV, г, п. Кроме того, как и в предыдущих случаях, необходимо предусмотреть вариант выплат процентов т раз в год, а также различные типы начисления процентов — в начале или в конце каждого периода. По умолчанию определим: т = 1, тип начисления — 0 (конец периода).

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

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

Таблица 2.5 Имена ячеек шаблона

Ячейка

Имя

А6

Сумма

В6

Срок

С6

Выплат

D6

Ставка

Е6

Тип

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

Например, формула =Количество*Цена несет в себе гораздо больше информации, чем формула =А1*В1. В свою очередь, формулу в ячейке можно также задать именем, например = Выручка, предварительно определив ее как = Количество''Цена, или =А1*В1. В общем случае символические имена (именные ссылки) могут быть использованы везде, где можно применить обычные адресные ссылки ППП EXCEL.

При определении имен следует руководствоваться следующими правилами:

  • • имя должно начинаться с буквы или символа «_»;
  • • использование пробелов в именах недопустимо, в качестве разделителей слов следует применять знак «_» (например, «Число_ выплат»);
  • • длина имени не должна превышать 255 символов.

Существует несколько способов определения имен. Наиболее

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

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

  • 1. Сделать ячейку активной (т. е. установить в нее указатель).
  • 2. Щелкнуть мышью по окну имен. При этом ссылка на ячейку будет выделена, а указатель примет вид вертикальной черты.
  • 3. Ввести с клавиатуры требуемое имя и нажать клавишу < Enter>.

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

Руководствуясь любым способом, определите имена, приведенные в табл. 2.5, для соответствующих ячеек шаблона. Продолжим его формирование.

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

Формулы, используемые в шаблоне, приведены в табл. 2.6.

Таблица 2.6

Формулы шаблона

Ячейка

Формула

СЮ

=-ППЛАТ(Ставка/Выплат; Срок*Выплат; Сумма; ; Тип)

С11

=-Срок*Выплат

С12

=-С10*С11

В15

=?-Сумма - Е15

С15

=-ОСПЛТ(Ставка/Выплат; А15; Срок*Выплат; Сумма; ; Тип)

D15

=-ПЛПРТ(Ставка/Выплат; А15; Срок*Выплат; Сумма; ; Тип)

Е15

=-ОБЩДОХОД(Ставка/Выплат; Срок*Выплат; Сумма; 1; А15; Тип)

F15

=-ОБЩПЛАТ(Ставка/Выплат; Срок*Выплат; Сумма; 1; А15; Тип)

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

Полученная в результате таблица-шаблон должна иметь следующий вид (см. рис. 2.12). Наличие ошибок в блоке формул B12.F12 связано с отсутствием исходных данных[2].

Сформированный шаблон содержит ряд моментов, которые требуют дополнительных пояснений.

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

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

Известно лишь то, что сроки проведения подобных операций составляют не менее одного года (периода). Поэтому при разработке шаблона необходимо было предусмотреть возможность выполнения необходимых расчетов по крайней мере для минимально возможного срока проведения операции п = 1. Именно такая «базовая» таблица-шаблон и была сформирована в результате выполнения описанных выше действий. Имея базовый шаблон, можно легко получить таблицу для любого числа периодов, скопировав необходимое количество раз формулы блока B12.F12.

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

Напомним, что параметры PV, г, п, т, «тип», принимающие участие в расчетах, являются постоянными на протяжении всего срока проведения операции. Тогда как номер периода t должен изменяться от 1 до тп. Поэтому после выполнения команды копирования, при относительном способе адресации, только номер периода (изменяемый параметр) в функциях будет указан правильно. Для того чтобы избежать подобных коллизий в формулах, содержащих постоянные параметры (PV, г, п, т, «тип»), необходимо использовать метод абсолютной адресации ячеек. Этот вид адресации и обеспечивают в данном случае пользовательские имена, присвоенные ячейкам А6, В6, С6, D6, Е6 (см. табл. 2.5). Кроме того, применение пользовательских имен повышает наглядность формул, делая их более понятными.

Ячейка С9 содержит формулу расчета периодического платежа, a F9 — общего числа периодов проведения операции. Значение последней показывает нам также предел копирования формул блока B12.F12.

Руководствуясь рис. 2.12, сформируйте шаблон для разработки планов погашения кредитов, оформите его по своему усмотрению и сохраните его на диске под именем PLANJKR.XLT.

Осуществим проверку работоспособности шаблона на следующем примере.

Банком выдан кредит в 10 000,00 на 5 лет под 12 % годовых, который должен быть погашен равными долями, выплачиваемыми 1 раз в конце каждого года. Разработать план погашения кредита.

Рассмотрим решение данного примера по этапам.

  • 1. Введите исходные данные в блок ячеек А6.Е6. После ввода данных в ячейке С9 появится результат расчета периодического платежа, а в F9 — результат общего числа периодов проведения операции.
  • 2. Сделайте активной ячейку А12. Введите первый номер периода — 1, установите курсор на правый нижний угол ячейки А12 (он должен измениться на крестик), нажмите клавишу и, не отпуская ее, протащите его мышью до ячейки А16. Результатом выполнения этих действий будет заполнение ячеек колонки А последовательным рядом чисел с 1 до 5, начиная с ячейки А121.
  • 3. Скопируйте формулы из блока B12.F12 необходимое число раз.

Полученная в результате таблица будет иметь следующий вид

(рис. 2.13).

Решение примера 2.17

Рис. 2.73. Решение примера 2.17

На рис. 2.14 приведен продвинутый вариант шаблона, обеспечивающий более высокую степень автоматизации разработки планов погашения долгосрочных ссуд[3] [4].

Его особенностью является использование небольших программных модулей, реализованных на языке VBA (Visual Basic for Applications) и автоматизирующих рутинные процессы копирования формул и последующей очистки шаблона от ненужных данных. Для выполнения требуемой операции достаточно нажать соответствующую кнопку. Участие пользователя при этом сводится к заполнению блока ячеек А6.Е6 параметрами операции и анализу полученных результатов. Читателю рекомендуется ознакомиться с текстами соответствующих процедур, приведенных на листе «Модуль 1» этого шаблона. Подробное описание технологии применения языка Visual Basic for Applications можно найти в [5, 23].

Усовершенствованный шаблон (погашение ссуд)

Рис. 2.14. Усовершенствованный шаблон (погашение ссуд)

Разработка подобных процедур позволяет существенно упростить и повысить эффективность решения многих финансовых задач.

Завершая рассмотрение материала главы, отметим, что анализ наиболее общего вида денежных потоков — с неравномерным распределением платежей во времени (mixed cash flow, uneven cash flow streams) вряд ли практически осуществим без применения современной вычислительной техники и соответствующего программного обеспечения. К счастью, табличные процессоры позволяют без труда справляться с подобными проблемами.

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

Необходимо запомнить

  • • Сегодняшние деньги обладают большей ценностью, чем будущие, так как могут быть немедленно инвестированы и начать приносить доход. Стоимость денег имеет тенденцию к снижению в результате инфляционных процессов. Поэтому при проведении финансовых операций, связанных с долгосрочными вложениями денежных средств, необходимо обязательно учитывать влияние фактора времени.
  • • Для учета фактора времени при количественном анализе эффективности долгосрочных финансовых операций используются два метода — наращение и дисконтирование. При этом важную роль играет процентная ставка, которую в общем случае рассматривают в качестве нормы доходности финансовой операции.
  • • Процесс увеличения денежной суммы в результате начисления процентов называется наращением. Метод наращения используется для определения будущего значения денежной величины.
  • • Процесс определения текущего значения денежной величины по ее известному значению в будущем называется дисконтированием.
  • • Движение денежных средств в процессе проведения финансовых операций удобно рассматривать как числовой ряд, состоящий из распределенных во времени платежей. Такой ряд называется денежным потоком.
  • • Денежные потоки многих финансовых операций представляет собой серии периодических равных или произвольных по величине платежей.
  • • Основными количественными характеристиками финансовых операций являются текущая и будущая величины (PV и FV) порождаемых ими денежных потоков. К важным характеристикам следует также отнести процентную ставку и количество периодов их начисления (г и п), а также величины отдельных платежей (CFt).
  • • Элементарные потоки платежей возникают при проведении наиболее простых финансовых операций, количественные характеристики которых определяются из соотношений (2.3)—(2.9).
  • • Денежный поток, состоящий из периодических равных по величине платежей, называется обыкновенным аннуитетом. Количественные характеристики аннуитетов могут быть определены из соотношений (2.10)—(2.18).
  • • Денежные потоки в виде платежей произвольной величины возникают при проведении наиболее сложных финансовых операций, связанных с инвестициями в долгосрочные активы. В случае если платежи равномерно распределены во времени, будущую и современную величину такого потока можно определить из соотношений (2.19)—(2.20).
  • • В современных ЭТ имеется специальная группа так называемых финансовых функций, позволяющих автоматизировать исчисление характеристик денежных потоков. В этой главе были рассмотрены две категории финансовых функций:
  • — функции для определения характеристик разовых платежей и аннуитетов БС(), КПЕР(), НОРМА(), ПС(), ППЛАТ() и др. (табл. 2.1);
  • — функции для разработки планов погашения кредитов ПЛПРТО, ОСПЛТО, ОБЩПЛАТО и ОБЩ ДОХОД () (табл. 2.2).

  • [1] Смысл этой операции будет раскрыт позднее.
  • [2] Техника обхода подобных ошибок была показана на примере шаблона для элементарных потоков платежей.
  • [3] Читатель может просто заполнить блок ячеек А12.А16 последовательностьючисел от 1 до 5.
  • [4] Шаблон можно скачать на сайте книги PLAN_KRM.XLT.
 
Посмотреть оригинал
< Пред   СОДЕРЖАНИЕ   ОРИГИНАЛ     След >