Таблица расчета процентов по кредиту в Эксель

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

Способы расчета суммы кредита с помощью Excel-таблицы

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

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

Аннутитетные платежи

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

Размер регулярного взноса принято рассчитывать по формуле:

Для расчета коэффициента можно применять такую зависимость:

j — ежемесячная ставка процентов, которая высчитывается при делении годовой на 12 (кол-во месяцев в году);

В таблице расчетов процентов по кредиту для эксель можно применять стандартную формулу аннуитета. Для этого используется аббревиатура ПТЛ:

• вносим входные параметры для расчета месячных взносов по кредиту;
• формируем график погашения с колонками «Номер месяца» и «Платеж»;
• для первой ячейки «Платеж» прописываем формулу =ПЛТ($B$3/12; $B$4; $B$2);
• можно заменить ссылки константными данными, тогда пример будет выглядеть таким образом =ПЛТ(12%/12; 24; 1000000).

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

Дифференцированная схема

Так как формулы расчета аннуитетного и дифференцированного платежа по кредиту в Excel отличаются, то приведем ее для второго типа:

МС — ежемесячная ставка процентов, которая вычисляется делением годовой на 12 месяцев.

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

Формула для расчета процентной части

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

В итоге по расчетам нужно будет вернуть банкирам от полумиллиона 684881,67 руб. Соответственно разница – 184 881, 67 руб. Тогда эффективная процентная ставка окажется на уровне 37%. Если же вести расчет без учета однопроцентной комиссии, то выйдем на эффективную процентную ставку в 13%.

Схема полного расчета кредита в Excel

Главная формула определения потребительского кредита регламентирована законом. В ней процентная ставка определяется до тысячных долей. Полную стоимость кредита (ПСК) рассчитываем по алгоритму:

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

Как рассчитать сумму переплаты при досрочном погашении займа

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

Обратите внимание! При уменьшении выплаты приходится перерасчитывать месячные взносы со следующего периода после досрочного погашения.

Выводы

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

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

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

• вносим входные параметры для расчета месячных взносов по кредиту;
• формируем график погашения с колонками «Номер месяца» и «Платеж»;
• для первой ячейки «Платеж» прописываем формулу =ПЛТ($B$3/12; $B$4; $B$2);
• можно заменить ссылки константными данными, тогда пример будет выглядеть таким образом =ПЛТ(12%/12; 24; 1000000).

В итоге по расчетам нужно будет вернуть банкирам от полумиллиона 684881,67 руб. Соответственно разница – 184 881, 67 руб. Тогда эффективная процентная ставка окажется на уровне 37%. Если же вести расчет без учета однопроцентной комиссии, то выйдем на эффективную процентную ставку в 13%.

Как рассчитать проценты по займу в экселе

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

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

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

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

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

Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.

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

Ежемесячная сумма аннуитетного платежа рассчитывается по формуле:

В программе Excel существует специальная функция, которая считает аннуитетные платежи. Это ПЛТ:

1. Заполним входные данные для расчета ежемесячных платежей по кредиту. Это сумма займа, проценты и срок.
2. Составим график погашения кредита. Пока пустой.
3. В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в Excel: =ПЛТ($B$3/12; $B$4; $B$2). Чтобы закрепить ячейки, используем абсолютные ссылки. Можно вводить в формулу непосредственно числа, а не ссылки на ячейки с данными. Тогда она примет следующий вид: =ПЛТ(18%/12; 36; 100000).

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

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

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

Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B$2. Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10 – номер текущего периода, В4 – срок кредита; Е9 – остаток по кредиту в предыдущем периоде; G9 – сумма основного долга в предыдущем периоде.

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

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9

Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.

Внесем формулы в соответствующие столбцы. Скопируем их на всю таблицу.

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

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

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

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

Рассчитаем ежемесячную процентную ставку и платежи по кредиту:

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

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

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

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

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

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

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

Расчет полной стоимости кредита в Excel

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

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

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

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

У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8

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

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

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

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

Но если уж случится так, что вам или вашим близким придется влезть в это дело, то неплохо бы перед походом в банк хотя бы ориентировочно прикинуть суммы выплат по кредиту, переплату, сроки и т.д. «Помассажировать числа» заранее, как я это называю 🙂 Microsoft Excel может сильно помочь в этом вопросе.

Вариант 1. Простой кредитный калькулятор в Excel

• Ставка — процентная ставка по кредиту в пересчете на период выплаты, т.е. на месяцы. Если годовая ставка 12%, то на один месяц должно приходиться по 1% соответственно.
• Кпер — количество периодов, т.е. срок кредита в месяцах.
• Пс — начальный баланс, т.е. сумма кредита.
• Бс — конечный баланс, т.е. баланс с которым мы должны по идее прийти к концу срока. Очевидно =0, т.е. никто никому ничего не должен.
• Тип — способ учета ежемесячных выплат. Если равен 1, то выплаты учитываются на начало месяца, если равен 0, то на конец. У нас в России абсолютное большинство банков работает по второму варианту, поэтому вводим 0.

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

Вариант 2. Добавляем детализацию

Функция ОСПЛТ (PPMT) в ячейке B17 вводится по аналогии с ПЛТ в предыдущем примере:

Чтобы сделать наш калькулятор более универсальным и способным автоматически подстраиваться под любой срок кредита, имеет смысл немного подправить формулы. В ячейке А18 лучше использовать формулу вида:

Т.е. если номер периода не пустой, то мы вычисляем сумму выплат с помощью наших формул с ПРПЛТ и ОСПЛТ. Если же номера нет, то выводим пустую текстовую строку:

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

В случае уменьшения срока придется дополнительно с помощью функции ЕСЛИ (IF) проверять — не достигли мы нулевого баланса раньше срока:

А в случае уменьшения выплаты — заново пересчитывать ежемесячный взнос начиная со следующего после досрочной выплаты периода:

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

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

• в зеленые ячейки пользователь вводит произвольные даты платежей и их суммы
• отрицательные суммы — наши выплаты банку, положительные — берем дополнительный кредит к уже имеющемуся
• подсчитать точное количество дней между двумя датами (и процентов, которые на них приходятся) лучше с помощью функции ДОЛЯГОДА (YEARFRAC)

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

Как рассчитывается кредит

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

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

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

Модель для расчета аннуитетного платежа

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

Как рассчитать процент по кредиту за год по аннуитетной схеме

Для начала вычислим сумму ежемесячного платежа можно по формуле, приведенной ниже. А после на основе полученных данных вычислим годовой процент:

Ежемесячный платеж = Сумма кредита × Ставка/ 1- (1 + Ставка)^ — Срок кредита

Обратите внимание, что в этой формуле используется ставка в месяц. Ее нужно вычислить отдельно, разделив годовой процент сначала на 100, а затем на 12. Срок кредита в формуле нужно показать в виде количества месяцев (например, три года – 36 месяцев). Знак «^» здесь обозначает возведение в степень.

Пример 1.

Разберем на конкретном примере, как работает формула. Узнаем размер ежемесячного платежа, если известны следующие исходные данные:

Тогда размер процентной ставки за месяц составит 0,01 (12/100/12). А платеж на каждый месяц вычислим по формуле:

Значит, общая сумма к выплате за все два года составит 38 095 х 24 = 914 280.

Из этой суммы 114 280 руб. уйдет на выплату процентов. Значит, за один год компания отдаст на уплату процентов 57 140 руб. Теперь очевидно, как рассчитать проценты по кредиту за месяц. 57 140 руб. разделим на 12, получается примерно 4 762 руб. Читайте также: Каким должен быть идеальный график платежей>>

Как планировать платежи по кредиту с помощью Excel

Скачайте кредитный калькулятор в Excel. Введите сумму и стоимость кредита, выберите аннуитетный, дифференцированный или индивидуальный график его погашения и спланируйте выгодный график погашения кредита.

Как рассчитать годовой процент по кредиту по дифференцированной схеме

Один из способов расчета дифференцированного платежа выглядит так:

Пример 2.

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

1 месяц: (1 000 000 / 36) + (1 000 000 х 0,17/12) = 27 778 + 14 167 = 41 945

2 месяц: (1 000 000 / 36) + (41 945 х 0,17/12) = 27 778 + 594 = 28 372

3 месяц: (1 000 000 / 36) + (28 372 х 0,17/12) = 27 778 + 402 = 28 179

Как сохранить проценты по кредиту в налоговых расходах, если есть отсрочки и авансы

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

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

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

Эту систему можно встретить редко, но о ее существовании все же стоит знать. Если кредитное учреждение хочет применить эту схему, с ним можно спорить. Ведь закон позволяет начислять только на основную сумму долга (ст. 317.1, 809 и 819 ГК). Схема «проценты на проценты» совсем не выгодна клиентам банка. Читайте также: Расчет процентов от суммы в Excel>>

Посмотрим, как рассчитать по формуле сложные проценты по кредиту. Условно ее можно выразить так:

Долг = Первоначальная сумма × (1 + Ставка за расчетный период/100%)^Количество расчетных периодов

Пример 3.

Рассчитаем сложные проценты по кредиту, если известно следующее:

Во-первых, определим ежемесячную процентную ставку 20/100/12. Она составляет 0,0167 процентов.

Далее применим формулу для вычисления сложных процентов:

Что выбрать: срочный кредит или кредитную линию

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

Какая схема лучше?

Нельзя однозначно ответить на вопрос, как лучше рассчитать проценты по кредиту: по дифференцированной или аннуитетной системе. Этот вопрос стоит решать самостоятельно. Для кого-то аннуитет окажется выгоднее, а кому-то, напротив, дифференцированная система поможет сберечь средства. Как найти баланс: свои деньги или заемные

Срок кредита — основной критерий, на который стоит ориентироваться при выборе схемы для выплаты кредита. Если компания или предприниматель берут заем на короткий срок, то больше подойдет аннуитет. А дифференцированные платежи стоит применять для долгосрочных кредитов. Заем от нерезидента: как выгоднее учесть

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

Как рассчитать лизинговые платежи с помощью Exсel

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

Резюме

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

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

Гость, уже успели прочесть в свежем номере?

Финансовый директор. Live – бизнес-встречи финансистов. Каждую неделю финансовые директоры делятся опытом онлайн. Участие бесплатное.

Любовь Франкевич, Руководитель проектов в компании «НАО Евроэксперт»

Ирина Нарчемашвили, Финансовый директор и соучредитель компании ATM Group

Как платить сотрудникам, чтобы зарплата мотивировала работать, а не приходить на работу

Фирме потребуется 5000 руб. через 12 лет. В настоящее время фирма располагает деньгами и готова положить их на депозит единым вкладом, чтобы через 12 лет он достиг 5000 руб. Определим необходимую сумму текущего вклада, если ставка процента по нему составляет 12% годовых.

Эффективная ставка по кредиту: понятие, что на нее влияет и примеры расчета

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

Надеемся, теперь всем понятно, откуда в графе «Погашение тела кредита» нашего графика аннуитетных платежей в выплатах за первый месяц взялась сумма 3763 руб. Да-да, это именно то, что осталось после того, как мы из суммы аннуитетного платежа (4680 руб.) вычли сумму процентов по кредиту (917 руб.). Аналогичным образом рассчитаны значения этой графы за последующие месяцы.