Аннуитет. расчет периодического платежа в excel. погашение ссуды (кредита, займа). примеры и описание

Как рассчитать аннуитетный платеж в 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

history 3 февраля 2015 г.

Расчет кредитов и вкладов

Составим в MS EXCEL график погашения кредита дифференцированными платежами.

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

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

График погашения кредита дифференцированными платежами

Задача . Сумма кредита =150т.р. Срок кредита =2 года, Ставка по кредиту = 12%. Погашение кредита ежемесячное, в конце каждого периода (месяца).

Решение. Сначала вычислим часть (долю) основной суммы кредита, которую заемщик выплачивает за период: =150т.р./2/12, т.е. 6250р. (сумму кредита мы разделили на общее количество периодов выплат =2года*12 (мес. в году)). Каждый период заемщик выплачивает банку эту часть основного долга плюс начисленные на его остаток проценты. Расчет начисленных процентов на остаток долга приведен в таблице ниже – это и есть график платежей.

Для расчета начисленных процентов может быть использована функция ПРОЦПЛАТ(ставка;период;кпер;пс), где Ставка — процентная ставка за период ; Период – номер периода, для которого требуется найти величину начисленных процентов; Кпер — общее число периодов начислений; ПС – приведенная стоимость на текущий момент (для кредита ПС — это сумма кредита, для вклада ПС – начальная сумма вклада).

Примечание . Не смотря на то, что названия аргументов совпадают с названиями аргументов функций аннуитета – ПРОЦПЛАТ() не входит в группу этих функций (не может быть использована для расчета параметров аннуитета).

Примечание . Английский вариант функции — ISPMT(rate, per, nper, pv)

Функция ПРОЦПЛАТ() предполагает начисление процентов в начале каждого периода (хотя в справке MS EXCEL это не сказано). Но, функцию можно использовать для расчета процентов, начисляемых и в конце периода для это нужно записать ее в виде ПРОЦПЛАТ(ставка;период-1;кпер;пс), т.е. «сдвинуть» вычисления на 1 период раньше (см. файл примера ). Функция ПРОЦПЛАТ() начисленные проценты за пользование кредитом указывает с противоположным знаком, чтобы отличить денежные потоки (если выдача кредита – положительный денежный поток («в карман» заемщика), то регулярные выплаты – отрицательный поток «из кармана»).

Расчет суммарных процентов, уплаченных с даты выдачи кредита

Выведем формулу для нахождения суммы процентов, начисленных за определенное количество периодов с даты начала действия кредитного договора. Запишем суммы процентов начисленных в первых периодов (начисление и выплата в конце периода): ПС*ставка (ПС-ПС/кпер)*ставка (ПС-2*ПС/кпер)*ставка (ПС-3*ПС/кпер)*ставка … Просуммируем полученные выражения и, используя формулу суммы арифметической прогрессии, получим результат. =ПС*Ставка* период*(1 — (период-1)/2/кпер) Где, Ставка – это процентная ставка за период (=годовая ставка / число выплат в году), период – период, до которого требуется найти сумму процентов. Например, сумма процентов, выплаченных за первые полгода пользования кредитом (см. условия задачи выше) = 150000*(12%/12)*6*(1-(6-1)/2/(2*12))=8062,50р. За весь срок будет выплачено =ПС*Ставка*(кпер+1)/2=18750р. Через функцию ПРОЦПЛАТ() формула будет сложнее: =СУММПРОИЗВ(ПРОЦПЛАТ(ставка;СТРОКА(ДВССЫЛ(«1:»&кпер))-1;кпер;-ПС))

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

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

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

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

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

Эти формулы применяются, когда они являются квотами, оплата которых происходит по истечении каждого периода. Таким образом, квоты называются «подлежащими погашению» или «подлежащими уплате до истечения срока». Например, кредит получен и должен быть погашен ежемесячными платежами.

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

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

Сумма основного долга — это и есть те деньги, которые вы , а теперь постепенно возвращаете. Если вы заключили договор на 100 тыс. р., то это и есть ваш основной долг. Каждый месяц вы выплачиваете его часть, соответственно, он уменьшается.
Проценты за пользование средствами — это ваше вознаграждение банку за то, что вы пользуетесь его деньгами. Размер этого платежа соответствует процентной ставке. Если вы взяли 100 тыс. р. под 12% годовых, то каждый месяц вам нужно будет уплатить 1 тыс. р. процентов.

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

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

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

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

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

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

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

А = К х С, где:

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

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

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

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

Правила использования функции ОСПЛТ в 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 соответственно.

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

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

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

Для всех подсчетов потребуются следующие данные:

— размер кредита;
— цель кредита;
— размер процентов по кредиту.

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

— функция КПЕР – сумма кредита;
— функция СТАВКА – ставка процентов по кредиту;
— функция ПС – цель кредита;
— функция ПЛТ – ежемесячный платеж по кредиту.

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

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

При желании, можно погасить кредит досрочно, при этом никаких комиссий или штрафов банк не должен требовать.

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

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

Мой кредит это 500 тысяч и страховка ещё 70 тысяч, банки очень любят не круглые проценты поэтому у меня 11,99% годовых. Всё это взято на 5 лет (платить естественно помесячно и накидываются проценты в конце периода — так у всех практически).

Можно зайти в википедию и посмотреть формулу расчёта аннуитета и набрать её в Экселе то получится такой расчёт:

Но в Экселе есть стандартная функция =ПЛТ(11,99/12;60;570000) . Аргументы функции: Процент в период; Количество периодов; Сумма кредита. Для моего случая выйдет так и оно совпадает с точностью округления до копеек:

Если посмотреть в график из банка, то он будет отличаться на пару сотен. Как так? Идея в том, что периоды в году неравномерные (февраль короче, майские праздники прицепить могут к апрелю и тд), и на самом деле проценты рассчитываются для дней и потом объединяются в месяцы. Но сумма за год будет отличаться тоже максимум на пару сотен (проценты за новогодние праздники), поэтому это можно проигнорировать.

В случае досрочного погашения (тут столбец «ДП») можно выбрать варианты сократить срок кредита или уменьшить сумму платежа, в этом примере я вставил значением платёж, чтобы потом нагляднее было видно изменение параметров. Процент первой строки ввёл по концу периода (10 число месяца), но он написан в первом графике, так что его 1ый раз печатаем руками.

Можно посмотреть стоит ли напрягаться на досрочное погашение. В моём примере следует логичный вывод, что только в 1ый год досрочное погашение даёт выгоду по сравнению с 10% инфляции, которая была у нас в стране (про этот год и дальше не решаюсь даже предполагать).

Согласно ФЗ-106 от 03.04.2020 в случае отсрочки на полгода проценты начисляются, а срок не сдвигается.

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

Аннуитетные платежи — расчёт, формула

Аннуитетный платеж — вариант ежемесячного платежа по кредиту, когда размер ежемесячного платежа остаётся постоянным на всём периоде кредитования.

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

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

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

Расчёт аннуитетного платежа

Рассчитать месячный аннуитетный платеж можно по следующей формуле:

, где

x – месячный платёж, S – первоначальная сумма кредита, P – (1/12) процентной ставки, N – количество месяцев.

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

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

, где – начисленные проценты, — остаток задолженности на период, P – годовая процентная ставка по кредиту.

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

s = x — , гдеs – часть выплаты, идущая на погашение долга, x – месячный платёж, — начисленные проценты, на момент n-ой выплаты.

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

Пример расчёта графика выплат по аннуитетному кредиту

Для примера рассчитаем график платежей по кредиту в размере 100000 р. и годовой процентной ставкой 10%. Сроком погашения кредита возьмём 6 месяцев.

Для начала рассчитаем ежемесячный платёж.

Затем рассчитаем по месяцам процентную и кредитную часть аннуитетного платежа.

1 месяц Проценты: 100000 * 0,1 / 12 = 833,33Основной долг: 17156,14 – 833, 33 = 16322,812 месяцОстаток кредита: 100000 – 16322,81 = 83677,19Проценты: 83677,19 * 0,1/12 = 697,31Основной долг: 17156,14 – 697,31 = 16458,833 месяцОстаток кредита: 83677,19 — 16458,83 = 67218,36Проценты: 67218,36 *0,1/12 = 560,15Основной долг: 17156,14 – 560,15 = 16595,994 месяцОстаток кредита: 67218,36 — 16595,99 = 50622,38Проценты: 50622.38 * 0,1/12 = 421.85Основной долг: 17156,14 – 421,85 = 16734,295 месяцОстаток кредита: 50622,38 — 16734,29 = 33888,09Проценты: 33888,09 * 0,1/12 = 282,40Основной долг: 17156,14 – 282,40 = 16873,746 месяцОстаток кредита: 33888.09 — 16873.74 = 17014,35Проценты: 17014,35 * 0,1/12 = 141,79Основной долг: 17156,14 – 141,79 = 17014,35

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

17156,14 * 6 – 100000 = 2936,84

Результат подсчётов по нашему примеру на сайте www.platesh.ru будет выглядеть так:

Форма ввода данных для расчёта аннуитетного платежаПример графика аннуитетных платежей

Что подтверждает правильность наших расчётов.

Разрабатываем калькулятор дифференцированных платежей в Excel

Прежде всего давайте разберемся, по какому принципу работает наш калькулятор. Откройте скачанный «экселевкий» файл. В верхнем левом углу страницы вы увидите две таблицы. Они называются: «Укажите данные для расчёта» и «Результаты расчёта». Также сверху над всеми столбцами нашей страницы Excel есть буквы A, B, C, D, E, F и т.д., а слева напротив строк – цифры 1, 2, 3, 4, 5, 6 и т. д. Именно эти буквы и цифры определяют координаты каждой ячейки таблицы.

Кликните левой кнопкой мыши по ячейке со значением «5958р.»
, которое находится в результатах расчёта в строке «Переплата по кредиту». В нашем калькуляторе эта ячейка имеет координаты B8
. Вот вам картинка для наглядности:

На изображении данную ячейку мы обвели красной линией и обозначили цифрой один

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

Например, на нашем изображении буква B
сверху и цифра 8
слева изменили цвет фона с серо-голубого на желтоватый. Также в верхней строке формул, слева от которой есть кнопка «fx»
(на рисунке она обведена красным и обозначена цифрой два) указано значение или формула, по которой выполняется расчёт данных для выделенной ячейки. В нашем примере для ячейки с координатой B8
выполняется расчёт по следующей формуле: =B7-B2
. В окне с координатой B7
указана общая сумма выплат по кредиту, которая в нашем примере равна 55 958 рублей
, а B2
– это сам кредит, который равен 50 000 рублей
. Выполнив простое математическое вычисление, наша программа занесла в ячейку B8
значение 5958
(55 958 – 50 000=5958).

Как видите, Microsoft Excel работает достаточно просто. По аналогичному принципу заданы формулы и значения для остальных ячеек нашего кредитного калькулятора дифференцированных платежей. Давайте рассмотрим, как они рассчитаны. Щёлкаем мышкой по изображению:

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

«Ежемесячный платёж»
– это ежемесячный дифференцированный платёж по займу. Он состоит из двух частей: суммы, идущей на погашение процентов (ячейка F14
), и суммы, идущей на погашение тела кредита (ячейка G14
). Именно потому ежемесячный платёж в первой строке рассчитан по формуле: =F14+ G14
.
«Погашение процентов»
– здесь работает формула расчёта процентов по кредиту за данный период: остаток задолженности (в первом платеже он равен сумме кредита 50 000 руб.
, вынесенную в ячейку H13
) умножить на (она равна 22%
и вынесена в ячейку A14
) и разделить на 12
(мы вынесли это значение в ячейку B14
). Собственно, эти условия и прописаны в формуле для ячейки F14
: =H13*A14/B14
. Кстати, вместо B14
можно просто указать фиксированную цифру – 12
.
«Погашение тела кредита»
– это фиксированное значение, которое не меняется на протяжении всего срока кредитования. Рассчитывается этот показатель очень просто: сумма кредита (ячейка B2
) делится на общий срок кредитования (ячейка B4
). В итоге для ячейки G14
получаем такую формулу: = B2/B4
.
«Долг на конец месяца»
– из суммы долга на конец предыдущего месяца (в первом платеже он у нас равен сумме кредита – 50 000 рублей
и вынесен в ячейку H13
) вычитаем выплату по телу кредита в текущем периоде (4167 рублей
– ячейка G14
). В результате, долг на конец месяца по первому платежу у нас равен 45 833 рубля
(50 000 – 4167 = 45 833), что и записано в формуле для ячейки H14
: = H13- G14
.

Вот таким нехитрым способом разработан кредитный калькулятор дифференцированных платежей в Excel. Он рассчитан на кредиты сроком до 12 месяцев. При желании, вы можете его усовершенствовать и расширить данный диапазон до 24, 36 и более месяцев. В общем, теперь всё в ваших руках, друзья. Как говорится, мы вам дали удочку, а вы сами решайте, что с ней дальше делать.

Портал сайт – ваш надёжный информационный помощник в вопросах кредитования. Оставайтесь с нами!

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

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

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

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

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

Заполним таблицу вида:

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

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

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

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

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

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

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

Ипотека

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

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

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

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

Поэтому список банков, предоставляющих выбор, крайне короток. В него входят: «Газпромбанк», «Нордеа Банк» и Петрокоммерц». Раньше такую возможность предоставлял еще и Сбербанк, теперь уже нет.

Ипотечный онлайн-калькулятор

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

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

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

Расчет оплаты

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

Этап 1: расчет ежемесячного взноса

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

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

Первоначальные действия

В списке функций выбрать «ПЛТ» и нажать «ОК».

Выбор функции в специальном окне

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

Алгоритм действий по заполнению аргументов функции «ПЛТ»

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

Финальный результат вычислений

Этап 2: детализация платежей

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

Составить исходную таблицу на 24 месяца.

Изначальный табличный массив

Поставить курсор в первую ячейку таблицы и вставить функцию «ОСПЛТ».

Выбор функции детализации платежей

Заполнить аргументы функции аналогичным образом.

Заполнение всех строк в окне аргументов оператора э

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

Заполнение аргумента «Период»

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

Заполнение оставшихся строчек

Выбрать функцию «ПРПЛТ» для заполнения второго столбца таблицы.
Заполнить все аргументы в открывшемся окошке в соответствии со скриншотом ниже.

Заполнение аргументов для оператора «ПРПЛТ»

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

Расчет ежемесячных взносов

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

Расчет остатка к выплате