Расчет процентов по займу в excel скачать

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

КОНСУЛЬТАЦИЯ ЮРИСТА


УЗНАЙТЕ, КАК РЕШИТЬ ИМЕННО ВАШУ ПРОБЛЕМУ — ПОЗВОНИТЕ ПРЯМО СЕЙЧАС

8 800 350 84 37

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

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

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

Начнём с расчёта ежемесячной суммы аннуитетного платежа. Для этого надо сделать активным окошко, в котором вы хотите видеть это значение (в нашем случае – это поле 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечание

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

Примечание

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

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

Анализ пенсионных отчислений с использованием функции СТАВКА в Excel

Пример 2. Определить темпы роста пенсионных отчислений (процентную ставку), если баланс средств на конец года составляет 12000 долларов, а в начале года – 2400 долларов. Еженедельные платежи на протяжении года составляли 150 долларов (то есть, количество периодов – 52).

Формула для расчета:

  • B2 – количество периодов выплат;
  • B3 – сумма платежа (расходная операция, поэтому отрицательное значение);
  • B4 – сумма средств до наступления первого периода выплат;
  • B5 – сумма по окончанию последнего периода выплат;
  • 0 – выплаты в конце периода;
  • 0,1 – произвольное значение из интервала от 0 до 1;
  • 52 – количество периода выплат для пересчета размера ставки в годовых.
Читайте так же:  Отримати кредит онлайн на карту

То есть, пенсионные отчисления выполняются под 7% годовых.

Функция СТАВКА в Excel и особенности ее использования

Функция СТАВКА имеет следующий синтаксис:

= СТАВКА( кпер; плт; пс; [бс]; [тип]; [прогноз])

  1. Единицы измерения величин, указанных в качестве аргументов кпер и [прогноз], должны соответствовать друг другу. Например, при расчете ставки по займу, выданному на два года под 16% с ежемесячными выплатами необходимо, в качестве аргумента [прогноз] необходимо использовать значение 16%/12, а кпер – 2*12.
  2. Если хотя бы в качестве одного из аргументов функции было передано текстовое значение, результатом выполнения функции будет являться код ошибки #ЗНАЧ!.
  3. Аннуитетная схема выплаты вознаграждения либо погашения задолженности предполагает выплаты фиксированной суммы, включающей вознаграждение или тело кредита и дополнительных процентов (в зависимости от установленной процентной ставки) на протяжении установленного количества периодов выплат. В отличие от классической схемы, при которой проценты начисляются на остаток вознаграждения или задолженности, в аннуитетной схеме соотношение тело кредита/проценты является изменяющейся величиной.
  4. При выполнении расчетов функция СТАВКА использует итерационный метод. Если после 20 итераций последующие результаты вычислений отличаются друг от друга более, чем на 10-7, результатом вычислений будет являться код ошибки #ЗНАЧ!.

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

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

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

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

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

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

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

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

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

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

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

Примечание

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

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

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

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

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

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

Определение реальной процентной ставки по кредиту

Пример 3. Ноутбук одной и той же модели можно приобрести за 1200 долларов в рассрочку (беспроцентную, судя по рекламе в первом магазине) или за 1050 долларов в другом магазине. Рассрочка выдается на 1 год с 12 периодами выплат. Определить реальный процент «беспроцентной» рассрочки платежей по кредиту.

Формула для расчета:

  • B2 – число периодов выплат;
  • -B3/B2 – выражение для расчета размера ежемесячного платежа;
  • B4 – реальная стоимость ноутбука (используется как начальная стоимость финансового инструмента, цена которого повысится до 1200 к окончанию последнего периода выплат);
  • 0 – остаток по окончанию последнего периода выплат;
  • 0 — выплаты в конце периода;
  • 0,01 — произвольное значение предполагаемой ставки.

То есть, фактически в первом магазине клиенту предложили кредит на ноутбук под 25,4% годовых.

Расчет в 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 рублей. Эти данные нам мгновенно выдал наш кредитный калькулятор, который вы можете скачать ссылке ниже:

Задача1

Определить величину ежемесячных равновеликих выплат по ссуде, размер которой составляет 100 000 руб., а процентная ставка составляет 10% годовых. Ссуда взята на срок 5 лет.

Разбираемся, какая информация содержится в задаче:

  1. Заемщик ежемесячно должен делать платеж банку. Этот платеж включает: сумму в счет погашения части ссуды и сумму для оплаты начисленных за прошедший период процентов на остаток ссуды ;
  2. Сумма ежемесячного платежа (аннуитета) постоянна и не меняется на протяжении всего срока, так же как и процентная ставка. Также не изменяется порядок платежей – 1 раз в месяц;
  3. Сумма для оплаты начисленных за прошедший период процентов уменьшается каждый период, т.к. проценты начисляются только на непогашенную часть ссуды;
  4. Как следствие п.3 и п.1, сумма, уплачиваемая в счет погашения основной суммы ссуды, увеличивается от месяца к месяцу.
  5. Заемщик должен сделать 60 равновеликих платежей (12 мес. в году*5 лет), т.е. всего 60 периодов (Кпер);
  6. Проценты начисляются в конце каждого периода (если не сказано обратное, то подразумевается именно это), т.е. аргумент Тип=0. Платеж должен производиться также в конце каждого периода;
  7. Процент за пользование заемными средствами в месяц (за период) составляет 10%/12 (ставка);
  8. В конце срока задолженность должна быть равна 0 (БС=0).

Расчет суммы выплаты по ссуде за один период, произведем сначала с помощью финансовой функции MS EXCEL ПЛТ() .

Примечание

. Обзор всех функций аннуитета в статье найдете здесь .

Эта функция имеет такой синтаксис: ПЛТ(ставка; кпер; пс; [бс]; [тип]) PMT(rate, nper, pv, [fv], [type]) – английский вариант.

Примечание

: Функция ПЛТ() входит в надстройку «Пакет анализа». Если данная функция недоступна или возвращает ошибку #ИМЯ?, то включите или установите и загрузите эту надстройку (в MS EXCEL 2007/2010 надстройка «Пакет анализа» включена по умолчанию).

Первый аргумент – Ставка. Это процентная ставка именно за период, т.е. в нашем случае за месяц. Ставка =10%/12 (в году 12 месяцев). Кпер – общее число периодов платежей по аннуитету, т.е. 60 (12 мес. в году*5 лет) Пс — Приведенная стоимость всех денежных потоков аннуитета. В нашем случае, это сумма ссуды, т.е. 100 000. Бс — Будущая стоимость всех денежных потоков аннуитета в конце срока (по истечении числа периодов Кпер). В нашем случае Бс = 0, т.к. ссуда в конце срока должна быть полностью погашена. Если этот параметр опущен, то он считается =0. Тип — число 0 или 1, обозначающее, когда должна производиться выплата. 0 – в конце периода, 1 – в начале. Если этот параметр опущен, то он считается =0 (наш случай).

Примечание

: В нашем случае проценты начисляются в конце периода. Например, по истечении первого месяца начисляется процент за пользование ссудой в размере (100 000*10%/12), до этого момента должен быть внесен первый ежемесячный платеж. В случае начисления процентов в начале периода, в первом месяце % не начисляется, т.к. реального пользования средствами ссуды не было (грубо говоря % должен быть начислен за 0 дней пользования ссудой), а весь первый ежемесячный платеж идет в погашение ссуды (основной суммы долга).

Решение1 Итак, ежемесячный платеж может быть вычислен по формуле =ПЛТ(10%/12; 5*12; 100 000; 0; 0) , результат -2 107,14р. Знак минус показывает, что мы имеем разнонаправленные денежные потоки: +100000 – это деньги, которые банк дал нам, -2107,14 – это деньги, которые мы возвращаем банку .

Альтернативная формула для расчета платежа (общий случай): =-(Пс*ставка*(1+ ставка)^ Кпер /((1+ ставка)^ Кпер -1)+ ставка /((1+ ставка)^ Кпер -1)* Бс)*ЕСЛИ(Тип;1/(ставка +1);1)

Если процентная ставка = 0, то формула упростится до =(Пс + Бс)/Кпер Если Тип=0 (выплата в конце периода) и БС =0, то Формула 2 также упрощается:

Вышеуказанную формулу часто называют формулой аннуитета (аннуитетного платежа) и записывают в виде А=К*S, где А — это аннуитетный платеж (т.е. ПЛТ), К — это коэффициент аннуитета, а S — это сумма кредита (т.е. ПС). K=-i/(1-(1+i)^(-n)) или K=(-i*(1+i)^n)/(((1+i)^n)-1), где i=ставка за период (т.е. Ставка), n — количество периодов (т.е. Кпер). Напоминаем, что выражение для K справедливо только при БС=0 (полное погашение кредита за число периодов Кпер) и Тип=0 (начисление процентов в конце периода).

Таблица ежемесячных платежей

Составим таблицу ежемесячных платежей для вышерассмотренной задачи.

Для вычисления ежемесячных сумм идущих на погашение основной суммы долга используется функция ОСПЛТ(ставка; период; кпер; пс; [бс]; [тип]) практически с теми же аргументами, что и ПЛТ() (подробнее см. статью Аннуитет. Расчёт в MS EXCEL погашение основной суммы долга ). Т.к. сумма идущая на погашение основной суммы долга изменяется от периода к периоду, то необходим еще один аргумент период , который определяет к какому периоду относится сумма.

Для вычисления ежемесячных сумм идущих на погашение процентов за ссуду используется функция ПРПЛТ (ставка; период; кпер; пс; [бс]; [тип]) с теми же аргументами, что и ОСПЛТ() (подробнее см. статью Аннуитет. Расчет в MS EXCEL выплаченных процентов за период ).

Примечание

. Для определения суммы переплаты по кредиту (общей суммы выплаченных процентов) используйте функцию ОБЩПЛАТ() , см. здесь .

Конечно, для составления таблицы ежемесячных платежей можно воспользоваться либо ПРПЛТ() или ОСПЛТ() , т.к. эти функции связаны и в любой период: ПЛТ= ОСПЛТ + ПРПЛТ

Читайте так же:  Кредит для ип менее 6 месяцев

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

Примечание

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

График платежей можно рассчитать без использования формул аннуитета. График приведен в столбцах K:P файла примера лист Аннуитет (ПЛТ) , а также на листе Аннуитет (без ПЛТ) . Также тело кредита на начало и конец периода можно рассчитать с помощью функции ПС и БС (см. файл примера лист Аннуитет (ПЛТ), столбцы H:I ).

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

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

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

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

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

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

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

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

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

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

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

А = К х S

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

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

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

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

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

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

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

Задача2

Ссуда 100 000 руб. взята на срок 5 лет. Определить величину ежеквартальных равновеликих выплат по ссуде, чтобы через 5 лет невыплаченный остаток составил 10% от ссуды. Процентная ставка составляет 15% годовых.

Решение2 Ежеквартальный платеж может быть вычислен по формуле =ПЛТ(15%/12; 5*4; 100 000; -100 000*10%; 0) , результат -6 851,59р. Все параметры функции ПЛТ() выбираются аналогично предыдущей задаче, кроме значения БС, которое = -100000*10%=-10000р., и требует пояснения. Для этого вернемся к предыдущей задаче, где ПС = 100000, а БС=0. Найденное значение регулярного платежа обладает тем свойством, что сумма величин идущих на погашение тела кредита за все периоды выплат равна величине займа с противоположным знаком. Т.е. справедливо равенство: ПС+СУММ(долей ПЛТ, идущих на погашение тела кредита)+БС=0: 100000р.+(-100000р.)+0=0. То же самое и для второй задачи: 100000р.+(-90000р.)+БС=0, т.е. БС=-10000р.

Примеры функции СТАВКА в Excel для инвестиций или кредитов

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

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

Как работает шаблон Excel для расчета кредита

Его работа очень проста. Просто скачайте шаблон для Excel здесь и откройте его на вашем компьютере с программой Microsoft Excel 2013.

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

  • Сумма ипотеки
  • Процентная ставка
  • Продолжительность кредита (в месяцах)
  • Сумма кредита

По мере того как вы будете вводить новые данные, расчётный лист Excel для бесплатного скачивания будет рассчитывать оставшиеся поля, которые вас интересуют: даты, квоты, статистика, проценты…

Расчет кредита в Excel

Кто как, а я считаю кредиты злом. Особенно потребительские. Кредиты для бизнеса — другое дело, а для обычных людей мышеловка»деньги за 15 минут, нужен только паспорт» срабатывает безотказно, предлагая удовольствие здесь и сейчас, а расплату за него когда-нибудь потом. И главная проблема, по-моему, даже не в грабительских процентах или в том, что это «потом» все равно когда-нибудь наступит. Кредит убивает мотивацию к росту. Зачем напрягаться, учиться, развиваться, искать дополнительные источники дохода, если можно тупо зайти в ближайший банк и там тебе за полчаса оформят кредит на кабальных условиях, попутно грамотно разведя на страхование и прочие допы?

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

Но если уж случится так, что вам или вашим близким придется влезть в это дело, то неплохо бы перед походом в банк хотя бы ориентировочно прикинуть суммы выплат по кредиту, переплату, сроки и т.д. «Помассажировать числа» заранее, как я это называю 🙂 Microsoft Excel может сильно помочь в этом вопросе.

Вариант 1. Простой кредитный калькулятор в Excel

Для быстрой прикидки кредитный калькулятор в Excel можно сделать за пару минут с помощью всего одной функции и пары простых формул. Для расчета ежемесячной выплаты по аннуитетному кредиту (т.е. кредиту, где выплаты производятся равными суммами — таких сейчас большинство) в Excel есть специальная функция ПЛТ (PMT) из категории Финансовые (Financial) . Выделяем ячейку, где хотим получить результат, жмем на кнопку fx в строке формул, находим функцию ПЛТ в списке и жмем ОК. В следующем окне нужно будет ввести аргументы для расчета:

  • Ставка — процентная ставка по кредиту в пересчете на период выплаты, т.е. на месяцы. Если годовая ставка 12%, то на один месяц должно приходиться по 1% соответственно.
  • Кпер — количество периодов, т.е. срок кредита в месяцах.
  • Пс — начальный баланс, т.е. сумма кредита.
  • Бс — конечный баланс, т.е. баланс с которым мы должны по идее прийти к концу срока. Очевидно =0, т.е. никто никому ничего не должен.
  • Тип — способ учета ежемесячных выплат. Если равен 1, то выплаты учитываются на начало месяца, если равен 0, то на конец. У нас в России абсолютное большинство банков работает по второму варианту, поэтому вводим 0.
Читайте так же:  Взять ипотечный кредит с первоначальным взносом

Также полезно будет прикинуть общий объем выплат и переплату, т.е. ту сумму, которую мы отдаем банку за временно использование его денег. Это можно сделать с помощью простых формул:

Вариант 2. Добавляем детализацию

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

Функция ОСПЛТ (PPMT) в ячейке B17 вводится по аналогии с ПЛТ в предыдущем примере:

Добавился только параметр Период с номером текущего месяца (выплаты) и закрепление знаком $ некоторых ссылок, т.к. впоследствии мы эту формулу будем копировать вниз. Функция ПРПЛТ (IPMT) для вычисления процентной части вводится аналогично. Осталось скопировать введенные формулы вниз до последнего периода кредита и добавить столбцы с простыми формулами для вычисления общей суммы ежемесячных выплат (она постоянна и равна вычисленной выше в ячейке C7) и, ради интереса, оставшейся сумме долга:

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

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

=ЕСЛИ(A18«»; текущая формула; «»)

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

Вариант 3. Досрочное погашение с уменьшением срока или выплаты

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

В случае уменьшения срока придется дополнительно с помощью функции ЕСЛИ (IF) проверять — не достигли мы нулевого баланса раньше срока:

А в случае уменьшения выплаты — заново пересчитывать ежемесячный взнос начиная со следующего после досрочной выплаты периода:

Вариант 4. Кредитный калькулятор с нерегулярными выплатами

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

  • в зеленые ячейки пользователь вводит произвольные даты платежей и их суммы
  • отрицательные суммы — наши выплаты банку, положительные — берем дополнительный кредит к уже имеющемуся
  • подсчитать точное количество дней между двумя датами (и процентов, которые на них приходятся) лучше с помощью функции ДОЛЯГОДА (YEARFRAC)

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

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

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

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

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

Примеры финансовых расчетов по функции СТАВКА в Excel

Пример 1. В МФО был взят кредит сроком на 16 дней, сумма которого составляет 1000 долларов. Сумма возврата составляет 1250 долларов. Определить годовую процентную ставку по указанным условиям займа.

Для расчета в ячейку B7 введем следующую формулу:

  • B4 – число периодов выплат (в данном случае – 1);
  • 0 – размер фиксированной выплаты (поскольку в данном примере только один период выплат, указано значение 0);
  • B5 – тело кредита;
  • B6 – сумма на момент погашения долга;
  • 0 – характеризует тип выплат, при котором выплата производится в конце периода;
  • 0,1 – предполагаемое значение процентной ставки (любое число из диапазона от 0 до 1);
  • B3/2 – коэффициент для пересчета полученного значения ставки к годовой процентной ставке.

Пи данных условиях микрокредитования сроком займа на 16 дней процентная ставка составляет 570,31% годовых! Несмотря на это, услуги по микрокредитованию сегодня продолжают набирать популярность.

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

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

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

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

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

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

Источники

Расчет процентов по займу в excel скачать
Оценка 5 проголосовавших: 1

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

Please enter your comment!
Please enter your name here