Формула расчета аннуитетного платежа в Excel

Содержание

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

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

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

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

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

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

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

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

А = К * S

где:

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

Формула коэффициента аннуитета:

К = (i * (1 + i)^n) / ((1+i)^n-1)

  • где i – процентная ставка за месяц, результат деления годовой ставки на 12;
  • n – срок кредита в месяцах.

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

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

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



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

Дифференцированный способ оплаты предполагает, что:

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

Формула расчета дифференцированного платежа:

ДП = ОСЗ / (ПП + ОСЗ * ПС)

где:

  • ДП – ежемесячный платеж по кредиту;
  • ОСЗ – остаток займа;
  • ПП – число оставшихся до конца срока погашения периодов;
  • ПС – процентная ставка за месяц (годовую ставку делим на 12).

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

Входные данные те же:

Составим график погашения займа:

Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$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

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

  • ПСК = i * ЧБП * 100;
  • где i – процентная ставка базового периода;
  • ЧБП – число базовых периодов в календарном году.

Возьмем для примера следующие данные по кредиту:

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

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

Далее находим ЧБП: 365 / 28 = 13.

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

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

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

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

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

Расчет аннуитетных платежей: формула, использование Excel

Финансирование бизнеса » Кредиты малому бизнесу »

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

Наиболее распространенный вариант – внесение аннуитетных платежей, то есть выплата кредита равными суммами.

Как рассчитать размер аннуитетного платежа?

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

А = К х S

В этой формуле:

A – размер платежа

K – коэффициент аннуитета

S – сумма полученного кредита

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

Здесь i – это месячная ставка процентов за пользование кредитом, которая рассчитывается путем деления годовой ставки на 12 месяцев

n – количество месяцев, на протяжении которого кредит необходимо погасить.

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

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

Чтобы не утруждать себя расчетами вручную, попробуйте сделать это при помощи таблицы Excel. Там есть специальная функция под названием ПЛТ. Для расчетов следует создать новую таблицу и ввести строку в любой ячейке. Если вам выдали кредит в сумме 30000 руб., под 18% годовых на 36 месяцев, необходимо ввести в ячейку вот такое выражение.

= ПЛТ(18%/12; 36; -30000)

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

Можно внести запись и в таком виде:

=ПЛТ(0,015; 36; -30000)

Получается 1084,57 рублей.

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

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

Справка: аннуитетные и дифференцированные платежи

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

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

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

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

В наш век высоких технологий и автоматизации как-то неприлично вручную выполнять сложные расчёты. Хоть аннуитетные платежи рассчитать не так и трудно, но как говорит Юрий Ашер:

«Не надо напрягать свой мозг там, где это могут сделать за вас другие!»

В нашей ситуации к вам на помощь придут: компьютер и программа Microsoft Excel.

Хотим предупредить, что команда портала temabiz.com поставила перед собой цель не просто дать вам «халяву» в виде «экселевского» файла с готовыми расчетами. Нет, в этой публикации мы вас научим самостоятельно рассчитывать аннуитетные платежи, а также составлять в программе 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). В строку вписываем такую формулу: =C12-C4. В нашем примере переплата равна: 6157 рублей.

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

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

Расчет в 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 по расчету графика аннуитетных платежей

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

Перед вами расширенная и доработанная версия нашего первого кредитного калькулятора (того, который рассчитывает размер ежемесячного аннуитетного платежа по кредиту). Здесь кроме стандартных блоков с исходными данными и расчётами, появилась таблица, в которой детально расписаны все наши будущие ежемесячные выплаты. Таблица имеет пять колонок:

  1. 1. Месяцы. В этой колонке по порядку указаны номера месяцев, в которые будут осуществляться выплаты. Обратите внимание, что речь идёт не о календарных, а о порядковых номерах. То есть, если первая выплата припадает на сентябрь месяц, то ему присваивается порядковый номер «1», как первому месяцу, а не «9», как календарному.
  2. 2. Ежемесячный платёж. Это тот самый аннуитетный платёж, который не меняется на протяжении всего срока кредитования. В сноске к одной из ячеек вы можете увидеть данные, которые внесены в строку формул: =ПЛТ(B3/12;B4;-H14). Вы уже знаете, что за расчёт аннуитетного платежа в экселе отвечает функция ПЛТ. Координаты необходимых значений для расчёта можно внести, как через строку формул, так и заполнив таблицу, которая появится при нажатии на кнопку «fx», находящуюся слева от строки формул.
  3. 3. Погашение процентов. Здесь рассчитывается доля процентов в аннуитетных платежах (в каждой новой выплате она будет уменьшаться). В программе Excel за расчёт данного показателя отвечает функция ПРПЛТ. Опять же, задать необходимые параметры для расчётов можно либо нажав на кнопку «fx» и заполнив таблицу, либо просто внеся нужную информацию в строку формул. В нашем примере для расчёта доли процентов в первом платеже, в строке формул записано следующее: =ПРПЛТ(A15/12;D15;B15;-C15).
  4. 4. Погашение тела кредита. Та самая выплата, которая вытягивает нас из долговой ямы и избавляет от банковского рабства. Мы рассчитали её просто: из суммы аннуитетного платежа вычли долю процентов, которую рассчитали в предыдущей колонке. Собственно, в строке формул по первому платежу так и записано: =E15-F15. Но можно пойти и другим, более изощрённым, путём. В программе Excel за расчёт этого платежа отвечает функция ОСПЛТ. Можете для интереса нажать кнопку «fx», выбрать функцию ОСПЛТ, внести все необходимые данные и получить сумму, идущую на погашение тела кредита в выбранном платеже.
  5. 5. Долг на конец месяца. Ну, здесь всё просто! В данной колонке отображается сумма вашего долга перед банком на конец текущего месяца. Из текущего остатка мы отнимаем долю, идущую на погашение тела кредита. А вот уплаченные проценты просто уходят в казну банка и никак не влияют на сумму вашего текущего долга по кредиту.

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

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

Наши группы: ⇧

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

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

Расчет эффективной ставки по кредиту в Excel

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

Заемщик взял кредит на сумму 150 000 рублей. Срок – 1 год (12 месяцев). Номинальная годовая ставка – 18%. Выплаты по кредиту укажем в таблице:

Поскольку в примере не предусмотрено дополнительных комиссий и сборов, определим годовую эффективную ставку с помощью функции ЭФФЕКТ.

Вызываем «Мастер функций». В группе «Финансовые» находим функцию ЭФФЕКТ. Аргументы:

  1. «Номинальная ставка» — годовая ставка по кредиту, указанная в договоре с банком. В примере – 18% (0,18).
  2. «Количество периодов» — число периодов в году, за которые начисляются проценты. В примере – 12 месяцев.

Эффективная ставка по кредиту – 19,56%.

Усложним задачу, добавив единовременную комиссию при выдаче кредита в размере 1% от суммы 150 000 рублей. В денежном выражении – 1500 рублей. Заемщик на руки получит 148 500 рублей.

Чтобы рассчитать эффективную ежемесячную ставку, воспользуемся функцией ВСД (возвращает внутреннюю ставку доходности для потока денежных средств):

Мы внесли в столбец с ежемесячными платежами 148 500 со знаком «-«, т.к. эти деньги банк сначала отдает. Платежи, которые вносит заемщик в кассу впоследствии, являются для банка положительными. Внутреннюю ставку доходности считаем с точки зрения банка: он выступает в качестве инвестора.

Функция дала эффективную ежемесячную ставку 1,69%. Для расчета номинальной ставки результат умножим на 12 (срок кредитования): 1,69% * 12 = 20,28%. Пересчитаем эффективную процентную ставку:

Единовременная комиссия в размере 1% повысила фактическую годовую процентную ставку на 2,72%. Стало: 22,28%.

Добавим в схему выплат по кредиту ежемесячный сбор за обслуживание счета в размере 300 рублей. Ежемесячная эффективная ставка будет равна 2,04%.

Номинальная ставка: 2,04% * 12 = 24,48%. Эффективная годовая ставка:

Ежемесячные сборы увеличили ее до 27,42%. Но в кредитном договоре по-прежнему будет стоять цифра 18%. Правда, новый закон обязует банки указывать в кредитном договоре эффективную годовую процентную ставку. Но заемщик увидит эту цифру после одобрения и заключения договора.



Чем отличается лизинг от кредита

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

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

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

Входные данные:

Можно пойти по уже проторенному пути: рассчитать внутреннюю ставку доходности, а потом умножить результат на 12. Но мы используем функцию ЧИСТВНДОХ (возвращает внутреннюю ставку доходности для графика денежных потоков).

Аргументы функции:

Эффективная ставка по лизингу составила 23,28%.

Расчет эффективной ставки по ОВГЗ в Excel

ОВГЗ – облигации внутреннего государственного займа. Их можно сравнить с депозитами в банке. Так как точно также вкладчик получает возврат всей суммы вложенных средств плюс дополнительный доход в виде процентов. Гарантом сохранности средств выступает центральный банк.

Эффективная ставка позволяет оценить настоящий доход, т.к. учитывает капитализацию процентов. Для примера «приобретем» годичные облигации на сумму 50 000 под 17%. Чтобы рассчитать свой доход, используем функцию БС:

Предположим, что проценты капитализируются ежемесячно. Поэтому 17% делим на 12. Результат в виде десятичной дроби вносим в поле «Ставка». В поле «Кпер» вводим число периодов капитализации. Ежемесячные фиксированные выплаты получать не будем, поэтому поле «Плт» оставляем свободным. В графу «Пс» вносим сумму вложенных средств со знаком «-«.

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

Самостоятельный расчет Эффективной Процентной Ставки

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

Как самостоятельно посчитать эффективную процентную ставку?

Давайте возьмем конкретный пример и рассчитаем его.

Исходные данные:

  • Сумма кредита: $100 000
  • Годовая ставка: 18%
  • Комиссия за обслуживание кредита: 1% от суммы (взимается ежемесячно)
  • Срок кредитования: 12 месяцев
  • Метод погашения: аннуитетные платежи.

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

Для начала — узнаем размер ежемесячного платежа. Для этого нам потребуется формула, приведенная в статье «Аннуитетные платежи».
Напомним, формула выглядит так:

A = K*S, где S — сумма кредита (в нашем случае S = 100 000); K — коэффициент аннуитета, рассчитывается по формуле, зависит от величин i и n.

В данном случае i = 0,015 n = 12. Если мы подставим полученные значения в вышеприведенную формулу, то получим, что
К = 0,09168.
А = $9168.

Способ 2.

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

Вводим в ячейку формулу вида:

=ПЛТ(0,015;12;-100000)

И получаем ту же сумму: $9168.

Пояснения к формуле:
0,015 — месячная процентная ставка i = 18 / 12 / 100;
12 — количество выплат n;
-100000 — сумма кредита S (вы не ошиблись — именно со знаком «минус»)

Теперь нам нужно составить в Excel’е таблицу ежемесячных платежей:

Cкачать таблицу расчёта в формате Excel

Если вы не до конца поняли, как происходит рассчет — перечитайте статью и смотрите на формулы в строке состояния Excel

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

Итоги расчета эффективной процентной ставки

  • Мы взяли кредит на $100 000.
  • Выплатили $122015,99.
  • Переплата составила $22015,99.
  • Ставка 22015,99 / 100000 = 22%.

Ежемесячная комиссия в 1% обошлась нам в 12000 долларов, что превышает основную сумму выплат по кредиту! Почему так? Потому что комиссию за обслуживание мы каждый месяц платили из рассчета от основной суммы кредита ($100’000), а проценты с каждым месяцем уменьшались ввиду того, что ежемесячно уменьшалась сумма кредита. Как видите, такая, безобидная, на первый взгляд, хитрость банка, как процент за обслуживание при ближайшем рассмотрении обходится очень дорого.

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

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

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

Как рассчитать аннуитетный платёж с помощью кредитного калькулятора?

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

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

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

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

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

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

Занимательный факт, изменяемая процентная ставка в реалиях нашей страны всегда являлась мифом, но это не так. Она была всегда, особенно тогда, когда Вы переставали платить по кредиту какой-то период. Некоторые банки используют её во благо, мотивируя заёмщика платить меньше со второго или третьего года кредита, а некоторые банки пишут красивую процентную ставку на рекламных буклетах, к примеру, 10,9% годовых. По факту эта ставка станет актуальной только со второго года выплат по кредиту, где первый год Вас обяжут платить бешенные 34%.

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

Самостоятельный расчёт аннуитетного платежа

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

Y = Sx(T+(T/(1+T)B-1))

Расшифруем указанные значения:

Y – сумма ежемесячного платежа;

T – процентная ставка;

B – время, на которое взят кредит в месяцах.

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

Tn = SnxT/12

В этом случае:

Tn – начисленные проценты;

Sn – остаток;

T – годовая ставка по проценту.

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

S = Y-Tn

Здесь Y – это регулярный платёж;

Tn – процент, начисленный к моменту определённого (то есть n-ого) платежа.

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

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

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

  • Создаём чистый лист и в любой свободной ячейке задаём соответствующую функцию;
  • Вводим необходимые параметры (которые запрашивает программа)

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

Плюсы и минусы аннуитетного кредита

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

Преимущества

  • Одинаковая сумма платежа;
  • Ежемесячный платёж ниже;
  • Предоставляется во всех банках;
  • Просто планировать собственные расходы;
  • Процент одобрения выше
Недостатки

  • Погашаются сначала проценты;
  • Значительные переплаты при ипотеке;
  • Значительные переплаты длительном кредитовании

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

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

Досрочное погашение аннуитетного кредита

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

  • Сократить период выплат в аннуитетной системе. Так, понадобится совершить дополнительный платёж. При этом месячная ставка не возрастёт. Платёж будет представлять собой компенсацию банку в размере процентов, которые он не получит.
  • Уменьшение ежемесячных выплат. В этом случае уменьшается аннуитетная ставка, но размер процента не меняется (допустимо только при условии сокращения выплат по основной задолженности).

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

Расчет кредита в excel: скачайте готовые формулы и калькуляторы

Финансирование бизнеса » Анализ кредитных предложений банков »

Если вы хотите расчитать платежи по кредиту (аннуитетные или дифференцированные), переплату и график платежей по месяцам в excel, то скачайте следующие xls-файлы:

Альтернатива использованию MS Excel — кредитный калькулятор on-line (функционал практически такой же, ничего качать на компьютер не нужно: все операции выполняются на нашем сайте).

Расскажем о файлах подробнее: дадим мини-инструкции.

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

Умеет:

  • Считать оба вида платежей.
  • Показывать полный график платежей.
  • Разбивать сумму выплаты на «погашение долга» и «погашение процентов».
  • Учитывать досрочные возвраты (отдельно для уменьшения срока и уменьшения размера выплат).

Пользоваться файлом довольно удобно: вбиваете значения в верхние четыре поля (сумма, процентная ставка, срок в месяцах, дата получения — последнее нужно для определения точного графика) и умное детище Билла Гейтса тут же заполняет все графы таблицы актуальной информацией.

Простая функция для платежей

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

Содержит всего одну функциональную ячейку:

Поменяйте значения на свои (вместо 14 — свою ставку, вместо 12 во втором случае — свой срок кредита в месяцах, вместо 100000 — свою сумму займа).

После смены данных достаточно нажать на «Энтер», чтобы получить результат:

Напоминаем, ссылки на оба xls-файла представлены в начале страницы.

Желаем успешного использования!

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

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

Прежде всего, нужно сказать, что существует два вида кредитных платежей:

  • Дифференцированные;
  • Аннуитетные.

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

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

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

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

Для расчета ежемесячного взноса при использовании аннуитетной схемы в Экселе существует специальная функция – ПЛТ. Она относится к категории финансовых операторов. Формула этой функции выглядит следующим образом:

=ПЛТ(ставка;кпер;пс;бс;тип)

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

Аргумент «Ставка» указывает на процентную ставку за конкретный период. Если, например, используется годовая ставка, но платеж по займу производится ежемесячно, то годовую ставку нужно разделить на 12 и полученный результат использовать в качестве аргумента. Если применяется ежеквартальный вид оплаты, то в этом случае годовую ставку нужно разделить на 4 и т.д.

«Кпер» обозначает общее количество периодов выплат по кредиту. То есть, если заём берется на один год с ежемесячной оплатой, то число периодов считается 12, если на два года, то число периодов – 24. Если кредит берется на два года с ежеквартальной оплатой, то число периодов равно 8.

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

«Бс» — это будущая стоимость. Эта величина, которую будет составлять тело займа на момент завершения кредитного договора. В большинстве случаев данный аргумент равен «0», так как заемщик на конец срока кредитования должен полностью рассчитаться с кредитором. Указанный аргумент не является обязательным. Поэтому, если он опускается, то считается равным нулю.

Аргумент «Тип» определяет время расчета: в конце или в начале периода. В первом случае он принимает значение «0», а во втором – «1». Большинство банковских учреждений используют именно вариант с оплатой в конце периода. Этот аргумент тоже является необязательным, и если его опустить считается, что он равен нулю.

Теперь настало время перейти к конкретному примеру расчета ежемесячного взноса при помощи функции ПЛТ. Для расчета используем таблицу с исходными данными, где указана процентная ставка по кредиту (12%), величина займа (500000 рублей) и срок кредита (24 месяца). При этом оплата производится ежемесячно в конце каждого периода.

  1. Выделяем элемент на листе, в который будет выводиться результат расчета, и щелкаем по пиктограмме «Вставить функцию», размещенную около строки формул.
  2. Производится запуск окошка Мастера функций. В категории «Финансовые» выделяем наименование «ПЛТ» и жмем на кнопку «OK».
  3. После этого открывается окно аргументов оператора ПЛТ.

    В поле «Ставка» следует вписать величину процентов за период. Это можно сделать вручную, просто поставив процент, но у нас он указан в отдельной ячейке на листе, поэтому дадим на неё ссылку. Устанавливаем курсор в поле, а затем кликаем по соответствующей ячейке. Но, как мы помним, у нас в таблице задана годовая процентная ставка, а период оплаты равен месяцу. Поэтому делим годовую ставку, а вернее ссылку на ячейку, в которой она содержится, на число 12, соответствующее количеству месяцев в году. Деление выполняем прямо в поле окна аргументов.

    В поле «Кпер» устанавливается срок кредитования. Он у нас равен 24 месяцам. Можно занести в поле число 24 вручную, но мы, как и в предыдущем случае, указываем ссылку на месторасположение данного показателя в исходной таблице.

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

    В поле «Бс» указывается величина займа, после полной его оплаты. Как помним, это значение практически всегда равно нулю. Устанавливаем в данном поле число «0». Хотя этот аргумент можно вообще опустить.

    В поле «Тип» указываем в начале или в конце месяца производится оплата. У нас, как и в большинстве случаев, она производится в конце месяца. Поэтому устанавливаем число «0». Как и в случае с предыдущим аргументом, в данное поле можно ничего не вводить, тогда программа по умолчанию будет считать, что в нем расположено значение равное нулю.

    После того, как все данные введены, жмем на кнопку «OK».

  4. После этого в ячейку, которую мы выделили в первом пункте данного руководства, выводится результат вычисления. Как видим, величина ежемесячного общего платежа по займу составляет 23536,74 рубля. Пусть вас не смущает знак «-» перед данной суммой. Так Эксель указывает на то, что это расход денежных средств, то есть, убыток.
  5. Для того, чтобы рассчитать общую сумму оплаты за весь срок кредитования с учетом погашения тела займа и ежемесячных процентов, достаточно перемножить величину ежемесячного платежа (23536,74 рубля) на количество месяцев (24 месяца). Как видим, общая сумма платежей за весь срок кредитования в нашем случае составила 564881,67 рубля.
  6. Теперь можно подсчитать сумму переплаты по кредиту. Для этого нужно отнять от общей величины выплат по кредиту, включая проценты и тело займа, начальную сумму, взятую в долг. Но мы помним, что первое из этих значений уже со знаком «-«. Поэтому в конкретно нашем случае получается, что их нужно сложить. Как видим, общая сумма переплаты по кредиту за весь срок составила 64881,67 рубля.

Урок: Мастер функций в Эксель

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

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

  1. Для определения величины оплаты по телу займа используем функцию ОСПЛТ, которая как раз предназначена для этих целей. Устанавливаем курсор в ячейку, которая находится в строке «1» и в столбце «Выплата по телу кредита». Жмем на кнопку «Вставить функцию».
  2. Переходим в Мастер функций. В категории «Финансовые» отмечаем наименование «ОСПЛТ» и жмем кнопку «OK».
  3. Запускается окно аргументов оператора ОСПЛТ. Он имеет следующий синтаксис:

    =ОСПЛТ(Ставка;Период;Кпер;Пс;Бс)

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

    Заполняем уже знакомые нам поля окна аргументов функции ОСПЛТ теми самыми данными, что были использованы для функции ПЛТ. Только учитывая тот факт, что в будущем будет применяться копирование формулы посредством маркера заполнения, нужно сделать все ссылки в полях абсолютными, чтобы они не менялись. Для этого требуется поставить знак доллара перед каждым значением координат по вертикали и горизонтали. Но легче это сделать, просто выделив координаты и нажав на функциональную клавишу F4. Знак доллара будет расставлен в нужных местах автоматически. Также не забываем, что годовую ставку нужно разделить на 12.

  4. Но у нас остается ещё один новый аргумент, которого не было у функции ПЛТ. Этот аргумент «Период». В соответствующее поле устанавливаем ссылку на первую ячейку столбца «Период». Данный элемент листа содержит в себе число «1», которое обозначает номер первого месяца кредитования. Но в отличие от предыдущих полей, в указанном поле мы оставляем ссылку относительной, а не делаем из неё абсолютную.

    После того, как все данные, о которых мы говорили выше, введены, жмем на кнопку «OK».

  5. После этого в ячейке, которую мы ранее выделили, отобразится величина выплаты по телу займа за первый месяц. Она составит 18536,74 рубля.
  6. Затем, как уже говорилось выше, нам следует скопировать данную формулу на остальные ячейки столбца с помощью маркера заполнения. Для этого устанавливаем курсор в нижний правый угол ячейки, в которой содержится формула. Курсор преобразуется при этом в крестик, который называется маркером заполнения. Зажимаем левую кнопку мыши и тянем его вниз до конца таблицы.
  7. В итоге все ячейки столбца заполнены. Теперь мы имеем график выплаты тела займа помесячно. Как и говорилось уже выше, величина оплаты по данной статье с каждым новым периодом увеличивается.
  8. Теперь нам нужно сделать месячный расчет оплаты по процентам. Для этих целей будем использовать оператор ПРПЛТ. Выделяем первую пустую ячейку в столбце «Выплата по процентам». Жмем на кнопку «Вставить функцию».
  9. В запустившемся окне Мастера функций в категории «Финансовые» производим выделение наименования ПРПЛТ. Выполняем щелчок по кнопке «OK».
  10. Происходит запуск окна аргументов функции ПРПЛТ. Её синтаксис выглядит следующим образом:

    =ПРПЛТ(Ставка;Период;Кпер;Пс;Бс)

    Как видим, аргументы данной функции абсолютно идентичны аналогичным элементам оператора ОСПЛТ. Поэтому просто заносим в окно те же данные, которые мы вводили в предыдущем окне аргументов. Не забываем при этом, что ссылка в поле «Период» должна быть относительной, а во всех других полях координаты нужно привести к абсолютному виду. После этого щелкаем по кнопке «OK».

  11. Затем результат расчета суммы оплаты по процентам за кредит за первый месяц выводится в соответствующую ячейку.
  12. Применив маркер заполнения, производим копирование формулы в остальные элементы столбца, таким способом получив помесячный график оплат по процентам за заём. Как видим, как и было сказано ранее, из месяца в месяц величина данного вида платежа уменьшается.
  13. Теперь нам предстоит рассчитать общий ежемесячный платеж. Для этого вычисления не следует прибегать к какому-либо оператору, так как можно воспользоваться простой арифметической формулой. Складываем содержимое ячеек первого месяца столбцов «Выплата по телу кредита» и «Выплата по процентам». Для этого устанавливаем знак «=» в первую пустую ячейку столбца «Общая ежемесячная выплата». Затем кликаем по двум вышеуказанным элементам, установив между ними знак «+». Жмем на клавишу Enter.
  14. Далее с помощью маркера заполнения, как и в предыдущих случаях, заполняем колонку данными. Как видим, на протяжении всего действия договора сумма общего ежемесячного платежа, включающего платеж по телу займа и оплату процентов, составит 23536,74 рубля. Собственно этот показатель мы уже рассчитывали ранее при помощи ПЛТ. Но в данном случае это представлено более наглядно, именно как сумма оплаты по телу займа и процентам.
  15. Теперь нужно добавить данные в столбец, где будет ежемесячно отображаться остаток суммы по кредиту, который ещё требуется заплатить. В первой ячейке столбца «Остаток к выплате» расчет будет самый простой. Нам нужно отнять от первоначальной величины займа, которая указана в таблице с первичными данными, платеж по телу кредита за первый месяц в расчетной таблице. Но, учитывая тот факт, что одно из чисел у нас уже идет со знаком «-«, то их следует не отнять, а сложить. Делаем это и жмем на кнопку Enter.
  16. А вот вычисление остатка к выплате после второго и последующих месяцев будет несколько сложнее. Для этого нам нужно отнять от тела кредита на начало кредитования общую сумму платежей по телу займа за предыдущий период. Устанавливаем знак «=» во второй ячейке столбца «Остаток к выплате». Далее указываем ссылку на ячейку, в которой содержится первоначальная сумма кредита. Делаем её абсолютной, выделив и нажав на клавишу F4. Затем ставим знак «+», так как второе значение у нас и так будет отрицательным. После этого кликаем по кнопке «Вставить функцию».
  17. Запускается Мастер функций, в котором нужно переместиться в категорию «Математические». Там выделяем надпись «СУММ» и жмем на кнопку «OK».
  18. Запускается окно аргументов функции СУММ. Указанный оператор служит для того, чтобы суммировать данные в ячейках, что нам и нужно выполнить в столбце «Выплата по телу кредита». Он имеет следующий синтаксис:

    =СУММ(число1;число2;…)

    В качестве аргументов выступают ссылки на ячейки, в которых содержатся числа. Мы устанавливаем курсор в поле «Число1». Затем зажимаем левую кнопку мыши и выделяем на листе первые две ячейки столбца «Выплата по телу кредита». В поле, как видим, отобразилась ссылка на диапазон. Она состоит из двух частей, разделенных двоеточием: ссылки на первую ячейку диапазона и на последнюю. Для того, чтобы в будущем иметь возможность скопировать указанную формулу посредством маркера заполнения, делаем первую часть ссылки на диапазон абсолютной. Выделяем её и жмем на функциональную клавишу F4. Вторую часть ссылки так и оставляем относительной. Теперь при использовании маркера заполнения первая ячейка диапазона будет закреплена, а последняя будет растягиваться по мере продвижения вниз. Это нам и нужно для выполнения поставленных целей. Далее жмем на кнопку «OK».

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

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

Урок: Финансовые функции в Excel

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

Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *