Расчет аннуитетных платежей по кредиту в Excel
В наш век высоких технологий и автоматизации как-то неприлично вручную выполнять сложные расчёты. Хоть аннуитетные платежи рассчитать не так и трудно, но как говорит Юрий Ашер:
«Не надо напрягать свой мозг там, где это могут сделать за вас другие!»
В нашей ситуации к вам на помощь придут: компьютер и программа Microsoft Excel.
Хотим предупредить, что команда портала temabiz.com поставила перед собой цель не просто дать вам «халяву» в виде «экселевского» файла с готовыми расчетами. Нет, в этой публикации мы вас научим самостоятельно рассчитывать аннуитетные платежи, а также составлять в программе Excel графики погашения аннуитетных кредитов. Ну а для ленивых мы, конечно же, выложим готовые файлы кредитных калькуляторов.
Как рассчитать аннуитетный платеж в Excel
Те, кто читал предыдущую публикацию, наверняка ещё долго будут с ужасом вспоминать формулу аннуитетного платежа. Но сейчас вы, дорогие друзья, можете облегчённо вздохнуть, ибо все расчёты за вас сделает программа Microsoft Excel.
Мы сделаем не просто файлик с одной циферкой. Нет! Мы разработаем настоящий инструмент, с помощью которого вы сможете рассчитать аннуитетный платёж не только для себя, но и для соседа, который ставит свою машину на детской площадке; прыщавого студента, который сутками курит в вашем подъезде; тётки, которая выгуливает свою собаку прямо под вашими окнами – короче, для всех особо одарённых. Кстати, можете поставить где-нибудь возле монитора купюроприёмник и брать с этой публики деньги.
Давайте приступим к разработке нашего кредитного калькулятора. Смотрим на первый рисунок:
Итак, вы видите два блока. Один с исходными данными, а второй – с расчётами. Исходные данные (сумма кредита, годовая процентная ставка, срок кредитования) вы будете вводить вручную, а во втором блоке будут мгновенно появляться расчёты.
Начнём с расчёта ежемесячной суммы аннуитетного платежа. Для этого надо сделать активным окошко, в котором вы хотите видеть это значение (в нашем случае – это поле C11, на рисунке оно обведено и указано под номером 1). Далее слева от строки формул жмём на «fx» (на рисунке эта кнопка обведена и указана под номером 2). После этих действий у вас появится такая табличка:
Выбираем функцию «ПЛТ» и жмём «Ок». Перед вами появится таблица, в которую надо будет ввести исходные данные:
Здесь нам требуется заполнить три поля:
Обратите внимание на то, что мы не вводим готовые цифры в эту таблицу, а указываем координаты ячеек нашего блока с исходными данными. Так, в поле «Ставка» мы указываем координаты ячейки, в которой будет вписываться вручную процентная ставка (C5) и делим её на 12; в поле «Кпер» указываются координаты ячейки, в которой будет вписываться срок кредитования (C6); в поле «Пс» – координаты ячейки в которой вписывается сумма кредита (C4). Так как сумма кредита у нас указывается со знаком минус, то перед координатой (C4) мы ставим знак минус.
После того как исходные данные будут введены, жмём кнопку «Ок». В результате мы видим в блоке расчетов точное значение ежемесячного аннуитетного платежа:
Итак, в данный момент сумма нашего аннуитетного платежа составляет 4680 руб (на рисунке он обведён и указан под номером 1). Если вы будете менять сумму кредита, процентную ставку и общий срок кредитования, то автоматически будет меняться значение вашего аннуитетного платежа.
Кстати, обратите внимание на значение функции, обозначенное на рисунке под номером 2: =ПЛТ(C5/12;C6;-C4). Да, да, это и есть те самые координаты, которые мы вводили в таблицу, выбрав функцию «ПЛТ». По сути, вы могли бы не проделывать всех тех сложных телодвижений, которые показаны на втором и третьем рисунках. Можно было просто вписать в строке формул то, что там сейчас вписано.
Зная размер аннуитетного платежа несложно посчитать остальные значения нашего расчётного блока:
На рисунке наглядно показано, как рассчитана общая сумма выплат (обведена и указана под номером 1). Так как она равна сумме аннуитетного платежа (ячейка C11) умноженной на общее количество месяцев кредитования (ячейка C6), то мы и вписываем в строку формул следующую формулу: =C11*C6 (на рисунке она обведена и указана под номером 2). В результате мы получили значение 56 157 рублей.
Переплата по кредиту рассчитывается ещё проще. От общей суммы выплат (ячейка C12) надо отнять сумму кредита (ячейка C4). В строку вписываем такую формулу: =C12-C4. В нашем примере переплата равна: 6157 рублей.
Ну и последнее значение – эффективная процентная ставка (или полная стоимость кредита). Она рассчитывается так: общую сумму выплат (ячейка C12) делим на сумму кредита (ячейка C4), отнимаем единицу, затем делим всё это на срок кредитования в годах (ячейка C6 делённая на 12). В строке будет такая формула: =(C12/C4-1)/(C6/12). В нашем примере эффективная процентная ставка составляет 12,3%.
Всё! Вот таким нехитрым способом мы с вами составили в программе Microsoft Excel автоматический калькулятор расчета аннуитетных платежей по кредиту, скачать который можно ссылке ниже:
Расчет в Excel суммы кредита для заданного аннуитетного платежа
В чём «фишка» аннуитетной схемы погашения кредита? Правильно! Основная «фишка» в том, что заёмщик выплачивает кредит равными суммами на протяжении всего срока кредитования. С такой схемой очень удобно планировать свой бюджет. Например, вы готовы ежемесячно выделять на погашение кредита 5000 рублей. По вашим скромным подсчётам, такая нагрузка будет для вас не слишком обременительной. Естественно, у вас возникает закономерный вопрос: «А на какую сумму кредита я могу рассчитывать?» В общем, нам нужен новый кредитный калькулятор, у которого в исходных данных будет не сумма кредита, а величина аннуитетного платежа.
Что же, друзья, не будем терять время! Открываем программу Microsoft Excel и приступаем к разработке нашего кредитного калькулятора!
Итак, структура нового кредитного калькулятора почти не изменилась. Здесь также есть блок с исходными данными и блок с расчётами. Единственное изменение, это то, что в исходных данных мы вводим ежемесячный аннуитетный платёж, который готовы выплачивать, а в расчётах получаем сумму кредита, на которую мы можем рассчитывать. Собственно, она на нашем рисунке обведена и отмечена под номером 1.
Чтобы рассчитать сумму ожидаемого кредита надо воспользоваться функцией ПС, предварительно кликнув по ячейке, в которой мы хотим видеть свой расчёт (в нашем калькуляторе это ячейка с координатой C11). Вызвать функцию ПС можно нажав на знакомую вам кнопку «fx», которая находится слева от строки формул. В появившемся окне выбираем «ПС» и жмём «Ок». В открывшейся таблице вводим следующие данные:
Жмём «Ок» и в ячейке С11 появилась сумма 53 422 руб. – именно на такой размер кредита может рассчитывать заёмщик, который готов на протяжении 12 месяцев ежемесячно выплачивать по 5000 руб.
Кстати, обратите внимание на данные в строке формул (на рисунке они обведены и указаны под номером 2). Вы всё правильно поняли, друзья! Да, это те данные, которые необходимы для расчёта суммы кредита в нашем калькуляторе: =ПС(C5/12;C6;-C4). Те самые параметры, которые мы вводили в таблице функции ПС.
Расчёт остальных показателей выполняется по такому же принципу, как и в предыдущем калькуляторе:
Кстати, интересный момент. Вот в нашем примере, выплачивая ежемесячно в течение года по 5000 рублей, мы можем рассчитывать на сумму кредита равную 53 422 рубля. А что делать, если надо больше денег? Как вариант, можно увеличить срок кредитования. Если вместо 12 месяцев поставить 24, то сумма кредита увеличится до 96 380 рублей. Эти данные нам мгновенно выдал наш кредитный калькулятор, который вы можете скачать ссылке ниже:
Кредитный калькулятор в Excel по расчету графика аннуитетных платежей
Два предыдущих кредитных калькулятора очень удобны, но они выполняют краткие (общие) расчёты. А иногда заёмщику нужна расширенная информация – график ежемесячных аннуитетных платежей с детальной расшифровкой каждой выплаты (с указанием сумм, идущих на погашение процентов, и сумм, погашающих тело кредита). В общем, сейчас мы сделаем в программе Excel ещё один кредитный калькулятор, который будет автоматически рассчитывать график аннуитетных платежей. Щёлкаем мышкой по рисунку:
Перед вами расширенная и доработанная версия нашего первого кредитного калькулятора (того, который рассчитывает размер ежемесячного аннуитетного платежа по кредиту). Здесь кроме стандартных блоков с исходными данными и расчётами, появилась таблица, в которой детально расписаны все наши будущие ежемесячные выплаты. Таблица имеет пять колонок:
Вот так легко и непринуждённо мы разработали кредитный калькулятор по расчёту графика аннуитетных платежей. Скачать его можно ссылке ниже:
Итак, друзья, теперь у вас есть целых три кредитных калькулятора по расчёту аннуитетных платежей, разработанных в программе Microsoft Excel. В следующей публикации мы расскажем о досрочном погашении аннуитетного кредита.
Расчет платежей
Мы разработали простой и удобный онлайн-калькулятор, с помощью которого можно сделать расчет ежемесячного платежа и суммы переплаты по потребительскому кредиту.
Рассчитать ежемесячный платеж
Виды расчета суммы кредита по ежемесячному платежу
Кредитные организации предлагают два вида расчета погашения задолженности:
Каждая из них имеет собственные особенности, достоинства, и недостатки.
Аннуитетная схема расчетов
Аннуитетный способ (или погашение равными суммами) означает, что в течение всего времени пользования кредитом заемщик вносит для погашения задолженности равные суммы за каждый период (как правило, месяц).
Главная особенность такой методики заключается в том, что обязательно погашается полная сумма процентов, которая начисляется в текущем периоде, а остаток регулярного взноса направляется на погашение части тела кредита (основной суммы задолженности). Величины и соотношение процентных выплат и средств на уменьшение основного долга изменяются в каждом периоде.
Основные понятия дифференцированного платежа
Этот вариант обслуживания кредита иначе называют погашением равными долями. При расчетах с использованием дифференцированного платежа, величина регулярных взносов также включает две составляющие:
Сравнение методов расчета
Заемщики, которые проводят сравнительные расчеты для обеих схем погашения, имеют основания утверждать, что дифференцированный платеж выгоднее аннуитетного. К примеру, для ипотечного кредита на сумму 6 миллионов рублей под 12% годовых со сроком кредитования 20 лет при дифференцированной методике расчета переплата составит 7230 тыс. рублей. Аннуитетная схема (погашение равными суммами) дает в результате сумму процентов (стоимость кредита) 9856 тыс. рублей. Особенно существенно проявляются отличия при долгосрочном кредитовании (например, в ипотечных кредитах на сроки более 10 лет).
Из других достоинств дифференцированного платежа принято выделять:
Важно! Аннуитетный платеж отлично подойдет не только для погашения наличными, но и для использования таких распространенных видов банковских услуг, как регулярные (в автоматическом режиме) платежи в счет оплаты долга с карт и/или счетов.
Основное различие между схемами состоит в определении суммы ежемесячного платежа.
При аннуитетной схеме она остается неизменной. Однако при погашении равными суммами в первые месяцы основную часть выплат составляют проценты. Остаток основной задолженности в это время уменьшается медленно, в результате чего и составляющая, идущая на процентные выплаты, остается весьма значительной.
Для заемщика, долг которого обслуживается по дифференцированной схеме, характерна другая проблема. Поскольку в состав регулярного взноса входит вся сумма начисленных за период процентов, его размер на первоначальном этапе (когда остаток тела велик) и в конце срока могут различаться весьма существенно. Так, для приведенного выше примера ипотечного кредита, сумма погашения основного долга составляет 25 тыс. руб. ежемесячно. При этом, за первый месяц пользования кредитом клиент должен перечислить банку 85 тыс.руб. (из них 60 000 – проценты по кредиту), а за последний – 25 250 рублей (2 250 рублей процентов). Такая неравномерность приводит и к усложнению долговременного планирования бюджета.
Эти отличия следует учитывать при рассмотрении других связанных с кредитованием вопросов:
Важно! Рассматривать отличие между схемами кредитования следует и в том случае, если заемщик хочет получить имущественные налоговые вычеты, например, при покупке жилья в ипотеку. В этом случае аннуитетная схема обслуживания долга даст лучшие результаты, поскольку на начальном этапе сумма процентных выплат, а, следовательно, и возврат налогов, максимальны.
Сегодня заемщику нет необходимости рассчитывать выплаты по кредиту вручную. Решить эту задачу онлайн поможет кредитный калькулятор.
Онлайн расчет платежей по кредиту на кредитном калькуляторе
Такой калькулятор предоставляют как специализированные порталы, например, banki.ru или calculator-credit.ru, так и официальные сайты кредитных организаций.
Расчет производится на основании следующих данных :
В результате расчета приведенного выше примера ипотечного кредита ориентировочные суммы по ежемесячному погашению аннуитетными платежами составляют 66 065 рублей, а общая стоимость пользования займом (сумма переплат) 9 855 640 рублей.
Более точные результаты можно получить, если воспользоваться кредитными калькуляторами банков. Они работают по тому же принципу, однако приводятся с учетом специфики условий кредитования и конкретных программ, предлагаемых финансовыми организациями.
Большинство из них позволяет учесть различные льготы для заемщиков, например, при расчете ипотеки с государственной поддержкой или пониженные кредитные ставки для клиентов-участников зарплатных проектов. Кроме того, такие калькуляторы, как правило, более функциональны, поскольку позволяют вести расчет по различным наборам исходных данных – сумме кредитования, стоимости покупки и первоначальному взносу, величине подтвержденного дохода, сроку кредитования. Практически все банковские сервисы учитывают и требования к финансовому состоянию заемщика, прежде всего, сумме доходов, в которой кредитный платеж не должен превышать 40-50%. Результатом расчета также являются параметры обслуживания кредита:
В зависимости от используемого набора входных значений определяются также необходимые суммы подтвержденного дохода, максимальный размер займа, допустимый срок кредитования.
Внимание! Результаты работы кредитных калькуляторов, даже размещенных на страницах официальных сайтов банков, не являются окончательными документами! Окончательный график погашения рассчитывается (и входит в пакет документов к кредитному договору) только после оформления и получения положительного решения по кредиту. Делает такой расчет специалист банка на этапе заключения договора.
Впрочем, для того, чтобы провести расчеты выплат по кредиту не обязательно использовать интернет-сервисы. Сделать это можно и собственными силами с помощью обычного (или встроенного в ПО смартфона) калькулятора.
Расчет аннуитетных платежей по кредиту
Поскольку аннуитетная схема является более сложной для самостоятельного расчета, ее следует рассмотреть более подробно. Для проведения вычислений используются следующие исходные данные:
Общая формула расчета регулярного платежа (Пл) выглядит просто:
Здесь Кр – расчетный коэффициент (коэффициент аннуитета) для определения величины вносимых за каждый период средств. Именно он и является основной неизвестной, для которой необходим точный расчет.
Пример расчета для приведенных выше параметров ипотечного платежа.
Ежемесячный взнос в этом случае составит
Пл = 6000000 * 0.0110108 = 66064.8 (руб.)
Полученный результат точно соответствует полученному при расчетах с использованием кредитного калькулятора.
Использование таблицы Excell для расчета платежей по кредиту
Достаточно просто сделать расчеты ежемесячных кредитных платежей с использование таблиц Excell. Программа содержит множество функций для финансовых расчетов, которые обеспечивают пользователю максимум удобства при выполнении вычислений.
Для расчета аннуитетных платежей в Excell пользователю достаточно обратиться к функции ПЛТ.
Внимание! Функция доступна только при подключенной надстройке «Пакет анализа»! В MS Excell 2007, 2010 надстройка включена по умолчанию. При отсутствии имени функции в списке ее следует подключить!
Функция (формула для расчета) имеет формат: ПЛТ(ставка;кпер;пс;[бс];[тип]).
В качестве аргументов используются:
Расчет графика погашения (если необходима подробная информация о распределении средств с остатками на конец каждого периода) использует не только приведенную формулу. Определяют также:
Расчет дифференцированного платежа по кредиту
В этом случае табличный калькулятор может использовать формулу ПРОЦПЛАТ(ставка;период;кпер;пс). Схожие по названиям аргументы аналогичны используемым при расчете аннуитета. Аргумент период означает номер периода, за который выплачиваются проценты.
Важно! Функция использует методику начисления процентов на начало периода! Для корректного расчета взносов по кредиту необходимо использовать номер периода, уменьшенный на 1 (например, для расчета за первый месяц в функции использовать период, равный 0).
Алгоритм расчета графиков погашения аналогичен, использующемуся при расчете аннуитетной схемы, только погашение тела кредита остается постоянным (сумма кредитования делится на количество периодов погашения).
Расчет кредита с остаточным платежом
Такой вид широко применяется при кредитовании автомобилей. Отличается он тем, что погашается в течение активного периода не вся основная сумма задолженности, часть ее остается для погашения последним взносом (остаточный платеж). В результате значительно уменьшается сумма регулярных выплат (особенно заметно это при дифференцированном платеже).
Формула расчета для аннуитета остается неизменной, только обязательно указывается параметр бс (ОБЯЗАТЕЛЬНО со знаком противоположным величине пс). Для дифференцированного метода изменяется только величина погашения тела кредита (рассчитывается не на полную сумму, а на разность между размером займа и величиной остаточного платежа).
Особенности расчета платежа по ипотечному кредиту
Главной отличительной особенностью расчетов выплат по ипотеке является наличие обязательных страховых платежей. Наиболее распространенным способом оплаты страховки является единовременное внесение страховых взносов за счет кредитных средств, и включение этой суммы в тело кредита. Таким образом, алгоритм расчета ежемесячного погашения остается неизменным.
В случае, если страховая компания использует другие принципы расчета с клиентами, например, ежегодные или ежемесячные выплаты взносов, их в кредитном платеже учитывают как суммы регулярных комиссий.
Использование формул Excel для определения объемов платежей и сбережений
Управление личными финансами может быть сложной задачей, особенно если вам нужно планировать свои платежи и сбережения. Excel формулы и шаблоны бюджетов помогут вам вычислить будущую стоимость своих задолженности и инвестиций, что упростит расчет времени, необходимого для достижения целей. Используйте следующие функции:
ПЛТ: возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и процентной ставки.
КПЕР: возвращает количество периодов выплаты для инвестиции на основе регулярных постоянных выплат и постоянной процентной ставки.
ПВ: возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на данный момент равноценна ряду будущих выплат.
БС: возвращает будущую стоимость инвестиции при условии периодических равных платежей и постоянной процентной ставки.
Расчет ежемесячных платежей для погашения задолженности по кредитной карте
Предположим, остаток к оплате составляет 5400 долларов США под 17% годовых. Пока задолженность не будет погашена полностью, вы не сможете рассчитываться картой за покупки.
С помощью функции ПЛТ(ставка;КПЕР;ПС)
получаем ежемесячный платеж в размере 266,99 долларов США, который позволит погасить задолженность за два года.
Аргумент «ставка» — это процентная ставка на период погашения кредита. Например, в данной формуле ставка 17% годовых делится на 12 — количество месяцев в году.
Аргумент КПЕР 2*12 — это общее количество периодов выплат по кредиту.
Аргумент ПС или приведенной стоимости составляет 5400 долларов США.
Расчет ежемесячных платежей по ипотеке
Представьте дом стоимостью 180 000 долларов США под 5% годовых на 30 лет.
С помощью функции ПЛТ(ставка;КПЕР;ПС)
получена сумма ежемесячного платежа (без учета страховки и налогов) в размере 966,28 долларов США.
Аргумент «ставка» составляет 5%, разделенных на 12 месяцев в году.
Аргумент КПЕР составляет 30*12 для ипотечного кредита сроком на 30 лет с 12 ежемесячными платежами, оплачиваемыми в течение года.
Аргумент ПС составляет 180 000 (нынешняя величина кредита).
Расчет суммы ежемесячных сбережений, необходимой для отпуска
Необходимо собрать деньги на отпуск стоимостью 8500 долларов США за три года. Процентная ставка сбережений составляет 1,5%.
С помощью функции ПЛТ(ставка;КПЕР;ПС;БС)
получаем, что чтобы собрать 8500 долларов США за три года, необходимо откладывать по 230,99 долларов США ежемесячно.
Аргумент «ставка» составляет 1,5%, разделенных на 12 месяцев — количество месяцев в году.
Аргумент КПЕР составляет 3*12 для двенадцати ежемесячных платежей за три года.
Аргумент ПС (приведенная стоимость) составляет 0, поскольку отсчет начинается с нуля.
Аргумент БС (будущая стоимость), которую необходимо достичь, составляет 8500 долларов США.
Теперь допустим, вы хотите собрать 8500 долларов США на отпуск за три года, и вам интересно, какую сумму необходимо положить на счет, чтобы ежемесячный взнос составлял 175,00 долларов США. Функция ПС рассчитает размер начального депозита, который позволит собрать желаемую сумму.
С помощью функции ПС(ставка;КПЕР;ПЛТ;БС)
мы узнаем, что необходим начальный депозит в размере 1969,62 долларов США, чтобы можно было откладывать по 175,00 долларов США в месяц и собрать 8500 долларов США за три года.
Аргумент «Ставка» составляет 1,5%/12.
Аргумент КПЕР составляет 3*12 (или двенадцать ежемесячных платежей за три года).
Аргумент БС (будущая стоимость) составляет 8500.
Расчет срока погашения потребительского кредита
Представьте, что вы взяли потребительский кредит на сумму 2500 долларов США и согласились выплачивать по 150 долларов США ежемесячно под 3% годовых.
С помощью функции КПЕР(ставка;ПЛТ;ПС)
выясняем, что для погашения кредита необходимо 17 месяцев и несколько дней.
Аргумент «Ставка» составляет 3%/12 ежемесячных платежей за год.
Аргумент ПС (приведенная стоимость) составляет 2500.
Расчет суммы первого взноса
Скажем, вы хотите приобрести автомобиль стоимостью 19 000 долларов США под 2,9 % годовых за три года. Вы хотите, чтобы ежемесячные платежи были на уровне 3500 долларов США в месяц, поэтому вам нужно выяснить сумму своего взноса. В этой формуле результатом функции ПС является сумма займа, которая затем вычитается из цены покупки, чтобы получить первый взнос.
С помощью функции ПС(ставка;КПЕР;ПЛТ)
выясняем, что первый взнос должен составлять 6946,48 долларов США.
Сначала в формуле указывается цена покупки в размере 19 000 долларов США. Результат функции ПС будет вычтен из цены покупки.
Аргумент «Ставка» составляет 2,9%, разделенных на 12.
Аргумент КПЕР составляет 3*12 (или двенадцать ежемесячных платежей за три года).
Оценка динамики увеличения сбережений
Начиная с 500 долларов США на счету, сколько можно собрать за 10 месяцев, если класть на депозит по 200 долларов США в месяц под 1,5% годовых?
С помощью функции БС(ставка;КПЕР;ПЛТ;ПС)
получаем, что за 10 месяцев выйдет сумма 2517,57 долларов США.
Аргумент «Ставка» составляет 1,5%/12.
Аргумент КПЕР составляет 10 (месяцев).