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

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

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

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

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

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

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

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

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

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

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

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

Не обошлось и без недостатков:

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

Из чего состоит платеж по кредиту?

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

• Переплаченные проценты при погашении кредита.
• Часть причитающегося капитала.

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

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

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

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

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

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

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

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

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

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

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

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

Действия, необходимые в окне «Аргументы функции». Вот порядок заполнения каждого параметра

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

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

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

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

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

1. Создайте таблицу исходных данных.

Таблица составлена ​​исходя из состояния задачи

1. Вычтите начальную сумму из общей суммы платежа по формуле «= ABS (PMT (B3 / B5; B4 * B5; B2) * B4 * B5) -B2». Его необходимо ввести в строке формул вверху главного меню программы.
2. В результате сумма переплат появится в последней строке созданного банка. На сумму более 41 606 рублей заемщик заплатит больше.

Окончательный результат. Почти двукратная переплата

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

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

1. Нарисуйте тарелку исходя из исходных данных.

Таблица составлена ​​из данных состояния проблемы

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

Окончательный результат расчета

Примечание! Таким образом, чтобы покупатель накопил на счете клиента 2 000 000 рублей по ставке 11% за 4 года, он должен ежемесячно вносить 28 188 рублей. Минус в сумме указывает на то, что клиент терпит убытки, отдавая деньги банку.

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

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

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

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

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

Этап 1: расчет ежемесячного взноса

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

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

Начальные действия

1. Выберите «PMT» в списке функций и нажмите «OK».

Выбор функции в специальном окне

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

Алгоритм действий по заполнению аргументов функции «PMT»

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

Окончательный результат расчетов

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

Этап 2: детализация платежей

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

1. Составьте справочную таблицу на 24 месяца.

Исходный массив таблиц

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

Выбор функции детализации платежа

1. Таким же образом дополните аргументы функции.

Заполните все строки в окне аргументов для оператора e

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

Заполнив тему «Срок»

1. Убедитесь, что первая ячейка столбца «Платеж кредитной организации» заполнена.
2. Чтобы заполнить все строки первого столбца, нужно растянуть ячейку до конца таблицы

Заполнение оставшихся строк

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

Заполнение аргументов для оператора «PRPLT»

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

Расчет ежемесячных платежей

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

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

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

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

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

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

Результат функции PMT

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

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

Вычисление остатка суммы основного долга (при БС=0, тип=0)

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

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

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

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

• = «- BS (ставка; end_period; plt; [ps]; [type])) / (1 + type * rate)».
• = «+ BS (rate; start_period-1; plt; [ps]; [type])) / SE (start_perio = 1; 1; 1 + type * rate)».

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

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

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

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

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

Снижение выплат по кредиту

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

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

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

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

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

Заключение

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