Как рассчитать кредит в эксель

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

Формула определения суммы процентов по взятому кредиту

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

1. Ставка в процентах за период (годовые, поделенные на 12).
2. Период – от первого до какого-либо нужного периода.
3. Общее количество периодов платежей по кредиту.
4. Приведенная стоимость, которая равна совокупности будущих платежей на данный момент.
5. Требуемое значение будущей стоимости, то есть остатка после последних выплат (если же этот аргумент вообще не указывать, то будет предполагаться, что он приравнен к нулю).
6. Тип – срок выплаты (0 – конец периода, 1 – начало периода).

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

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

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

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

• произвести такие расчеты достаточно сложно – при таком методе сумма платежа каждый месяц меняется;
• способ крайне редко применяется банковской системой, что делает рассмотрение здесь математических формулировок не целесообразным.

Рассмотрим более детально, какие формулы следует применять при стандартном, аннуитетном варианте погашения долга.

Какие формулы использовать

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

А = К х С, где:

• А – искомая величина взноса;
• К – аннуитетный коэффициент;
• С – сумма по договору (тело кредита).

В свою очередь, коэффициент получаем по формуле:

К = (i * (1 + i)^n) / ((1+i)^n-1), где:

• i – ставка в месяц. Она привязана к размеру годовой переплаты по процентам и составляет двенадцатую ее часть;
• n – период актуальности кредитного договора. Данный промежуток времени следует исчислять только в календарных месяцах.

Кредитный калькулятор в Excel

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

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

Обратите внимание! Программа достаточно популярна в бухгалтерском учете.

Для того чтобы получить данные, потребуется минимум информации:

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

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

• КПЕР – размер кредита в рублях;
• СТАВКА – величина переплаты. Измеряется в процентах на определенный промежуток времени. Чаще всего – за год;
• ПС – целевое предназначение займа;
• ПЛТ – текущий взнос по кредиту.

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

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

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

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

Мы сделаем не просто файлик с одной циферкой. Нет! Мы разработаем настоящий инструмент, с помощью которого вы сможете рассчитать аннуитетный платёж не только для себя, но и для соседа, который ставит свою машину на детской площадке; прыщавого студента, который сутками курит в вашем подъезде; тётки, которая выгуливает свою собаку прямо под вашими окнами – короче, для всех особо одарённых. Кстати, можете поставить где-нибудь возле монитора купюроприёмник и брать с этой публики деньги.

Давайте приступим к разработке нашего кредитного калькулятора. Смотрим на первый рисунок:

Итак, вы видите два блока. Один с исходными данными, а второй – с расчётами. Исходные данные (сумма кредита, годовая процентная ставка, срок кредитования) вы будете вводить вручную, а во втором блоке будут мгновенно появляться расчёты.

Начнём с расчёта ежемесячной суммы аннуитетного платежа. Для этого надо сделать активным окошко, в котором вы хотите видеть это значение (в нашем случае – это поле C11, на рисунке оно обведено и указано под номером 1). Далее слева от строки формул жмём на «fx» (на рисунке эта кнопка обведена и указана под номером 2). После этих действий у вас появится такая табличка:

Выбираем функцию «ПЛТ» и жмём «Ок». Перед вами появится таблица, в которую надо будет ввести исходные данные:

Здесь нам требуется заполнить три поля:

• «Ставка» – годовая процентная ставка по кредиту делённая на 12.
• «Кпер» – общий срок кредитования.
• «Пс» – сумма кредита (указывается со знаком минус).

Обратите внимание на то, что мы не вводим готовые цифры в эту таблицу, а указываем координаты ячеек нашего блока с исходными данными. Так, в поле «Ставка» мы указываем координаты ячейки, в которой будет вписываться вручную процентная ставка (C5) и делим её на 12; в поле «Кпер» указываются координаты ячейки, в которой будет вписываться срок кредитования (C6); в поле «Пс» – координаты ячейки в которой вписывается сумма кредита (C4). Так как сумма кредита у нас указывается со знаком минус, то перед координатой (C4) мы ставим знак минус.

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

Итак, в данный момент сумма нашего аннуитетного платежа составляет 4680 руб (на рисунке он обведён и указан под номером 1). Если вы будете менять сумму кредита, процентную ставку и общий срок кредитования, то автоматически будет меняться значение вашего аннуитетного платежа.

Кстати, обратите внимание на значение функции, обозначенное на рисунке под номером 2: =ПЛТ(C5/12;C6;-C4). Да, да, это и есть те самые координаты, которые мы вводили в таблицу, выбрав функцию «ПЛТ». По сути, вы могли бы не проделывать всех тех сложных телодвижений, которые показаны на втором и третьем рисунках. Можно было просто вписать в строке формул то, что там сейчас вписано.

Зная размер аннуитетного платежа несложно посчитать остальные значения нашего расчётного блока:

На рисунке наглядно показано, как рассчитана общая сумма выплат (обведена и указана под номером 1). Так как она равна сумме аннуитетного платежа (ячейка C11) умноженной на общее количество месяцев кредитования (ячейка C6), то мы и вписываем в строку формул следующую формулу: =C11*C6 (на рисунке она обведена и указана под номером 2). В результате мы получили значение 56 157 рублей.

Ну и последнее значение – эффективная процентная ставка (или полная стоимость кредита). Она рассчитывается так: общую сумму выплат (ячейка C12) делим на сумму кредита (ячейка C4), отнимаем единицу, затем делим всё это на срок кредитования в годах (ячейка C6 делённая на 12). В строке будет такая формула: =(C12/C4-1)/(C6/12). В нашем примере эффективная процентная ставка составляет 12,3%.

Всё! Вот таким нехитрым способом мы с вами составили в программе Microsoft Excel автоматический калькулятор расчета аннуитетных платежей по кредиту, скачать который можно ссылке ниже:

Скачать калькулятор расчёта аннуитетного платежа по кредиту в Excel

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

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

В основу снова ляжет пример. Клиент взял в банке 180 тысяч рублей на 3 года. Ставка – 13% годовых. Погашение предполагается каждый месяц, в конце периода.

Для расчетов необходимо узнать ежемесячную базовую сумму, подлежащую выплате. Каждый месяц клиент обязан возвращать банку равную сумму – часть долга. В рассматриваемом случае это 180000 / 3 / 12 = 5000 рублей. Каждый месяц на остаток начисляются прописанные в договоре проценты. Соответственно, уменьшается остаток – меньше становится и сумма, начисляемая банком.

Расчет основывается на функции ПРОЦПЛАТ. Через точку с запятой в ней обозначаются четыре показателя:

Расчет процентов по кредиту

• ставка за период (13%/12)
• номер периода, за который будет считаться величина
• число периодов начисления суммы долга к уплате
• приведенная стоимость (сумма кредита)

Функция ПРОЦПЛАТ совпадает по аргументам с предыдущей формулой, однако не имеет с ней ничего схожего, подменять их друг другом нельзя. В англоязычной версии наименование функции – ISPMT, аргументы в ней такие же.

В ПРОЦПЛАТ предполагается начисление суммы процентов в начале периода. Сдвинуть эту функцию на конец месяца можно, если сместить вычисления на период раньше (не «период», а «период-1»). Итоги будут отображены с противоположным знаком, то есть минусом. Таким образом отличаются расчеты при начислении процентов по кредиту и вкладу.

Расчет в Excel суммы кредита для заданного аннуитетного платежа

В чём «фишка» аннуитетной схемы погашения кредита? Правильно! Основная «фишка» в том, что заёмщик выплачивает кредит равными суммами на протяжении всего срока кредитования. С такой схемой очень удобно планировать свой бюджет. Например, вы готовы ежемесячно выделять на погашение кредита 5000 рублей. По вашим скромным подсчётам, такая нагрузка будет для вас не слишком обременительной. Естественно, у вас возникает закономерный вопрос: «А на какую сумму кредита я могу рассчитывать?» В общем, нам нужен новый кредитный калькулятор, у которого в исходных данных будет не сумма кредита, а величина аннуитетного платежа.

Что же, друзья, не будем терять время! Открываем программу Microsoft Excel и приступаем к разработке нашего кредитного калькулятора!

Итак, структура нового кредитного калькулятора почти не изменилась. Здесь также есть блок с исходными данными и блок с расчётами. Единственное изменение, это то, что в исходных данных мы вводим ежемесячный аннуитетный платёж, который готовы выплачивать, а в расчётах получаем сумму кредита, на которую мы можем рассчитывать. Собственно, она на нашем рисунке обведена и отмечена под номером 1.

Чтобы рассчитать сумму ожидаемого кредита надо воспользоваться функцией ПС, предварительно кликнув по ячейке, в которой мы хотим видеть свой расчёт (в нашем калькуляторе это ячейка с координатой C11). Вызвать функцию ПС можно нажав на знакомую вам кнопку «fx», которая находится слева от строки формул. В появившемся окне выбираем «ПС» и жмём «Ок». В открывшейся таблице вводим следующие данные:

• «Ставка» – годовая процентная ставка по кредиту делённая на 12 (в нашем случае: C5/12).
• «Кпер» – общий срок кредитования (в нашем калькуляторе, это ячейка с координатой C6).
• «Плт» – ежемесячный аннуитетный платёж, перед которым ставим знак минус (в нашем калькуляторе, это ячейка C4, перед данной координатой мы и ставим знак минус).

Жмём «Ок» и в ячейке С11 появилась сумма 53 422 руб. – именно на такой размер кредита может рассчитывать заёмщик, который готов на протяжении 12 месяцев ежемесячно выплачивать по 5000 руб.

Кстати, обратите внимание на данные в строке формул (на рисунке они обведены и указаны под номером 2). Вы всё правильно поняли, друзья! Да, это те данные, которые необходимы для расчёта суммы кредита в нашем калькуляторе: =ПС(C5/12;C6;-C4). Те самые параметры, которые мы вводили в таблице функции ПС.

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

• Общая сумма выплат – это ежемесячный аннуитетный платёж (ячейка С4) умноженный на общий срок кредитования (ячейка С6). В строку формул вводим следующие данные: =C4*C6.
• Переплата (проценты) по кредиту – это общая сумма выплат (ячейка С12) минус сумма кредита (ячейка С11). В строку формул записываем: =C12-C11.
• Эффективная процентная ставка (или полная стоимость кредита) – это общая сумма выплат (ячейка С12) делённая на сумму кредита (ячейка С11) и минус единица. Затем всё это делим на срок кредитования, выраженный в годах (ячейка C6 делённая на 12). В строку формул записываем: = (C12/C11-1)/(C6/12).

Кстати, интересный момент. Вот в нашем примере, выплачивая ежемесячно в течение года по 5000 рублей, мы можем рассчитывать на сумму кредита равную 53 422 рубля. А что делать, если надо больше денег? Как вариант, можно увеличить срок кредитования. Если вместо 12 месяцев поставить 24, то сумма кредита увеличится до 96 380 рублей. Эти данные нам мгновенно выдал наш кредитный калькулятор, который вы можете скачать ссылке ниже:

Скачать калькулятор расчёта суммы аннуитетного кредита в Excel

Расчет суммы переплаты при досрочном погашении

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

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

Достоинства данного калькулятора:

1. Кредитный калькулятор в Excel практически точно считает аннуитетный график платежей и дифференцированный график платежей
2. Изменения в графике платежей — учет досрочных погашений в уменьшение суммы основного долга
3. Построение и расчет графика платежей в виде таблицы в Excel. Таблица графика платежей может также редактироваться
4. При расчете учитывается високосный и невисокосный год. За счет этого сумма начисленных процентов практически совпадает с значениями,  рассчитываемыми ВТБ24 и Сбербанком
5. Точность расчетов — рассчеты совпадают с расчетами кредитного калькулятора ВТБ24 и Сбербанка
6. Калькулятор можно редактировать под себя, задавая разные варианты расчета.

Скачать кредитный калькулятор в Excel

Первый кредитный калькулятор в Excel можно скачать по ссылке.
Но Excel есть не на всех компьютерах. Пользователи MAC и Linux не пользуются Excel обычно, т.к. это продукт Microsoft.
Для расчета досрочного погашения можно также воспользоваться онлайн версией калькулятора с досрочным погашением. В нем предусмотрена возможность экспорта результатов расчета в Excel.

На основе этого калькулятора был разработан ипотечный калькулятор для Android и iPhone. Найти и скачать мобильные версии калькуляторов можно с главной страницы сайта.

Особенности кредитных платежей по аннуитетной схеме

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

Аннуитет имеет следующую специфику:

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

Аннуитетные платежи по кредитному договору: как рассчитать в Excel

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

Например, был выдан кредит на сумму 100 тысяч рублей под 15% годовых на два года. Соответственно, в ячейке необходимо отразить выражение:

=ПЛТ(15%/12;24;-100000)

В скобках после наименования данные вводятся в определенном порядке:

Плата процентов по кредиту

• процентная ставка
• продолжительность обязательства по уплате в месяцах
• полученная в кредит сумма

Знак минуса перед суммой означает, что данное число представляет собой обязательство. Если это единичный расчет, ставить его необязательно. Но если число в дальнейшем используется в других формулах, он важен. Процентная ставка может быть отражена десятичной дробью (15% годовых = 0,0125).

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