Как рассчитать дифференцированный платеж по кредиту в Excel

Обновлено: 24.11.2024

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

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

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

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

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

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

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

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

S – сумма, сумма платежа по кредиту;
C – коэффициент, это ставка аннуитетного платежа;
V — стоимость кредита.

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

С = (i * (1 + i) ^ n) / ((1 + i) ^ n-1)

где i – процентная ставка за месяц, результат деления годовой ставки на 12;
n – срок кредита в месяцах.

В Excel есть специальная функция, посвященная аннуитетным платежам. Это: =PMT().

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

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

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

основная сумма распределяется на период выплаты равными долями;
проценты по кредиту, начисленные на остаток.

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

DP = NEO / (PP + PS * NEO)

DP – ежемесячный платеж по кредиту;
NEO – остаток по кредиту;
PP – количество оставшихся до конца периода погашения;
PS — процентная ставка в месяц (годовая ставка, деленная на 12).

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

Даты ввода совпадают:

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

Условия кредитования
Сумма кредита	100 000 долларов США
Процентная ставка	18%
Продолжительность месяцев	36

td>

Номер месяца	Остаток к получению по кредиту	Выплата процентов	Выплата основного долга	Окончательный платеж
1 месяц

D10 – номер текущего периода;
$B$4 – срок кредита;
E8 – остаток кредита в предыдущем периоде;
G9 - основной долг в предыдущем периоде.

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

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

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

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

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

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

Мы осуществляем расчет процентов по кредиту и рассчитываем эффективную процентную ставку с учетом следующей информации о кредитных предложениях банка.

Заполните тип таблицы:

Комиссия взимается ежемесячно со всей суммы. Общий платеж по кредиту - это аннуитетный платеж плюс комиссия. Сумма основного долга и сумма процентов – составные части аннуитетного платежа.

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

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

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

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

взял кредит 500 000 рублей;
Возврат в банк - 684 881,67 руб. (сумма всех платежей по кредиту);
переплата составила 184 881,67 рубля;
процентная ставка - 184 881 67/500 000*100, или 37%.
безобидная комиссия в размере 1% обошлась заемщику так дорого.

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

Расчет эффективной процентной ставки в Excel

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

EIR = i * CHBP * 100;
где i - процентная ставка базового периода;
NBP – количество базовых периодов в календарном году.

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

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

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

Затем находим NBP: 365/28 = 13.

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

У нас есть все необходимые даты — подставьте их в формулу EIR: =B9*B8

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

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

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

PMT, одна из финансовых функций, рассчитывает платеж по кредиту на основе постоянных платежей и постоянной процентной ставки.

Используйте Excel Formula Coach, чтобы рассчитать ежемесячный платеж по кредиту. В то же время вы узнаете, как использовать функцию ПЛТ в формуле.

Синтаксис

ПЛТ(коэффициент, nper, pv, [fv], [type])

Примечание. Более полное описание аргументов PMT см. в описании функции PV.

Синтаксис функции PMT имеет следующие аргументы:

Требуется ставка. Процентная ставка по кредиту.

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

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

Fv Необязательный. Будущая стоимость или денежный баланс, который вы хотите получить после последнего платежа. Если fv опущено, предполагается, что оно равно 0 (ноль), то есть будущая стоимость кредита равна 0.

Введите Необязательно. Число 0 (ноль) или 1 указывает, когда должны быть произведены платежи.

Установить тип равным

Если платежи должны быть выполнены

В конце периода

В начале периода

Примечания

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

Убедитесь, что вы используете одинаковые единицы измерения для указания скорости и числа. Если вы делаете ежемесячные платежи по четырехлетнему кредиту с годовой процентной ставкой 12 процентов, используйте 12%/12 для ставки и 4 * 12 для nper. Если вы делаете ежегодные платежи по одному и тому же кредиту, используйте 12 процентов для ставки и 4 для nper.

Совет. Чтобы найти общую сумму, выплаченную в течение срока кредита, умножьте возвращенное значение ПЛТ на nper.

Пример

Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете изменить ширину столбцов, чтобы увидеть все данные.

Аналитика, визуализация данных, бизнес-аналитика и другие актуальные темы.

Давайте общаться!

Последние публикации

Категории

Формулы Excel и функции — Калькулятор автокредита (PMT)

Если вы похожи на меня, вы используете термины "Формулы Excel" и "Функции" взаимозаменяемо. Однако эти выражения представляют совершенно разные понятия. Мы можем думать о формуле как об операторе, который возвращает результаты определенного пользовательского вычисления. Формулы могут содержать значения (числовые, дату, текст), ссылки на ячейки, диапазоны ячеек, именованные диапазоны; они могут выполнять любые математические операции (сложение, вычитание, умножение, деление, возведение в степень), а также оценивать результаты встроенных функций Excel… Функция — это заранее определенная формула, которая помогает нам упростить сложные процедуры и операции. Например, замечательные люди из Microsoft запрограммировали различные функции, которые позволяют нам суммировать группу чисел, искать определенное слово в текстовой строке, определять текущую дату и время или даже вычислять сумму нашего платежа по кредиту. Способность создавать сложные формулы Excel с использованием нескольких функций Excel является одним из необходимых условий для того, чтобы стать экспертом в Excel. Формулы всегда начинаются со знака равенства ( = ), и мы всегда заключаем функциональные аргументы (если они есть) в круглые скобки.

Предположим, вам нужно рассчитать платеж по автокредиту для автомобиля вашей мечты — Ford Mustang GT 2015 года. Согласно TrueCarPrice, за базовую модель V8 вы должны раскошелиться на 32 864 доллара из ваших с трудом заработанных долларов за этот автомобиль; если вы проживаете в штате, который Ford Motor Company называет своим домом. Эта цена включает оценку истинной стоимости в размере 29 612 долларов США, рыночную надбавку в размере 567 долларов США, стоимость доставки в размере 825 долларов США и налог с продаж штата. Согласно Bank Rate, вы можете реально профинансировать эту покупку с помощью кредита с процентной ставкой 3%. Поскольку вы только что получили свой праздничный бонус, вы хотите использовать его в качестве первоначального взноса в размере 5000 долларов США за новый автомобиль. Давайте рассчитаем ваш ежемесячный платеж за автомобиль, используя: метод формулы без использования функций Excel: =P*i*(1+i)^n/((1+i)^n-1) , а также формулу, включающую PMT Excel. функция. Оба этих метода основаны на формулах для выполнения необходимых вычислений, но только во втором методе используется функция Excel.

=P*i*(1+i)^n/((1+i)^n-1) формула платежа по кредиту имеет следующие параметры: P — это сумма основного долга или сумма, подлежащая финансированию. В нашем сценарии вы будете финансировать 27 864 доллара (цена автомобиля 32 864 доллара за вычетом 5000 долларов авансового платежа). i — процентная ставка за период. Обратите внимание, что наш кредит указан с использованием годовой процентной ставки, в то время как мы будем производить ежемесячные платежи по кредиту. Это означает, что наша процентная ставка составит 3%, деленное на 12 (количество месяцев/платежей в год) или 0,25%. Наконец, n — это количество периодов или количество платежей, которые вы будете делать с течением времени. Поскольку срок погашения вашего кредита составляет 5 лет, вы должны будете сделать 60 платежей, прежде чем погасить свой Stang. Не забывайте, что Excel следует математическим правилам при установлении порядка операций — «Простите, моя дорогая тетя Салли»:

На нашем листе Сумма кредита находится в ячейке D19, Процентная ставка — в D21, а Срок кредита — в ячейке D23. . Используя формулу кредитного калькулятора, ваш платеж по кредиту определяется по следующей формуле Excel:
=D19*(D21/12)*(1+D21/12)^(D23*12)/((1+D21/12) ^(D23*12)-1) .

Ничего особенного, просто замена алгебраических аргументов значениями ячеек Excel. Будет непросто ежемесячно выплачивать по 500,68 долларов каждый месяц в течение следующих 5 лет. Что еще хуже, низкая процентная ставка в размере 3 % добавит к общей стоимости автомобиля 2 127 долларов США, в результате чего она составит 35 041 доллар США.

Мы можем упростить эту формулу, используя именованные диапазоны Excel (Formulas-Name Manager) и выполняя вспомогательные вычисления в ячейках аргументов, а не в выходной ячейке. Чтобы продемонстрировать этот метод, давайте назначим имя P ячейке D19. Назовем ячейку D21 i и ячейку D23 n. Кроме того, убедимся, что D21 = 3%/12, а D23 = 5*12. Теперь наш расчет выглядит просто так:

К настоящему моменту вы готовы по-настоящему оценить преимущества сотен встроенных функций Excel. Одна из Финансовых функций, называемая PMT, предназначена для выполнения вышеуказанных расчетов, не раздражая вас в процессе.У него всего три обязательных параметра: rate (процентная ставка), nper (количество периодов/платежей) и PV (текущая стоимость вашего кредит.) Передав наши аргументы этой функции, мы можем определить наш платеж по кредиту:
=-PMT(D21/12,D23*12,D19) без вспомогательных вычислений, или
=-PMT(D21,D23,D19) используя вспомогательные вычисления.

Я использовал отрицательный знак перед этой функцией, чтобы умножить ее на -1, поскольку Excel интерпретирует платеж по кредиту как отрицательное число (отток денежных средств). Эта функция будет работать с любым кредитом, который у вас может быть: кредитная карта , студент или даже ваша ипотека:

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

Как выделиться в Excel

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

Конечно, классический пример — положение и скорость:

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

Производная формула Excel с использованием метода конечных разностей

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

Чтобы использовать метод конечных разностей в Excel, мы вычисляем изменение "y" между двумя точками данных и делим на изменение "x" между теми же точками данных:

Это называется односторонней оценкой, поскольку учитывает наклон данных только с одной стороны интересующей точки. Приведенная выше формула возвращает тот же результат, что и функция НАКЛОН в Excel, поэтому мы могли бы ее использовать. Однако это не предпочтительный метод.

Лучшей оценкой будет вычисление среднего уклона в интересующей точке путем усреднения уклона непосредственно до и после этой точки.

Итак, если бы мы хотели найти наклон при y₂ (z), мы могли бы использовать этот расчет:

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

Пример: вычисление производной в Excel

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

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

Как рассчитать скорость в Excel

Во-первых, я вычисляю скорость как производную от данных о местоположении, используя вышеприведенное уравнение конечных разностей. Поскольку нам нужны y3 и y1, я начинаю расчет в ячейке E5 и заполняю ее.

Как вычислить первую производную в Excel

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

Результаты

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

Но насколько серьезна ошибка? Давайте сравним.

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

Таблицы данных не являются идеальным способом изучения этих данных, поэтому давайте посмотрим на графики:

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

Как насчет ускорения?

Здесь мы видим, что в периоды стабильного или постоянного ускорения два набора данных очень похожи. Однако если в данных об ускорении есть разрыв (т. е. в моменты времени 0,1, 0,45, 0,5, 0,7 и 0,75 сек.), ускорение, полученное дифференцированием (оранжевый цвет), не соответствует исходным данным ускорения (синий цвет).

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

Подведение итогов

Если вы следовали инструкциям, поздравляем! Вы только что выполнили численное дифференцирование с помощью Excel. Вычислить производную в Excel не так сложно, если вы знаете, как это сделать.

Читайте также: