Главная / Бухгалтерия и кадры

Рассчитывать кредит на калькуляторе. Как посчитать ежемесячный платеж по кредиту? Экспорт данных в Excel

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

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

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

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

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

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

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

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

А = К * S

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

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

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

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

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

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

Расчет платежей в 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 дней.

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

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

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

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

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

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

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

Настройки Кредитного калькулятора

Способ расчета
Возможен расчет кредита и платежей, как по Сумме кредита, так и по Стоимости покупки и первоначальному взносу. При расчете кредита по Стоимости покупки сначала рассчитывается сумма кредита, при этом проценты и комиссии на первоначальный взнос не начисляются.

Выбор валюты кредита
Кредитный калькулятор может рассчитывать кредит онлайн в одной из 3-х валют: рубли, доллары или евро.

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

Процентная ставка
Традиционно процентная ставка исчисляется из расчета процентов/год. Изменив настройки кредитного калькулятора можно рассчитывать платежи исходя из месячной процентной ставки.

Тип платежей
Обычно банки для расчета кредита используют аннуитетный метод расчета платежей по кредиту (равные ежемесячные платежи). Однако возможен и второй вариант - дифференцированные платежи (начисление процентов на остаток). Используя выпадающее меню, выберите нужный Вам тип расчета платежей. Более подробную информацию о типах и способах расчета смотрите в разделах аннуитетный калькулятор или калькулятор дифференцированных платежей .

Дополнительные настройки

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

Ежемесячная комиссия
Учитывается в общей стоимости кредита и в ежемесячных платежах

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

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

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

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

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

Как работает калькулятор?

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

Чтобы воспользоваться сервисом:

Введите нужную вам сумму и выберите валюту займа;
Установите удобный для вас срок погашения;
Введите процентную ставку, на которую рассчитываете;
И предполагаемую дату выдачи;
Выберите схему оплаты – аннуитетную (то есть равными долями) или дифференцированными платежами;
Собираетесь закрыть кредит как можно быстрее? Добавьте досрочные погашения.

Когда вы кликните на кнопку «Рассчитать расходы», кредитный калькулятор подсчитает займ с учетом ваших параметров и представит сводку по нему. В ней указаны размер ежемесячных платежей, общую сумму и размер начисленных процентов. А в графике, также представленном на странице, вы увидите, какая часть от взноса пойдет в основной долг, а какая – в стоимость займа, а также остаток задолженности после каждой выплаты.

Аннуитетный или дифференцированный платеж?

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

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

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

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

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

Настройки Кредитного калькулятора

Дополнительные настройки

Ежемесячная комиссия
Учитывается в общей стоимости кредита и в ежемесячных платежах

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

Как рассчитать ежемесячный платеж?

Прежде всего, следует отметить, что существует два варианта ежемесячных платежей:

Аннуитетные. Данная схема погашения кредита предусматривает регулярные выплаты равного размера на протяжении всего срока действия займа. Следствием применения такого варианта является одинаковая финансовая нагрузка на заемщика;
Дифференцированные. Размер ежемесячной выплаты рассчитывается с учетом оставшейся на данный момент задолженности по кредиту. В результате использования такого варианта погашения займа финансовая нагрузка на клиента банка постепенно сокращается.

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

Расчет ежемесячного платежа по кредиту онлайн

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

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

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

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

Расчет в отделении банка

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

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

Другие способы расчета

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

Формула расчета

Тем не менее, в некоторых случаях вычисления вручную все-таки осуществляются. При этом используются следующие формулы:

для аннуитетного платежа:
- Платеж=Кредит*(Проц+(Проц/(1+Проц)*Мес-1)), где
  - Платеж - размер ежемесячной аннуитетной выплаты;
  - Кредит - сумма кредита;
  - Проц - величина процентной ставки;
  - Мес - срок действия кредита.
- для дифференцированного платежа:
  - Платеж=Кредит/Мес + Остаток*Проц./12, где
    - Платеж - размер дифференцированного платежа;
    - Кредит - сумма кредита;
    - Мес - срок действия займа;
    - Остаток - задолженность по кредиту на момент начисления процентов;
    - Проц - величина процентной ставки.

Из чего состоит ежемесячный платеж?

Приведенные выше формулы учитывают только две главных составляющих ежемесячной выплаты, направляемые на:

Погашение основного долга по кредиту;
Выплату начисленных за пользование заемными средствами процентов.

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

Особенности платежа в зависимости от вида кредитования

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