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

Розробка планів погашення кредитів – одне із важливих завдань, яке часто зустрічається на практиці. Як правило, кредит погашається однаковими платежами, рівномірно розподіленими у часі. Такий метод погашення часто називають амортизацією боргу. Грошові потоки, що при цьому виникають, представляють собою вже добре знайомий нам ануїтет.

Основне завдання планування надходжень (виплат) по кредитах зводиться до обчислення складових елементів платежів і розподілу їх у часі. Для цих цілей у ППП Excel реалізована спеціальна група функцій, формат яких наведений у табл. 1.4.

Таблиця 1.4.

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

Найменування функції		Формат функції
Англомовна версія	Російськомовна версія
IPMT	ПРПЛТ (ПЛПРОЦ)	ПРПЛТ (ставка; период; кпер; пс; бс; тип)
PPMT	ОСПЛТ (ОСНПЛАТ)	ОСПЛТ (ставка; период; кпер; пс; бс; тип)
CUMIPMT	ОБЩПЛАТ	ОБЩПЛАТ (ставка; кол_пер; нз; нач_период; кон_период; тип)
CUMPRINC	ОБЩДОХОД	ОБЩДОХОД (ставка; период; нз; нач_период; кон_период; тип)

До вже відомих нам аргументів фінансових функцій додаються:

период – номер періоду виплати;

нач_период – номер періоду першого платежу;

кон_период – номер періоду останнього платежу.

Розглянемо механізм роботи функції на наступному прикладі:

Приклад 1.16

Банком виданий кредит у 10 000 гр. од. на 5 років під 15% річних, що нараховуються один раз в кінці кожного періоду. За умовами договору кредит повинен бути погашений рівними частинами впродовж зазначеного терміну, що виплачуються в кінці кожного періоду. Розробити план погашення кредиту для банку.

Перш за все необхідно визначити величину періодичного платежу. Для цього скористаємося вже відомою для нас функцією ПЛТ (). Майбутня величина угоди на даному етапі аналізу нам поки що невідома. Проте вона не є обов’язковим аргументом, тому ми можемо її опустити.

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

=ПЛТ (0,15; 5; -10000,00) (Результат: 2983,16)

Тепер неважко визначити майбутнє значення суми, яку отримає банк в результаті здійснення операції:

=ПЛТ (0,15; 5; -10000,00)*5 (Результат: 14915,78)

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

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

Функція ПРПЛТ (ставка; период; кпер; пс; бс; тип)

Функція ПРПЛТ ( ) виділяє із періодичного платежу його процентну частину.

Визначимо процентну частину платежу на перший період для прикладу, що розглядається:

=ПРПЛТ (0,15; 1; 5; -10000,00) (Результат: 1 500,00).

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

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

Визначимо основну частину платежу:

=ОСПЛТ (0,15; 1; 5; -10000,00) (Результат: 1483,16).

Неважко помітити, що:

ПРПЛТ ( ) + ОСПЛТ ( ) = ПЛТ ( ) = 2983,16.

Таким чином, процентний дохід банку від виданого кредиту на кінець першого періоду складе 1 500 гр. од., а повернена частина основного боргу – 1 483,16.

Дві решту функції цієї групи – ОБЩПЛАТ ( ) і ОБЩДОХОД ( ) – призначені для обчислення накопичених процентів і суми погашеного боргу між будь-якими двома періодами виплат. Для цих функцій необхідно зазначати всі аргументи, причому у вигляді позитивних величин.

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

Функція ОБЩПЛАТ ( ) служить для обчислення накопиченої суми процентів за період між двома будь-якими виплатами. Визначення цієї величини відіграє важливу роль у банківській справі.

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

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

Скористаємося цими функціями для перевірки підсумкових результатів (тобто за 5 років) по прикладу 1.16.

=ОБЩПЛАТ (0,15; 5; 10000; 1; 5; 0) (Результат: – 4915,78).

=ОБЩДОХОД (0,15; 5; 10000; 1; 5; 0) (Результат: –10000,00).

Як випливає із наведених розрахунків, сума отриманих величин дорівнює загальній сумі, виплаченій по даній позиці:

10 000 + 4 915,78 = 14 915,78.

В силу закладеного алгоритму обидві функції повертають від’ємні величини. Для отримання додатних значень просто задайте їх із знаком мінус.

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

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

Перша частина цього шаблону призначена для введення умов, на основі яких отриманий (виданий) кредит, тобто для задання величин PV, r, n. Окрім того, як і у попередніх випадках, необхідно передбачити варіант виплат процентів m раз в рік, а також різні типи нарахування процентів – на початку і в кінці кожного періоду. По замовчуванні визначимо: m = 1, тип нарахування – 0 (кінець періоду).

Для запису вихідних даних зручно використовувати табличну форму з більш компактним і наглядним їх представленням. Із врахуванням оформлення, заголовків і таблиці для введення вихідних даних ця частина шаблону буде займати перші шість рядків ЕТ.

Перед тим, як приступити до проектування другої частини шаблону, доцільно виконати ще одну корисну операцію – визначити власні імена для комірок, в які будуть вводитися вихідні дані. Пропоновані імена для комірок наведені у табл. 1.5⁴.

Таблиця 1.5.

Імена комірок шаблону

Комірка	Ім’я
А6	Сумма
В6	Срок
С6	Выплат
D6	Ставка
E6	Тип

Пригадаємо, що у ППП Excel коміркам можна присвоювати символічні імена, котрі визначаються користувачем. Ці імена можуть використовуватися в якості адресних посилань на комірки, блоки, окремі значення або формули. Визначення імен – свого роду правило хорошого тону і дає цілий ряд переваг. Наприклад, формула =Количество*Цена несе в собі значно більше інформації, ніж формула =A1*В1. У загальному випадку символічні імена (іменні посилання) можуть бути використані кругом, де можна застосувати звичайні адресні посилання ППП Excel.

При визначенні імен слід користуватися правилами:

• ім’я повинно починатися з букви або символу ­_;

• використання пробілів в іменах недопустиме, в якості розділювачів слів слід використовувати знак _ (наприклад, Число_выплат);

• довжина імені не повинна перевищувати 255 символів.

Існує декілька способів визначення імен. Найпростіший – використання окна имен, котре розташоване в лівій частини рядка вводу ППП EXCEL.

По замовчуванні, якщо імена в робочій книзі не визначені, вікно імені завжди показує адрес активної комірки (наприклад, у новій таблиці його вмістом буде посилання на першу комірку – А1). Для того, щоб визначити ім’я для комірки, необхідно виконати наступні дії:

1. зробити комірку активною (тобто встановити на ній вказівник);

2. клацнути мишею по вікні імен. При цьому посилання на комірку буде виділене, а вказівник набуде вигляду вертикальної риски.

3. ввести з клавіатури необхідне ім’я і натиснути кнопку [ENTER].

Після виконання зазначених дій при активізації даної комірки у вікні завжди буде показане визначене для неї ім’я. Задання імен також можна здійснити в режимі діалогу, скориставшись пунктом Имя теми Вставка головного меню ППП Excel.

Керуючись будь-яким способом, визначіть імена, наведені у табл. 1.5 для відповідних комірок шаблону. Продовжимо його формування.

Друга частина шаблону повинна містити результати обчислень по періодах. Її можна представити у вигляді таблиці, що складається із шести граф: номер періоду, баланс на кінець періоду, сума основного боргу, сума процентів, сума накопиченого боргу, сума накопичених процентів. Формули, що використовуються у шаблоні, наведені у табл. 1.6.

Таблиця 1.6.

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

Комірка	Формула
C9	=-ПЛТ(Ставка/Выплат;Срок*Выплат;Сумма;;Тип)
F9	=Срок*Выплат
B12	=Сумма-E12
C12	=-ОСПЛТ(Ставка/Выплат;A12;Срок*Выплат;Сумма;;Тип)
D12	=-ПРПЛТ(Ставка/Выплат;A12;Срок*Выплат;Сумма;;Тип)
E12	=-ОБЩДОХОД(Ставка/Выплат;Срок*Выплат;Сумма;1;A12;Тип)
F12	=-ОБЩПЛАТ(Ставка/Выплат;Срок*Выплат;Сумма;1;A12;Тип)

Зверніть увагу на те, що всі функції задані із відємним знаком. Це забезпечує можливість введення вихідних даних і отримання результатів обчислень у вигляді позитивних величин, позбавляючи нас від проблем інтерпретації знаків. Окрім того, вимога введення вихідних даних у вигляді додатних величин зумовлена специфікою форматів функцій ОБЩПЛАТ ( ) і ОБЩДОХОД ( ).

Отримана в результаті таблиця-шаблон повинна мати вигляд, показаний на рис. 1.12. Наявність помилок у блоці формул В12.F12 пов’язана із відсутністю вихідних даних.⁵

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

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

При розробці універсального шаблону для автоматизації розрахунків по складанню планів погашення довготермінових кредитів ми наперед не можемо знати, які терміни проведення операції будуть передбачені тим або іншим контрактом. Відомо лише, що терміни проведення подібних операцій складають не менше одного року (періоду). Тому при розробці шаблону необхідно передбачити можливість виконання необхідних розрахунків по крайній мірі для мінімально можливого терміну проведення операції n=1. саме така «базова» таблиця-шаблон і була сформована в результаті виконання описаних вище дій. Маючи базовий шаблон, можна легко отримати таблицю для будь-якого числа періодів, скопіювавши необхідну кількість раз формули блоку В12.F12.

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

Пригадаємо, що параметри PV, r, n, m, тип, що беруть участь у розрахунках, є постійними впродовж всього терміну проведення операції, тоді як номер періоду t повинен змінюватися від 1 до . Тому після виконання команди копіювання при відносному способі адресації лише номер періоду (параметр, що змінюється) у функціях буде зазначений правильно. Щоби уникнути подібних колізій у формулах, що місять постійні параметри (PV, r, n, m, тип), необхідно використовувати метод абсолютної адресації комірок. Цей вид адресації і забезпечують в даному випадку користувацькі імена, присвоєні коміркам A6, B6, C6, D6, E6 (табл. 1.5). Окрім того, використання користувацьких імен підвищує наочність формул, роблячи їх зрозумілішими.

Комірка С9 містить формулу розрахунку періодичного платежу, а F9 – загального числа періодів проведення операції. Значення останньої показує нам також межу копіювання формул блоку B12.F12.

Керуючись рис. 1.12, сформуйте шаблон для розробки планів погашення кредитів, оформите його на свій розсуд і збережіть його на диску під іменем PLAN_KR.XLT.

Перевіримо працездатність шаблону на наступному прикладі.

Приклад 1.17

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

Розглянемо розв’язання даного прикладу по етапах.

1. Введіть вихідні дані у блок комірок А6.Е6. Після вводу даних у комірці С9 появиться результат розрахунку періодичного платежу, а в F9 – загального числа періодів проведення операції.

2. Зробіть активною комірку А12. Виберіть у головному меню тему Правка пункт Заполнить підпункт Прогрессия. На екрані появиться діалогова форма підпункту Прогрессия. Зробіть активним перемикач по столбцам і натисніть лівою кнопкою миші в поле Предварительное значение. Введіть число періодів (комірка F9) в поле Предварительное значение. Натисніть кнопку [ОК] або клавішу [ENTER]. Результатом виконання цих дій буде заповнення комірок колонки А послідовним рядом чисел, починаючи із комірки А12.

3. Скопіюйте формули із блоку B12.F12 необхідне число разів.

Отримана в результаті таблиця буде мати вигляд, показаний на рис. 1.13.

Рис. 1.13. Розв’язання прикладу 1.17

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

1. зробити активною комірку А12 і встановити вказівник миші на її нижній правий кут. При цьому вказівник набуде вигляду маркеру заповнення - +;

2. натиснути кнопку [CTRL] і не відпускаючи її протягнути мишкою маркер заповнення необхідну кількість разів вниз (по колонці А). При цьому у лівому куті рядка вводу буде виводитися значення лічильника ряду.

На рис. 1.14 наведений просунутий варіант шаблону, що забезпечує вищу ступінь автоматизації розробки планів погашення довготермінових позик. Його особливістю є використання невеликих програмних модулів, реалізованих на мові VBA (Visual Basic for Application) і автоматизуючи рутинні процеси копіювання формул і подальшої очистки шаблону від непотрібних даних. Для виконання необхідної операції достатньо натиснути відповідну кнопку. Участь користувача при цьому зводиться до заповнення блоку комірок А6.Е6 параметрами операції і до аналізу отриманих результатів. Рекомендується ознайомитися з текстами відповідних процедур, наведених на листі Модуль 1 цього шаблону і у додатку 3.

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

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

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

Необхідно запам’ятати

• Сьогоднішні гроші володіють більшою цінністю, ніж майбутні, оскільки можуть бути негайно інвестовані і почати приносити дохід. Вартість грошей має тенденцію до зменшення в результаті інфляційних процесів. Тому при здійсненні фінансових операцій, пов’язаних із довготерміновими вкладаннями грошових коштів, необхідно обов’язково враховувати вплив фактору часу.

• Для врахування фактору часу при кількісному аналізі ефективності довготермінових фінансових операцій використовуються два методи – нарощення і дисконтування. При цьому важливу роль відіграє процентна ставка, яку у загальному випадку розглядають в якості норми доходності фінансової операції.

• Процес збільшення грошової суми в результаті нарахування процентів називається нарощенням. Метод нарощення використовується для визначення майбутнього значення грошової величини.

• Процес визначення поточного значення грошової величини за її відомим значенням у майбутньому називається дисконтуванням.

• Рух грошових коштів у процесі проведення фінансових операцій зручно розглядати як числовий ряд, що складається із розподілених у часі платежів. Такий ряд називається грошовим потоком.

• Грошові потоки багатьох фінансових операцій представляють собою серії періодичних рівних або довільних за величиною платежів.

• Основні кількісні характеристики фінансових операцій – поточна і майбутня величини (PV і FV) породжуваних ними грошових потоків. До важливих характеристик слід також віднести процентну ставку і кількість періодів їх нарахування (r і n), а також величини окремих платежів (СF_t).

• Елементарні потоки платежів виникають при проведенні найбільш простих фінансових операцій, кількісні характеристики яких визначаються із співвідношень (1.3) – (1.9).

• Грошовий потік, що складається із періодичних рівних за величиною платежів, називається звичайним ануїтетом. Кількісні характеристики ануїтетів можуть бути визначені із співвідношень (1.10) – (1.18).

• Грошові потоки у вигляді платежів довільної величини виникають при проведенні найбільш складних фінансових операцій, пов’язаних з інвестиціями у довготермінові активи. Якщо платежі рівномірно розподілені у часі, майбутню і теперішню величину такого потоку можна визначити із співвідношень (1.19) – (1.20).

• У сучасних ЕТ є спеціальна група так званих фінансових функцій, що дозоляють автоматизувати обчислення характеристик грошових потоків. У цій темі розглянуто дві категорії фінансових функцій: функції для визначення характеристик разових платежів та ануїтетів – БС ( ), КПЕР ( ), СТАВКА ( ), ПС ( ), ПЛТ ( ) та ін. (табл. 1.1); функції для розробки планів погашення кредитів ПРПЛТ ( ), ОСПЛТ ( ), ОБЩПЛАТ ( ), ОБЩДОХОД ( ) (табл. 1.2).