формула расчета процентов по договору займа в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9 Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Как рассчитать проценты по займу

Как начисляются проценты по займу?

Какие есть варианты начисления процентов?

— вознаграждение в твердой сумме с единовременной либо ежемесячной выплатой;

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

— начисление процентов может быть договором не предусмотрено. В этом случае ( но только если это не безвозмездный займ, при котором максимальная сумма долга ограничена 50 МРОТ и который может быть выдан только одним физическим лицом другому), проценты будут начисляться по ставке рефинансирования Банка России, которая с 1 января 2016 года равна ключевой ставке.

Что такое сложные проценты?

Пример расчета процентов

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

Как рассчитать проценты по займу в зависимости от способа их начисления?

1. Формула для расчета простых процентов выглядит следующим образом:

Сумма процентов по займу = Сумма займа * Процентная ставка / 365 (366) дней * Количество дней пользования займом;

2. Сложные проценты ( иначе говоря капитализированные) рассчитываются путем умножения изначальной суммы займа на величину, равную ( 1+r)n, где r – процентная ставка ( выраженная в долях), а n – количество прошедших платежных периодов.

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

Крайне важным в процессе обслуживания долга является осуществление всех предусмотренных договором платежей в строго оговоренные сроки. В случае их нарушения условиями всех договоров займа предусмотрена неустойка (пени). Штраф за просрочку также учитывается при расчете итоговой суммы процентов по займу. Согласно закону «О потребительском кредита ( займе)» максимальный размер неустойки не может превышать 20% годовых ( 0,05% в день). В случае, если условиями договора неустойка не оговорена, ее расчет производится по размеру ключевой ставки Банка России, информацию о значении которой возможно узнать на официальном сайте регулятора.

Источник

Как рассчитать проценты по займу

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

Формулы для расчета

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

Простые проценты

На практике расчет простых процентов по займу — тех, что чаще всего применимы МФО, осуществляется по формуле, имеющей нижеследующую структуру:

ДОЛГ = ЗАЕМ * (СТАВКА /100)) / ГОД * ПЕРИОД + ЗАЕМ, где:

В случае со сложными процентами формула совсем иная.

Сложные проценты

Здесь применяется нижеследующая формула (условимся, что по банковскому кредиту — в процентах годовых):

ДОЛГ = ЗАЕМ * (1 + (СТАВКА / 100) ) ^ ГОДЫ, где:

Это самая простая формула. На практике может выглядеть гораздо сложнее — например, если учитывается то, сколько раз в течение года осуществляется пересчет ставки в соответствии с кредитным договором.

Расчет при рефинансировании

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

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

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

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

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

Пример 1

Пусть Иванов взял в МФО заем в размере 10 000 рублей на 20 дней по ставке 1,5% в день в 2019 году.

Для начала подсчитаем показатель СТАВКА: 1,5% умножаем на 365, получается 547,5% годовых. Далее подставляем имеющиеся данные в формулу:

ДОЛГ = (10 000 * (547,5/100)) / 365 * 20 + 10 000 = 13 000 рублей.

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

Пример 2

Предположим, что Петров взял кредит в 100 000 рублей на месяц — 30 дней, в 2019 году. Ставка — 10% годовых. Подставляем показатели в формулу:

ДОЛГ = (100 000 * (10 / 100)) / 365 * 30 + 10 000 = 10 821,92 рубля.

Примечательно, что если в кредитном договоре — неважно, составляется он с МФО или с банком, не указана ставка, то используется ключевая ставка ЦБ РФ, действующая за период пользования денежными средствами.

Теперь — ознакомимся с примером начисления процентов по банковскому займу по сложной формуле.

Пример 3

Условимся, что Сидоров оформил кредит в банке на сумму 1 000 000 рублей под 10% годовых сроком на 5 лет. Подставляем значения в формулу:

ДОЛГ = 1 000 000 * (1+ (10/100)) ^ 5 = 1 610 510 рублей.

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

Источник

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

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

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

На размер переплаты и ежемесячного платежа влияет множество факторов. Один из них – порядок начисления процентов.

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

Состав суммы кредита

Кредит состоит не только из основной суммы, которую вы занимаете у банка. А также:

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

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

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

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

Чтобы рассчитать ПСК, нужно сложить сумму кредита – СК, проценты – %, сумму всех комиссий – СВК.

Допустим, вы хотите взять кредит на 10 000 рублей.

Переплата по процентам – 1 000 рублей.

Комиссии – 500 рублей.

ПСК: 10 000 + 1 000 + 500 = 11 500 рублей.

Что влияет на размер ставки по кредиту

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

Рассмотрим факторы, которые позволят снизить ставку:

Чем больше денег вы берете в долг, тем ниже будет размер переплаты.

Чем он больше – тем ниже ставка.

Кредитные организации более благосклонны к своим клиентам.

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

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

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

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

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

Какими бывают ежемесячные платежи

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

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

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

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

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

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

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

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

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

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

Рассчитать его можно самостоятельно или через специальные сервисы. Для этого вам понадобятся:

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

Рассчитать размер выплат можно разными способами. По старинке – вручную или в отделении банка. Либо более современным способом – в Excel или через специальные калькуляторы.

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

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

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

Вы взяли кредит на 300 тысяч руб. сроком на 2 года по ставке 12%.

Ежемесячная выплата составит 14 122 руб.

Переплата – 38 928 руб.

Общая выплата – 338 928 руб.

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

Чаще всего банки предлагают аннуитетный способ расчета по потребительским кредитам. Чтобы рассчитать его в Excel, в категории «Финансы», есть специальная функция под названием ПЛТ (PMT). Она рассчитывается следующим образом:

=ПЛТ (ставка; кпер; пс; [бс]; [тип]), где

«ставка» – это % ставка по кредиту в месяц.

В нашем случае это 12%/12

«кпер» – срок кредита в месяцах.

В нашем случае это 12*2=24

В нашем случае это 300 000

«бс» – конечный баланс, равный нулю.

«тип» – способ учета ежемесячных выплат.
1 – если выплаты приходятся на начало месяца, 0 – если на конец месяца.

Проведем расчет для нашего примера:

Результат тот же, что и в калькуляторе.

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

Формулы достаточно объемные и считать придется много, поэтому разберем отдельные примеры.

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

Формула аннуитетного платежа:

– размер ежемесячных выплат

– остаток – 300 000 рублей

ПС – % ставка в месяц = 12% / 12 = 0,01

ПП — % периоды до окончания срока (в месяцах)

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

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

Для этого размер ежемесячного взноса умножаем на срок кредита в месяцах и вычитаем основной заем.

14 122 х 24 – 300 000 = 38 928 руб.

Как рассчитать дифференцированный платеж

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

Она не изменяется и рассчитывается простым делением размера займа на срок займа.

300 000 / 24 = 12 500 руб.

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

Проценты по кредиту = остаток тела кредита х ПС

ПС = % ставка в месяц = 12% / 12 = 0,01

300 000 х ПС = 300 000 х 0,01 = 3 000

Это и есть формула расчета процентов по кредиту.

Итак, наш первый взнос составляет 12 500 + 3 000 = 15 500 рублей. Следующие выплаты будут уменьшаться. Давайте рассмотрим их в таблице.

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

Источник