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

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

Ипотечный кредитный калькулятор в Excel. Как правильно рассчитать кредит в Excel?

Когда вы взяли кредит, вы так или иначе думаете о досрочном погашении.
Есть люди которые платят кредит и все. А есть те, которые каждый раз смотрят, сколько осталось платить, какая сумма основного долга. Я отношу себя ко второму типу людей, я смотрю сколько сейчас сумма основного долга, пытаюсь рассчитать, сколько будет платеж, если я сделаю досрочное погашение.
На данный момент у меня есть два калькулятора кредита для своих расчетов. Оба калькулятора сделаны в Excel. Калькуляторы позволяют достаточно быстро и просто рассчитать ипотеку.
Читайте также: Как рассчитать кредит в Excel самому?

Первый кредитный калькулятор в Excel можно скачать по ссылке.
Но Excel есть не на всех компьютерах. Пользователи MAC и Linux не пользуются Excel обычно, т.к. это продукт Microsoft.
Для расчета досрочного погашения можно также воспользоваться онлайн версией калькулятора с досрочным погашением. В нем предусмотрена возможность экспорта результатов расчета в Excel.
Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=http%3A%2F%2Fmobile-testing.ru%2Fwp-content%2Fuploads%2F2012%2F07%2Fexprot

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

  1. Кредитный калькулятор в Excel практически точно считает аннуитетный график платежей и дифференцированный график платежей
  2. Изменения в графике платежей — учет досрочных погашений в уменьшение суммы основного долга
  3. Построение и расчет графика платежей в виде таблицы в Excel. Таблица графика платежей может также редактироваться
  4. При расчете учитывается високосный и невисокосный год. За счет этого сумма начисленных процентов практически совпадает с значениями, рассчитываемыми ВТБ24 и Сбербанком
  5. Точность расчетов — рассчеты совпадают с расчетами кредитного калькулятора ВТБ24 и Сбербанка
  6. Калькулятор можно редактировать под себя, задавая разные варианты расчета.
Нет тематического видео для этой статьи.
Видео (кликните для воспроизведения).
  1. Нет учета возможное изменение процентной ставки во время выплат кредита
  2. Если сделать расчет, делая досрочные платежи в изменение срока и суммы, то расчет будет неверным
  3. Если сумма процентов, начисленных за период больше суммы аннуитетного платежа, то расчет будет не верным
  4. Не рассчитывается вариант — первый платеж только проценты. В случае когда дата выдачи не совпадает с датой первого платежа, вам нужно будет заплатить проценты банку за период между датой выдачи и датой первого платежа.
  5. Расчет производится для процентой ставки с 2мя знаками после запятой.

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

Попробуйте посчитать к примеру кредит 1 млн. руб под 90 процентов на срок 30 лет.
У второго калькулятора нет данного недостатка. Однако он делит кредит на 2 периода, т.е. возможно что после деления в графике снова будут отрицательные значения. Тогда график платежей нужно делить на 3 и более периода.
Естественно сам файл также можно отредактировать под свои нужды.

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

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

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

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

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

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

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

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

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

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

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

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

  1. Заполним входные данные для расчета ежемесячных платежей по кредиту. Это сумма займа, проценты и срок. Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=https%3A%2F%2Fexceltable.com%2Fotchety%2Fimages%2Fotchety17-1
  2. Составим график погашения кредита. Пока пустой. Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=https%3A%2F%2Fexceltable.com%2Fotchety%2Fimages%2Fotchety17-2
  3. В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в Excel: =ПЛТ($B$3/12; $B$4; $B$2). Чтобы закрепить ячейки, используем абсолютные ссылки. Можно вводить в формулу непосредственно числа, а не ссылки на ячейки с данными. Тогда она примет следующий вид: =ПЛТ(18%/12; 36; 100000).
Читайте так же:  Номер телефона ощадбанка с мобильного бесплатно

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=https%3A%2F%2Fexceltable.com%2Fotchety%2Fimages%2Fotchety17-3

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

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

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

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

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

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

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

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

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

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

Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B$2. Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10 – номер текущего периода, В4 – срок кредита; Е9 – остаток по кредиту в предыдущем периоде; G9 – сумма основного долга в предыдущем периоде.

Выплата процентов: остаток по кредиту в текущем периоде умножить на месячную процентную ставку, которая разделена на 12 месяцев: =E9*($B$3/12).

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9

Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.

Внесем формулы в соответствующие столбцы. Скопируем их на всю таблицу.

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

Красная цифра – аннуитет (брали 100 000 руб.), черная – дифференцированный способ.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=http%3A%2F%2Fbiznes-kredit.info%2Fmedia%2Fexcel-prostaya-formula

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

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

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

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

Читайте так же:  Каким образом банки проверяют своих заемщиков

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

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

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

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=https%3A%2F%2Fwww.planetaexcel.ru%2Fupload%2Fmedialibrary%2F91a%2Fcredit1

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

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

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=https%3A%2F%2Fwww.planetaexcel.ru%2Fupload%2Fmedialibrary%2F35d%2Fcredit2

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

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=https%3A%2F%2Fwww.planetaexcel.ru%2Fupload%2Fmedialibrary%2F936%2Fcredit3

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

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=https%3A%2F%2Fwww.planetaexcel.ru%2Fupload%2Fmedialibrary%2F93b%2Fcredit4

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

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=https%3A%2F%2Fwww.planetaexcel.ru%2Fupload%2Fmedialibrary%2F20a%2Fcredit5

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

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

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

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

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=https%3A%2F%2Fwww.planetaexcel.ru%2Fupload%2Fmedialibrary%2Ff06%2Fcredit6

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

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

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

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=https%3A%2F%2Fwww.planetaexcel.ru%2Fupload%2Fmedialibrary%2F4b6%2Fcredit8

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

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=https%3A%2F%2Fwww.planetaexcel.ru%2Fupload%2Fmedialibrary%2F980%2Fcredit9

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

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

Читайте так же:  Можно ли открыть несколько вкладов в сбербанке

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=https%3A%2F%2Fwww.planetaexcel.ru%2Fupload%2Fmedialibrary%2F54f%2Fcredit7

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

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

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

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

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

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

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

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

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=http%3A%2F%2Fwww.temabiz.com%2Fimages%2Ffinterm-img%2Fannuitet-excel%2Fannuitetnyj-platezh-v-excel-1

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

Начнём с расчёта ежемесячной суммы аннуитетного платежа. Для этого надо сделать активным окошко, в котором вы хотите видеть это значение (в нашем случае – это поле C11, на рисунке оно обведено и указано под номером 1). Далее слева от строки формул жмём на «fx» (на рисунке эта кнопка обведена и указана под номером 2). После этих действий у вас появится такая табличка:

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=http%3A%2F%2Fwww.temabiz.com%2Fimages%2Ffinterm-img%2Fannuitet-excel%2Fannuitetnyj-platezh-v-excel-2

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

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=http%3A%2F%2Fwww.temabiz.com%2Fimages%2Ffinterm-img%2Fannuitet-excel%2Fannuitetnyj-platezh-v-excel-3

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

  • «Ставка» – годовая процентная ставка по кредиту делённая на 12.
  • «Кпер» – общий срок кредитования.
  • «Пс» – сумма кредита (указывается со знаком минус).

Обратите внимание на то, что мы не вводим готовые цифры в эту таблицу, а указываем координаты ячеек нашего блока с исходными данными. Так, в поле «Ставка» мы указываем координаты ячейки, в которой будет вписываться вручную процентная ставка (C5) и делим её на 12; в поле «Кпер» указываются координаты ячейки, в которой будет вписываться срок кредитования (C6); в поле «Пс» – координаты ячейки в которой вписывается сумма кредита (C4). Так как сумма кредита у нас указывается со знаком минус, то перед координатой (C4) мы ставим знак минус.

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

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=http%3A%2F%2Fwww.temabiz.com%2Fimages%2Ffinterm-img%2Fannuitet-excel%2Fannuitetnyj-platezh-v-excel-4

Итак, в данный момент сумма нашего аннуитетного платежа составляет 4680 руб (на рисунке он обведён и указан под номером 1). Если вы будете менять сумму кредита, процентную ставку и общий срок кредитования, то автоматически будет меняться значение вашего аннуитетного платежа.

Кстати, обратите внимание на значение функции, обозначенное на рисунке под номером 2: =ПЛТ(C5/12;C6;-C4). Да, да, это и есть те самые координаты, которые мы вводили в таблицу, выбрав функцию «ПЛТ». По сути, вы могли бы не проделывать всех тех сложных телодвижений, которые показаны на втором и третьем рисунках. Можно было просто вписать в строке формул то, что там сейчас вписано.

Зная размер аннуитетного платежа несложно посчитать остальные значения нашего расчётного блока:

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=http%3A%2F%2Fwww.temabiz.com%2Fimages%2Ffinterm-img%2Fannuitet-excel%2Fannuitetnyj-platezh-v-excel-5

На рисунке наглядно показано, как рассчитана общая сумма выплат (обведена и указана под номером 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 и приступаем к разработке нашего кредитного калькулятора!

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=http%3A%2F%2Fwww.temabiz.com%2Fimages%2Ffinterm-img%2Fannuitet-excel%2Fraschet-annuitetnogo-kredita-v-e%25D1%2585cel

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

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=http%3A%2F%2Fwww.temabiz.com%2Fimages%2Ffinterm-img%2Fannuitet-excel%2Fraschet-grafika-annuitetnyh-platezhej-v-e%25D1%2585cel-mini

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

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

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

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

Читайте так же:  Нпф сберегательный

Горячая линия

Горячая линия

+7 (499) 350-80-61

+7 (812) 309-17-81

+7 (499) 350-80-61

+7 (812) 309-17-81

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=http%3A%2F%2Fcabinet-lawyer.ru%2Fwp-content%2Fuploads%2F2017%2F08%2FKak-rasschitat-ipoteku

Существует несколько основных факторов, влияющих на величину ежемесячного платежа по ипотеке:
  • Сумма первоначального взноса. Обычно она составляет не меньше 20 %. По некоторым льготным программам она может быть установлена 10 %. О минимальном размере первоначального взноса лучше уточнить непосредственно в кредитном учреждении.
  • Наличие страхования недвижимости или жизни и здоровья заемщика, а также членов его семейства. Совместно с кредитным договором заемщик подписывает договор о страховании, это отдельный продут, от которого клиент может отказаться. Закон оставляет право за заемщиком оформлять страховку или нет.
  • Тип платежа: аннуитетный или дифференцированный. Обычно, по ипотечному кредитованию предлагается первая схема погашения, так как общая сумма займа всегда большая. При дифференцированных платежах размер первой оплаты может оказаться несоизмерим с доходами заемщика.
  • Дополнительные комиссии банка. Сюда можно включить смс – оповещение, комиссия за оплату кредита или снятие денег.
  • Присутствие программ софинансирования. Если получатель ипотеки попадает под действие государственной программы помощи, он получит существенную экономию.

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

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

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

  • Использовать специальную формулу для аннуитентного либо дифференцированного платежа. Не каждые человек пожелает этим заниматься, так как здесь требуются математические навыки.
  • Можно забить формулу в exel и подставив данные, получить расчет. Такой калькулятор также предусматривает ряд банков. Достаточно скачать его себе на рабочий стол и пользоваться.
  • Также большую популярность имеют онлайн калькулятора на сайтах банков. Такие кредитные организации, как Сбербанк и ВТБ — 24.

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

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

  • Внести данные:
  • Сумму ипотеки;
  • Размер процентов по кредиту;
  • Срок выплаты займа.

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

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

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel proxy?url=http%3A%2F%2Fcabinet-lawyer.ru%2Fwp-content%2Fuploads%2F2017%2F08%2FKak-rasschitat-ipoteku2

Расчет состоит из 2 формул:
  1. ЕПОД = СК / М:
  • «ЕПОД» – платеж основного долга;
  • «СК» – размер кредитования;
  • «М» – срок ипотеки.
  1. ЕПП = ОД * С / 12:
  • «ЕПП» – взнос за проценты по кредиту;
  • «ОД» – остаток долга;
  • «С» – процент.

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

Рассчитывается ежемесячный платеж по формуле — ЕП = К * СВ:

  • «ЕП» – ежемесячный платеж;
  • «К»– коэффициент;
  • «СВ» – возврат.

К = (С*(1+С) * М) / ((1+С)* М– 1):

  • «С» – процент кредита в месяц, рассчитанная — годовая ставка разделенная на 12 месяцев;
  • «М» – срок кредитования.

Когда используется ипотека с материнским капиталом в окошке «первоначальный взнос» необходимо внести сумму мат. капитала + сумму взноса лично от вас (если есть такая).

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

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

Видео: Пример и способ р асчета ипотечного кредитования в Excel

Изображение - Формула расчета платежа по ипотеке и калькулятор в excel 469623265
Автор статьи: Евгений Антропов

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

Обо мнеОбратная связь
Оцените статью:
Оценка 3.6 проголосовавших: 25

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

Please enter your comment!
Please enter your name here