Експрес-аналіз фінансової оренди з допомогою EXCEL

Володимир ЛАВРЄНОВ

Як розрахувати економічні показники при оренді

Розрахунок показників орендної плати вже розглядався в "Дебеті-Кредиті" (див. статті Леоніда Гуйди в "ДК" №39 і №43). Використання для цього формули, наведеної в П(С)БО 14, не дозволяє швидко підібрати необхідні параметри орендних платежів і проаналізувати рентабельність фінансової оренди або вигідність кредиту.

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

Суму мінімального орендного платежу (ануїтету) можна розрахувати за допомогою формули:

ППЛАТ(Ставка;Кпер;Нз;Бз;Тип),

а величина справедливої вартості об'єкта оренди, яка компенсується в одному періоді, розраховується за допомогою формули:

ОСНПЛАТ(Ставка;Период;Кпер;Нз;Бз;Тип).

У наведених формулах аргументи означають:

Ставка - орендна ставка відсотка або процентна ставка за кредитом (позикою);

Период - період платежу (значення від 1 до Кпер);

Кпер - загальна кількість виплат за орендою або кредитом (позикою);

Нз - величина справедливої вартості об'єкта оренди або дебіторська заборгованість орендаря на початок терміну оренди, або справжня вартість мінімальних орендних платежів;

Бз - майбутня сума або баланс готівки, якої треба досягнути після останньої виплати. Цей аргумент використовується, якщо необхідно розрахувати показники оренди при умові досягнення об'єктом оренди певної справедливої вартості.

Тип - цифра 0 або 1, що означає, коли має відбуватися виплата (0 або пропуск - наприкінці періоду, 1 - на початку періоду).

Ці формули можна використати також і для інших фінансових розрахунків. Наприклад, якщо ви поставили за мету накопичити 10 000 грн за 2 роки, передбачаючи, що вдасться забезпечити на внеску 18% річних, за допомогою функції "ППЛАТ" можна визначити, яку суму треба відкладати щомісяця:

ППЛАТ(18%/12; 2*12; 0; 10000) дорівнює 349,24 грн.

При щомісячному, в розмірі 349,24 грн, поповненні внеску протягом 2 років на 18%-му депозиті ви отримаєте 10000 грн.

Як заповнити розрахункову таблицю

Малюнок 1

Розрахункова відомість, показана на малюнку 1, складається з 4 частин:

- таблиці початкових даних;

- таблиці вибору умов виплати;

- таблиці підбору параметрів;

- розрахункової таблиці.

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

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

Спочатку введіть в комірку F19 формулу "=F3" (оскільки це допоміжне значення, воно виділене блідим кольором), яка дасть можливість заповнити всі рядки розрахункової таблиці однотипними формулами, скопіювавши їх з рядка 20 (мал. 1).

Заповніть комірки цього рядка такими формулами:

Увага! Наявність подвійного символу ;; у функціях "ППЛАТ" і "ОСНПЛАТ" обов'язкова.

А тепер скопіюйте ці формули на достатню кількість комірок (це залежить від кількості виплат за договором, але можете не обмежувати себе і 100 рядками). Усі розрахункові дані щодо вибраного варіанта орендного договору автоматично з'являться в потрібних елементах таблиці, як тільки ви введете початкові значення в комірки F3:F7. Якщо їх не буде введено, таблиця залишиться порожньою. Наприклад, якщо орендні платежі або платежі за кредитом здійснюються щомісяця протягом 3-х років, автоматично заповняться тільки 36 рядків, інші елементи таблиці залишаться порожніми. Саме з цією метою у формулах використовується функція "ЕТЕКСТ( )"*

У наведених формулах на елементи таблиці початкових даних і комірку А10 використовуються змішані посилання (з абсолютним посиланням на адресу рядка, наприклад A$10)

Аргумент F$3 має бути зі знаком "мінус", оскільки передбачається, що розрахунок здійснює орендодавець.

Сподіваюся, ви звернули увагу, що в розрахунковій таблиці підсумковий рядок займає незвичне місце (А18:F18)? По-перше, це дозволить при збільшенні кількості періодів сплати просто копіювати останній рядок з формулами нижче, по-друге, це зручніше.

Вибір умов виплати орендних платежів

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

Використайте автоматичні перемикачі режимів сплати. Для цього в панелі "Форма" виберіть елемент "перемикач" і двічі скопіюйте його на аркуш.

Увага! Першому введеному на аркуш перемикачу присвойте назву "в кінці періоду", а другому - "на початку періоду". Ця послідовність використовуватиметься у формулах розрахунку.

Перемикач має властивість зв'язуватися з коміркою. Коли перемикач встановлений, у комірці, з якою він зв'язаний, відображається його номер у групі*. У вікні "Формат элемента управления" (мал. 2) в полі "Связь с ячейкой" встановіть адресу якої-небудь порожньої комірки, наприклад А10. Те ж зробіть і для другого перемикача. Тепер, якщо ввімкнути, наприклад, перемикач "в кінці періоду", в комірці А10 з'явиться його номер - "1" (на мал. 1 - сірим кольором), якщо "на початку періоду" - 2. Цей номер використовується як аргумент у наведених вище формулах розрахунку. Тепер для зміни в розрахунковій таблиці всіх даних щодо платежів залежно від варіанта внесення орендного платежу досить клацнути потрібним перемикачем.

Малюнок 2

Для автоматизації вибору параметрів розрахунку можна скористатися таким керуючим елементом, як "лічильник". З його допомогою можна швидко встановити потрібний параметр, утримуючи ЛКМ стрілку на "лічильнику". Його розташування на аркуші не має значення, єдине, що треба зробити, - ввести в поле "Связать с ячейкой" вікна "Формат элемента управления" посилання на комірку, в якій ви змінюватимете дані (мал. 2).

Підбір параметрів договору фінансової оренди

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

Використання таблиці підбору параметрів допоможе вам швидко проаналізувати можливі варіанти договору на основі підбору значення мінімальної суми орендних платежів (ануїтету).

У таблиці "Підбір параметрів..." дані вводяться вручну тільки в комірку С14, де можна вказати будь-який розмір ануїтету, в інших комірках значення з'являться автоматично.

Отже, введіть формули в комірки:

D14 (фінансовий прибуток за вказаний в комірці F10 період) =C14-E14;

Е14 (компенсація у зазначеному періоді справедливої вартості об'єкта оренди) =ЕСЛИ(A10=1;ОСНПЛАТ(F4/F5;F10;F5*F6;-E15);ОСНПЛАТ(F4/F5;F10;F5*F6;-E15;;1));

С15 (сума виплат за договором) =C14*F5*F6;

D15 (сумарний фінансовий дохід) =C15-E15;

Е15 (справедлива вартість об'єкта оренди на дату договору) =ЕСЛИ(A10=1;ПЗ(F4/F5;F5*F6;-C14);ПЗ(F4/F5;F5*F6;-C14;;1)).

Оскільки на стадії підбору параметрів ще не розраховано розміри компенсації справедливої вартості в кожному періоді, тут використано функцію "ПЗ", що розраховує загальну суму платежів. Аргументи цієї функції аналогічні "ППЛАТ".

Для повернення дати внесення орендного платежу, що стосується періоду, зазначеного в комірці F10, введіть у комірку F11 формулу: =ЕСЛИ(A10=2;F7+365/F5*(F10-1);F7+365/F5*F10).

Для розрахунку тривалості періоду у формулі використано 365-денний рік. Звичайно, дату періоду можна ввести і вручну, однак формула дасть можливість вибирати період і дату виплати, перемикаючи лічильник, розташований у комірках D10:D11 (не забудьте при встановленні лічильника зв'язати його з коміркою F10, як показано вище).

Тепер можна, варіюючи значення в комірках F5:F7, F10, С14 і змінюючи перемикачами період внесення платежів (В10, В11), вибрати оптимальні параметри кредитно-фінансового договору. Внаслідок цієї процедури в комірці Е15 має бути підібране значення, близьке до справедливої вартості об'єкта оренди (порівняйте значення в комірках F3, Е15 на мал. 1).

Малюнок 3

---

Примітки:

* Функція "ЕТЕКСТ(комірка-джерело)" дає можливість залишити цільову комірку порожньою у випадку, якщо в комірці-джерелі записано формулу, що не повертає значення. Наприклад, за договором оренди здійснюється 20 платежів, а розрахункові формули скопійовані в 100 рядків. Починаючи з 21-го рядка в комірці-джерелі (наприклад А41) значення періоду (21) не буде. Оскільки фактично комірка А41 порожня, хоч в ній записано текст (формулу), інші комірки цього рядка, які посилаються на А41, будуть порожніми.