Формула расчета платежа по кредиту excel

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

Добавить калькулятор в Excel

Как быстро посчитать основные параметры кредита (ипотеки) в Excel, как быстро сформировать кредитный калькулятор график платежей в Excel? Как посчитать параметры досрочного погашения кредита в Excel?

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

Калькулятор выводит основные параметры вашего кредита или может сформировать лист с кредитным калькулятором и графиком платежей ! Работает как по схеме «вернуть в конце срока», так и с аннуитетными (равномерными платежами).

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

При возврате полной суммы кредита в конце срока для ее вычисления используется формула:

=сумма кредита*(100%+годовая ставка)^срок кредита(лет)

При аннуитетных (равномерных) платежах:

Для того, чтобы узнать величину ежемесячного платежа используйте функцию: =ПЛТ(годовая проц.ставка/12;кол-во месяцев;сумма кредита)*-1

График платежей по кредиту в Excel

Также вы можете добавить кредитный калькулятор в свой Excel с надстройкой SubEx, чтобы он, вместе с другими финансовыми инструментами, был под рукой в любую минуту!

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

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

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

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

А = К * S

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

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

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

Каждый клиент может погасить кредит в банковском учреждении посредством аннуитетного платежа. По договору между банком и заемщиком ежемесячная выплата по кредиту получается фиксированной (одинаковой). Клиент должен выплатить всю сумму в течение всего срока кредитования независимо от оставшейся суммы задолженности.

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

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

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

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

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

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

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

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

Данная графа рассчитана для указания формулы вида =ПЛТ( процентная ставка; срок в месяцах; сумма кредита). Процентная ставка делится на 12. Сумма кредита определяется через деление всего долга на основной срок кредитования.

Используемая формула

Для определения аннуитетного платежа, необходимо рассмотреть следующую формулу:

АП= KA*SM, где АП принадлежит к сумме данного платежа, КА – это значение коэффициента, SM определяет величину погашения долга в течение каждого месяца.

На практике рассматриваются различные формулы, которые помогают осуществлять расчет коэффициента платежа. В наиболее популярной формуле можно отнести: КА = (j*(1+j)m) / ( (1+j)m– 1):

  • KA является коэффициентом,
  • j определяет ставку кредитования (по ежемесячной оплате),
  • m – количество выплачиваемых дней кредита,
  • i обозначается в виде годовой ставки.

Детализация

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

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

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

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

Читайте так же:  Кредит не выходя из дома на карту

Калькулятор по кредиту с нерегулярными проплатами

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

Если размер долга не слишком большой, тогда лучше всего погасить долг целиком. Таким образом, банк прекратит начислять лишние проценты. Чтобы выполнить данную процедуру, необходимо указать показатели кредита в соответствующей графе. К примеру рассматривается сумма кредита на 6000 с процентной ставкой 3%, ежемесячным аннуитетным платежом – 30 единиц. Число платежей составляет 18 сроком на 18 месяцев без лишних просрочек. При помощи онлайн-калькулятора, сумма в целом составляет что к ровно 5724,19.

Расчет полной стоимости ссуды с помощью Excel

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

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

Выводы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Установление полной стоимости кредита в программе

Формула для определения ставки по кредиту рекомендована Центробанком России. Ее возможно посчитать, если использовать формулу, указанную в письме ФНС.

Читайте так же:  Займы под залог птс без поручителей

Для понимания вопроса необходимы многочисленные данные:

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

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

Полную стоимость при исчислении составляют:

Самостоятельный расчет выплат

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

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

В подходящую формулу входят:

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

Законодательные рекомендации в данном аспекте окончательно не сформированы. Однако функция уже действует.

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

Заметили ошибку? Выделите ее и нажмите Ctrl+Enter, чтобы сообщить нам.

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

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

Где можно бесплатно скачать такой калькулятор

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

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

Получилось? Вот и отлично! Приступаем к «разбору полётов»!

Разрабатываем калькулятор дифференцированных платежей в 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 работает достаточно просто. По аналогичному принципу заданы формулы и значения для остальных ячеек нашего кредитного калькулятора дифференцированных платежей. Давайте рассмотрим, как они рассчитаны. Щёлкаем мышкой по изображению:

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

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

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

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

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

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

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

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

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

Читайте так же:  Займ под расписку от частного лица отзывы

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

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

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

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

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

Видео (кликните для воспроизведения).

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

Расчет аннуитетных платежей: формула, использование 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). После этих действий у вас появится такая табличка:

Читайте так же:  Кредит с 18 лет без кредитной истории

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

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

  • «Ставка» – годовая процентная ставка по кредиту делённая на 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 ещё один кредитный калькулятор, который будет автоматически рассчитывать график аннуитетных платежей. Щёлкаем мышкой по рисунку:

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

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

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

Расчет платежей по кредиту в excel

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

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

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

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

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

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

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

Срок кредита, как правило, может выбираться заемщиком. Обычно он является кратным 12 месяцам и не превышает 7 лет (по ипотеке – до 30 лет).

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

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

  • ставка (в годовых процентах, разделенная на 12);
  • период кредита (в месяцах);
  • сумма предполагаемого платежа.

Для расчета аннуитетных платежей по кредиту в Excel используются следующие функции:

  • ПЛТ – определяет сумму платежа с учетом части основного долга и процентов. Аргументы: ставка (в годовых процентах, разделенная на 12); период кредита (в месяцах); размер займа.
  • ПРПЛТ – рассчитывает величину процентов в составе платежа. Аргументы: ставка (в годовых, разделенная на 12); номер периода выплат; время кредита (в месяцах); сумма займа.
  • ОСПЛТ – определяет сумму основного долга в структуре платежа. Аргументы: ставка (в годовых, разделенная на 12); номер периода выплат; время кредита (в месяцах); сумма займа.

Пример расчета графика платежей по кредиту

Данные для проведения вычислений:

  • ставка 20% годовых;
  • срок кредита 12 месяцев;
  • сумма платежа 5 тыс. р. в месяц (для расчета размера кредита);
  • сумма займа 100 тыс. р. (для расчета размера платежа).

В данном случае функция ПС представлена следующим образом: ПС(20%/12;12;5000). Результатом вычислений является максимально возможная сумма кредита 53 976 р.

Функции, используемые при расчете платежа, будут представлены таким образом:

Итогом расчетов будут значения:

  • сумма регулярного платежа 9 263 р.;
  • величина процентов в составе аннуитета от 1 667 р. до 152 р.;
  • размер погашаемого долга в структуре платежа от 7 597 р. до 9 112 р.

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

  • Размер текущей задолженности * процентная ставка (%) / 365 (366) дней в году * фактическое количество дней в месяце.

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

  • Размер текущей задолженности * процентная ставка / 12 месяцев в году.

В примере ниже использован именно такой подход.

Видео (кликните для воспроизведения).

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

Формула расчета платежа по кредиту excel
Оценка 5 проголосовавших: 1

ОСТАВЬТЕ ОТВЕТ

Please enter your comment!
Please enter your name here