Розрахунок дисконту в Excel: покроковий алгоритм

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

Трохи теорії

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

Усі стовпці та рядки робочого листа пронумеровані: стовпці позначаються латинськими літерами в алфавітному порядку, рядки – натуральними числами в порядку зростання. При цьому кожна комірка, розташована на перетині певних стовпця і рядка, має свою унікальну адресу, що формується з літери/літер (номер стовпця) і числа (номер рядка). Наприклад, комірка D35 – це комірка на перетині стовпця D і рядка 35.

Розглянемо на практичних прикладах порядок обчислення дисконту. У прикладах будемо виходити з того, що бухоблік заборгованості ведеться згорнуто на субрахунку 55 «Інші довгострокові зобов’язання», без використання субрахунків 551 (для номіналу заборгованості) та 552 (для дисконту).

Практичні приклади

Приклад 1

ТОВ «Сіріус» 30.09.2024 отримало від юрособи – ТОВ «Олімпус» безпроцентну поворотну фінансову допомогу (далі – ПФД) у сумі 800 000 грн зі строком погашення 30.09.2026 (тобто на 2 роки). Проміжні платежі не передбачено, ПФД погашатиметься однією сумою наприкінці строку користування за договором.

Згідно з обліковою політикою підприємства:

• період дисконтування – квартал;
• ринкова ставка дисконтування визначається на рівні облікової ставки НБУ станом на дату виникнення заборгованості.

Отже, позика «житиме» в обліку два роки (8 кварталів), а ринкову ставку вибрано на рівні 13,0 % річних.

Тож діятимемо покроково так.

Крок 1. Визначаємо квартальну процентну ставку, ґрунтуючись на річній.

Для цього скористаємося формулою:

Р_кв = (1 + Р_річ)^1/4 – 1;

Щоб зробити відповідне обчислення в Excel, у будь-яку зручну комірку (виберемо комірку B9, див. фрагмент 1) потрібно занести таку формулу:

=(1+13%)^(1/4)-1.

Зазначимо, що після введення формули в комірці відобразиться число – результат обчислень за формулою. Саму формулу можна буде переглянути в рядку формул (біля символу fx), навівши курсор на комірку та за потреби відредагувати її.

Результатом розрахунку буде ставка 3,102598 %.

Фрагмент 1. Розрахунок квартальної ставки дисконтування

Крок 2. Визначаємо теперішню вартість заборгованості та суму дисконту

Щоб визначити теперішню вартість ПФД на дату її отримання, використовуємо фінансову функцію PV. Для цього потрібно активувати (натиснути лівою кнопкою миші) комірку, до якої планується ввести результат обчислень за формулою (ми вибрали комірку В12), потім зайти в розділ меню «Формули» і там серед фінансових функцій знайти PV.

Функція PV має такий формат:

PV(Ставка;Кількість_періодів;Виплата;Майбутня_сума;Тип)

де

• Ставка – ставка дисконтування, у процентах;
• Кількість_періодів – кількість періодів дисконтування;
• Виплата – регулярні платежі в кожний проміжок часу зі знаком «–». Якщо такі платежі не передбачено (погашення планується однією сумою наприкінці строку за договором, як у прикладі 1), ставимо «0» або не ставимо нічого;
• Майбутня_сума  – майбутня вартість. Тут наводимо номінал заборгованості, що підлягає дисконтуванню (зі знаком «–»), якщо вона погашається одним платежем наприкінці строку за договором;
• Тип – ставимо «0», якщо платіж буде здійснено наприкінці періоду (року, кварталу, місяця), або «1» – якщо на початку періоду. Зазвичай при розрахунку дисконтованої вартості вибираємо «0» («0» можна не проставляти та залишати поле порожнім).

У нашому прикладі (повернення всієї суми ПФД наприкінці строку) у вікні введення даних для функції PV  необхідно занести таку інформацію (див. фрагмент 2).

Фрагмент 2. Введення даних для функції PV
(погашення ПФД однією сумою наприкінці строку дії договору)

Як варіант, можна безпосередньо в комірці ввести функцію PV вручну в такому форматі:

=PV(3,102598%;8;0;-800000;0).

Результатом обчислень буде теперішня вартість, що дорівнюватиме 626 517,37 грн (див. фрагмент 3, розрахункову формулу введено в комірці В12).

Сума дисконту розраховується як різниця між номінальною сумою ПФД та її продисконтованою вартістю та становитиме 173 482,63 грн (у комірку В13 введено формулу, в якій від значення комірки B2, де відображено номінальну вартість ПФД, віднімається значення комірки В12).

Сума дисконту відображається проведенням Дт 55 – Кт 733 на дату отримання ПФД.

Фрагмент 3. Розрахунок теперішньої вартості заборгованості та суми дисконту

Крок 3. Визначаємо амортизацію дисконту

Для цього формуємо розрахункову таблицю, де буде 9 рядків (нульовий та з 1-го по 8-й періоди дисконтування). Див. робочу таблицю з розрахунковими формулами (див. фрагмент 4)  та з результативними числовими значеннями, які мають з’явитися в тих комірках, де було введено відповідні формули (див. фрагмент 5).

Фрагмент 4. Формули для розрахунку амортизації дисконту

Фрагмент 5. Числові значення амортизації дисконту

Нагадаємо, амортизація дисконту за отриманою ПФД є фінансовими витратами та відображається проведенням Дт 952 – Кт 55 наприкінці кожного періоду дисконтування (кварталу).

Приклад 2

ТОВ «Сіріус» 30.09.2024 надало юрособі – ТОВ «Олімпус» безпроцентну ПФД у сумі 800 000 грн зі строком погашення 30.09.2026. ПФД погашатиметься рівними платежами наприкінці кожного кварталу (починаючи із 31.12.2024) у сумі 100 000 грн.

Згідно з обліковою політикою підприємства:

• період дисконтування – квартал;
• ринкова ставка дисконтування визначається на рівні облікової ставки НБУ станом на дату виникнення заборгованості.

Отже, позика «житиме» в обліку два роки (8 кварталів), а ринкову ставку вибрано на рівні 13,0 % річних.

Наш покроковий алгоритм дій буде такий.

Крок 1. Визначаємо квартальну процентну ставку, ґрунтуючись на річній.

Формула для її розрахунку така:

Р_кв = (1 + Р_річ)^1/4 – 1;

В Excel формула матиме вигляд:

=(1+13%)^(1/4)-1.

Тут все так само, як і в прикладі 1.

Крок 2. Визначаємо суму дисконту

Використовується та сама функція PV, як і у прикладі 1. Але з огляду на опис заповнення, наведений у прикладі 1, у прикладі 2 її реквізити потрібно заповнити трохи по-іншому (див. фрагмент 6). Справа в тім, що в прикладі 2 ПФД повертається не однією сумою наприкінці строку дії договору (800 000 грн), а рівномірно протягом строку договору рівними частинами (по 100 000 грн щоквартально наприкінці кварталу).

Фрагмент 6. Введення даних для функції PV
(рівномірне погашення ПФД рівними частинами протягом строку дії договору)

Як варіант, можна безпосередньо в комірці ввести функцію PV вручну в такому форматі:

=PV(3,102598%;8;-100000;0;0).

Результатом обчислень буде теперішня вартість, що дорівнюватиме 698 940,98 грн (див. фрагмент 7, розрахункова формула в комірці В12).

Сума дисконту розраховується як різниця між номінальною сумою ПФД та її продисконтованою вартістю та становитиме 101 059,02 грн (у комірку В13 введено формулу, в якій від значення комірки B2, де відображено номінальну вартість ПФД, віднімається значення комірки В12).

Сума дисконту відображається проведенням Дт 55 – Кт 733 на дату отримання ПФД.

Фрагмент 7. Розрахунок теперішньої вартості заборгованості та суми дисконту

Крок 3. Визначаємо амортизацію дисконту

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

Фрагмент 8. Розрахункові формули для обчислення амортизації дисконту

Фрагмент 9. Числові значення амортизації дисконту

Нагадаємо, амортизація дисконту за отриманою ПФД є фінансовими витратами та відображається проведенням Дт 952 – Кт 55 наприкінці кожного періоду дисконтування (кварталу).