Расчет аннуитетных платежей: формула, использование Excel

Опубликовано 26 октября 2018 автором Консультант

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

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

Первый кредитный калькулятор в Excel можно скачать здесь. Но Excel доступен не на всех компьютерах. Пользователи Mac и Linux обычно не используют Excel, потому что это продукт Microsoft. Вы можете воспользоваться Кредитным калькулятором Сбербанка онлайн. На основе этого калькулятора был разработан ипотечный калькулятор для Android и iPhone. Разработанный ипотечный калькулятор универсален, так как подходит для расчета кредита с учетом комиссий, страховки и сравнительных таблиц для кредитов до и после предоплаты. Этот калькулятор идеально подходит для расчета ипотечной ссуды и имитации наличия предоплаты. Расчетный график соответствует моему графику платежей.

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

Предварительная оплата

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

Вы можете настроить неограниченное количество частичных авансовых возвратов.

Достоинства данного калькулятора:

1. Кредитный калькулятор в Excel почти точно рассчитывает график аннуитетных платежей и график дифференцированных платежей
2. Изменения в графике платежей — учет предоплаты при уменьшении суммы основного долга
3. Построение и расчет графика платежей в виде таблицы в Excel. Таблицу времени выплат также можно изменить
4. При расчете учитываются високосные и невисокосные годы. По этой причине размер начисленных процентов практически совпадает со значениями, рассчитанными ВТБ24 и Сбербанком
5. Точность расчетов: расчеты совпадают с расчетами кредитного калькулятора ВТБ24 и Сбербанка
6. Калькулятор можно модифицировать лично, задав различные варианты расчета.

Порядок расчета займа с помощью калькулятора

Есть 2 варианта расчета кредита: первый — это предварительный расчет, когда вы хотите взять деньги в долг. Дата первого платежа не требуется для этого расчета. Его можно оставить по умолчанию. На размер ежемесячного платежа это не влияет. Сумма кредита: указывается в кредитном договоре и берется без учета первоначального платежа за товар или услугу. Процентная ставка: номинальная ставка по кредиту без учета комиссий и страховки. Взято из кредитного договора. Вы можете ввести 3 десятичных знака. Экспресс без деления на единицу. Срок — целое количество месяцев, на которые берется заем. Например, если вам 2 года, вы должны ввести 24 месяца. Второй вариант — расчет существующей ссуды. В следующем поле: дата первого платежа. Этот параметр важен уже при оформлении кредита, для кредита оговоренного важен расчет по дате. То есть при построении графика указывается дата следующего платежа — номер дня месяца. Расчет по дате важен для досрочного погашения. От даты авансового депозита средств зависит, в каком месяце будет новый сниженный платеж.

Недостатки калькулятора

1. Возможные изменения процентных ставок не учитываются при выплате кредита
2. Если произвести расчет путем внесения предоплаты на изменение срока и суммы, расчет будет некорректным
3. Если сумма начисленных процентов за период больше суммы аннуитетного платежа, расчет будет некорректным
4. Опция не рассчитывается: первый платеж — только проценты. В случае, если дата выдачи не совпадает с датой первого платежа, необходимо выплатить банку проценты за период между датой выдачи и датой первого платежа.
5. Расчет производится для процентной ставки с двумя знаками после запятой.

Кредитный калькулятор для iPad / iPhone лишен всех перечисленных выше недостатков. В целом недостатки не очень критичные и присущи любому онлайн-калькулятору кредита. Другой калькулятор ссуды Excel можно скачать по этой ссылке. Калькулятор кредита не позволяет рассчитать досрочное погашение. Однако преимущество в том, что он рассчитывает ссуду с разными процентными периодами. Если сумма процентов по ссуде за определенный месяц превышает сумму аннуитетного платежа, диаграмма для первого калькулятора ссуды в Excel построена неправильно. Отрицательные суммы получены на графике.

Попробуйте посчитать, например, ссуду в 1 миллион рублей под 90 процентов сроком на 30 лет. Второй калькулятор лишен этого недостатка. Однако разделите ссуду на 2 периода, например после разделения диаграммы все еще могут быть отрицательные значения. Таким образом, план выплат необходимо разбить на 3 или более периодов. Конечно, сам файл также можно редактировать в соответствии с вашими потребностями.

запрещается копирование материалов с сайта без согласия автора. Подробнее о

https://mobile-testing.ru/rules

Все вы, вероятно, рано или поздно придете к мысли о ссуде. Кому нужна машина, кому нужна квартира. Я сам через это прошел, я знаю. И тут надо посчитать. Когда вы спрашиваете только цену ссуды, то достаточно тех калькуляторов, которые есть в сети. Хотя они не отвечают на все задаваемые им вопросы. Но когда вы уже берете ссуду, не рассчитывая график выплат, вы можете пойти куда угодно. Поэтому я предлагаю иметь свой кредитный (ипотечный) калькулятор в своей книге Excel.

Итак, у любого кредита есть 4 основных параметра:

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

Зная 3 показателя, можно брать четвертый.

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

— Term — функция PS()

— Сумма — функция КПЕР()

— Функция Ставки — СТАВКА()

— Ежемесячный платеж — Функция ПЛТ()

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

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

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

Переходим ко второму листу.

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

На второй строчке –

1. Дата — такая же дата, как определена выдача кредита, но в следующем месяце. Мы используем функцию ДАТА, где год и день такие же, как в предыдущем периоде, а месяц — на один больше. Но есть нюанс: банк не примет платежи в выходные дни. Поэтому исправляем число с помощью функции ДЕНЬНЕД. Важно: дату можно изменить вручную, на следующую дату это не повлияет.
2. Сумма ежемесячного платежа (определяется функцией PMT).
3. Сумма выплаты процентов путем умножения значения прошлого периода на соответствующий процент. Функция ДЛИННЕ используется для устранения эффекта високосных лет. Банки скрупулезны в своих расчетах, поэтому период исчисляется днями, иначе было бы проще: взять годовой процент, разделить на 12 месяцев и умножить на сумму.
4. Сумма погашения основного долга принимается как разница между ежемесячным платежом и суммой погашения процентов.
5. Досрочное погашение и его дата устанавливаются случайным образом. Единственное условие помещается в период, в котором дата меньше или совпадает с датой досрочного погашения.
6. Сумма, причитающаяся после платежа, определяется как сумма предыдущего периода за вычетом погашения основного долга и суммы досрочного погашения.

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

Теперь давайте сделаем такой же график для многоуровневых платежей.

Изменим две формулы:

1) Сумма погашения основного долга. Он останется без изменений: сумма долга делится на количество периодов (месяцев).

2) Ежемесячный платеж определяется как сумма двух платежей — погашения основного долга и процентов.

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

Какие еще показатели вы можете выделить, которые важны для нас, но не учитываются в доступных калькуляторах?

Для меня это была мотивация взять кредит. На тот момент я снимал квартиру, поэтому мне нужно было рассчитать стоимость ипотеки. Цена кредита для меня равнялась сумме уплаченных процентов за вычетом арендной платы за весь срок кредита. Если сумма небольшая или даже отрицательная, стоит взять кредит. Бонусом для меня было проживание в МОЕМ СОБСТВЕННОМ (!) Доме, где я знал, что могу вбить гвоздь в СВОЮ стену, и действительно, психологическое воздействие велико.

Если кому-то это нужно более наглядно, вот где это сформировалось.

«Глаза боятся, а руки делают»

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

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

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

Функции Microsoft Excel позволяют быстро перемещаться по сложным формулам, рассчитывать проценты, выплаты и переплаты.

Пример расчета аннуитетного кредита с досрочными платежами

Теперь попробуем рассчитать ипотеку. Например, возьмем заем со следующими параметрами

В итоге получаем следующий график платежей.

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

где OD погашение — это сумма, подлежащая погашению по основной части ссуды.Проценты — это сумма процентов по ссуде за один месяц. Где сумма OD — это сумма основного долга на дату расчета. Ставка — процентная ставка в текущем периоде. Если произошло изменение процентной ставки, используется новая ставка. Количество дней между датами — разница в днях между «Текущей датой платежа» и предыдущей датой платежа. Количество дней в году — это целое число дней в текущем году. Если рассматривать выплату процентов, например, с 22 декабря 2011 года по 22 января 2012 года, формула выплаты процентов аналогична. То есть нужно рассчитывать проценты на декабрь и январь отдельно, в зависимости от количества дней в году.

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

Как видите, на выплату процентов должно пойти 10191,78. Рассчитываем сумму, которую нужно вернуть кредитной организации

Теперь мы рассчитаем сумму основного долга после выплаты первого взноса по ипотеке

Кроме того, с этой суммы будут начислены проценты. Затем вы можете рассчитать график всех выплат. Из схемы выплат видно, что основная сумма на 1 сентября 2012 года составляет 831206,27 руб.

Теперь допустим, что в августе 2012 года мы выплатили 100 000 рублей. Тип погашения — уменьшение суммы кредита. То есть срок останется прежним, а ежемесячный платеж уменьшится. Попробуем посчитать, сколько будет выплачиваться после проведения предоплаты. В октябре будет новая рассрочка по кредиту с учетом предоплаты.

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

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

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

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

Так выглядят промежуточные расчеты

Давайте проверим это с помощью калькулятора ссуды

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

Есть много причин для этого. Во-первых, банк использует другую формулу для расчета аннуитетных платежей. На самом деле существует 3 формулы выплаты аннуитетов. Знаменатель может быть разницей (n-1), (n-2) или просто n. Саму формулу можно найти в кредитном договоре. Здесь также указаны параметры, которые необходимо подставить в формулу.

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

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

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

Чаще используется аннуитет — это выгоднее для банка и дешевле для большинства клиентов.

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

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

А = К * С

где это находится:

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

К = (я * (1 + я) ^ п) / ((1 + я) ^ п-1)

В Excel есть специальная функция для подсчета аннуитетных платежей. Это PMT:

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

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

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

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

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

DP = NEO / (PP + NEO * PS)

где это находится:

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

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

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

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

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

Выплата основного долга: общая сумма кредита, разделенная на срок: = SE (D9

Окончательный платеж: сумма «процентов» и «основного долга» в текущем периоде: = F8 + G8.

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

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

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

Основная формула аннуитетного платежа в Excel

В программе есть специальная формула для расчета равных переводов по кредиту. Вы можете найти его по следующему пути: «Формулы»> «Финансы»> «PMT».

= ПЛТ (Ставка; КПЕР; Пс)

, где это находится

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

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

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

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

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

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

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

Основная сумма = аннуитетный платеж — проценты.

Сумма процентов = Остаточная задолженность * Ежемесячная процентная ставка.

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

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

Эффективная процентная ставка по без комиссии составляет 13%. Расчет производится аналогично.

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

Мы рассчитаем в MS EXCEL сумму регулярного аннуитета при погашении кредита. Мы сделаем это либо с помощью функции PMT (), либо напрямую с помощью формулы аннуитета. Также будет составлена ​​таблица ежемесячных платежей с указанием остаточной части долга и начисленных процентов.

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

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

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

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

Для расчета полной стоимости кредита необходимо составить план выплат (см. Процедуру выше).

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

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

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

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

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

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

Скачать калькулятор ссуды в Excel

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

Кто еще, как не я считаю, кредиты плохи. Особенно потребительские. Другое дело — бизнес-кредиты, но для обычных людей мышеловка «деньги за 15 минут, нужен только паспорт» работает отлично, доставляя удовольствие здесь и сейчас, а расплачиваясь за это когда-нибудь позже. И главная проблема, на мой взгляд, даже не в непомерной заинтересованности и не в том, что это «потом» все равно когда-нибудь наступит. Кредит убивает мотивацию к росту. Зачем заморачиваться, учиться, развиваться, искать дополнительные источники дохода, если можно по глупости сходить в ближайший банк и там за полчаса получишь ссуду на дорогих условиях, попутно грамотно выкладывая еще ДОПА на страхование?

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

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