Формула расчета аннуитетного платежа в Excel

Платежи по кредиту проще и быстрее рассчитываться с помощью Microsoft Office Excel. Гораздо больше времени уходит на расчет вручную. В данной статье речь пойдет о аннуитетных выплатах, особенностях их расчета, преимуществах и недостатках.

Что такое аннуитетный платеж

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

При этом проценты по кредиту уже включены в общую сумму, выплачиваемую банку.

Классификация аннуитета

Аннуитетные выплаты можно разделить на следующие виды:

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

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

Преимущества и недостатки аннуитетных выплат

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

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

Не без недостатков:

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

Каков платеж по кредиту?

Аннуитетный платеж состоит из следующих компонентов:

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

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

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

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

Важно! В знаменателе этого выражения невозможно раскрыть скобки, чтобы упростить его.

Основные значения формулы расшифровываются следующим образом:

• АП – аннуитетный платеж (название сокращенно).
• О – размер основного долга заемщика.
• PS – процентная ставка, выдвигаемая ежемесячно конкретным банком.
• C – количество месяцев действия кредита.

Для усвоения информации достаточно привести несколько примеров использования этой формулы. О них пойдет речь дальше.

Примеры использования функции ПЛТ в Excel

Приведем простое условие задачи. Рассчитать ежемесячный платеж по кредиту необходимо, если банк выставляет проценты в размере 23%, а общая сумма составляет 25000 рублей. Кредитование будет длиться 3 года. Задача решается по алгоритму:

1. Составьте общую таблицу в Excel на основе исходных данных.

2. Активируйте функцию PMT и введите для нее аргументы в соответствующем поле.
3. В поле «Ставка» введите формулу «В3/В5». Это будет процентная ставка по кредиту.
4. В строке «Nper» пропишите значение в виде «B4*B5». Это и будет общее количество платежей за весь срок кредита.
5. Заполните поле «ПС». Здесь нужно указать первоначальную сумму, взятую в банке, прописав значение «В2».

6. Убедитесь, что после нажатия «ОК» в исходной таблице было рассчитано значение «Ежемесячный платеж».

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

Пример расчета суммы переплаты по кредиту в Excel

В этой задаче нужно посчитать сумму, которую переплатит человек, взявший кредит в размере 50000 рублей под процентную ставку 27% сроком на 5 лет. Всего заемщик производит 12 платежей в год. Решение:

1. Составьте исходную таблицу данных.

2. Из общей суммы выплат вычтите первоначальную сумму по формуле «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Его необходимо вставить в строку формул вверху главного меню программы.
3. В результате сумма переплаты появится в последней строке созданной таблички. Заемщик переплатит сверху 41606 рублей.

Формула расчета оптимального ежемесячного платежа по кредиту в Excel

Задача с условием: у клиента зарегистрирован счет в банке на 200000 рублей с возможностью ежемесячного пополнения. Необходимо посчитать сумму платежа, которую человек должен совершать каждый месяц, чтобы через 4 года на его счету было 2000000 рублей. Ставка составляет 11%. Решение:

1. Создайте электронную таблицу на основе исходных данных.

2. Введите формулу в строку ввода Excel. «=ПЛТ(B3/B5;B6*B5;-B2;B4)» и нажмите «Ввод» на клавиатуре. Буквы будут различаться в зависимости от ячеек, в которых помещена таблица.
3. Убедитесь, что сумма взноса рассчитывается автоматически в последней строке таблицы.

Обращать внимание! Таким образом, чтобы клиент накопил 4 рубля под ставку 2000000% за 11 лет, ему необходимо каждый месяц вносить 28188 рублей. Минус в сумме говорит о том, что клиент несет убытки, отдавая деньги банку.

Особенности использования функции ПЛТ в Excel

В общем виде эта формула записывается следующим образом: =PMT(ставка; nper; ps; [bs]; [тип]). Функция имеет следующие возможности:

1. При расчете ежемесячных взносов учитывается только годовая ставка.
2. При указании процентной ставки важно произвести перерасчет исходя из количества платежей в год.
3. Вместо аргумента «Нпер» в формуле указывается конкретное число. Это период оплаты.

Расчет оплаты

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

1 этап: расчет ежемесячного платежа

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

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

2. В списке функций выберите «PLT» и нажмите «ОК».

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

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

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

Этап 2: реквизиты платежа

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

1. Создайте таблицу на 24 месяца.

2. Поместите курсор в первую ячейку таблицы и вставьте функцию «OSPLT».

3. Аналогично заполните аргументы функции.

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

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

7. Выберите функцию «ПРПЛТ» для заполнения второго столбца таблицы.
8. Заполните все аргументы в открывшемся окне в соответствии со скриншотом ниже.

9. Рассчитайте общий ежемесячный платеж, сложив значения в двух предыдущих столбцах.

10. Для расчета «Остатка к оплате» необходимо прибавить процентную ставку к платежу по телу кредита и растянуть ее до конца таблички, чтобы заполнить все месяцы кредита.

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

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

Функция ПЛТ отвечает за расчет аннуитета в Excel. В целом принцип расчета заключается в выполнении следующих шагов:

1. Составьте исходную таблицу данных.
2. Составьте график погашения долга на каждый месяц.
3. Выделите первую ячейку в графе «Платежи по кредиту» и введите формулу расчета. «PLT ($3/12; $B$4; $B$2)».
4. Полученное значение растягивается на все столбцы таблички.

Расчет в MS Excel погашения основной суммы долга

Аннуитетные выплаты должны производиться ежемесячно в фиксированных суммах. И процентная ставка не изменится.

Расчет остатка основной суммы (при БС=0, тип=0)

Предположим, что кредит в размере 100000 10 рублей взят на 9 лет под 1%. Необходимо рассчитать сумму основного долга в 3-м месяце XNUMX-го года. Решение:

1. Составьте таблицу данных и рассчитайте ежемесячный платеж, используя приведенную выше формулу PV.
2. Рассчитайте долю платежа, необходимую для погашения части долга, по формуле «=-PMT-(PS-PS1)*item=-PMT-(PS +PMT+PS*item)».
3. Рассчитайте сумму основного долга за 120 периодов по известной формуле.
4. С помощью оператора HPMT найдите сумму процентов, выплаченных за 25-й месяц.
5. Проверьте результат.

Расчет суммы основного долга, выплаченного между двумя периодами

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

• =«-BS(item; con_ period; plt; [ps]; [type]) /(1+type *item)».
• = «+ BS(ставка; начальный_период-1; plt; [пс]; [тип]) /IF(начальный_период =1; 1; 1+тип *ставка)».

Обращать внимание! Буквы в скобках заменяются конкретными значениями.

Досрочное погашение с сокращенным сроком или платежом

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

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

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

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

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

Расчет периодического платежа в MS Excel. Временный депозит

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

Заключение

Таким образом, аннуитетные платежи проще, быстрее и эффективнее рассчитать в Excel. За их расчет отвечает оператор ПЛТ. Более подробные примеры можно найти выше.