Расчет процентов по займу в Excel

  • автор:

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

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

Как рассчитать платежи по кредиту в 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).

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



Расчет платежей в 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. Достаточно выставить для ячейки с результатом процентный формат.

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

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

Таблица расчета процентов по займу

Расчет кредита в Excel
Аннуитет. Расчет периодического платежа в MS EXCEL. Погашение ссуды (кредита, займа)
Учетный ряд кредитных процентов

Excel — это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы банки, инвесторы и т. Быстро сориентироваться в мудреных формулах, рассчитать проценты, суммы выплат, переплату позволяют функции программы Microsoft Excel. Ежемесячные выплаты зависят от схемы погашения кредита. Различают аннуитетные и дифференцированные платежи:. Остаток задолженности по кредиту: Во второй и последующие — рассчитывается по формуле: Где D10 — номер текущего периода, В4 — срок кредита; Е9 — остаток по кредиту в предыдущем периоде; G9 — сумма основного долга в предыдущем периоде. Проведем расчет процентов по кредиту в Excel и вычислим эффективную процентную ставку, имея следующую информацию по предлагаемому банком кредиту:. Комиссия берется ежемесячно со всей суммы. Общий платеж по кредиту — это аннуитетный платеж плюс комиссия. Сумма основного долга и сумма процентов — составляющие части аннуитетного платежа. Подсчет ведется по той же схеме. Согласно Закону о потребительском кредите для расчета полной стоимости кредита ПСК теперь применяется новая формула. ПСК определяется в процентах с точностью до третьего знака после запятой по следующей формуле:. Нужно определить базовый период БП. В законе сказано, что это стандартный временной интервал, который встречается в графике погашения чаще всего. Чтобы получить проценты в Excel, не нужно умножать на Достаточно выставить для ячейки с результатом процентный формат. Таким образом, для расчета аннуитетных платежей по кредиту используется простейшая функция ПЛТ. Как видите, дифференцированный способ погашения несколько сложнее. Скачать кредитный калькулятор в Excel. Excel TABLE работа с таблицами. Калькулятор расчета кредита в Excel и формулы ежемесячных платежей Excel — это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы банки, инвесторы и т.

Расчет кредита в excel: скачайте готовые формулы и калькуляторы

Проверка свойств и значений. Работы с датами и временем. Преобразования в системы счисления. Вход Регистрация Напомнить пароль. Главная сайта Главная форумов Блог Пройти тестирование Заказать работу Главные страницы. О сайте Контакты Карта сайта Книга отзывов Статистика Информация о сайте. Новости Помочь сайту Развитие сайта. Часто задаваемые вопросы FAQ. Рабочая область Формулы Сводные таблицы Форматирование Выпадающие списки Примечания Гиперссылки Приемы работы. Инструменты Тонкая настройка Инструменты и настройки. Импорт данных Excel и Web. Различия Excel для Windows и MacOS Excel Windows и Excel Macintosh. Формулы Сводные таблицы Диаграммы Полезные приемы Excel. Процедуры Пользовательские функции Полезные приемы VBA. Правила форума Главная форумов Главные страницы. Вопросы по Excel Вопросы по VBA Готовые решения Excel и другие приложения Вопросы и решения. Вопросы по работе форума и сайта Объявления администрации Работа форума и сайта. Ленточный вариант форума Новые сообщения. Функции и формулы Графики и диаграммы Сводные таблицы Excel и VBA Книги по Excel. Применение VBA в Excel Программирование на VBA Книги по VBA. Сборники книг Книги для студентов ВУЗов Специализированные книги Разное. Видео к статьям Видеофайлы Видео. Все функции Автосумма Часто используемые ВПР ЕСЛИ СУММЕСЛИ. Статистические Инженерные Аналитические Проверка свойств и значений Баз данных. Visual Basic Макросы Пользовательские функции Пользовательские формы Код. Надстройки Надстройки COM Надстройки. Элементы управления формы Элементы ActiveX Элементы управления. Листы макросов Макрофункции XLM. Программы для работы с Excel Программы на VBA Программы. Нам отрыли кредитную линию, то есть мы можем в пределах лимита выбирать и погашать кредит в т. Имеется набор данных по выданным и возвращенным суммам. Ну естественно, есть процентная ставка. Как посчитать проценты к уплате, сформировав таблицу по отдельным суммам, с расчетам по каждой из них я знаю. Но мне кажется должна быть стандартный оператор не знаю как это называется наподобии расчета эффективной ставки процента. Вот и прошу помощи может кто мне подскажет Сообщение Нам отрыли кредитную линию, то есть мы можем в пределах лимита выбирать и погашать кредит в т. Автор — kidlena Дата добавления — Откуда-то у меня завалялось это. Может, поможет как есть или сможете доработать сами. Может, поможет как есть или сможете доработать сами alx К сообщению приложен файл: Сообщение Откуда-то у меня завалялось это. Может, поможет как есть или сможете доработать сами Автор — alx74 Дата добавления — Спасибо огромное очень прикольная програмка, но это немного не то. Идет постоянное движение на получение денежных средст и их возврат. И финансовые функции мне известны. По ценным бумагам предполагаются только выплаты от вложения, это немного не то. Да и амортизация здесь не причем Сообщение Спасибо огромное очень прикольная програмка, но это немного не то. Как мне кажется, из того что Вы обозначили в посте должна подойти функция ПЛТ. Сообщение отредактировал alx74 — Среда, Сообщение Посмотрите первый лист. Автор — alx74 Дата добавления — Функция ПЛТ возвращает сумму периодического платежа для аннуитетных платежей при условии постоянства в т. Ну вот смотрите пример я выложила Я хочу получить сумму процентов в зеленой ячейке пользуясь одной функцией. Как задать это формулами и растащить на всю таблицу я знаю, хочется ОП и все. Сообщение Функция ПЛТ возвращает сумму периодического платежа для аннуитетных платежей при условии постоянства в т. На каждый остаток займа разное количество дней использования в Вашем примере. На мой взгляд без промежуточных вычислений тут не обойтись. Сообщение На каждый остаток займа разное количество дней использования в Вашем примере. Может кто знает какой-то секрет При расчете эффективной ставки процента тоже разный остаток, но функция есть. Сообщение Может кто знает какой-то секрет А в чем проблема набрать единожды функцию и протянуть вниз? Сообщение А в чем проблема набрать единожды функцию и протянуть вниз? А по поводу просто скопировать, все зависит от объема данных и от куда они беруться. Мне было бы проще в массиве около ячеек задать один большой диапазон, и знать что считается как надо. И еще насчет единожды набрать тоже не всегда получается. Данные постоянно меняются причем координально. Больше других платежей нет. А даже если есть один дополнительный столбец задать не проблема. Сообщение А по поводу просто скопировать, все зависит от объема данных и от куда они беруться. Должен быть срок и величина минимального платежа. И есть некоторая разница — за каждый день или каждый день. Сообщение Должен быть срок и величина минимального платежа. Так я тоже могу сделать. Ну вот мне непонятно, если при расчете ставки эффективного процента задаются не одинаковые суммы погашения, и все считается, то почему нет функции, которая делала бы наоборот. А именно расчитывала не процент, а сумму в рублях. Мне кажется такая функция должна быть. Сообщение Так я тоже могу сделать. Я не говорю, что формула не подошла. Каждый день формируется ведомость, а еще и не одна. Массив данных большой до ячеек. Задавать формулу и копировать, это долго хочется раз и все. А могу ошибаться, но если есть одна функция, должна быть противоположная ей. Я не настаиваю, что она есть, но ведь хочется верить Сообщение отредактировал kidlena — Четверг, Сообщение Я не говорю, что формула не подошла. Главная Excel Готовые решения Форумы Библиотека Видео Справочники Разработчик. Отметить все сообщения прочитанными и перейти на главную страницу форума. Страница 1 из 1. Расчет процентов по кредиту. Может, поможет как есть или сможете доработать сами alx74 К сообщению приложен файл: А еще страховка, обслуживание счета и т.

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

Адвокат по возврату долга в Харькове АК «Виалекс» напоминает, что согласно нормам ГКУ заимодатель вправе получать от заемщика проценты за пользование денежными средствами, которые даны в долг если другое не указано в договорном соглашении или законе. При этом размер и порядок получения процентов обозначается договорным соглашением.

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

Проанализировав актуальную судебную практику по вопросу начисления и взыскании процентов от суммы долга, в частности решения Верховного Суда, юрист по возврату долга в Харькове АК «Виалекс» обращает внимание на следующее:

Не так давно Большая Палата Верховного Суда сделала правовой вывод (его закрепила), в частности, что право кредитодателя начислять предусмотренный сделкой проценты по кредиту прекращается после того, как истек срок кредитования, указанный в соглашении или в случае предъявления к заемщику требования согласно с ч. 2 ст.1050 ГК Украины. Данное дело касалось возврата долга по кредит. договору.

Однако, юрист в Харькове АК «Виалекс» отмечает, что данный правовой вывод был также применен ВС в постановлении от 03 октября 2018 г.

Так, в 2007 г. между двумя физ. лицами (истцом и ответчиком) был заключен договор займа, согласно условиям которого ответчик получил в долг от истца 1 100 дол. США с конечным термином возврата долга 01 августа 2008 года. Согласно заключенного между сторонами договора займа такой долг должен увеличиваться ежемесячно на 10% от указанной суммы.

Истец обращался в суд неоднократно и таким образом решением 2010 года с ответчика было взысканию сумму долга и проценты за пользование займом в период с 2008 по 2010 года. Решением 2015 года суд удовлетворил еще один иск истца теперь уже по взысканию процентов по договору займа за период с 2010 по 2014 года.

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

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

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

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *