Как рассчитать кредит в excel

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

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

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

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

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

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

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

Ну а для ленивых мы, конечно же, выложим готовые файлы кредитных калькуляторов.

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

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

Мы сделаем не просто файлик с одной циферкой.

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

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

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

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

Для этого надо сделать активным окошко, в котором вы хотите видеть это значение (в нашем случае – это поле C11, на рисунке оно обведено и указано под номером 1).

Далее слева от строки формул жмём на «fx» (на рисунке эта кнопка обведена и указана под номером 2). После этих действий у вас появится такая табличка:

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

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

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

Обратите внимание на то, что мы не вводим готовые цифры в эту таблицу, а указываем координаты ячеек нашего блока с исходными данными.

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

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

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

Кстати, обратите внимание на значение функции, обозначенное на рисунке под номером 2: =ПЛТ(C5/12;C6;-C4).

Да, да, это и есть те самые координаты, которые мы вводили в таблицу, выбрав функцию «ПЛТ».

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

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

На рисунке наглядно показано, как рассчитана общая сумма выплат (обведена и указана под номером 1).

Так как она равна сумме аннуитетного платежа (ячейка C11) умноженной на общее количество месяцев кредитования (ячейка C6), то мы и вписываем в строку формул следующую формулу: =C11*C6 (на рисунке она обведена и указана под номером 2). В результате мы получили значение 56 157 рублей.

Ну и последнее значение – эффективная процентная ставка (или полная стоимость кредита).

Она рассчитывается так: общую сумму выплат (ячейка C12) делим на сумму кредита (ячейка C4), отнимаем единицу, затем делим всё это на срок кредитования в годах (ячейка C6 делённая на 12).

В строке будет такая формула: =(C12/C4-1)/(C6/12). В нашем примере эффективная процентная ставка составляет 12,3%.

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

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

Два предыдущих кредитных калькулятора очень удобны, но они выполняют краткие (общие) расчёты.

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

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

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

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

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

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

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

Источник:

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

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

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

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

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

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

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

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

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

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

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

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

Обратите внимание! Программа достаточно популярна в бухгалтерском учете.

Для того чтобы получить данные, потребуется минимум информации:

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

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

• КПЕР – размер кредита в рублях;
• СТАВКА – величина переплаты. Измеряется в процентах на определенный промежуток времени. Чаще всего – за год;
• ПС – целевое предназначение займа;
• ПЛТ – текущий взнос по кредиту.

Если ввести в программу верхние три пункта, сервис в автоматическом режиме подсчитает последний, четвертый.

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

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

Расчет кредитных выплат с помощью Excel.

Многие из нас брали кредит в банке.

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

Впрочем, поэтому я за них и не берусь.

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

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

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

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

Построение таблицы

Построить такую таблицу совсем несложно. использовать мы будем три функции:

• ПЛТ — рассчитывает сумму регулярного платежа при той или иной процентной ставке;
• ОСПЛТ — рассчитывает часть суммы регулярного платежа, которая уходит на погашение основного долга;
• ПРПЛТ — рассчитывает часть суммы регулярного платежа, которая уходит на погашение процентов.

Для начала, внесем основные данные, а именно — сумму кредита, срок кредита в годах, и процентную ставку. Например, это будет 200 тыс. руб, на 2 года под 18%.

Составим колонки таблицы расчета:

1. Месяц — порядковый номер месяца очередной выплаты;
2. Аннуитетный платеж — общая сумма ежемесячных выплат;
3. В т.ч. основная сумма — часть общей суммы, уходящей на погашение основного долга;
4. В т.ч. проценты — оставшаяся часть суммы, уходящей на погашение процентов;
5. Остаток долга на конец месяца.

За два года пройдет 24 месяца, поэтому в колонке месяц проставляем порядковые номера от 1 до 24.

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

Подводим курсор мыши к правому нижнему углу выделенной ячейки (там должен быть виден маленький черный квадратик), зажимаем левую клавишу мыши и, одновременно, клавишу Ctrl на клавиатуре, и протягиваем курсор вниз до тех пор, пока справа от курсора мыши не появиться значение «24».

Внесение формул

В верхнюю ячейку колонки «Аннуитетный платеж» вносим такую формулу: =ПЛТ($B$4/12;$B$3*12;-$B$2). Знак $ перед адресом колонки и номером строки обозначает, что при протягивании этой формулы на другие ячейки, этот адрес не будет изменяться.

• Первый аргумент функции ПЛТ обозначает процентную ставку. Мы делаем ссылку на B4. Но, поскольку ставка указывается годовая, а нам необходимо вычислить ставку за месяц, мы это значение делим на 12.
• Второй аргумент должен указывать период, на который взят кредит. В нашем случае это 5 лет, однако, поскольку мы уже заложили ежемесячный расчет, то и период необходимо указать в количестве месяцев. Поэтому ссылку на B3 мы умножаем на 12.
• Третий аргумент — сумма кредита. Ссылаемся на B2. Однако, если мы просто сошлемся на сумму кредита, то будет выпадать отрицательное значение. И это логично, ведь каждая выплата, это — убыток. Поэтому, если мы не хотим видеть отрицательные значения, то перед ссылкой поставим минус.

В верхнюю строку колонки «в т.ч. основная сумма» вносим формулу: =ОСПЛТ($B$4/12;A7;$B$3*12;-$B$2). Здесь все аналогично, кроме второго аргумента, который указывает на порядковый номер периода (в нашем случае, это порядковый номер месяца).

Так мы заполнили первую строку таблицы (последнюю колонку пока не трогаем).

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

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

При расчете долга на конец месяца, мы должны учитывать и выплату на погашение процента. Поэтому это не просто остаток долга минус текущий аннуитетный платеж, а остаток долга плюс сумма на погашение процента минус текущий аннуитетный платеж (уж простите меня за сложность). Поэтому формула, которую мы внесем, будет такой: =B2+D7-B7.

• B2 — это ссылка на сумму кредита (остаток на прошлый месяц);
• D7 — ссылка на сумму погашения процента, набежавшего к концу первого месяца;
• B7 — ссылка на сумму первой выплаты по кредиту.

Протянуть на все ячейки ниже для автозаполнения эту формулу мы не можем, т.к. адрес с остатком долга поменялся. Теперь это будет не В2, а значение в ячейке выше. Поэтому формула теперь будет следующей: =E7+D8-B8.

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

Анализ «что-если»

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

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

На пересечении наименований строк и колонок необходимо сделать ссылку на зависимую ячейку. В нашем случае это любая ячейка из колонки «Аннуитетный платеж». Поставим ссылку на В7 (появиться значение с этой ячейки — 9986). Теперь выделяем эту нашу таблицу, и нажимаем на кнопку меню «Анализ «что-если«. В выпавшем меню выбираем строку «Таблица данных«.

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

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

Источник:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На рисунке наглядно показано, как рассчитана общая сумма выплат (обведена и указана под номером 1). Так как она равна сумме аннуитетного платежа (ячейка C11) умноженной на общее количество месяцев кредитования (ячейка C6), то мы и вписываем в строку формул следующую формулу: =C11*C6 (на рисунке она обведена и указана под номером 2). В результате мы получили значение 56 157 рублей.

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

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

Скачать калькулятор расчёта аннуитетного платежа по кредиту в 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