Расчет платежей по кредиту удобнее и быстрее с помощью Microsoft Office Excel. Ручной расчет занимает намного больше времени. В этой статье речь пойдет об аннуитетных выплатах, особенностях их расчета, преимуществах и недостатках.
Что такое аннуитетный платеж
Метод ежемесячного погашения кредита, при котором сумма депозита не меняется в течение всего срока кредита. Это человек, в определенные дни каждого месяца, вкладывает определенную сумму денег до тех пор, пока ссуда не будет полностью погашена.
Кроме того, проценты по кредиту уже включены в общую сумму, выплачиваемую банку.
Классификация аннуитета
Аннуитетные выплаты можно разделить на следующие виды:
- Фиксированный. Платежи, которые не меняются, имеют фиксированную ставку независимо от внешних условий.
- Валюта. Возможность изменения суммы платежа в случае падения или повышения курса обмена.
- Проиндексировано. Выплаты в зависимости от уровня, уровня инфляции. В период аккредитации их размер часто меняется.
- Переменные. Доход, который может меняться в зависимости от состояния финансовой системы, инструментов.
Примечание! Фиксированные платежи предпочтительны для всех заемщиков, поскольку они несут небольшой риск.
Преимущества и недостатки аннуитетных платежей
Чтобы лучше разобраться в предмете, необходимо изучить основные характеристики этого вида кредитных выплат. Он имеет следующие преимущества:
- Установите конкретную сумму платежа и дату его выплаты.
- Высокая доступность для заемщиков. Подать заявку на получение аннуитета может практически любой, независимо от своего финансового положения.
- Возможность уменьшения размера ежемесячного платежа по мере роста инфляции.
Не обошлось и без недостатков:
- Высокий процент. Заемщик переплатит большую сумму сверх дифференцированного платежа.
- Проблемы, возникающие, когда вы хотите погасить долг раньше, чем предполагалось.
- Нет пересчета на предоплату.
Из чего состоит платеж по кредиту?
Аннуитетный платеж состоит из следующих компонентов:
- Переплаченные проценты при погашении кредита.
- Часть причитающегося капитала.
В результате общая сумма процентов почти всегда превышает сумму, внесенную заемщиком для уменьшения долга.
Основная формула аннуитетного платежа в Excel
Как упоминалось выше, вы можете работать с различными типами ссуд и платежей по ссуде в Microsoft Office Excel. Аннуитет не исключение. В общих чертах формула, по которой можно быстро рассчитать аннуитетные взносы, выглядит следующим образом:
Важно! Вы не можете раскрывать скобки в знаменателе данного выражения, чтобы упростить его.
Основные значения формулы расшифровываются следующим образом:
- AP — аннуитетный платеж (сокращенное наименование).
- О — размер основного долга заемщика.
- PS — процентная ставка, предлагаемая ежемесячно конкретным банком.
- С — количество месяцев, в течение которых предоставляется кредит.
Для усвоения информации достаточно привести несколько примеров использования этой формулы. О них и пойдет речь ниже.
Примеры использования функции ПЛТ в Excel
Вот простая постановка проблемы. Рассчитывать ежемесячный платеж по кредиту необходимо, если банк представляет процентную ставку 23%, а общая сумма составляет 25000 рублей. Срок кредита составляет 3 года. Задача решается по алгоритму:
- Создайте общую таблицу в Excel на основе исходных данных.
Таблица составлена исходя из состояния задачи. Фактически, вы можете использовать другие столбцы, чтобы подогнать его под
- Активируйте функцию PMT и введите аргументы для нее в соответствующее поле.
- В поле «Ставка» напишите формулу «B3 / B5». Это будет процентная ставка по кредиту.
- В строке «Nper» напишите значение в виде «B4 * B5». Это будет общее количество выплат за весь срок кредита.
- Заполните поле «Ps». Здесь необходимо указать начальную снимаемую с банка сумму, прописав значение «В2».
Действия, необходимые в окне «Аргументы функции». Вот порядок заполнения каждого параметра
- Убедитесь, что после нажатия «ОК» в исходной таблице значение «Ежемесячный платеж» было рассчитано».
Окончательный результат. Ежемесячный платеж рассчитан и выделен красным
Дополнительная информация! Отрицательное число указывает на то, что заемщик тратит деньги.
Пример расчета суммы переплаты по кредиту в Excel
В этой задаче необходимо рассчитать сумму, которую человек, взявший ссуду в размере 50 000 рублей с процентной ставкой 27% на 5 лет, переплатит. Всего заемщик производит 12 платежей в год. Решение:
- Создайте таблицу исходных данных.
Таблица составлена исходя из состояния задачи
- Вычтите начальную сумму из общей суммы платежа по формуле «= ABS (PMT (B3 / B5; B4 * B5; B2) * B4 * B5) -B2». Его необходимо ввести в строке формул вверху главного меню программы.
- В результате сумма переплат появится в последней строке созданного банка. На сумму более 41 606 рублей заемщик заплатит больше.
Окончательный результат. Почти двукратная переплата
Формула вычисления оптимального ежемесячного платежа по кредиту в Excel
Задача с условием: клиент зарегистрировал банковский счет на 200 000 рублей с возможностью ежемесячного пополнения. Необходимо рассчитать сумму платежа, которую человек должен производить каждый месяц, чтобы через 4 года на его счету было 2 000 000 рублей. Ставка 11%. Решение:
- Нарисуйте тарелку исходя из исходных данных.
Таблица составлена из данных состояния проблемы
- Введите формулу «= PMT (B3 / B5; B6 * B5; -B2; B4)» в строке ввода Excel и нажмите «Enter» с клавиатуры. Буквы будут отличаться в зависимости от того, в каких ячейках находится таблица.
- Убедитесь, что сумма платежа рассчитана автоматически в последней строке таблицы.
Окончательный результат расчета
Примечание! Таким образом, чтобы покупатель накопил на счете клиента 2 000 000 рублей по ставке 11% за 4 года, он должен ежемесячно вносить 28 188 рублей. Минус в сумме указывает на то, что клиент терпит убытки, отдавая деньги банку.
Особенности использования функции ПЛТ в Excel
В общих чертах эта формула записывается следующим образом: = PMT (rate; nper; ps; [bs]; [type]). Функция имеет следующие характеристики:
- При расчете ежемесячных платежей учитывается только годовой платеж.
- При указании процентной ставки важно производить пересчет исходя из количества годовых платежей.
- Конкретное число указывается в формуле вместо аргумента Nper. Это период выплаты долга.
Расчет оплаты
Как правило, аннуитетный платеж рассчитывается в два этапа. Чтобы разобраться в теме, нужно рассматривать каждый из этапов отдельно. Об этом и пойдет речь далее.
Этап 1: расчет ежемесячного взноса
Чтобы рассчитать сумму, которую вы должны платить ежемесячно по ссуде с фиксированной ставкой в Excel, вам необходимо:
- Создайте исходную таблицу, выберите ячейку, в которой вы хотите просмотреть результат, и нажмите кнопку «Вставить функцию» вверху.
Начальные действия
- Выберите «PMT» в списке функций и нажмите «OK».
Выбор функции в специальном окне
- В следующем окне задайте аргументы функции, указав соответствующие строки в скомпилированной таблице. В конце каждой строки нужно щелкнуть значок, после чего выбрать нужную ячейку в массиве.
Алгоритм действий по заполнению аргументов функции «PMT»
- Когда все аргументы будут заполнены, соответствующая формула будет записана в строке для ввода значений, а результат вычислений со знаком минус появится в поле таблицы «Ежемесячный платеж.
Окончательный результат расчетов
Важно! После расчета гранта можно будет рассчитать сумму, которую заемщик переплатит за весь срок кредита.
Этап 2: детализация платежей
Сумму переплаты можно рассчитывать ежемесячно. В результате человек поймет, сколько денег он будет тратить в ссуду каждый месяц. Расчет деталей выполняется следующим образом:
- Составьте справочную таблицу на 24 месяца.
Исходный массив таблиц
- Поместите курсор в первую ячейку таблицы и вставьте функцию «OSPLT».
Выбор функции детализации платежа
- Таким же образом дополните аргументы функции.
Заполните все строки в окне аргументов для оператора e
- При заполнении поля «Период» необходимо указать на табличке первый месяц, указав ячейку 1.
Заполнив тему «Срок»
- Убедитесь, что первая ячейка столбца «Платеж кредитной организации» заполнена.
- Чтобы заполнить все строки первого столбца, нужно растянуть ячейку до конца таблицы
Заполнение оставшихся строк
- Выберите функцию «PRPLT», чтобы заполнить второй столбец таблицы.
- Заполните все темы в открывшемся окне, как показано на скриншоте ниже.
Заполнение аргументов для оператора «PRPLT»
- Рассчитайте общий ежемесячный платеж, сложив значения в двух предыдущих столбцах.
Расчет ежемесячных платежей
- Чтобы рассчитать «Кредиторский остаток», вам нужно сложить процентную ставку с выплатой по основной части ссуды и растянуть до конца банка, чтобы заполнить все месяцы ссуды.
Расчет остатка к оплате
Дополнительная информация! При вычислении остальной части формулы необходимо повесить знаки доллара, чтобы они не выскользнули при растяжении.
Расчет аннуитетных платежей по кредиту в Excel
Функция PMT отвечает за расчет аннуитета в Excel. Принцип расчета в общих чертах заключается в выполнении следующих шагов:
- Создайте таблицу исходных данных.
- Составьте план погашения долга на каждый месяц.
- Выберите первую ячейку в столбце «Платежи по ссуде» и введите формулу для расчета «PMT ($ B3 / 12; $ B $ 4; $ B $ 2)».
- Растягивает полученное значение для всех столбцов в горшке.
Результат функции PMT
Расчет в MS Excel погашение основной суммы долга
Аннуитетные выплаты должны производиться ежемесячно в определенных размерах. Кроме того, процентная ставка не меняется.
Вычисление остатка суммы основного долга (при БС=0, тип=0)
Допустим, кредит в 100000 рублей взят на 10 лет под 9%. Необходимо рассчитать сумму основного долга за 1 месяц 3-го года. Решение:
- Создайте таблицу и рассчитайте ежемесячный платеж, используя приведенную выше формулу PS.
- Рассчитайте сумму взноса, необходимую для погашения части долга, по формуле «= -PMT- (PS-PS1) * ставка = -PMT- (PS + PMT + PS * ставка)».
- Рассчитайте сумму основного долга за 120 периодов по известной формуле.
- С помощью оператора ПРПЛТ найдите количество выплаченных процентов за 25-й месяц.
- Проверить результат.
Вычисление суммы основного долга, которая была выплачена в промежутке между двумя периодами
Этот расчет лучше всего производить просто. Для расчета суммы за два периода необходимо использовать следующие формулы:
- = «- 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 вам необходимо:
- Введите дни месяца, за которые производятся выплаты, и укажите их количество.
- Проверьте отрицательные и положительные суммы. Отрицательный вариант предпочтительнее.
- Подсчитайте дни между двумя датами внесения денег.
Расчет периодического платежа в MS Excel. Срочный вклад
В Excel вы можете быстро рассчитать сумму регулярных платежей, если фиксированная сумма уже накоплена. Это действие выполняется с помощью функции PMT после заполнения исходной таблицы.
Заключение
Таким образом, рассчитывать аннуитетные платежи в Excel проще, быстрее и эффективнее. За их расчет отвечает оператор PMT. Подробные примеры можно найти выше.