Расчет процентов по договору займа в таблицах excel

Кто как, а я считаю кредиты злом. Особенно потребительские. Кредиты для бизнеса — другое дело, а для обычных людей мышеловка»деньги за 15 минут, нужен только паспорт» срабатывает безотказно, предлагая удовольствие здесь и сейчас, а расплату за него когда-нибудь потом. И главная проблема, по-моему, даже не в грабительских процентах или в том, что это «потом» все равно когда-нибудь наступит. Кредит убивает мотивацию к росту. Зачем напрягаться, учиться, развиваться, искать дополнительные источники дохода, если можно тупо зайти в ближайший банк и там тебе за полчаса оформят кредит на кабальных условиях, попутно грамотно разведя на страхование и прочие допы?

Так что очень надеюсь, что изложенный ниже материал вам не пригодится.

Но если уж случится так, что вам или вашим близким придется влезть в это дело, то неплохо бы перед походом в банк хотя бы ориентировочно прикинуть суммы выплат по кредиту, переплату, сроки и т.д. «Помассажировать числа» заранее, как я это называю 🙂 Microsoft Excel может сильно помочь в этом вопросе.

Вариант 1. Простой кредитный калькулятор в Excel

Для быстрой прикидки кредитный калькулятор в Excel можно сделать за пару минут с помощью всего одной функции и пары простых формул. Для расчета ежемесячной выплаты по аннуитетному кредиту (т.е. кредиту, где выплаты производятся равными суммами — таких сейчас большинство) в Excel есть специальная функция ПЛТ (PMT) из категории Финансовые (Financial). Выделяем ячейку, где хотим получить результат, жмем на кнопку fx в строке формул, находим функцию ПЛТ в списке и жмем ОК. В следующем окне нужно будет ввести аргументы для расчета:

• Ставка — процентная ставка по кредиту в пересчете на период выплаты, т.е. на месяцы. Если годовая ставка 12%, то на один месяц должно приходиться по 1% соответственно.
• Кпер — количество периодов, т.е. срок кредита в месяцах.
• Пс — начальный баланс, т.е. сумма кредита.
• Бс — конечный баланс, т.е. баланс с которым мы должны по идее прийти к концу срока. Очевидно =0, т.е. никто никому ничего не должен.
• Тип — способ учета ежемесячных выплат. Если равен 1, то выплаты учитываются на начало месяца, если равен 0, то на конец. У нас в России абсолютное большинство банков работает по второму варианту, поэтому вводим 0. 

Также полезно будет прикинуть общий объем выплат и переплату, т.е. ту сумму, которую мы отдаем банку за временно использование его денег. Это можно сделать с помощью простых формул:

Вариант 2. Добавляем детализацию

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

Функция ОСПЛТ (PPMT) в ячейке B17 вводится по аналогии с ПЛТ в предыдущем примере:

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

Чтобы сделать наш калькулятор более универсальным и способным автоматически подстраиваться под любой срок кредита, имеет смысл немного подправить формулы. В ячейке А18 лучше использовать формулу вида:

=ЕСЛИ(A17>=$C$7;»»;A17+1)

Эта формула проверяет с помощью функции ЕСЛИ (IF) достигли мы последнего периода или нет, и выводит пустую текстовую строку («») в том случае, если достигли, либо номер следующего периода. При копировании такой формулы вниз на большое количество строк мы получим номера периодов как раз до нужного предела (срока кредита). В остальных ячейках этой строки можно использовать похожую конструкцию с проверкой на присутствие номера периода:

=ЕСЛИ(A18<>»»; текущая формула; «»)

Т.е. если номер периода не пустой, то мы вычисляем сумму выплат с помощью наших формул с ПРПЛТ и ОСПЛТ. Если же номера нет, то выводим пустую текстовую строку:

Вариант 3. Досрочное погашение с уменьшением срока или выплаты

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

В случае уменьшения срока придется дополнительно с помощью функции ЕСЛИ (IF) проверять — не достигли мы нулевого баланса раньше срока:

А в случае уменьшения выплаты — заново пересчитывать ежемесячный взнос начиная со следующего после досрочной выплаты периода:

Вариант 4. Кредитный калькулятор с нерегулярными выплатами

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

Предполагается что:

• в зеленые ячейки пользователь вводит произвольные даты платежей и их суммы
• отрицательные суммы — наши выплаты банку, положительные — берем дополнительный кредит к уже имеющемуся
• подсчитать точное количество дней между двумя датами (и процентов, которые на них приходятся) лучше с помощью функции ДОЛЯГОДА (YEARFRAC)

М. Стромов
журнал «Расчет» № 9/2008

Дополнительный выпуск «Практическая бухгалтерия».
Подготовлен специально для журнала «Расчёт»
при участии экспертов журнала «Практическая бухгалтерия»

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

Как будем учитывать?

Для начала немного теории.

В целях налогообложения прибыли проценты за пользование кредитом учитываются в составе внереализационных расходов (пп. 2 п. 1 ст. 265 НК РФ). При этом учесть их можно только в соответствии с требованиями статьи 269 Налогового кодекса.

Коммерческие кредиты относятся к долговым обязательствам, равно как и товарные кредиты, займы, банковские вклады, банковские счета или иные заимствования независимо от их оформления. Расходами по ним признаются проценты при том условии, что их размер не отклоняется существенно от среднего уровня процентов, которые взимаются по долговым обязательствам, выданным в том же квартале или месяце на сопоставимых условиях (п. 1 ст. 269 НК РФ).

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

Расшифровка этих понятий дана в Налоговом кодексе. Так, существенным признается отклонение более чем на 20%, причем в любую сторону — понижения или повышения (абз. 3 п. 1 ст. 269 НК РФ).

При этом долговые обязательства считаются выданными на сопоставимых условиях, если они:

• выданы в той же валюте;
• на те же сроки;
• в сопоставимых объемах;
• под аналогичные обеспечения (абз. 2 п. 1 ст. 269 НК РФ).

Каких-либо иных критериев, при которых долговые обязательства признавались бы выданными на сопоставимых условиях, НК РФ не устанавливает. На это указал и Минфин в письме от 11 декабря 2007 г. № 03-03-06/1/850.

Практические иллюстрации

Рассмотрим на примере 1, в каком случае кредиты нельзя признать выданными на сопоставимых условиях.

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

Сопоставимость займов налицо

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

Рассчитаем сумму, которую можно учесть при расчете налога на прибыль при каждом способе.

1 способ — на основе среднего уровня процентов

1) Определим сопоставимые долговые обязательства.

2) Рассчитаем средний уровень процентов (Уср), который уплачивается по сопоставимым долговым обязательствам.

Этот показатель вычисляется по формуле:

Уср = ((сумма произведений всех полученных долговых обязательств и процентных ставок по ним) / (сумма всех полученных долговых обязательств)) × 100%

3) Рассчитаем максимальный уровень процентов, который можно учесть в целях налогообложения прибыли.

Для этого необходимо увеличить Уср на максимальное отклонение в размере 20%:

Уmax = Уср × 1,2

Полученное значение максимальной ставки процентов сравним с аналогичным параметром по каждому долговому обязательству. Если ставка по договору окажется больше максимальной, то в состав расходов включается сумма процентов, рассчитанная исходя из максимальной ставки. При этом разница, на которую превышен предельный размер, при исчислении налога не учитывается (п. 8 ст. 270 НК РФ).

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

Теперь покажем алгоритм расчета на практическом примере.

2 способ — на основе ставки рефинансирования Банка России

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

Обратите внимание: фирма может получить кредит как в рублях, так и в иностранной валюте. В зависимости от этого предельная ставка будет определяться по-разному. Так, по долговым обязательствам в рублях норматив по процентам рассчитывается исходя из ставки рефинансирования, увеличенной в 1,1 раза. Если же долговое обязательство выдано в валюте — исходя из 15% годовых (абз. 4 п. 1 ст. 269 НК РФ).

В данной статье мы говорим о кредитах, полученных в рублях. Поэтому дальнейшие расчеты будем вести на основании ставки рефинансирования (Среф), увеличенной в 1,1 раза.

Предельную ставку по процентам (Спр) рассчитываем по формуле:

Спр = Среф × 1,1

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

Рассмотрим пример.

Сухие цифры

Как показали расчеты, способ расчета на основе среднего уровня процентов (сп. 1) оказался более выгодным, чем тот, который осуществлялся исходя из ставки рефинансирования (сп. 2). Это объясняется тем, что в налоговом учете фирма смогла учесть сумму процентов по займу больше, чем при сп. 2. (43 073 руб. > 27 618 руб.). Следовательно, расхождение между бухгалтерским и налоговым учетом оказалось меньше.

Сопоставить займы не удалось

Если у организации нет долговых обязательств, полученных на сопоставимых условиях, то лимит учета процентов принимается равным ставке рефинансирования ЦБ РФ, увеличенной в 1,1 раза (п. 1 ст. 269, пп. 2 п. 1 ст. 265 НК РФ). Соответствующий принцип расчета процентов описан выше.

Последние статьи по теме:

Горячие темы. Эти статьи обсуждают: