Расчет процентов по займу в экселе

Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.п.) и заемщики (предприниматели, компании, частные лица и т.д.).

Быстро сориентироваться в мудреных формулах, рассчитать проценты, суммы выплат, переплату позволяют функции программы Microsoft Excel.

Как рассчитать платежи по кредиту в Excel

Ежемесячные выплаты зависят от схемы погашения кредита. Различают аннуитетные и дифференцированные платежи:

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

Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.

Расчет аннуитетных платежей по кредиту в Excel

Ежемесячная сумма аннуитетного платежа рассчитывается по формуле:

А = К * S

где:

• А – сумма платежа по кредиту;
• К – коэффициент аннуитетного платежа;
• S – величина займа.

Формула коэффициента аннуитета:

К = (i * (1 + i)^n) / ((1+i)^n-1)

• где i – процентная ставка за месяц, результат деления годовой ставки на 12;
• n – срок кредита в месяцах.

В программе Excel существует специальная функция, которая считает аннуитетные платежи. Это ПЛТ:

1. Заполним входные данные для расчета ежемесячных платежей по кредиту. Это сумма займа, проценты и срок.



2. Составим график погашения кредита. Пока пустой.



3. В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в Excel: =ПЛТ($B$3/12; $B$4; $B$2). Чтобы закрепить ячейки, используем абсолютные ссылки. Можно вводить в формулу непосредственно числа, а не ссылки на ячейки с данными. Тогда она примет следующий вид: =ПЛТ(18%/12; 36; 100000).

Ячейки окрасились в красный цвет, перед числами появился знак «минус», т.к. мы эти деньги будем отдавать банку, терять.



Расчет платежей в Excel по дифференцированной схеме погашения

Дифференцированный способ оплаты предполагает, что:

• сумма основного долга распределена по периодам выплат равными долями;
• проценты по кредиту начисляются на остаток.

Формула расчета дифференцированного платежа:

ДП = ОСЗ / (ПП + ОСЗ * ПС)

где:

• ДП – ежемесячный платеж по кредиту;
• ОСЗ – остаток займа;
• ПП – число оставшихся до конца срока погашения периодов;
• ПС – процентная ставка за месяц (годовую ставку делим на 12).

Составим график погашения предыдущего кредита по дифференцированной схеме.

Входные данные те же:

Составим график погашения займа:

Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B$2. Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10 – номер текущего периода, В4 – срок кредита; Е9 – остаток по кредиту в предыдущем периоде; G9 – сумма основного долга в предыдущем периоде.

Выплата процентов: остаток по кредиту в текущем периоде умножить на месячную процентную ставку, которая разделена на 12 месяцев: =E9*($B$3/12).

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9

Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.

Внесем формулы в соответствующие столбцы. Скопируем их на всю таблицу.

Сравним переплату при аннуитетной и дифференцированной схеме погашения кредита:

Красная цифра – аннуитет (брали 100 000 руб.), черная – дифференцированный способ.

Формула расчета процентов по кредиту в Excel

Проведем расчет процентов по кредиту в Excel и вычислим эффективную процентную ставку, имея следующую информацию по предлагаемому банком кредиту:

Рассчитаем ежемесячную процентную ставку и платежи по кредиту:

Заполним таблицу вида:

Комиссия берется ежемесячно со всей суммы. Общий платеж по кредиту – это аннуитетный платеж плюс комиссия. Сумма основного долга и сумма процентов – составляющие части аннуитетного платежа.

Сумма основного долга = аннуитетный платеж – проценты.

Сумма процентов = остаток долга * месячную процентную ставку.

Остаток основного долга = остаток предыдущего периода – сумму основного долга в предыдущем периоде.

Опираясь на таблицу ежемесячных платежей, рассчитаем эффективную процентную ставку:

• взяли кредит 500 000 руб.;
• вернули в банк – 684 881,67 руб. (сумма всех платежей по кредиту);
• переплата составила 184 881, 67 руб.;
• процентная ставка – 184 881, 67 / 500 000 * 100, или 37%.
• Безобидная комиссия в 1 % обошлась кредитополучателю очень дорого.

Эффективная процентная ставка кредита без комиссии составит 13%. Подсчет ведется по той же схеме.

Расчет полной стоимости кредита в Excel

Согласно Закону о потребительском кредите для расчета полной стоимости кредита (ПСК) теперь применяется новая формула. ПСК определяется в процентах с точностью до третьего знака после запятой по следующей формуле:

• ПСК = i * ЧБП * 100;
• где i – процентная ставка базового периода;
• ЧБП – число базовых периодов в календарном году.

Возьмем для примера следующие данные по кредиту:

Для расчета полной стоимости кредита нужно составить график платежей (порядок см. выше).

Нужно определить базовый период (БП). В законе сказано, что это стандартный временной интервал, который встречается в графике погашения чаще всего. В примере БП = 28 дней.

Далее находим ЧБП: 365 / 28 = 13.

Теперь можно найти процентную ставку базового периода:

У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8

Примечание. Чтобы получить проценты в Excel, не нужно умножать на 100. Достаточно выставить для ячейки с результатом процентный формат.

ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.

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

Потребительский кредит — займ, который вы берете на различные нужды потребления. К примеру, вы хотите купить телевизор в магазине или стиральную машину, или поехать в отпуск.
Покупка тура у оператора — это покупка услуги. Т.е. вы потребляете услугу и берете потребительский займ.
Калькулятор потребительского кредита предназначен для расчета ссуд наличными с учетом комиссий и страховки. Здесь вы можете выбрать подходящий кредит и оформить онлайн заявку

Кредит физическим лицам от Хоум Кредит банка Лицензия ЦБ РФ №316

• Сумма кредита от 10 тыс. до 1 млн. рублей
• Низкая ставка от 7.9 % годовых
• Срок кредита до 6 лет.
• Ваш возраст от 22 до 64 лет.

Оформить кредит На официальном сайте
Заявка на кредит в Совкомбанк Лицензия ЦБ РФ №963

• Сумма до 1 млн. руб, ставка от 12 % в год
• Вам дадут кредит, если вы старше 35 лет
• Для получения нужно 2 документа
• 0% по кредиту при выполнении условий акции
• Быстрое решение за 30 мин.

Подать заявку На официальном сайте
Кредит наличными в банке Тинькофф Лицензия ЦБ РФ №2673

• Вам выдается карта, на которую дают кредит.
• Снятие с карты бесплатно в любом банкомате
• Сумма кредита до 1 млн. рублей
• Срок кредита до 36 месяцев
• Процентная ставка по кредиту от 14,9%

Оформить кредит На официальном сайте

Варианты расчета

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

Как сравнить два кредита

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

Порядок расчета займа с помощью калькулятора

Существует 2 варианта расчета займа
Первый — предварительный расчет, когда вы хотите взять наличные в кредит. Для данного расчета дата первого платежа не нужна. Ее можно оставить по умолчанию. Она не влияет на размер ежемесячного платежа.
Сумма займа — она прописана в кредитном договоре и берется без учета первоначального взноса на товар или услугу.
Процентная ставка — номинальная ставка по займу без учета комиссий и страховки. Берется из договора займа. Можно ввести 3 знака после запятой.
Выражается без деления на сто.
Срок — целое число месяцев на которе берется займ. Если у вас 2 года к примеру, то нужно ввести 24 месяца
Второй вариант — расчет существующего займа
Далее идет поле — дата первого платежа. Этот параметр уже важен когда вы взяли кредит
Для взятой ссуды важен расчет по дате. Т.е при построении графика указывается дата очередной выплаты — номер дня в месяце.
Расчет с учетом дат важен при досрочных погашениях. От даты досрочного внесения средств зависит то, в каком месяце будет новый уменьшенный платеж.

Как использовать калькулятор?

После ввода необходимых указанных выше данных нужно нажать на кнопку расчет.
После ее нажатия возможны следующие варианты

• Ошибки при вводе данных. Обратите внимание — даты нужно вводить через точку в формате дд.мм.уууу. Суммы вводятся в через точку, ставка может иметь 3 знака после запятой
• Произошел успешный расчет кредита. Построился график платежей. Посчиталась переплата по ссуде

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

Экспорт данных в Excel

После расчета вы сможете сохранить результаты на своем компьютере. Для этого нужно нажать на ссылку «Получить Exсel файл». Произойдет генерация файла в формате Excel 2003 и появится ссылка «Скачать файл». Нужно нажать на эту ссылку и файл будет сохранен у вас. Вы можете распечатать файл и вернуться к нему в любой момент.
При расчете кредита ссылка на данный файл исчезает и вам нужно будет снова сгенерировать Excel-файл.
Рекомендую добавить калькулятор в закладки для удобства использования в дальнейшем.

Популярные вопросы и ответы

Как рассчитать процент годовых по кредиту?

Процент годовых не считается. Ставку по кредиту годовых определяет банк на основании вашего дохода, кредитной истории и других параметров скоринга. Проценты по кредиту начисляются каждый день, но платить их нужно каждый месяц. Если хотите узнать ставку в месяц, нужно ставку годовых разделить на 12. Аналогично — если в день, то умножить разделить на 365(366) Если хотите посчитать процент за каждый месяц пользования кредитом, то нужно ставку в день умножить на число дней в месяце и на остаток долга в начале месяца.

Дмитрий Тачков Разработчик калькуляторов Финансовое образование Опыт работы в банке Специалист по микрозаймам Создатель калькулятора Привет. Я разработчик данного калькулятора. Буду рад, если вы оцените⭐ калькулятор, выбрав один из 3х вариантов ниже. Ваши оценки помогут улучшить работу🔧 инструмента. За оценку респект и спасибо. Непонятно 7 Сойдет 9 Годнота 44

Как в учете организации-заемщика отразить получение краткосрочного процентного займа в белорусских рублях эквивалентно иностранной валюте, его возврат и уплату процентов?

Организация (заемщик) заключила с юридическим лицом — резидентом Республики Беларусь (заимодавец) договор краткосрочного денежного займа в белорусских рублях эквивалентно 70 000 долл. США с целью пополнения собственных оборотных средств (на оплату товаров (работ, услуг)).

Заем поступил на расчетный счет организации в прошлом месяце. В текущем месяце заем был возвращен в полном объеме в установленный договором срок.

В соответствии с договором за пользование займом заемщик в день возврата суммы основного долга уплачивает заимодавцу проценты в белорусских рублях по курсу белорусского рубля к доллару США, установленному Национальным банком (далее — курс Нацбанка), на дату осуществления платежа.

Проценты по договору займа составили:

— за прошлый месяц (со дня получения по последний день месяца) — 278,08 долл. США;

— за текущий месяц (с 1-го числа месяца по день возврата) — 76,71 долл. США.

Курс Нацбанка (условно):

— на дату поступления займа на расчетный счет организации — 2,0442 руб. за 1 долл. США;

— на последний день прошлого месяца (начисления процентов за прошлый месяц) — 2,0348 руб. за 1 долл. США;

— на дату возврата займа (начисления процентов за отчетный месяц; уплаты процентов) — 2,0487 руб. за 1 долл. США.

Общие положения

По договору займа одна сторона (заимодавец) передает в собственность другой стороне (заемщику) деньги или другие вещи, определенные родовыми признаками, а заемщик обязуется возвратить заимодавцу такую же сумму денег (сумму займа) или равное количество других полученных им вещей того же рода и качества. Такой договор считается заключенным с момента передачи денег или других вещей <*>.

В случае когда заимодавцем является юридическое лицо, договор займа заключается в письменной форме независимо от суммы <*>.

Заимодавец имеет право на получение с заемщика процентов на сумму займа в размерах и порядке, определенных договором. При отсутствии иного соглашения проценты выплачиваются ежемесячно до дня возврата суммы займа <*>.

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

Бухгалтерский учет

Расчеты по полученным организацией краткосрочным (на срок не более 12 месяцев) займам, а также начисленным и уплаченным по ним процентам отражаются на счете 66 «Расчеты по краткосрочным кредитам и займам», к которому могут быть открыты субсчета:

— 66-2 «Расчеты по краткосрочным займам» — для учета наличия и движения краткосрочных займов;

— 66-3 «Расчеты по процентам по краткосрочным кредитам и займам» — для учета начисления и уплаты процентов по полученным краткосрочным кредитам и займам.

Получение краткосрочных займов отражается по дебету счета 51 «Расчетные счета» и кредиту счета 66 «Расчеты по краткосрочным кредитам и займам» (субсчет 66-2 «Расчеты по краткосрочным займам»). Погашение отражается обратной проводкой.

Начисленные по полученным краткосрочным займам проценты (за исключением процентов по займам, которые относятся на стоимость инвестиционных активов в соответствии с законодательством) признаются в бухгалтерском учете в составе расходов по финансовой деятельности ежемесячно и отражаются по дебету счета 91 «Прочие доходы и расходы» (субсчет 91-4 «Прочие расходы») и кредиту счета 66 «Расчеты по краткосрочным кредитам и займам» (субсчет 66-3 «Расчеты по процентам по краткосрочным кредитам и займам»). Уплата процентов отражается по дебету счета 66 «Расчеты по краткосрочным кредитам и займам» (субсчет 66-3 «Расчеты по процентам по краткосрочным кредитам и займам») и кредиту счета 51 «Расчетные счета» <*>.

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

Курсовые разницы, возникающие при погашении кредиторской задолженности, включаются в состав доходов и расходов по финансовой деятельности и отражаются по дебету (кредиту) счета 66 «Расчеты по краткосрочным кредитам и займам» в корреспонденции c кредитом (дебетом) счета 91 «Прочие доходы и расходы» <*>.

НДС

При получении денежного займа объект обложения НДС у заемщика отсутствует <*>.

Курсовая разница НДС не облагается <*>.

Налог на прибыль

Проценты за пользование займом (за исключением процентов по займам, которые относятся на стоимость инвестиционных активов в соответствии с законодательством, а также процентов по просроченным платежам по основному долгу по займам), признаваемые в бухгалтерском учете расходами, принимаются в качестве затрат по производству и реализации, учитываемых при налогообложении прибыли <*>.

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

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

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

Таблица бухгалтерских записей

	Дебет	Кредит	Сумма, руб.	Первичный документ
Бухгалтерские записи в предшествующем месяце
Получен краткосрочный заем в денежной форме (70 000 долл. США x 2,0442)	51	66-2	143 094	Платежное поручение, выписка банка по расчетному счету, договор займа
Произведена переоценка кредиторской задолженности по основному долгу <*> ((2,0442 — 2,0348) x 70 000 долл. США)	66-2	91-1	658	Бухгалтерская справка-расчет
Начислены проценты по договору займа <**> (278,08 долл. США x 2,0348)	91-4	66-3	565,84	Расчет процентов, бухгалтерская справка-расчет
Бухгалтерские записи в отчетном месяце
Возвращен краткосрочный заем (70 000 долл. США x 2,0487)	66-2	51	143 409	Платежное поручение, выписка банка по расчетному счету, договор займа
Произведена переоценка кредиторской задолженности по основному долгу <*> ((2,0487 — 2,0348) x 70 000 долл. США)	91-4	66-2	973	Бухгалтерская справка-расчет
Начислены проценты по договору займа <**> (76,71 долл. США x 2,0487)	91-4	66-3	157,16	Расчет процентов, бухгалтерская справка-расчет
Перечислены (уплачены) проценты по договору займа ((278,08 долл. США + 76,71 долл. США) x 2,0487)	66-3	51	726,86	Платежное поручение, выписка банка по расчетному счету
Произведена переоценка кредиторской задолженности по процентам <*> ((2,0487 — 2,0348) x 278,08 долл. США)	91-4	66-3	3,86	Бухгалтерская справка-расчет