101 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Расчет годовой процентной ставки о кредиту в Excel

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

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

В данном уроке рассмотрим как рассчитать процентную годовую ставку по кредиту с равными платежами (аннуитет) имея другие параметры кредита:

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

Расчет годовой процентной ставки по кредиту

Годовая процентная ставка по кредиту с равными платежами (аннуитет) рассчитывается с помощью функции «СТАВКА», находится в категории функций «ФИНАНСОВЫЕ».

Аргументы функции «СТАВКА»:

  • Кпер — количество платежей по кредиту (срок кредитования);
  • ПТЛ — платеж по кредиту. В нашем примере, в ежемесячный платеж включена ежемесячная комиссия по кредиту. Для того чтобы правильно вычислить процентную ставку по кредиту, нам необходимо найти ежемесячный платеж без ежемесячной комиссии:

ПЛТ1 — платеж, который не учитывает сумму ежемесячной комиссии;

ПЛТ — общий ежемесячный платеж;

ПЛТ2 — сумма ежемесячной комиссии рассчитано как Процент от Суммы кредита.

  • Пс — сумма долга со знаком «-«. Так как в условиях присутствует единоразовая комиссия, то:

Пс = Сумма кредита + Сумма кредита * Единоразовая комисся

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

Формат ячейки Процентной ставки — «Процентный»

На ниже представленном рисунке показа формула расчета Процентной ставки одной строкой.

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

Добавить калькулятор в Excel

Как быстро посчитать основные параметры кредита (ипотеки) в Excel, как быстро сформировать кредитный калькулятор график платежей в Excel? Как посчитать параметры досрочного погашения кредита в Excel?

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

Калькулятор выводит основные параметры вашего кредита или может сформировать лист с кредитным калькулятором и графиком платежей ! Работает как по схеме «вернуть в конце срока», так и с аннуитетными (равномерными платежами).

При построении графика платежей по кредиту используются функции =ОСПЛТ и =ПРПЛТ для вычисления структуры ежемесячного платежа: сколько в нем составляет основная сумма и сколько проценты.

При возврате полной суммы кредита в конце срока для ее вычисления используется формула:

=сумма кредита*(100%+годовая ставка)^срок кредита(лет)

При аннуитетных (равномерных) платежах:

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

График платежей по кредиту в Excel

Также вы можете добавить кредитный калькулятор в свой Excel с надстройками SubEx_Fin или SubEx, чтобы он, вместе с другими финансовыми инструментами, был под рукой в любую минуту!

Подробнее в ВКонтакте

Финансовые статьи по Excel

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

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

Читать еще:  Какие кредиты предлагает студентам россельхозбанк

Аннуитетный кредит — что это? Это займ с такими условиями погашения, когда вы погашаете одинаковые суммы через равные промежутки времени.

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

ПараметрОписание
СтавкаПроцентная ставка за один период
ПериодПорядковый номер периода, для которого рассчитывается выплата. Он должен быть не больше, чем Кпер, иначе формула вернет ошибку
КперКоличество периодов, на которое рассчитан кредит
ПсСумма (тело) кредита. Для кредита это число записываем отрицательным, а для депозита — положительным
БсБудущая стоимость – величина остатка по кредиту после окончания срока выплат
ТипУкажите «0» (значение по умолчанию), если оплаты производим в конце периода, «1» — в начале периода
ПлтРазмер периодической платы (тело кредита плюс процент)
ОценкаНачальная оценка ожидаемого результата

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

Чтобы посчитать ежемесячные выплаты по телу кредита – используйте функцию =ОСПЛТ(Ставка; Период; Кпер; Пс; Бс; Тип) .

Вот какой результат даёт эта функция:

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

Чтобы узнать переплату (проценты) по кредиту, используем функцию: =ПРПЛТ(Ставка; Период; Кпер; Пс; Бс; Тип) :

Для получения полного ежемесячного платежа, нужно сложить основной платеж и процент, т.е. ОСПЛТ + ПРПЛТ.

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

Чтобы узнать, какая процентная ставка по вашему кредиту – используйте функцию =СТАВКА(Кпер; Плт; Пс; Бс; Тип; Оценка) .

Если нужно получить годовую ставку – умножьте результат функции на количество периодов (платежей) в году. Например, на 12 месяцев, 4 квартала, 2 полугодия и т.п.

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

Как посчитать количество периодов на погашение кредита

Чтобы произвести расчет срока кредита по ежемесячному платежу – используйте функцию =КПЕР(Ставка; Плт; Пс; Бс; Тип) .

Давайте применим эту формулу к нашему примеру:

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

Если вдруг вы забыли, на какую сумму кредит – применяем функцию =ПС(Кпер; Ставка; Плт; Бс; Тип). И снова попробуем вычислить для нашего примера:

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

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

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

5 комментариев

Через какую функцию просчитать сумму фиксированных платежей при сумме займа 100000, прцентной ставке 20% годовых и количестве периодов 24,53 месяца
Функция ОСПЛТ даёт разные платежи в каждом месяце

Тогда у меня ещё один вопрос можно ли вычислить количество периодов по выплате займа через подбор параметров, зная сумму займа(100000 руб), процентной ставке(20% годовых) и сумме ежемесячных платежей(5000). Как не производил подбор мешает дело. Через формулу всё просто =КПЕР(20%/12;-5000;100000). Через подбор вроде можно вычислить процентную ставку, а вот как сроки выплаты по кредиту не получается

Олег, здравствуйте. Отвечаю на два Ваших вопроса. В Экселе нет функции расчета постоянной выплаты, т.к. она и без того просто вычисляется: Плт = Пс * (Ставка + Ставка / (1 + Ставка)^Кпер — 1). Для Вашего примера и периода 24 мес. расчет будет таким: 100000*(1,67% + 1,67% / (1 + 1,67%)^24 — 1) = 5089,58. Здесь 1,67% = 20%/12. Правда, этой схемой пользуются редко, т.к. кредит получается дороже.
В связке с этой формулой работает и подбор параметра, в результате получаются указанные Вами 24,53 мес.

Добрый день!
как в excel рассчитать платеж по кредиту с разной процентной ставкой
если первый год 8%
остальные 19 лет 12%

Здравствуйте, Наталья. Видимо, речь идет о кредите с переменной ежемесячной платой (не аннуитет). Тогда Считаем так: = /240 + * ЕСЛИ(

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector