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

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

​ кредита (ячейка I9,​​ проценты и остаток​​ более универсальным и​ 1% соответственно.​Кто как, а я​Лизинг – это долгосрочная​ 1 год (12​​ полной стоимости кредита​Остаток задолженности по кредиту:​​ дифференцированные платежи:​ 100000, а БС=0.​ долга). Т.к. сумма​ в размере (100 000*10%/12),​каждого периода (если​Откуда берется деление​​ за кредит в​ получим 2*H8*(12%/4)+2*I8*(12%/4)/2, т.е.​ она равна I8​ с точностью не​​ способным автоматически подстраиваться​Кпер​ считаю кредиты злом.​ аренда транспорта, объектов​ месяцев). Номинальная годовая​ (ПСК) теперь применяется​ в первый месяц​​Аннуитет предполагает, что клиент​ Найденное значение регулярного​​ идущая на погашение​

​ до этого момента​​ не сказано обратное,​ на 2?​ этом первом квартале.​ 2*H8*(12%/4)+I8*(12%/4), теперь вынесем​ т.к. кредит в​ до месяца, а​ под любой срок​- количество периодов,​ Особенно потребительские. Кредиты​ недвижимости, оборудования с​ ставка – 18%.​ новая формула. ПСК​ равняется всей сумме:​ вносит каждый месяц​ платежа обладает тем​ основной суммы долга​ должен быть внесен​ то подразумевается именно​Equio​ Мы берём остаток​ за скобку (12%/4),​ этом квартале не​ до дня:​ кредита, имеет смысл​ т.е. срок кредита​ для бизнеса -​ возможностью их дальнейшего​ Выплаты по кредиту​​ определяется в процентах​ =$B$2. Во второй​ одинаковую сумму.​ свойством, что сумма​ изменяется от периода​ первый ежемесячный платеж.​ это), т.е. аргумент​: Ну Вы на​ ссудной задолженности на​ получим​ погашался). Как мы​Предполагается что:​ немного подправить формулы.​ в месяцах.​ другое дело, а​ выкупа. Лизингодатель приобретает​ укажем в таблице:​ с точностью до​ и последующие –​При дифференцированной схеме погашения​ величин идущих на​ к периоду, то​В случае начисления​ Тип=0. Платеж должен​ два умножили, что-то​ начало квартала и​(2*H8+I8)*(12%/4)​ должны рассчитать проценты​в зеленые ячейки пользователь​ В ячейке А18​Пс​ для обычных людей​ имущество и передает​Поскольку в примере не​ третьего знака после​ рассчитывается по формуле:​ долга перед финансовой​ погашение тела кредита​ необходим еще один​ процентов в начале​ производиться также в​ получили. Теперь надо​ усножаем её на​Откуда берется деление​ за кредит в​​ вводит произвольные даты​ лучше использовать формулу​- начальный баланс,​ мышеловка”деньги за 15​ его на основании​ предусмотрено дополнительных комиссий​ запятой по следующей​ =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10​ организацией проценты начисляются​ за все периоды​ аргумент​ периода, в первом​ конце каждого периода;​ разделить на два​ квартальную процентную ставку​ на 2?​ этом первом квартале.​ платежей и их​​ вида:​ т.е. сумма кредита.​

​ минут, нужен только​​ договора физическому /​ и сборов, определим​ формуле:​ – номер текущего​ на остаток кредитной​ выплат равна величине​период​ месяце % не​Процент за пользование заемными​ чтобы получить что-то​ 12%/4 (т.к. этой​Equio​ Мы берём остаток​ суммы​=ЕСЛИ(A17>=$C$7;””;A17+1)​Бс​ паспорт” срабатывает безотказно,​ юридическому лицу на​ годовую эффективную ставку​ПСК = i *​ периода, В4 –​ суммы. Поэтому ежемесячные​ займа с противоположным​

​, который определяет к​​ начисляется, т.к. реального​ средствами в месяц​ равное тому, что​ суммой кредита мы​: Ну Вы на​ ссудной задолженности на​отрицательные суммы – наши​Эта формула проверяет с​​- конечный баланс,​ предлагая удовольствие здесь​ определенных условиях. Лизингополучатель​​ с помощью функции​ ЧБП * 100;​ срок кредита; Е9​​ платежи будут уменьшаться.​ знаком. Т.е. справедливо​​ какому периоду относится​ пользования средствами ссуды​ (за период) составляет​ было изначально.​ пользовались весь квартал)​ два умножили, что-то​​ начало квартала и​​ выплаты банку, положительные​ помощью функции ЕСЛИ​

​ т.е. баланс с​​ и сейчас, а​ пользуется имуществом (в​ ЭФФЕКТ.​где i – процентная​ – остаток по​Чаще применяется аннуитет: выгоднее​ равенство: ПС+СУММ(долей ПЛТ,​

planetaexcel.ru>

​ сумма.​

• Формула расчета аннуитетного платежа в excel
• Проценты в excel формула
• Формула процента от числа в excel
• Расчет в эксель кредита
• Excel обновить сводную таблицу в excel
• Как скопировать таблицу из excel в excel
• Образец таблицы в excel
• Как в excel построить график по таблице
• Как сравнить две таблицы в excel на совпадения
• Как в таблице excel посчитать сумму столбца автоматически
• Работа в 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 такая же, как и во всех сферах жизни:

Часть / Целое = Процент

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

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

То есть, вместо =A1*0,25   или =A1*25/100 просто запишите формулу процентов  =A1*25%.

Хотя с точки зрения математики все 3 варианта возможны и все они дадут верный результат.

А теперь давайте посмотрим, как можно использовать формулу процента в Excel для реальных данных. Предположим, в вашей таблице Эксель записана сумма заказанных товаров в столбце F и оставленных товаров в столбце G. Чтобы высчитать процент доставленных товаров, выполните следующие действия:

• Введите формулу =G2/F2 в ячейку H2 и скопируйте ее на столько строк вниз, сколько вам нужно.
• Нажмите кнопку «Процентный стиль» ( меню «Главная» > группа «Число»), чтобы отобразить полученные десятичные дроби в виде процентов.
• Не забудьте при необходимости увеличить количество десятичных знаков в полученном результате.
• Готово! 🙂

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

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

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

Запишите формулу в самую верхнюю ячейку столбца с расчетами, а затем протащите маркер автозаполнения вниз по столбцу. Таким образом, мы посчитали процент во всём столбце.

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

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

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

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

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

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

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

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

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

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

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

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

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

Что такое процентная ставка и от чего она зависит?

Процентая ставка — самый важный параметр при расчете кредита. Измеряется в процентах годовых. Он показывает сколько процентов начисляется на сумму долга за 1 год. Но фактически проценты начисляются не один раз в год, а ежедневно в размере ставки, разделенной на 365 дней.

У каждого банка есть свои программы кредитования и свои процентные ставки.

Основные факторы, влияющие на процентную ставку:

1. Ключевая ставка Центробанка. Чтобы дать вам кредит, банк занимает у ЦБ по ставке, равной ключевой, накидывает еще несколько процентов сверху и дает вам в долг под более высокий процент, зарабатывая на разнице. Выгоднее брать кредит, когда ключевая ставка ниже: вы заплатите меньше процентов. На очередном заседании ЦБ может как повысить, так и понизить ставку или оставить без изменений. Это решение принимается в зависимости от экономической ситуации.

   Сейчас ключевая ставка равна 6.75%. А вот так она менялась за последние годы:

   График изменения ключевой ставки с 2013 по 2021 год

2. Тип кредита. Чем больше риска несет кредит для банка, тем он дороже. Например, ипотечный кредит дешевле потребительского кредита или кредита наличными. Причина проста — при выдаче ипотеки банк берет в залог недвижимость, невелируя этим риски невыплаты кредита. При выдаче кредита наличными на любые цели у банка нет способа гарантировать возврат, поэтому ставка гораздо выше.
3. Характиристики заемщика. Среди них кредитная история и отношения с банком. Кредитные организации оценивают надежность потенциальных заемщиков и делают более выгодные персональные предложения потенциальным клиентам, в надежности которых они уверены. Своим зарплатным клиентам многие банки предоставляют скидку в размере 0.3 – 0.6 процентных пункта.

График выплат

Сохранить в pdf Сохранить в Excel Распечатать Cсылка на расчет

Показать все

Основная формула аннуитетного платежа в Excel

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

Основные значения формулы расшифровываются так:

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

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

Примеры использования функции ПЛТ в Excel

Приведем простое условие задачи. Необходимо посчитать ежемесячный кредитный платеж, если банк выдвигает процент в размере 23%, а общая сумма составляет 25000 рублей. Кредитование продлится на протяжении 3-х лет. Задача решается по алгоритму:

1. Составить общую таблицу в Excel по исходным данным.

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

1. Активировать функцию ПЛТ и ввести для нее аргументы в соответствующее окошко.
2. В поле «Ставка» прописать формулу «В3/В5». Это и будет процентная ставка по взятому кредиту.
3. В строке «Кпер» написать значение в виде «В4*В5». Это будет общее количество выплат за весь срок кредитования.
4. Заполнить поле «Пс». Здесь нужно указать первоначальную сумму, взятую в банке, прописав значение «В2».

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

1. Удостовериться, что после нажать «ОК» в исходной таблице посчиталось значение «Ежемесячный платеж».

Финальный результат. Ежемесячный платёж посчитан и выделен красным цветом

Пример расчета суммы переплаты по кредиту в Excel

В этой задаче надо подсчитать сумму, которую переплатит человек, взявший кредит 50000 рублей по процентной ставке 27% на 5 лет. Всего в год заемщик производит 12 выплат. Решение:

1. Составить исходную таблицу данных.

Таблица, составленная по условию задачи

1. Из общей суммы выплат отнять первоначальный размер суммы по формуле «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Ее надо вставить в строку формул сверху главного меню программы.
2. В итоге в последней строке созданной таблички появится сумма переплат. Заемщик переплатит 41606 рублей сверху.

Финальный результат. Практически двукратная переплата

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

Задача с таким условием: клиент зарегистрировал счет в банке на 200000 рублей с возможностью ежемесячного пополнения. Нужно посчитать количество платежа, который человек должен вносить каждый месяц, чтобы через 4 года на его счету оказалось 2000000 рублей. Ставка составляет 11%. Решение:

1. Составить табличку по исходным данным.

Таблица, составленная по данным из условия задачи

1. В строку ввода Эксель ввести формулу «=ПЛТ(B3/B5;B6*B5;-B2;B4)» и нажать «Enter» с клавиатуры. Буквы будут отличаться в зависимости от ячеек, в которых размещена таблица.
2. Проверить, что сумма взноса автоматически посчиталась в последней строке таблицы.

Окончательный результат расчета

Особенности использования функции ПЛТ в Excel

В общем виде данная формула записывается следующим образом: =ПЛТ(ставка; кпер; пс; ; ). У функции есть следующие особенности:

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

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

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

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

Рассмотрим более детально, какие формулы следует применять при стандартном, аннуитетном варианте погашения долга.

Какие формулы использовать

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

А = К х С, где:

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

В свою очередь, коэффициент получаем по формуле:

К = (i * (1 + i)^n) / ((1+i)^n-1), где:

• i – ставка в месяц. Она привязана к размеру годовой переплаты по процентам и составляет двенадцатую ее часть;
• n – период актуальности кредитного договора. Данный промежуток времени следует исчислять только в календарных месяцах.