Как посчитать процент переплаты. Расчет полной стоимости кредита в Excel

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

Как посчитать процент переплаты. Расчет полной стоимости кредита в Excel

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

Быстро сориентироваться в мудреных формулах, рассчитать проценты, суммы выплат, переплату позволяют функции программы Microsoft 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).

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



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

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

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

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

где:

  • ДП – ежемесячный платеж по кредиту;
  • ОСЗ – остаток займа;
  • ПП – число оставшихся до конца срока погашения периодов;
  • ПС – процентная ставка за месяц (годовую ставку делим на 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. Достаточно выставить для ячейки с результатом процентный формат.

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

Скачать кредитный калькулятор в Excel

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

Источник: https://exceltable.com/otchety/kalkulyator-rascheta-kredita

Как самому рассчитать полную стоимость взятого кредита

Как посчитать процент переплаты. Расчет полной стоимости кредита в Excel

Реклама

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

Центробанк РФ обязал все банки и микрофинансовые компании указывать ПСК крупным шрифтом в верхнем углу справа — в % и рублях.

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

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

Почему ПСК выше процентной ставки по договору

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

На что обращать внимание при чтении кредитного договора ⇒

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

Банки, выдающие кредиты под самый низкий процент ⇒

Фактическая кредитная ставка — это проценты, начисляемые на сумму основной задолженности (тело займа). Она ниже ПСК, потому что помимо % предусмотрены и другие платежи.

Какие платежи входят в стоимость кредита

Согласно законодательству, кредитор вправе включать в ПСК следующие статьи:

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

Какие банки выдают ссуды без страховки ⇒

Что банки не учитывают в ПСК

Кредиторы не включают в полную сумму кредита следующие расходы:

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

Все перечисленные платежи могут влиять на переплату, но фактически они не учитываются в ПСК.

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

Как рассчитать стоимость кредита

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

ТОП банков, в которых можно взять кредит срочно уже сегодня ⇒

Основная формула расчета ПСК:

ПСК = i * ЧБП * 100

Здесь ЧПБ — это число базовых периодов в календарном году. Под этим показателем понимают количество временных промежутков между платежами по кредиту за год. Например, по графику клиент должен вносить выплаты раз в месяц, значит ЧПБ равен 12. Если платежи вносятся ежеквартально, ЧПБ — 4, если раз в полгода — 2 и т.д.

Базовый период (БП) — это временной отрезок между платежами по кредиту. Например, если заемщик погашает долг ежемесячно, БП равен 1 месяцу.

Сейчас расчет ведется уже по новой формуле:

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

, где:

m — число денежных потоков, которое равно количеству выплат по кредиту + еще один платеж при выдаче займа;

ДП k — размер k-го денежного платежа (выдача кредитных средств — со знаком «-», возврат долга — «+»;

qk — количество полных БП с момента предоставления ссуды до k-го платежа;

Реклама

ek — период, выраженный в долях БП с момента окончания qk -го БП до внесения k-го платежа. Рассчитывается по формуле:

ek = mod [(ДПk -ДП1 )/БП]/БП, где mod — остаток от деления.

Эта формула используется в алгоритмах расчета банковских программ, которые автоматически определяют стоимость ссуды после ввода всех параметров. Для самостоятельных расчетов вручную эта формула не используется. Вычислить ПСК удобнее в Excel с помощью специальных формул.

Что и как говорить в банке, обращаясь за кредитом ⇒

Как посчитать ПСК в Excel

Параметр i можно рассчитать посредством функции ВСД. Допустим, заемщик получил 100 т.р. на 12 месяцев под 19%.

Расчет i будет выглядеть следующим образом:

После этого необходимо умножить полученный результат на ЧПБ и получить стоимость кредита. В нашем случае — 0,01584*12*100 = 19%. То есть ПСК совпадает с процентной ставкой по договору. Если банк начисляет дополнительные комиссии по выдаче кредита, ежемесячный платеж увеличивается. Соответственно возрастает и общая стоимость ссуды.

Кроме того, в Excel встроена функция определения внутренней нормы доходности (IRR), которая подходит для расчета ПСК. Эта функция называется ЧИСТВНДОХ.

Допустим, заемщик получил кредит на сумму 120000 руб. на 12 месяцев, под 28% годовых. Для определения ПСК введите данные из договора. В первой ячейке в столбце «Сумма» укажите размер полученного кредита со знаком «-» — это размер первого денежного потока.

Далее в ячейке С15 введите знак равенства и выберите функцию ЧИСТВНДОХ. В формуле с помощью выделения укажите все значения из столбца «Сумма», а потом — из «Дата», после чего нажмите «Ввод».

Полученное значение ПСК — 32,04%. Это значит, что стоимость кредита сроком на 1 год под 28% годовых составит 32,04%.

Если ссуда была предоставлена без дополнительных сборов и комиссий, ПСК будет равна договорной процентной ставке.

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

Скачать шаблон расчета ПСК по новой формуле в Excel ⇒

Расчет по упрощенной формуле

Упрощенная формула позволяет вычислить стоимость ссуды вручную:

Алгоритм расчета:

  • сумму выплат за весь срок кредитования (S) разделить на полученную от кредитора сумму (S0 ) и вычесть единицу;
  • полученную цифру разделить на количество лет кредитования и умножить на 100.

ПСК=(S/S0 — 1)/n*100

Например, потребитель получил 2 млн. руб. на 2 года, под 10% годовых с ежегодным дополнительным сбором по 12 тыс. руб.. Тип платежей аннуитетный, размер ежемесячной выплаты составляет 47144,93 р. с учетом % и дополнительных сборов.

Общая сумма долга:

47144,93 р.* 24 = 1131478,32 р.

Подставляем эту цифру в упрощенную формулу:

Цена кредита в год составляет 6,57%, за два года — 13,15%.

Как снизить переплату по взятой в банке ссуде ⇒

Среднерыночную ПСК можно уточнить на сайте ЦБ РФ. Этот срез по ТОП-100 ведущих банков страны.

Согласно законодательству РФ, цена кредита не может превышать среднее значение по стране более, чем на 1/3. Например, если потребительские займы выдают под средний процент 25%, максимальная ставка равна 32,5%.

Реклама

Все полезные статьи о кредитах

Вам помогла эта статья? Не скупитесь, поделитесь ей с другими!

Источник: https://UsloviyaKredita.ru/polnaya-stoimost-kredita

Как рассчитать переплату по кредиту: калькулятор переплаты по кредиту

Как посчитать процент переплаты. Расчет полной стоимости кредита в Excel

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

Частные клиенты банков обычно берут кредиты наличными либо заводят кредитную карту. При этом далеко не все из них знают как рассчитать переплату по кредиту и сколько придётся выплатить банку за пользование финансами.

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

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

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

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

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

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

Что включает в себя переплата по кредиту

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

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

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

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

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

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

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

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

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

Аннуитетный платёж может быть посчитан по формуле:

где СК – сумма кредита, ПС – процентная ставка в долях за месяц, М – количество месяцев, на которое берётся кредит.

Суммарная переплата может быть посчитана по формуле:

Суммарная переплата = (аннуитетный платёж * срок кредита в месяцах) – сумма кредита.

Так мы получим общую сумму, которую мы должны будем заплатить с учётом суммы кредита и переплаты по нему.

Электронные таблицы

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

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

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

Кредитный калькулятор

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

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

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

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

Услуга банка

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

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

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

Источник: https://creditcalculator.ru/rasschitat-pereplatu-po-kreditu.html

Расчет кредита в Excel

Как посчитать процент переплаты. Расчет полной стоимости кредита в Excel
37558 25.06.2014 Скачать пример

Кто как, а я считаю кредиты злом. Особенно потребительские. Кредиты для бизнеса – другое дело, а для обычных людей мышеловка”деньги за 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)

Источник: https://www.planetaexcel.ru/techniques/11/202/

Полная стоимость кредита(займа) – пример/алгоритм расчета

Как посчитать процент переплаты. Расчет полной стоимости кредита в Excel

Не так давно вступил в силу Федеральный закон №353, обязывающий финансовые организации раскрывать информацию о так называемой «Полной стоимости кредита(займа)» (далее — ПСК). В этой статье (в принципе относящейся только к трудящимся в финансовой сфере), я бы хотел привести пример расчета ПСК.

Возможно, кому-то пригодится.

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

Еще важнее! Вся приведенная ниже информация актуальна для случая, когда кредит выдается ОДНИМ платежом, т.е. заемщик получает денежные средства один раз, а возвраты происходят по заранее определенному графику платежей. Такой вариант покрывает 99% выдаваемых кредитов (кредитные карты не в счет). Собственно, вот сам зверь:

Понимаем значения терминов

ПСК определяется как произведение 3 величин – i, ЧБП и числа 100. Разберем используемые термины и обозначения:

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

    Если в графике платежей по договору потребительского кредита (займа) отсутствуют временные интервалы между платежами продолжительностью менее одного года или равные одному году, то БП – один год. Фактически БП – это наиболее часто встречающийся временной интервал между платежами.

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

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

    Если платежи ежемесячные, то БП=365/12~=30

  2. Определение в законе весьма размытое, но как я понимаю – это количество базовых периодов, которые «влезают» в один календарный год, т.е.

    :

    • Для стандартного графика платежей с ежемесячными выплатами: ЧБП = 12
    • Ежеквартальные выплаты: ЧБП=4
    • Выплаты раз в год или реже: ЧБП=1
    • Если график платежей хитрый: например предусмотрено сначала 2 выплаты раз в квартал, а затем 6 выплат раз в месяц, затем 3 выплаты раз в день, то базовый период – 1 месяц. А ЧБП=12 (12 БП за календарный год).

  3. Это невозможно понять (по крайней мере мне). Возможно, в определении числа i есть какой-то смысл, но этот смысл уловить интуитивно не представляется возможным. Как считать i — разберем в следующем разделе.

Как считать i

Оставим на потом попытки понять «физический» смысл числа i, и дадим ему такое определение: Число i вычисляется путем решения следующего уравнения:

где:

  • m – количество денежных потоков, что равно количеству платежей в графике платежей плюс один (еще один платеж возникает из-за первого платежа – выдачи кредита).
  • ДПк – размер к-го денежного потока (выдача кредита со знаком «минус», возвраты со знаком «плюс»).
  • Qк — количество полных базовых периодов с момента выдачи кредита до k-го денежного потока. Qк можно вычислить по формуле:Qк=floor[ (ДПк-ДП1)/БП ], где
    • ДПк – дата к-го денежного потока,
    • ДП1 – дата первого денежного потока (т.е. дата выдачи),
    • БП – срок базового периода,
    • floor[ ] – округление вниз до целого.

  • Eк — здесь сразу напишем формулу, чтобы ваш мозг не взорвался от формулировки в законе:
    Ek=mod[ (ДПк-ДП1) /БП ]/БП, где mod – остаток от деления

Алгоритм расчета ПСК

Входящие данные: два массива. Ключ – номер денежного потока, значения – даты платежа и сумма платежа. Исходящие данные: значение ПСК (число).

Порядок расчета:

  1. Вычисляем ЧБП(число базовых периодов). Число базовых периодов – сколько таких периодов «влезет» в 365 дней, т.е. ЧБП=floor[ 365/БП ].
  2. Для каждого k-го платежа считаем ДПk, Qk, Ek.
  3. Методами приближенного вычисления в точности до двух знаков после запятой считаем i.
  4. Умножаем ЧБП*i*100.

Код!

Есть готовое решение на javascript, а также на VBA (будет даже excel-файл для расчетов).
Зачем VBA и Excel?Если вдруг у вас случится пожар и ничего не будет работать 1 сентября 2014 года, то самое разумное — это разослать excel-табличку по местам заключения договоров, чтобы можно было рассчитывать ПСК хотя бы так в первое время.
В примерах взят график для кредита в 100 000 рублей на 3 месяца по ставке 12% годовых. Дата выдачи — 1 сентября 2014:

КлючДата денежного потокаСумма денежного потока
001.09.2014-100 000
1

Источник: https://habr.com/post/233987/

Как правильно произвести расчет процентов по кредиту в таблице Эксель

Как посчитать процент переплаты. Расчет полной стоимости кредита в Excel

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

Российские банки предлагают два варианта исполнения обязательств по договору – аннуитетный и дифференцированный.

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

Особенности кредитных платежей по аннуитетной схеме

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

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

Доля самого займа растет, а величина по процентам меняется в сторону снижения.

Аннуитет имеет следующую специфику:

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

Кредитный калькулятор в Excel

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

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

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

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

Обратите внимание! Программа достаточно популярна в бухгалтерском учете.

Для того чтобы получить данные, потребуется минимум информации:

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

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

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

Если ввести в программу верхние три пункта, сервис в автоматическом режиме подсчитает последний, четвертый.

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

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

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

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

Рассмотрим более детально, какие формулы следует применять при стандартном, аннуитетном варианте погашения долга.

Какие формулы использовать

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

А = К х С, где:

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

В свою очередь, коэффициент получаем по формуле:

К = (i * (1 + i)n) / ((1+i)n-1), где:

  • i – ставка в месяц. Она привязана к размеру годовой переплаты по процентам и составляет двенадцатую ее часть;
  • n – период актуальности кредитного договора. Данный промежуток времени следует исчислять только в календарных месяцах.

Расчет суммы переплаты при досрочном погашении

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

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

Калькулятор по кредиту с нерегулярными оплатами

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

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

Это позволит погасить долг в более короткие сроки, перекрыв, тем самым, переплату по процентам.

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

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

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

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

ПСК = i х ЧБП х 100, где

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

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

Большинство отечественных финансовых учреждений применяют 28 дневный срок. Таким образом, базовый период равен 28. Следовательно, ЧПБ = 365 (количество дней в году) : 28. Получаем 13.

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

Теперь, имея на руках все необходимые цифры, можно подставлять их в формулу. Например, клиент берет в долг 400 000 рублей, сроком на 2 года (24 месяца), под ставку 22% годовых.

Выходит:

ПСК = 22/13 х 13 х 100 = 22%

Получаем, что при отсутствии дополнительных или скрытых комиссионных начислений, нередко устанавливаемых банками, ПСК полностью совпала с величиной ставки.

Чтобы узнать, сколько же всего необходимо отдать компании, чтобы кредит считался закрытым, нужно к размеру взятых денежных средств прибавить ПСК – в нашем случае, это 22%.

В этом видео подробно рассказано о том, как правильно подсчитать проценты по кредиту в таблице Эксель:

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

Источник: https://kredit-pensioner.ru/raschet-protsentov-po-kreditu-v-tablitse-eksel/

Поделиться:
Нет комментариев

    Добавить комментарий

    Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.