как посчитать проценты по кредиту в excel
Как рассчитать проценты по кредиту в Excel без специальных знаний
Часто прописанные в кредитном договоре величины и числа не вызывают доверия. Проверить их можно с помощью программы MO Excel, применив, в зависимости от вопроса, одну из финансовых функций.
Аннуитетные платежи по кредитному договору: как рассчитать в Excel
Как предполагается, по аннуитетной схеме клиенту необходимо вносить для погашения задолженности равные суммы в течение срока договора с кредитной организацией. Для того чтобы рассчитать такие платежи, в программе есть специальная функция – ПЛТ. Ее использование требует создание новой таблицы и ввода данных в любой ячейке поля.
Например, был выдан кредит на сумму 100 тысяч рублей под 15% годовых на два года. Соответственно, в ячейке необходимо отразить выражение:
В скобках после наименования данные вводятся в определенном порядке:
Плата процентов по кредиту
Знак минуса перед суммой означает, что данное число представляет собой обязательство. Если это единичный расчет, ставить его необязательно. Но если число в дальнейшем используется в других формулах, он важен. Процентная ставка может быть отражена десятичной дробью (15% годовых = 0,0125).
Расчет таких платежей позволит проверить, насколько правильно сотрудниками банка определен ежемесячный платеж клиента.
Расчет дифференцированных платежей в программе MO Excel
При выборе дифференцированного варианта возврата денег банку клиент теряет намного меньше, так как проценты с каждым разом уменьшаются. Банки же такой вариант предлагают реже. Но и для лица этот вариант менее удобен, так как регулярно нужно рассчитывать новую сумму к оплате.
В основу снова ляжет пример. Клиент взял в банке 180 тысяч рублей на 3 года. Ставка – 13% годовых. Погашение предполагается каждый месяц, в конце периода.
Для расчетов необходимо узнать ежемесячную базовую сумму, подлежащую выплате. Каждый месяц клиент обязан возвращать банку равную сумму – часть долга. В рассматриваемом случае это 180000 / 3 / 12 = 5000 рублей. Каждый месяц на остаток начисляются прописанные в договоре проценты. Соответственно, уменьшается остаток – меньше становится и сумма, начисляемая банком.
Расчет основывается на функции ПРОЦПЛАТ. Через точку с запятой в ней обозначаются четыре показателя:
Расчет процентов по кредиту
Функция ПРОЦПЛАТ совпадает по аргументам с предыдущей формулой, однако не имеет с ней ничего схожего, подменять их друг другом нельзя. В англоязычной версии наименование функции – ISPMT, аргументы в ней такие же.
В ПРОЦПЛАТ предполагается начисление суммы процентов в начале периода. Сдвинуть эту функцию на конец месяца можно, если сместить вычисления на период раньше (не «период», а «период-1»). Итоги будут отображены с противоположным знаком, то есть минусом. Таким образом отличаются расчеты при начислении процентов по кредиту и вкладу.
Формула определения суммы процентов по взятому кредиту
Вычисление сумм, перечисляемых на погашение процентов, возможно с использованием функции ПРПЛТ. Ее аргументы не отличаются от необходимых в ОСПЛТ:
Такие расчеты подходят для аннуитетных платежей, когда не известно тело кредита. Определить процент при дифференцированной схеме начисления процентов можно, узнав, какие денежные средства направляются ежемесячно на погашение задолженности.
От суммы ежемесячного платежа необходимо отнять то самое тело кредита, направляемое на погашение непосредственно занятой у банка суммы. Разница и будет процентами, постепенно снижающимися при дифференцированном порядке их начисления.
Установление полной стоимости кредита в программе
Формула для определения ставки по кредиту рекомендована Центробанком России. Ее возможно посчитать, если использовать формулу, указанную в письме ФНС.
Для понимания вопроса необходимы многочисленные данные:
В процессе определения стоимости кредита (т.е. связанные с ним суммы за выдачу кредита или первоначальное рассмотрение заявки) важно отразить все дополнительные расходы, например, за выдачу, чтобы сумма стала максимально корректной.
Полную стоимость при исчислении составляют:
Самостоятельный расчет выплат
С целью расчетов установлена новая формула, которая уже давно используется за рубежом для установления эффективной годовой ставки.
В подходящую формулу входят:
Законодательные рекомендации в данном аспекте окончательно не сформированы. Однако функция уже действует.
Таким образом, финансовые функции Excel позволяют проверить отсутствие переплат ввиду ошибок и огрех в договоре, случайных или намеренных. Каждый шаблон не требует тяжело получаемых данных, поэтому удобен в применении.
Заметили ошибку? Выделите ее и нажмите Ctrl+Enter, чтобы сообщить нам.
Расчет кредита в Excel
Так что очень надеюсь, что изложенный ниже материал вам не пригодится.
Но если уж случится так, что вам или вашим близким придется влезть в это дело, то неплохо бы перед походом в банк хотя бы ориентировочно прикинуть суммы выплат по кредиту, переплату, сроки и т.д. «Помассажировать числа» заранее, как я это называю 🙂 Microsoft Excel может сильно помочь в этом вопросе.
Вариант 1. Простой кредитный калькулятор в Excel
Также полезно будет прикинуть общий объем выплат и переплату, т.е. ту сумму, которую мы отдаем банку за временно использование его денег. Это можно сделать с помощью простых формул:
Вариант 2. Добавляем детализацию
Функция ОСПЛТ (PPMT) в ячейке B17 вводится по аналогии с ПЛТ в предыдущем примере:
Чтобы сделать наш калькулятор более универсальным и способным автоматически подстраиваться под любой срок кредита, имеет смысл немного подправить формулы. В ячейке А18 лучше использовать формулу вида:
Эта формула проверяет с помощью функции ЕСЛИ (IF) достигли мы последнего периода или нет, и выводит пустую текстовую строку («») в том случае, если достигли, либо номер следующего периода. При копировании такой формулы вниз на большое количество строк мы получим номера периодов как раз до нужного предела (срока кредита). В остальных ячейках этой строки можно использовать похожую конструкцию с проверкой на присутствие номера периода:
=ЕСЛИ(A18<>«»; текущая формула; «»)
Т.е. если номер периода не пустой, то мы вычисляем сумму выплат с помощью наших формул с ПРПЛТ и ОСПЛТ. Если же номера нет, то выводим пустую текстовую строку:
Вариант 3. Досрочное погашение с уменьшением срока или выплаты
Реализованный в предыдущем варианте калькулятор неплох, но не учитывает один важный момент: в реальной жизни вы, скорее всего, будете вносить дополнительные платежи для досрочного погашения при удобной возможности. Для реализации этого можно добавить в нашу модель столбец с дополнительными выплатами, которые будут уменьшать остаток. Однако, большинство банков в подобных случаях предлагают на выбор: сокращать либо сумму ежемесячной выплаты, либо срок. Каждый такой сценарий для наглядности лучше посчитать отдельно.
Вариант 4. Кредитный калькулятор с нерегулярными выплатами
Существуют варианты кредитов, где клиент может платить нерегулярно, в любые произвольные даты внося любые имеющиеся суммы. Процентная ставка по таким кредитам обычно выше, но свободы выходит больше. Можно даже взять в банке еще денег в дополнение к имеющемуся кредиту. Для расчета по такой модели придется рассчитывать проценты и остаток с точностью не до месяца, а до дня:
Расчет платежей по кредиту в excel
Необходимые данные для расчета графика платежей по кредиту в Excel
Основные вопросы, связанные с расчетом кредита, заключаются, как правило, в следующем:
Чтобы ответить на оба вопроса потребуется информация о ставке процента и сроке кредитования. Дополнительно для ответа на первый вопрос необходима информация о сумме платежа, для ответа на второй – данные о размере кредита.
Величина процентной ставки зависит от многих параметров: от кредитной политики конкретного банка, срока займа, вида программы кредитования, обеспечения и т.д.
Срок кредита, как правило, может выбираться заемщиком. Обычно он является кратным 12 месяцам и не превышает 7 лет (по ипотеке – до 30 лет).
Формула расчета ежемесячного платежа по кредиту в Excel
Если величина платежа по аннуитетной схеме известна и требуется определить возможный размер кредита, используйте формулу ПС. Ее аргументы:
Для расчета аннуитетных платежей по кредиту в Excel используются следующие функции:
Пример расчета графика платежей по кредиту
Данные для проведения вычислений:
В данном случае функция ПС представлена следующим образом: ПС(20%/12;12;5000). Результатом вычислений является максимально возможная сумма кредита 53 976 р.
Функции, используемые при расчете платежа, будут представлены таким образом:
Итогом расчетов будут значения:
В случае расчета дифференцированного платежа сумма погашаемого основного долга остается одинаковой в течение всего периода. Ее размер рассчитывается как отношение суммы кредита к сроку кредитования в месяцах. Вычисление размера процентов в составе платежа происходит следующим образом:
Для простоты расчетов (без вычисления количества дней в каждом из периодов) используется следующая формула:
В примере ниже использован именно такой подход.
Скачать таблицу расчета платежей по кредиту в Excel с приведенными примерами можно здесь.
Формула для расчета аннуитетного платежа в Excel
Платежи по кредитам удобнее и быстрее рассчитывать с Microsoft Office Excel. На ручное вычисление уходит гораздо больше времени. В данной статье речь пойдет об аннуитетных платежах, особенностях их расчета, преимуществах и недостатках.
Что такое аннуитетный платеж
Способ ежемесячного погашения кредита, при котором вносимая сумма не меняется в течение всего времени кредитования. Т.е. человек по определенным числам каждого месяца вносит конкретную сумму денег до тех пор, пока полностью не погасит кредит.
Причем проценты по кредиту уже включены в общую сумму, вносимую в банк.
Классификация аннуитета
Аннуитетные платежи можно разделить на следующие виды:
Обратите внимание! Фиксируемые платежи предпочтительнее для всех заемщиков, т.к. имеют небольшой риск.
Преимущества и недостатки аннуитетных платежей
Чтобы лучше разбираться в теме, необходимо изучить ключевые особенности данного типа кредитных платежей. Он имеет следующие преимущества:
Без недостатков не обошлось:
Из чего состоит платеж по кредиту?
Аннуитетный платеж имеет следующие составляющие части:
В итоге общее количество процентов практически всегда превышает вносимую заемщиком сумму для уменьшения долга.
Основная формула аннуитетного платежа в Excel
Как и говорилось выше, в Microsoft Office Excel можно работать с различными типами платежей по кредитам и ссудам. Аннуитет не является исключением. В общем виде формула, с помощью которой можно быстро вычислить аннуитетные взносы, выглядит следующим образом:
Важно! Раскрывать скобки в знаменателе данного выражения для его упрощения нельзя.
Основные значения формулы расшифровываются так:
Для усвоения информации достаточно привести несколько примеров использования данной формулы. О них пойдет речь далее.
Примеры использования функции ПЛТ в Excel
Приведем простое условие задачи. Необходимо посчитать ежемесячный кредитный платеж, если банк выдвигает процент в размере 23%, а общая сумма составляет 25000 рублей. Кредитование продлится на протяжении 3-х лет. Задача решается по алгоритму:
Дополнительная информация! Отрицательное число свидетельствует о том, что заемщик расходует деньги.
Пример расчета суммы переплаты по кредиту в Excel
В этой задаче надо подсчитать сумму, которую переплатит человек, взявший кредит 50000 рублей по процентной ставке 27% на 5 лет. Всего в год заемщик производит 12 выплат. Решение:
Формула вычисления оптимального ежемесячного платежа по кредиту в Excel
Задача с таким условием: клиент зарегистрировал счет в банке на 200000 рублей с возможностью ежемесячного пополнения. Нужно посчитать количество платежа, который человек должен вносить каждый месяц, чтобы через 4 года на его счету оказалось 2000000 рублей. Ставка составляет 11%. Решение:
Обратите внимание! Таким образом, чтобы на счету клиенту через 4 года накопилось 2000000 рублей по ставке 11%, ему нужно каждый месяц вносить по 28188 рублей. Минус в сумме свидетельствует о том, что клиент несет убытки, отдавая деньги в банк.
Особенности использования функции ПЛТ в Excel
В общем виде данная формула записывается следующим образом: =ПЛТ(ставка; кпер; пс; [бс]; [тип]). У функции есть следующие особенности:
Расчет оплаты
В общем виде оплата по аннуитету рассчитывается в два этапа. Чтобы разбираться в теме, каждый из этапов необходимо рассмотреть по отдельности. Об этом пойдет речь далее.
Этап 1: расчет ежемесячного взноса
Чтобы в Excel посчитать сумму, которую нужно вносить каждый месяц по кредиту с фиксируемой ставкой, необходимо:
Важно! После расчета взноса можно будет рассчитать сумму, которую переплатит заемщик за весь период кредитования.
Этап 2: детализация платежей
Сумму переплаты можно посчитать помесячно. В итоге человек поймет, сколько денег каждый месяц он будет тратить на кредит. Расчет по детализации выполняется следующим образом:
Дополнительная информация! При расчете остатка на формулу надо навешивать знаки долларов, чтобы она не съехала при растягивании.
Расчет аннуитетных платежей по кредиту в Excel
За вычисление аннуитета в Excel отвечает функция ПЛТ. Принцип вычисления в общем виде заключается в выполнении следующих шагов:
Расчет в MS Excel погашение основной суммы долга
Аннуитетные платежи должны вноситься ежемесячно определенными суммами. Причем процентная ставка не изменяется.
Вычисление остатка суммы основного долга (при БС=0, тип=0)
Предположим, что кредит на 100000 рублей берется на 10 лет под 9%. Необходимо рассчитать сумму основного долга в 1 месяце 3-го года. Решение:
Вычисление суммы основного долга, которая была выплачена в промежутке между двумя периодами
Такой расчет лучше сделать простым способом. Нужно использовать следующие формулы для вычисления суммы в промежутке за два периода:
Обратите внимание! Буквы в скобках заменяются конкретными значениями.
Досрочное погашение с уменьшением срока или выплаты
Если потребуется уменьшить срок кредитования, то придется производить дополнительные вычисления с помощью оператора ЕСЛИ. Так можно будет контролировать нулевой баланс, который не должен быть достигнут раньше окончания сроков выплаты.
Досрочное погашение с уменьшением срока
Чтобы снизить выплаты, нужно пересчитывать взнос за каждый предыдущий месяц.
Уменьшение выплат кредитования
Кредитный калькулятор с нерегулярными выплатами
Есть несколько вариантов аннуитета, когда заемщик может вносить нефиксированные суммы в любой день месяца. В такой ситуации остаток долга и проценты считаются за каждый день. При этом в Экселе надо:
Расчет периодического платежа в MS Excel. Срочный вклад
В Excel можно быстро посчитать размер регулярных выплат при условии, что уже накопилась фиксированная сумма. Данное действие выполняется с использованием функции ПЛТ после составления исходной таблицы.
Заключение
Таким образом, аннуитетные платежи проще, быстрее и эффективнее рассчитывать именно в Эксель. За их вычисление отвечает оператор ПЛТ. С подробными примерами можно ознакомиться выше.
Расчет аннуитетных платежей по кредиту в Excel
В наш век высоких технологий и автоматизации как-то неприлично вручную выполнять сложные расчёты. Хоть аннуитетные платежи рассчитать не так и трудно, но как говорит Юрий Ашер:
«Не надо напрягать свой мозг там, где это могут сделать за вас другие!»
В нашей ситуации к вам на помощь придут: компьютер и программа Microsoft Excel.
Хотим предупредить, что команда портала temabiz.com поставила перед собой цель не просто дать вам «халяву» в виде «экселевского» файла с готовыми расчетами. Нет, в этой публикации мы вас научим самостоятельно рассчитывать аннуитетные платежи, а также составлять в программе Excel графики погашения аннуитетных кредитов. Ну а для ленивых мы, конечно же, выложим готовые файлы кредитных калькуляторов.
Как рассчитать аннуитетный платеж в Excel
Те, кто читал предыдущую публикацию, наверняка ещё долго будут с ужасом вспоминать формулу аннуитетного платежа. Но сейчас вы, дорогие друзья, можете облегчённо вздохнуть, ибо все расчёты за вас сделает программа Microsoft Excel.
Мы сделаем не просто файлик с одной циферкой. Нет! Мы разработаем настоящий инструмент, с помощью которого вы сможете рассчитать аннуитетный платёж не только для себя, но и для соседа, который ставит свою машину на детской площадке; прыщавого студента, который сутками курит в вашем подъезде; тётки, которая выгуливает свою собаку прямо под вашими окнами – короче, для всех особо одарённых. Кстати, можете поставить где-нибудь возле монитора купюроприёмник и брать с этой публики деньги.
Давайте приступим к разработке нашего кредитного калькулятора. Смотрим на первый рисунок:
Итак, вы видите два блока. Один с исходными данными, а второй – с расчётами. Исходные данные (сумма кредита, годовая процентная ставка, срок кредитования) вы будете вводить вручную, а во втором блоке будут мгновенно появляться расчёты.
Начнём с расчёта ежемесячной суммы аннуитетного платежа. Для этого надо сделать активным окошко, в котором вы хотите видеть это значение (в нашем случае – это поле C11, на рисунке оно обведено и указано под номером 1). Далее слева от строки формул жмём на «fx» (на рисунке эта кнопка обведена и указана под номером 2). После этих действий у вас появится такая табличка:
Выбираем функцию «ПЛТ» и жмём «Ок». Перед вами появится таблица, в которую надо будет ввести исходные данные:
Здесь нам требуется заполнить три поля:
Обратите внимание на то, что мы не вводим готовые цифры в эту таблицу, а указываем координаты ячеек нашего блока с исходными данными. Так, в поле «Ставка» мы указываем координаты ячейки, в которой будет вписываться вручную процентная ставка (C5) и делим её на 12; в поле «Кпер» указываются координаты ячейки, в которой будет вписываться срок кредитования (C6); в поле «Пс» – координаты ячейки в которой вписывается сумма кредита (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 суммы кредита для заданного аннуитетного платежа
В чём «фишка» аннуитетной схемы погашения кредита? Правильно! Основная «фишка» в том, что заёмщик выплачивает кредит равными суммами на протяжении всего срока кредитования. С такой схемой очень удобно планировать свой бюджет. Например, вы готовы ежемесячно выделять на погашение кредита 5000 рублей. По вашим скромным подсчётам, такая нагрузка будет для вас не слишком обременительной. Естественно, у вас возникает закономерный вопрос: «А на какую сумму кредита я могу рассчитывать?» В общем, нам нужен новый кредитный калькулятор, у которого в исходных данных будет не сумма кредита, а величина аннуитетного платежа.
Что же, друзья, не будем терять время! Открываем программу Microsoft Excel и приступаем к разработке нашего кредитного калькулятора!
Итак, структура нового кредитного калькулятора почти не изменилась. Здесь также есть блок с исходными данными и блок с расчётами. Единственное изменение, это то, что в исходных данных мы вводим ежемесячный аннуитетный платёж, который готовы выплачивать, а в расчётах получаем сумму кредита, на которую мы можем рассчитывать. Собственно, она на нашем рисунке обведена и отмечена под номером 1.
Чтобы рассчитать сумму ожидаемого кредита надо воспользоваться функцией ПС, предварительно кликнув по ячейке, в которой мы хотим видеть свой расчёт (в нашем калькуляторе это ячейка с координатой C11). Вызвать функцию ПС можно нажав на знакомую вам кнопку «fx», которая находится слева от строки формул. В появившемся окне выбираем «ПС» и жмём «Ок». В открывшейся таблице вводим следующие данные:
Жмём «Ок» и в ячейке С11 появилась сумма 53 422 руб. – именно на такой размер кредита может рассчитывать заёмщик, который готов на протяжении 12 месяцев ежемесячно выплачивать по 5000 руб.
Кстати, обратите внимание на данные в строке формул (на рисунке они обведены и указаны под номером 2). Вы всё правильно поняли, друзья! Да, это те данные, которые необходимы для расчёта суммы кредита в нашем калькуляторе: =ПС(C5/12;C6;-C4). Те самые параметры, которые мы вводили в таблице функции ПС.
Расчёт остальных показателей выполняется по такому же принципу, как и в предыдущем калькуляторе:
Кстати, интересный момент. Вот в нашем примере, выплачивая ежемесячно в течение года по 5000 рублей, мы можем рассчитывать на сумму кредита равную 53 422 рубля. А что делать, если надо больше денег? Как вариант, можно увеличить срок кредитования. Если вместо 12 месяцев поставить 24, то сумма кредита увеличится до 96 380 рублей. Эти данные нам мгновенно выдал наш кредитный калькулятор, который вы можете скачать ссылке ниже:
Кредитный калькулятор в Excel по расчету графика аннуитетных платежей
Два предыдущих кредитных калькулятора очень удобны, но они выполняют краткие (общие) расчёты. А иногда заёмщику нужна расширенная информация – график ежемесячных аннуитетных платежей с детальной расшифровкой каждой выплаты (с указанием сумм, идущих на погашение процентов, и сумм, погашающих тело кредита). В общем, сейчас мы сделаем в программе Excel ещё один кредитный калькулятор, который будет автоматически рассчитывать график аннуитетных платежей. Щёлкаем мышкой по рисунку:
Перед вами расширенная и доработанная версия нашего первого кредитного калькулятора (того, который рассчитывает размер ежемесячного аннуитетного платежа по кредиту). Здесь кроме стандартных блоков с исходными данными и расчётами, появилась таблица, в которой детально расписаны все наши будущие ежемесячные выплаты. Таблица имеет пять колонок:
Вот так легко и непринуждённо мы разработали кредитный калькулятор по расчёту графика аннуитетных платежей. Скачать его можно ссылке ниже:
Итак, друзья, теперь у вас есть целых три кредитных калькулятора по расчёту аннуитетных платежей, разработанных в программе Microsoft Excel. В следующей публикации мы расскажем о досрочном погашении аннуитетного кредита.