Как рассчитать аннуитетный платеж в Excel
Те, кто читал предыдущую публикацию, наверняка ещё долго будут с ужасом вспоминать формулу аннуитетного платежа. Но сейчас вы, дорогие друзья, можете облегчённо вздохнуть, ибо все расчёты за вас сделает программа Microsoft Excel.
Мы сделаем не просто файлик с одной циферкой. Нет! Мы разработаем настоящий инструмент, с помощью которого вы сможете рассчитать аннуитетный платёж не только для себя, но и для соседа, который ставит свою машину на детской площадке; прыщавого студента, который сутками курит в вашем подъезде; тётки, которая выгуливает свою собаку прямо под вашими окнами – короче, для всех особо одарённых. Кстати, можете поставить где-нибудь возле монитора купюроприёмник и брать с этой публики деньги.
Давайте приступим к разработке нашего кредитного калькулятора. Смотрим на первый рисунок:
Итак, вы видите два блока. Один с исходными данными, а второй – с расчётами. Исходные данные (сумма кредита, годовая процентная ставка, срок кредитования) вы будете вводить вручную, а во втором блоке будут мгновенно появляться расчёты.
Начнём с расчёта ежемесячной суммы аннуитетного платежа. Для этого надо сделать активным окошко, в котором вы хотите видеть это значение (в нашем случае – это поле C11, на рисунке оно обведено и указано под номером 1). Далее слева от строки формул жмём на «fx» (на рисунке эта кнопка обведена и указана под номером 2). После этих действий у вас появится такая табличка:
Выбираем функцию «ПЛТ» и жмём «Ок». Перед вами появится таблица, в которую надо будет ввести исходные данные:
Здесь нам требуется заполнить три поля:
- «Ставка» – годовая процентная ставка по кредиту делённая на 12.
- «Кпер» – общий срок кредитования.
- «Пс» – сумма кредита (указывается со знаком минус).
Обратите внимание на то, что мы не вводим готовые цифры в эту таблицу, а указываем координаты ячеек нашего блока с исходными данными. Так, в поле «Ставка» мы указываем координаты ячейки, в которой будет вписываться вручную процентная ставка (C5) и делим её на 12; в поле «Кпер» указываются координаты ячейки, в которой будет вписываться срок кредитования (C6); в поле «Пс» – координаты ячейки в которой вписывается сумма кредита (C4). Так как сумма кредита у нас указывается со знаком минус, то перед координатой (C4) мы ставим знак минус
Так как сумма кредита у нас указывается со знаком минус, то перед координатой (C4) мы ставим знак минус.
После того как исходные данные будут введены, жмём кнопку «Ок». В результате мы видим в блоке расчетов точное значение ежемесячного аннуитетного платежа:
Итак, в данный момент сумма нашего аннуитетного платежа составляет 4680 руб (на рисунке он обведён и указан под номером 1). Если вы будете менять сумму кредита, процентную ставку и общий срок кредитования, то автоматически будет меняться значение вашего аннуитетного платежа.
Кстати, обратите внимание на значение функции, обозначенное на рисунке под номером 2: =ПЛТ(C5/12;C6;-C4). Да, да, это и есть те самые координаты, которые мы вводили в таблицу, выбрав функцию «ПЛТ». По сути, вы могли бы не проделывать всех тех сложных телодвижений, которые показаны на втором и третьем рисунках
Можно было просто вписать в строке формул то, что там сейчас вписано
По сути, вы могли бы не проделывать всех тех сложных телодвижений, которые показаны на втором и третьем рисунках. Можно было просто вписать в строке формул то, что там сейчас вписано.
Зная размер аннуитетного платежа несложно посчитать остальные значения нашего расчётного блока:
На рисунке наглядно показано, как рассчитана общая сумма выплат (обведена и указана под номером 1). Так как она равна сумме аннуитетного платежа (ячейка C11) умноженной на общее количество месяцев кредитования (ячейка C6), то мы и вписываем в строку формул следующую формулу: =C11*C6 (на рисунке она обведена и указана под номером 2). В результате мы получили значение 56 157 рублей.
Переплата по кредиту рассчитывается ещё проще. От общей суммы выплат (ячейка C12) надо отнять сумму кредита (ячейка C4). В строку вписываем такую формулу: =C12-C4. В нашем примере переплата равна: 6157 рублей.
Ну и последнее значение – эффективная процентная ставка (или полная стоимость кредита). Она рассчитывается так: общую сумму выплат (ячейка C12) делим на сумму кредита (ячейка C4), отнимаем единицу, затем делим всё это на срок кредитования в годах (ячейка C6 делённая на 12). В строке будет такая формула: =(C12/C4-1)/(C6/12). В нашем примере эффективная процентная ставка составляет 12,3%.
Всё! Вот таким нехитрым способом мы с вами составили в программе Microsoft 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» с клавиатуры. Буквы будут отличаться в зависимости от того, в каких ячейках находится таблица.
- Убедитесь, что сумма платежа рассчитана автоматически в последней строке таблицы.
Окончательный результат расчета
Особенности использования функции ПЛТ в Excel
В общих чертах эта формула записывается следующим образом: = PMT (rate; nper; ps; ; ). Функция имеет следующие характеристики:
При расчете ежемесячных платежей учитывается только годовой платеж.
При указании процентной ставки важно производить пересчет исходя из количества годовых платежей.
Конкретное число указывается в формуле вместо аргумента Nper. Это период выплаты долга.
Что такое процентная ставка и от чего она зависит?
Процентая ставка — самый важный параметр при расчете кредита. Измеряется в процентах годовых. Он показывает сколько процентов начисляется на сумму долга за 1 год. Но фактически проценты начисляются не один раз в год, а ежедневно в размере ставки, разделенной на 365 дней.
У каждого банка есть свои программы кредитования и свои процентные ставки.
Основные факторы, влияющие на процентную ставку:
-
Ключевая ставка Центробанка. Чтобы дать вам кредит, банк занимает у ЦБ по ставке, равной ключевой, накидывает еще несколько процентов сверху и дает вам в долг под более высокий процент, зарабатывая на разнице. Выгоднее брать кредит, когда ключевая ставка ниже: вы заплатите меньше процентов. На очередном заседании ЦБ может как повысить, так и понизить ставку или оставить без изменений. Это решение принимается в зависимости от экономической ситуации.
Сейчас ключевая ставка равна 6.75%. А вот так она менялась за последние годы:
График изменения ключевой ставки с 2013 по 2021 год
-
- Тип кредита. Чем больше риска несет кредит для банка, тем он дороже. Например, ипотечный кредит дешевле потребительского кредита или кредита наличными. Причина проста — при выдаче ипотеки банк берет в залог недвижимость, невелируя этим риски невыплаты кредита. При выдаче кредита наличными на любые цели у банка нет способа гарантировать возврат, поэтому ставка гораздо выше.
- Характиристики заемщика. Среди них кредитная история и отношения с банком. Кредитные организации оценивают надежность потенциальных заемщиков и делают более выгодные персональные предложения потенциальным клиентам, в надежности которых они уверены. Своим зарплатным клиентам многие банки предоставляют скидку в размере 0.3 – 0.6 процентных пункта.
Преимущества и недостатки использования Excel
Перед тем как использовать программное обеспечение для составления платёжного графика, необходимо подробно изучить его преимущества и недостатки. Благодаря им можно значительно ускорить процесс и избежать большинства простых ошибок. Основные достоинства использования Excel:
- Способность выполнения дополнительных функций. Помимо платёжного календаря и собственного реестра программа может составлять бюджеты и лимитировать платежи относительно них.
- Возможность организовать обмен информацией с платёжной подсистемой 1С. Благодаря этому преимуществу можно компенсировать необходимость повторного ввода некоторых данных.
- Использование в расчётах простых и понятных формул. Это помогает новичкам быстрее разобраться в процессе составления графика платежей и устранить некоторые ошибки.
Несмотря на эти важные достоинства, у использования Excel есть и несколько недостатков. Их нужно учитывать перед началом проведения расчётов и составлением платёжного графика, в противном случае можно столкнуться с различными трудностями, которые осложнят процесс и увеличат вероятность получения недостоверных данных. Отрицательные характеристики проведения расчётов Excel:
- Невозможность контроля ссылочной ценности. Программа не способна противостоять удалению каких-либо данных из ячеек, даже если на них установлены макросы или специальная защита.
- Трудности с многопользовательским режимом работы. В Excel довольно трудно организовать одновременную работу большого количества людей, так как программа способна функционировать только в единичном режиме. Выходом из такой ситуации будет создание специальной базы данных.
- Конфиденциальность информации и ограничение доступа. Для специалиста не составит труда взломать установленный пароль, поэтому Excel редко используется в тех случаях, когда нужно составить платёжный график для большого количества людей. Из-за отсутствия необходимой защиты доступ к файлу с информацией должен быть строго ограничен.
- Повторный ввод данных. Excel, в отличие от 1С, не способен обмениваться информацией с клиентом банка. Из-за этого необходимо будет дорабатывать используемую базу данных и составлять платёжный календарь в ней.
- Ограничение размера файла. Для ведения некоторых расчётов Excel подойдёт идеально, но для большого количества данных возможностей программы будет недостаточно.
Правила использования функции ОСПЛТ в Excel
Функция ОСПЛТ имеет следующий синтаксис:
=ОСПЛТ(ставка;период;кпер;пс;;)
Описание аргументов:
- ставка – обязательный для заполнения, принимает числовое значение процентной ставки в отношении финансового продукта (например, банковского кредита. Задается в виде десятичной дроби. Например, если кредит был взят по 17%, необходимо ввести значение 0,17;
- период – обязательный для заполнения, принимает числовые значения из диапазона от 1 до числа, указанного в качестве следующего аргумента рассматриваемой функции (кпер);
- кпер – обязательный для заполнения, принимает числовое значение, указывающее число периодов платежей в отношении финансового продукта;
- пс – обязательный для заполнения, принимает значение текущей стоимости финансового продукта, то есть суммы кредита, которую клиент должен вернуть банковской организации после заключения договора;
- – необязательный для заполнения, принимает значение будущей стоимости финансового продукта на момент совершения последнего платежа по утвержденной схеме платежей. Если явно не указан, принимается значение, равное 0 (нулю). Значение 0 означает, что задолженность будет выплачена в полном объеме;
- – необязательный для заполнения, принимает значения 0 или 1, указывающие на способ совершения платежей (в конце или начале периода). Если явно не указан, принимает значение 0.
Примечания:
- Если аргумент период принимает значение не из диапазона , функция ОСПЛТ вернет код ошибки #ЧИСЛО!
- Обязательные аргументы могут быть указаны в виде чисел, а также значений текстовых или других типов данных, которые могут быть преобразованы к числовым. Например, записи =ОСПЛТ(0,12;ИСТИНА;12;1000) или =ОСПЛТ(0,17;«4»;10;32000) являются допустимыми.
- При указании аргументов ставка и кпер необходимо согласовывать единицы измерения этих показателей с учетом периодичности выплат. Например, для кредита, оформленного сроком на 1 год со ставкой 23% и ежемесячными платежами аргументы ставка и кпер функции ОСПЛТ должны быть заданы как 0,23/12 и 1*12 соответственно.
Задача2
Требуется накопить за 5 лет сумму 1 000 000 руб. Определить величину регулярных пополнений вклада, если процентная ставка составляет 10% годовых, пополнение вклада производится ежеквартально, капитализация процентов также производится ежеквартально, на счету уже содержится 100 000 руб.
Решение2
Накопить за счет взносов нам потребуется всего 900 000руб. (1 000 000-100 000). Ежеквартальный платеж может быть вычислен по формуле
=ПЛТ(10%/4; 5*4; -100000;1000000; 0)
, результат -32732,42р.
Все параметры функции
ПЛТ()
выбираются аналогично предыдущей задаче, кроме значения ПС = -100000р., который требует пояснения. Вспомним, что для
аннуитета
справедливо тождество: ПС СУММ(ОСПЛТ()) БС=0, т.е. ПС (-900000р.) 1 000 000=0. Отсюда получим ПС = -100000р.
Примечание
. В
файле примера
также приведен расчет графика прироста вклада без использования формул аннуитета (см. столбцы K:O).
Для других банков
Если вы видите по графику банка, что платить нужно последний день каждого месяца, то поставьте
- Установите флаг — Первый платеж только проценты и введите дату выдачи
- Платеж в последний день месяца
Следует также понимать, что некоторые режимы расчета не сочетаются. Например: Учет досрочного погашения в дату платежа и учитывать выходные. При этих параметрах расчет может быть неверен
Это также достаточно точный и универсальный кредитный калькулятор Хоум Кредит банка. Данный калькулятор используют многие, кто хочет взять кредит в Хоум. Кредитный калькулятор онлайн позволяет им прикинуть, сколько они будут платить по кредиту в месяц, а также насколько изменится их платеж, если погашение займа будет идти досрочно.
Аннуитет или ниспадающий остаток?
Желающие разобраться, как рассчитываются ипотечные кредиты нужно изучить теоретические выкладки, способы начисления процентов, действующие формулы. Необходимо понимать, что выплата ипотеки сопряжена с платежами нескольких типов:
- первоначальный взнос;
- ежемесячный платеж, который направляется на погашение основного долга и процентов;
- комплексное страхование объекта недвижимости плюс жизни и здоровья заемщика, дополнительные банковские услуги.
В реальности применяются две схемы вычисления регулярного платежа: аннуитет и модель «ниспадающего остатка». От выбранной схемы будет зависеть сумма платежа, структура погашения долга и величина переплаты. Рассмотрим способы подробно.
Аннуитетная модель предполагает платежи равными долями на протяжении всего периода действия договора. Сначала рассчитываются проценты за весь срок, потом они складываются с телом кредита и делятся на количество выплат.
Получается, что первые платежи практически полностью уходят на уплату процентов. Тело кредита не уменьшается. Да и досрочно погашать кредит не особо выгодно. Зато на заемщика работает инфляция. Постепенно регулярные платежи обесцениваются.
Варианты расчетов c помощью ипотечного калькулятора: так при сумме залога 1 500 000 рублей на 15 лет под 10% годовых регулярная ежемесячная выплата составит 16 119 рублей. И останется такой все последующие годы.
Дифференцированная модель (или Ниспадающий остаток) предполагает, что регулярный платеж состоит из двух частей, фиксированная сумма от тела кредита и процентный платеж, который уменьшается с каждым месяцем. Первое время заемщику придется платить крупные суммы, но постепенно платежи становятся меньше. Общая сумма переплат в этом случае тоже меньше, также выгодно гасить кредит досрочно.
Варианты расчетов: при тех же условиях ипотечного кредитования, но с дифференцированными платежами первый взнос составит 20 833 рубля. А последний – 8 403 рубля.
Для проведения самостоятельных расчетов необходимо знать следующие числовые параметры:
- сумма ипотеки
- первоначальный взнос (как взять квартиру в ипотеку без первоначального взноса?)
- процентная ставка,
- период кредитования (месяцы).
На следующем этапе выбирайте нужный запрос и считайте. Все теоретические выкладки подтверждаются практическими вычислениями.
График платежей
Проводить самостоятельные расчеты сложно, процесс требует хороших математических навыков и аккуратности.
Формула ежемесячного аннуитетного платежа выглядит так:
X = S * (P + P / (1 + P)n – 1),
при этом:
- под X понимается ежемесячный платеж;
- S – это первоначальная сумма ипотечного кредита;
- P – 1/12 часть процентной ставки;
- n – количество месяцев.
Для вычисления дифференцированного платежа пользуются двумя формулами. Сосчитать размер регулярного платежа несложно, сумму основного долга делят на количество месяцев.
Формула выглядит так — Y = S / N, где:
- Y – сумма основного платёжа;
- S – размер ипотеки;
- N – количество месяцев.
Сумму выплаты процентов для каждого месяца необходимо рассчитывать индивидуально, по формуле —
Z = Sn * P / 12, где:
- Z– начисленные проценты;
- Sn — остаток основной задолженности;
- P – годовая процентная ставка по ипотеке.
На десятилетний период придется обсчитать 120 платежей. Поэтому гораздо проще воспользоваться программами-помощниками.
Напоминаем, что вы можете получить быструю бесплатную консультацию по телефону: 8 (800) 350-14-90просто кликните для звонка
График погашения кредита аннуитетными платежами
Вначале мы продемонстрируем вам сам график аннуитетных платежей, проанализируем его вместе с вами, а уж затем детально расскажем о том, как и по каким формулам мы его рассчитали.
Вот так выглядит аннуитетный график погашения нашего кредита:
А это диаграмма (для наглядности):
И график, и диаграмма подтверждают написанное в публикации: Что такое аннуитетные платежи. Если вы по каким-то причинам её не читали, то обязательно это сделайте – не пожалеете. А те, кто читал, могут убедиться, что в аннуитетном графике погашения кредита выплаты осуществляются равными суммами, на начальном этапе доля процентов по кредиту самая высокая, а ближе к окончанию срока она существенно снижается.
Обратите внимание на то, что тело кредита погашается с первого же месяца кредитования. Просто на некоторых сайтах можно прочитать что-то типа такого: «При аннуитетной схеме погашения займа, вначале выплачиваются проценты, а уже потом само тело кредита». Как видите, это утверждение не соответствует действительности
Правильнее будет сказать так:
Как видите, это утверждение не соответствует действительности. Правильнее будет сказать так:
Аннуитетные платежи содержат в себе на начальном этапе высокую долю процентов по кредиту.
Тело же кредита тоже погашается с первого месяца кредитования. Тем самым, уменьшается сумма долга и, соответственно, размер выплат процентов по кредиту.
Теперь давайте детальнее изучим наш график аннуитетных платежей. Как видите, ежемесячный платёж у нас составляет 4680 рублей. Именно эту сумму мы будем каждый месяц выплачивать банку на протяжении всего срока кредитования (в нашем случае – на протяжении 12 месяцев). В результате, общая сумма выплат составит 56 157 рублей. В кредит же мы брали 50 000 рублей (в графике это четвёртая колонка, которая называется «Погашение тела кредита»). Получается, что переплата по данному займу составит 6157 рублей. Собственно, это и есть проценты по кредиту, которые указаны в третьей колонке нашего графика аннуитетных платежей. Получается, что эффективная процентная ставка (или полная стоимость кредита) у нас составит – 12,31%. Давайте «красиво» оформим данную информацию:
Ежемесячный аннуитетный платёж: 4680 руб.
Тело кредита: 50 000 руб.
Общая сумма выплат: 56 157 руб.
Переплата (проценты) по кредиту: 6157 руб.
Эффективная процентная ставка: 12,31%.
Итак, мы с вами проанализировали график аннуитетных платежей. Осталось понять, как вычисляется процентная доля и доля тела кредита в ежемесячных выплатах. Вот почему в первый месяц проценты составляют именно 917 рублей, во второй – 848 рублей, в третий – 777 рублей и т.д.? Хотите узнать? Тогда читайте дальше!
Как рассчитать процент по кредиту за год по аннуитетной схеме
Для начала вычислим сумму ежемесячного платежа можно по формуле, приведенной ниже. А после на основе полученных данных вычислим годовой процент:
Ежемесячный платеж = Сумма кредита × Ставка/ 1- (1 + Ставка)^ — Срок кредита
Обратите внимание, что в этой формуле используется ставка в месяц. Ее нужно вычислить отдельно, разделив годовой процент сначала на 100, а затем на 12
Срок кредита в формуле нужно показать в виде количества месяцев (например, три года – 36 месяцев). Знак «^» здесь обозначает возведение в степень.
Как планировать платежи по кредиту с помощью Excel
Скачайте кредитный калькулятор в Excel. Введите сумму и стоимость кредита, выберите аннуитетный, дифференцированный или индивидуальный график его погашения и спланируйте выгодный график погашения кредита.
Скачать шаблон в Excel
График выплат
Сохранить в pdf Сохранить в Excel Распечатать Cсылка на расчет
| Дата платежа | Остаток задолженности, руб. | Начисленные %, руб. | Платеж в основной долг, руб. | Сумма платежа, руб. |
|---|---|---|---|---|
| 08.10.2018 | 310 000,00 | 3 821,92 | 3 552,96 | 7 374,88 |
| 08.11.2018 | 306 447,04 | 3 904,05 | 3 470,83 | 7 374,88 |
| 08.12.2018 | 302 976,21 | 3 735,32 | 3 639,56 | 7 374,88 |
| 08.01.2019 | 299 336,66 | 3 813,47 | 3 561,41 | 7 374,88 |
| 08.02.2019 | 295 775,25 | 3 768,10 | 3 606,78 | 7 374,88 |
| 08.03.2019 | 292 168,46 | 3 361,94 | 4 012,94 | 7 374,88 |
| 08.04.2019 | 288 155,52 | 3 671,02 | 3 703,86 | 7 374,88 |
| 08.05.2019 | 284 451,67 | 3 506,94 | 3 867,94 | 7 374,88 |
| 08.06.2019 | 280 583,73 | 3 574,56 | 3 800,32 | 7 374,88 |
| 08.07.2019 | 276 783,41 | 3 412,40 | 3 962,48 | 7 374,88 |
| 08.08.2019 | 272 820,93 | 3 475,66 | 3 899,21 | 7 374,88 |
| 08.09.2019 | 268 921,71 | 3 425,99 | 3 948,89 | 7 374,88 |
| 08.10.2019 | 264 972,83 | 3 266,79 | 4 108,09 | 7 374,88 |
| 08.11.2019 | 260 864,74 | 3 323,35 | 4 051,53 | 7 374,88 |
| 08.12.2019 | 256 813,20 | 3 166,19 | 4 208,69 | 7 374,88 |
| 08.01.2020 | 252 604,51 | 3 218,11 | 4 156,77 | 7 374,88 |
| 08.02.2020 | 248 447,75 | 3 165,16 | 4 209,72 | 7 374,88 |
| 08.03.2020 | 244 238,03 | 2 910,78 | 4 464,10 | 7 374,88 |
| 08.04.2020 | 239 773,93 | 3 054,65 | 4 320,22 | 7 374,88 |
| 08.05.2020 | 235 453,71 | 2 902,85 | 4 472,02 | 7 374,88 |
| 08.06.2020 | 230 981,68 | 2 942,64 | 4 432,23 | 7 374,88 |
| 08.07.2020 | 226 549,45 | 2 793,08 | 4 581,80 | 7 374,88 |
| 08.08.2020 | 221 967,64 | 2 827,81 | 4 547,07 | 7 374,88 |
| 08.09.2020 | 217 420,57 | 2 769,88 | 4 605,00 | 7 374,88 |
| 08.10.2020 | 212 815,57 | 2 623,75 | 4 751,12 | 7 374,88 |
| 08.11.2020 | 208 064,45 | 2 650,68 | 4 724,19 | 7 374,88 |
| 08.12.2020 | 203 340,25 | 2 506,93 | 4 867,94 | 7 374,88 |
| 08.01.2021 | 198 472,31 | 2 528,48 | 4 846,40 | 7 374,88 |
| 08.02.2021 | 193 625,91 | 2 466,74 | 4 908,14 | 7 374,88 |
| 08.03.2021 | 188 717,78 | 2 171,55 | 5 203,33 | 7 374,88 |
| 08.04.2021 | 183 514,44 | 2 337,92 | 5 036,95 | 7 374,88 |
| 08.05.2021 | 178 477,49 | 2 200,41 | 5 174,47 | 7 374,88 |
| 08.06.2021 | 173 303,02 | 2 207,83 | 5 167,05 | 7 374,88 |
| 08.07.2021 | 168 135,97 | 2 072,91 | 5 301,97 | 7 374,88 |
| 08.08.2021 | 162 834,00 | 2 074,46 | 5 300,42 | 7 374,88 |
| 08.09.2021 | 157 533,59 | 2 006,93 | 5 367,94 | 7 374,88 |
| 08.10.2021 | 152 165,64 | 1 876,01 | 5 498,86 | 7 374,88 |
| 08.11.2021 | 146 666,78 | 1 868,49 | 5 506,38 | 7 374,88 |
| 08.12.2021 | 141 160,40 | 1 740,33 | 5 634,54 | 7 374,88 |
| 08.01.2022 | 135 525,85 | 1 726,56 | 5 648,32 | 7 374,88 |
| 08.02.2022 | 129 877,54 | 1 654,60 | 5 720,27 | 7 374,88 |
| 08.03.2022 | 124 157,26 | 1 428,66 | 5 946,22 | 7 374,88 |
| 08.04.2022 | 118 211,04 | 1 505,98 | 5 868,90 | 7 374,88 |
| 08.05.2022 | 112 342,14 | 1 385,04 | 5 989,84 | 7 374,88 |
| 08.06.2022 | 106 352,30 | 1 354,90 | 6 019,98 | 7 374,88 |
| 08.07.2022 | 100 332,32 | 1 236,97 | 6 137,90 | 7 374,88 |
| 08.08.2022 | 94 194,42 | 1 200,01 | 6 174,87 | 7 374,88 |
| 08.09.2022 | 88 019,55 | 1 121,34 | 6 253,53 | 7 374,88 |
| 08.10.2022 | 81 766,02 | 1 008,07 | 6 366,80 | 7 374,88 |
| 08.11.2022 | 75 399,21 | 960,57 | 6 414,31 | 7 374,88 |
| 08.12.2022 | 68 984,90 | 850,50 | 6 524,38 | 7 374,88 |
| 08.01.2023 | 62 460,52 | 795,73 | 6 579,15 | 7 374,88 |
| 08.02.2023 | 55 881,37 | 711,91 | 6 662,96 | 7 374,88 |
| 08.03.2023 | 49 218,41 | 566,35 | 6 808,53 | 7 374,88 |
| 08.04.2023 | 42 409,88 | 540,29 | 6 834,59 | 7 374,88 |
| 08.05.2023 | 35 575,29 | 438,60 | 6 936,28 | 7 374,88 |
| 08.06.2023 | 28 639,01 | 364,85 | 7 010,03 | 7 374,88 |
| 08.07.2023 | 21 628,99 | 266,66 | 7 108,22 | 7 374,88 |
| 08.08.2023 | 14 520,77 | 184,99 | 7 189,89 | 7 374,88 |
| 08.09.2023 | 7 330,88 | 93,39 | 7 330,88 | 7 424,27 |
Показать все
Как определить полную стоимость займа
По новым требованиям законодательства для определения полной стоимости кредита используется новая формула:
ПСК = ставка процента за базовый период (i) * число периодов за 1 календарный год (ЧБП) * 100
Например, мужчина оформил кредит на сумму 200 тыс. долларов на 1 год по ставке 22%. При этом ему пришлось внести единовременную комиссию 2,7 тыс. долларов.
Для определения полной стоимости займа формируется график:
Базовый период (БП) представлен интервалом времени, встречающимся в графике наиболее часто. На примере он представлен 28 днями. В этом случае число периодов составляет: 365/28=13. Только после этого рассчитывается ставка базового периода:
В результате легко рассчитывается полная стоимость кредита (ПСК), которая составляет 22%. Благодаря использованию новой формулы данный показатель равен годовой ставке по займу.
Как рассчитать уровень платежей в Excel
Для начала открывается новый документ в программе Excel и создается таблица, где письменно оформляются все кредитные условия. В договоре указывают:
- Сумму долга;
- Процентную ставку;
- Срок погашения долга.
Наравне с этим создается таблица для расчета с номерами месяцев и платежами по кредиту.
Данная графа рассчитана для указания формулы вида =ПЛТ( процентная ставка; срок в месяцах; сумма кредита). Процентная ставка делится на 12. Сумма кредита определяется через деление всего долга на основной срок кредитования.
Используемая формула
Для определения аннуитетного платежа, необходимо рассмотреть следующую формулу:
АП= KA*SM, где АП принадлежит к сумме данного платежа, КА – это значение коэффициента, SM определяет величину погашения долга в течение каждого месяца.
На практике рассматриваются различные формулы, которые помогают осуществлять расчет коэффициента платежа. В наиболее популярной формуле можно отнести: КА = (j*(1+j)m) / ( (1+j)m– 1):
- KA является коэффициентом,
- j определяет ставку кредитования (по ежемесячной оплате),
- m – количество выплачиваемых дней кредита,
- i обозначается в виде годовой ставки.
Детализация
Если есть возможность, заемщик имеет право погасить долг досрочно. В данном варианте, физическое лицо освобождается от выплаты комиссий или штрафов.
Как правило, достоверные сведения о погашении кредита, то есть о выплате ежемесячного платежа либо фактической переплаты, можно получить непосредственного у самого кредитора. Существует такая кредитная программа, которая предусматривает выплату скрытых комиссий. Поэтому рассматривая детали ежемесячного платежа, клиент может заметить некоторые изменения. То есть суммарный показатель будет отличаться от заранее рассчитанных. Иногда некоторые банковские учреждения меняют процентную ставку. Все эти нюансы необходимо анализировать перед тем, как подписать договор.
Расчет при досрочном погашении
Для того чтобы правильно рассчитаться с банком при погашении досрочного кредита, необходимо воспользоваться . Данный метод позволит безошибочно покрыть долг раньше указанного времени в договоре. Данный калькулятор представляет собой универсальную формулу, которая позволяет осуществлять точный расчет. Калькулятор предназначен для любых типов кредита. В этот список включены: ипотека, потребительский или автокредит. При помощи данного аппарата можно подсчитать величину суммы. То есть насколько уменьшается ежемесячный платеж.
Калькулятор по кредиту с нерегулярными проплатами
При возникновении непредвиденных обстоятельств, у некоторых клиентов фиксируется просроченный платеж. Воспользовавшись услугами онлайн-калькулятора для расчета остатка по кредиту, можно определить сумму в целом, которую придется покрыть после погашения просроченного платежа.
Если размер долга не слишком большой, тогда лучше всего погасить долг целиком. Таким образом, банк прекратит начислять лишние проценты. Чтобы выполнить данную процедуру, необходимо указать показатели кредита в соответствующей графе. К примеру рассматривается сумма кредита на 6000 с процентной ставкой 3%, ежемесячным аннуитетным платежом – 30 единиц. Число платежей составляет 18 сроком на 18 месяцев без лишних просрочек. При помощи онлайн-калькулятора, сумма в целом составляет что к ровно 5724,19.
Методы расчета суммы кредита при помощи Excel-таблицы
Размер платежа зависит от метода исчисления. Современные российские банки используют два метода расчета: аннуитетный и дифференцированной. В любом из их имеется тело кредита и начисляемые проценты. Оба типа можно включить в Excel-таблицу, где заблаговременно будут прописаны формулы.
Аннутитетные платежи
В 2019 году русские банки для дизайна кредитов берут почаще всего аннуитетные системы, которые предполагают каждомесячные выплаты по кредиту, при всем этом вносимая заемщиком сумма не меняется в протяжении всего периода кредитования. Таковая практика пришла к нам из Европы, где банкиры успели ее оценить по достоинству.
Размер постоянного взноса принято рассчитывать по формуле:
Е = К * S, где
Е — месячный платеж;
К — коэффициент аннуитетного платежа;
S — начальная сумма задолженности.
Для расчета коэффициента можно использовать такую зависимость:
К = (j * (1 + j)^m) / ((1+j)^m-1), где
j — каждомесячная ставка процентов, которая высчитывается при делении годичный на 12 (кол-во месяцев в году);
m — период кредитования в месяцах.
В таблице расчетов процентов по кредиту для эксель можно использовать обычную формулу аннуитета. Для этого употребляется аббревиатура ПТЛ:
- вносим входные характеристики для расчета месячных взносов по кредиту;
- формируем график погашения с колонками «Номер месяца» и «Платеж»;
- для первой ячейки «Платеж» прописываем формулу =ПЛТ($B$3/12; $B$4; $B$2);
- можно поменять ссылки постоянными данными, тогда пример будет смотреться таковым образом =ПЛТ(12%/12; 24; 1000000).
В полях «Платежи» значения воспримут красноватый цвет и будут отрицательными. Это соединено с тем, что суммы необходимо будет отдавать.
Дифференцированная схема
Проводить расчеты потребительского кредита в Эксель можно по дифференцированному принципу. Сущность дифференцированных платежей заключается в том, что во время погашения миниатюризируется остаток долга, на который начисляются проценты. Соответственно месячный платеж повсевременно понижается. Практически долг распределяется умеренно по всему периоду, а процентный платеж, уплачиваемый каждый месяц на остаток, становится меньше, за счет чего же понижается общая сумма месячного платежа.
Потому что формулы расчета аннуитетного и дифференцированного платежа по кредиту в Excel различаются, то приведем ее для второго типа:
МП = ОСЗ / (ПП + ОСЗ * МС), где
МП — месячный кредитный платеж;
ОСЗ — сумма остатка тела кредита;
ПП — количество периодов до полного погашения;
МС — каждомесячная ставка процентов, которая рассчитывается делением годичный на 12 месяцев.
Для первого месяца задолженность по кредиту составит =$B$2. Последующие оплаты со 2-ой включительно нужно рассчитывать по формуле в таблицах эксель =ЕСЛИ(D10>$B$4;0;E9-G9). В данной зависимости под D10 прячется номер периода, под В4 время кредита, в Е9 вносится остаток от предшествующего периода, а G9 – размер главный задолженности в прошедшем периоде. При сопоставлении схожих сумм и времени погашения будет таковой итог.
Разумеется, что в черном цвете дифференцированный метод выгодней для клиента. При его расчете оказывается наименьшая переплата.