Расчет дисконта в 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 в конце каждого периода дисконтирования (квартала).