Рассчитать платёж по кредиту

Разрабатываем калькулятор дифференцированных платежей в Excel

Прежде всего давайте разберемся, по какому принципу работает наш калькулятор. Откройте скачанный «экселевкий» файл. В верхнем левом углу страницы вы увидите две таблицы. Они называются: «Укажите данные для расчёта» и «Результаты расчёта». Также сверху над всеми столбцами нашей страницы Excel есть буквы A, B, C, D, E, F и т.д., а слева напротив строк – цифры 1, 2, 3, 4, 5, 6 и т. д. Именно эти буквы и цифры определяют координаты каждой ячейки таблицы.


Кликните левой кнопкой мыши по ячейке со значением «5958р.», которое находится в результатах расчёта в строке «Переплата по кредиту». В нашем калькуляторе эта ячейка имеет координаты B8. Вот вам картинка для наглядности:

На изображении данную ячейку мы обвели красной линией и обозначили цифрой один

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

Например, на нашем изображении буква B сверху и цифра 8 слева изменили цвет фона с серо-голубого на желтоватый. Также в верхней строке формул, слева от которой есть кнопка «fx» (на рисунке она обведена красным и обозначена цифрой два) указано значение или формула, по которой выполняется расчёт данных для выделенной ячейки. В нашем примере для ячейки с координатой B8 выполняется расчёт по следующей формуле: =B7-B2. В окне с координатой B7 указана общая сумма выплат по кредиту, которая в нашем примере равна 55 958 рублей, а B2 – это сам кредит, который равен 50 000 рублей. Выполнив простое математическое вычисление, наша программа занесла в ячейку B8 значение 5958 (55 958 – 50 000=5958).

Как видите, Microsoft Excel работает достаточно просто. По аналогичному принципу заданы формулы и значения для остальных ячеек нашего кредитного калькулятора дифференцированных платежей. Давайте рассмотрим, как они рассчитаны. Щёлкаем мышкой по изображению:

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

  • «Ежемесячный платёж» – это ежемесячный дифференцированный платёж по займу. Он состоит из двух частей: суммы, идущей на погашение процентов (ячейка F14), и суммы, идущей на погашение тела кредита (ячейка G14). Именно потому ежемесячный платёж в первой строке рассчитан по формуле: =F14+ G14.
  • «Погашение процентов» – здесь работает формула расчёта процентов по кредиту за данный период: остаток задолженности (в первом платеже он равен сумме кредита 50 000 руб., вынесенную в ячейку H13) умножить на годовую процентную ставку (она равна 22% и вынесена в ячейку A14) и разделить на 12 (мы вынесли это значение в ячейку B14). Собственно, эти условия и прописаны в формуле для ячейки F14: =H13*A14/B14. Кстати, вместо B14 можно просто указать фиксированную цифру – 12.
  • «Погашение тела кредита» – это фиксированное значение, которое не меняется на протяжении всего срока кредитования. Рассчитывается этот показатель очень просто: сумма кредита (ячейка B2) делится на общий срок кредитования (ячейка B4). В итоге для ячейки G14 получаем такую формулу: = B2/B4.
  • «Долг на конец месяца» – из суммы долга на конец предыдущего месяца (в первом платеже он у нас равен сумме кредита – 50 000 рублей и вынесен в ячейку H13) вычитаем выплату по телу кредита в текущем периоде (4167 рублей – ячейка G14). В результате, долг на конец месяца по первому платежу у нас равен 45 833 рубля (50 000 – 4167 = 45 833), что и записано в формуле для ячейки H14: = H13- G14.

Вот таким нехитрым способом разработан кредитный калькулятор дифференцированных платежей в Excel. Он рассчитан на кредиты сроком до 12 месяцев. При желании, вы можете его усовершенствовать и расширить данный диапазон до 24, 36 и более месяцев. В общем, теперь всё в ваших руках, друзья. Как говорится, мы вам дали удочку, а вы сами решайте, что с ней дальше делать.

Портал temabiz.com – ваш надёжный информационный помощник в вопросах кредитования. Оставайтесь с нами!

Наши группы:

Тот, который законный.

Расчёт процентов и по аннуитетному методу и по дифференцированному очень похож и подчиняется обычной житейской логике. В обоих случаях можно использовать как формулу простых, так и формулу сложных процентов. Главное — помнить, что в кредитных отношениях банка и гражданина-потребителя установление в договоре сложных процентов ЗАПРЕЩЕНО законом!

Однако, банки не любят заморачиваться с законными правами потребителей и разделять своих клиентов на простых граждан и на субъектов, занимающихся предпринимательской деятельностью. Всех под одну гребёнку! Со всех — прибыль по максимуму! 

Основываясь на этом ЗАПРЕТЕ и исходя из элементарной логики расчёта процентов, надо усвоить главный момент:

Проценты начисляются на ту сумму остатка задолженности по кредиту, которой заёмщик ФАКТИЧЕСКИ пользовался в предыдущем периоде.

Теперь рассмотрим  4 вида начисления процентов:

  • Дифференцированный метод по формуле простых процентов.
  • Дифференцированный метод по формуле сложных процентов.
  • Аннуитетный метод по формуле простых процентов.
  • Аннуитетный метод по формуле сложных процентов.

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

Различие между ними лишь в используемом порядке начисления процентов. А точнее, в том — какая сумма для начисления процентов является базовой.

Что такое банковский день?

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

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

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

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

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

Банковский день отличается от обыкновенного рабочего дня рядом особенностей. Рассмотрим их более подробно:

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

Банковский день для юридических лиц

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

Банковский день для физических лиц

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

Классификация банковских расчетных операций

В зависимости от участников, расчетные операции банков подразделяются на две основные группы:1) Клиентские расчетные операции, при осуществлении которых участвуют в качестве основных субъектов как банки, так и их клиенты. Наиболее типичные:

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

2) Внутренние банковские расчетные операции или межбанковские расчеты, в качестве основных субъектов которых выступают банки.Межбанковские расчеты строятся на основе системы межфилиальных оборотов (МФО).Помимо межбанковских расчетов, финансовые учреждения осуществляют платежные операции между собой посредством корреспондентских счетов, открываемых каждым коммерческим банком в ЦБ. Коммерческие банки имеют также право открывать корреспондентские счета в других коммерческих банках и могут осуществлять по ним как свои операции, так и операции клиентов.В зависимости от вида используемых платежных инструментов выделяют наличные и безналичные расчетные операции, которые в свою очередь делятся на:

  • расчеты платежными поручениями (требованиями-поручениями);
  • расчеты чеками (дебетовые переводы средств с одного счета на другой);
  • расчеты векселями;
  • расчеты аккредитивами;
  • расчеты пластиковыми карточками;
  • расчеты наличными.

Исходя из форм расчетов, можно выделить:

  • Аккредитивные расчетные операции;
  • Инкассовые расчетные операции;
  • Переводные операции.
  • Клиринговые расчетные операции – осуществление платежей путем взаимозачета платежных обязательств и требований банка.

Расчёт суммы процентов по кредиту с помощью кредитного калькулятора онлайн

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

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

Нажмите кнопку “Рассчитать” и Вы получите полный график платежей, включая процентную ставку, указанные ранее комиссии и досрочные погашения кредита

Обратите внимание на блок “Общие данные”, в нём есть колонка “Начисленные проценты” – это и будет сумма процентов по кредиту. Если Вы хотите более детально рассмотреть в какой из месяцев и сколько заплатите процентов по кредиту, нажмите на кнопку “Загрузить ещё … количество строк”

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

Расчёт дифференцированного платежа


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

b = S / N , где b – основной платёж, S – размер кредита, N – количество месяцев.

Для расчета начисленных процентов нужно остаток кредита на указанный период умножить на годовую процентную ставку и всё это поделить на 12 (количество месяцев в году).

p = Sn * P / 12, где p – начисленные проценты, Sn — остаток задолженности на период, P – годовая процентная ставка по кредитy.

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

Sn = S — (b * n) , где n – количество прошедших периодов.

Пример расчёта графика выплат по дифференцированному кредиту

Для примера рассчитаем график платежей по кредиту в размере 100000 р. и годовой процентной ставкой 10%. Сроком погашения кредита возьмём 6 месяцев.

Определим размер основного платежа:

100000 / 6 = 16666,67

Определим размер выплаты за каждый месяц периода кредитовния:

Первый месяц: 16666,67 + (100000 – (16666,67 * 0))*0,1/12 = 17500 Второй месяц: 16666,67 + (100000 – (16666,67 * 1))*0,1/12 = 17361,11 Третий месяц: 16666,67 + (100000 – (16666,67 * 2))*0,1/12 = 17222,22 Четвёртый месяц: 16666,67 + (100000 – (16666,67 * 3))*0,1/12 = 17083,33 Пятый месяц: 16666,67 + (100000 – (16666,67 * 4))*0,1/12 = 16944,44 Шестой месяц: 16666,67 + (100000 – (16666,67 * 5))*0,1/12 = 16805,56

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

17500 + 17361,11 + 17222,22 + 17083,33 + 16944,44 + 16805,56 – 100000 = 2916,67

Результат подсчётов по нашему примеру на сайте www.platesh.ru будет выглядеть так:

Что подтверждает правильность наших расчётов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B$2. Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10

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

Что такое «банковские дни»?


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

  • Физическими лицами.
  • Юридическими лицами.
  • Учреждениями финансового направления.
  • Межбанковскими организациями.

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

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

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

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

Аннуитетные платежи – это…

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

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

Аннуитетные платежи – это регулярные платежи по кредиту, которые вносятся равными суммами. Часть денег из аннуитетного платежа идёт на погашение тела кредита, а часть – на уплату процентов.

Итак, главная отличительная особенность («фишка») аннуитетного платежа – это его фиксированная сумма. Заёмщик регулярно (чаще всего ежемесячно) вносит по кредиту платежи, размер которых не меняется на протяжении всего срока кредитования. Например, банк вам рассчитал аннуитетный платёж – 2536 руб. в месяц. Вот по 2536 рублей вы и будете платить ежемесячно до конца кредитования. Всё поняли? Отлично! Двигаемся дальше!

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

На самом деле всё не так, и этих проблем не существует. Запомните:

При аннуитетном погашении кредита, проценты авансом никто никому не платит! Проценты начисляются исключительно на остаток суммы основного долга.

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

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

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

Как видите, здесь работают простые математические законы, и нет никакого обмана, друзья! Переходим к достоинствам и недостаткам.


С этим читают