Если вы хотите расчитать платежи по кредиту (аннуитетные или дифференцированные), переплату и график платежей по месяцам в excel, то скачайте следующие xls-файлы:
Альтернатива использованию MS Excel — кредитный калькулятор on-line (функционал практически такой же, ничего качать на компьютер не нужно: все операции выполняются на нашем сайте).
Расскажем о файлах подробнее: дадим мини-инструкции.
- Считать оба вида платежей.
- Показывать полный график платежей.
- Разбивать сумму выплаты на «погашение долга» и «погашение процентов».
- Учитывать досрочные возвраты (отдельно для уменьшения срока и уменьшения размера выплат).
Пользоваться файлом довольно удобно: вбиваете значения в верхние четыре поля (сумма, процентная ставка, срок в месяцах, дата получения — последнее нужно для определения точного графика) и умное детище Билла Гейтса тут же заполняет все графы таблицы актуальной информацией.
Вы и сами может сконстурировать подобный расчет — подробно это описано в статье про расчет по формуле аннуитета. Ничего сложного нет, но для удобства мы подготовили специальный файл.
Содержит всего одну функциональную ячейку:
Поменяйте значения на свои (вместо 14 — свою ставку, вместо 12 во втором случае — свой срок кредита в месяцах, вместо 100000 — свою сумму займа).
После смены данных достаточно нажать на «Энтер», чтобы получить результат:
Напоминаем, ссылки на оба xls-файла представлены в начале страницы.
Часто прописанные в кредитном договоре величины и числа не вызывают доверия. Проверить их можно с помощью программы MO Excel, применив, в зависимости от вопроса, одну из финансовых функций.
Как предполагается, по аннуитетной схеме клиенту необходимо вносить для погашения задолженности равные суммы в течение срока договора с кредитной организацией. Для того чтобы рассчитать такие платежи, в программе есть специальная функция – ПЛТ. Ее использование требует создание новой таблицы и ввода данных в любой ячейке поля.
Например, был выдан кредит на сумму 100 тысяч рублей под 15% годовых на два года. Соответственно, в ячейке необходимо отразить выражение:
В скобках после наименования данные вводятся в определенном порядке:
Плата процентов по кредиту
- процентная ставка
- продолжительность обязательства по уплате в месяцах
- полученная в кредит сумма
Знак минуса перед суммой означает, что данное число представляет собой обязательство. Если это единичный расчет, ставить его необязательно. Но если число в дальнейшем используется в других формулах, он важен. Процентная ставка может быть отражена десятичной дробью (15% годовых = 0,0125).
Расчет таких платежей позволит проверить, насколько правильно сотрудниками банка определен ежемесячный платеж клиента.
При выборе дифференцированного варианта возврата денег банку клиент теряет намного меньше, так как проценты с каждым разом уменьшаются. Банки же такой вариант предлагают реже. Но и для лица этот вариант менее удобен, так как регулярно нужно рассчитывать новую сумму к оплате.
В основу снова ляжет пример. Клиент взял в банке 180 тысяч рублей на 3 года. Ставка – 13% годовых. Погашение предполагается каждый месяц, в конце периода.
Для расчетов необходимо узнать ежемесячную базовую сумму, подлежащую выплате. Каждый месяц клиент обязан возвращать банку равную сумму – часть долга. В рассматриваемом случае это 180000 / 3 / 12 = 5000 рублей. Каждый месяц на остаток начисляются прописанные в договоре проценты. Соответственно, уменьшается остаток – меньше становится и сумма, начисляемая банком.
Расчет основывается на функции ПРОЦПЛАТ. Через точку с запятой в ней обозначаются четыре показателя:
Расчет процентов по кредиту
- ставка за период (13%/12)
- номер периода, за который будет считаться величина
- число периодов начисления суммы долга к уплате
- приведенная стоимость (сумма кредита)
Функция ПРОЦПЛАТ совпадает по аргументам с предыдущей формулой, однако не имеет с ней ничего схожего, подменять их друг другом нельзя. В англоязычной версии наименование функции – ISPMT, аргументы в ней такие же.
Вычисление сумм, перечисляемых на погашение процентов, возможно с использованием функции ПРПЛТ. Ее аргументы не отличаются от необходимых в ОСПЛТ:
- Ставка в процентах за период (годовые, поделенные на 12).
- Период – от первого до какого-либо нужного периода.
- Общее количество периодов платежей по кредиту.
- Приведенная стоимость, которая равна совокупности будущих платежей на данный момент.
- Требуемое значение будущей стоимости, то есть остатка после последних выплат (если же этот аргумент вообще не указывать, то будет предполагаться, что он приравнен к нулю).
- Тип – срок выплаты (0 – конец периода, 1 – начало периода).
Такие расчеты подходят для аннуитетных платежей, когда не известно тело кредита. Определить процент при дифференцированной схеме начисления процентов можно, узнав, какие денежные средства направляются ежемесячно на погашение задолженности.
От суммы ежемесячного платежа необходимо отнять то самое тело кредита, направляемое на погашение непосредственно занятой у банка суммы. Разница и будет процентами, постепенно снижающимися при дифференцированном порядке их начисления.
Формула для определения ставки по кредиту рекомендована Центробанком России. Ее возможно посчитать, если использовать формулу, указанную в письме ФНС.
Для понимания вопроса необходимы многочисленные данные:
- дата произведения платежа
- день первого платежа, которая является и датой передачи денег клиенту
- установленное количество обязательных платежей
- сумма определенного платежа. Платежи, направленные на получение и расходование средств, обозначаются различными знаками
- стоимость кредита, которая отражена в годовых
В процессе определения стоимости кредита (т.е. связанные с ним суммы за выдачу кредита или первоначальное рассмотрение заявки) важно отразить все дополнительные расходы, например, за выдачу, чтобы сумма стала максимально корректной.
Полную стоимость при исчислении составляют:
Самостоятельный расчет выплат
- бесспорные платежи по договору, связанные с заключением и уменьшением суммы кредитного договора
- проценты
- комиссии и сборы за удовлетворение после рассмотрения сотрудником заявки, заключение договора, открытие и обслуживание счета, выдачу денег и пр.
- комиссии за обслуживание лица – операционное, расчетное
- при безналичном расчете – комиссии за выпуск и обслуживание кредитки
С целью расчетов установлена новая формула, которая уже давно используется за рубежом для установления эффективной годовой ставки.
В подходящую формулу входят:
- показатель величины займа (для денег по кредитному договору – с отрицательным знаком)
- количество сумм, то есть платежей
- периоды и их количество
- ставка периода в формате десятичной дроби
Законодательные рекомендации в данном аспекте окончательно не сформированы. Однако функция уже действует.
Таким образом, финансовые функции Excel позволяют проверить отсутствие переплат ввиду ошибок и огрех в договоре, случайных или намеренных. Каждый шаблон не требует тяжело получаемых данных, поэтому удобен в применении.
Заметили ошибку? Выделите ее и нажмите Ctrl+Enter, чтобы сообщить нам.
Кредиты прочно вошли в нашу жизнь, и мы уже не можем представить себя без них. Мы занимаем деньги в банках, чтобы купить автомобиль, квартиру, бытовую технику. Кредиты стремительно набирают популярность. К слову, один из самых популярных и доступных кредитов сегодня — предложение от Альфа-Банка . То есть, благодаря кредитам, мы делаем нашу жизнь лучше. Всё хорошо, только нужно держать выплаты по кредитам под контролем. А для этого, отлично подходит Эксель.
Расчеты по выплатам зависят от выбранной системы кредитования. В большинстве случаев, чтобы просчитать выплаты по кредиту, достаточно владеть элементарными знаниями о формулах и пользоваться правилами арифметики.
Например, у нас кредит на 10 000 у.е. со среднегодовой ставкой 6% и сроком 36 месяцев. Вычислим ежемесячную выплату тела кредита : =10 000/36 . Получим 277,78 у.е .
Выплаты процентов по кредиту просчитываем от остатка по телу кредита на данный период. Первый платёж будет от полной суммы в 10 тыс, второй – от величины 10 000 у.е. – 277,78 у.е. Т.е. платёж по процентам во втором месяце составит: =(10 000,00 – 277,78) * 6% / 12 . В этой формуле мы добавили деление на двенадцать, поскольку 6% — среднегодовая ставка, а ежемесячная – в 12 раз меньше. Результат вычисления – 48,61 у.е.
Полный платёж составит 277,78 у.е. + 48,61 у.е. = 326,39 грн. Таким образом, можно просчитать все 36 платежей.
Однако, банки предлагают и систему с фиксированной ежемесячной платой. Такой кредит просчитать сложнее. Для этого в Эксель несколько функций.
Чтобы посчитать ежемесячные выплаты по телу кредита – используйте функцию =ОСПЛТ(Ставка; Период; Кпер; Пс; Бс; Тип) . Аргументы функции:
Вот какой результат даёт эта функция для рассмотренного выше примера:
Функция ОСПЛТ в Excel
Основную часть платежа посчитали, теперь проценты. Для этого используем функцию =ПРПЛТ(Ставка; Период; Кпер; Пс; Бс; Тип) . Аргументы у функции те же, что и в предыдущей функции. Результат вычисления для нашего примера такой:
Функция ПРПЛТ в Эксель
Чтобы получить полный ежемесячный платеж, нужно сложить результаты функций ОСПЛТ и ПРПЛТ.
Чтобы узнать, какая процентная ставка по вашему кредиту – используйте функцию =СТАВКА(Кпер; Плт; Пс; Бс; Тип; Оценка) . Помимо уже известных Вам аргументов, здесь применяются:
- Плт – размер периодической платы (тело кредита плюс процент)
- Оценка – необязательный аргумент – начальная оценка ожидаемого результата. Обычно его не задают
Если нужно получить годовую ставку – умножьте результат функции на количество периодов в году. Например, на 12 месяцев, 4 квартала, 2 полугодия и т.п. Попробуем просчитать процентную ставку для нашего примера:
Функция СТАВКА в Excel
Если нужно узнать сколько периодов понадобится, чтобы погасить кредит – используйте функцию =КПЕР(Ставка; Плт; Пс; Бс; Тип) .
Давайте опять применим эту формулу к нашему примеру:
Функция КПЕР в Эксель
А если вы вдруг забыли, на какую сумму взяли кредит – применяем функцию =ПС(Кпер; Ставка; Плт; Бс; Тип). И снова попробуем вычислить для нашего примера:
Функция ПС в Эксель
С помощью описанных выше методик, Вы можете оценить риски до оформления кредита, просчитать переплату. Многие оценивают различные варианты кредита для определения наиболее выгодного варианта. Для этого можно воспользоваться таблицами подстановки, перебрать в одной таблице несколько вариантов займа и выбрать оптимальный.
Ну что, теперь Вы вооружены знаниями, чтобы сделать правильный выбор. Это Ваш огромный успех, ведь их практическую ценность можно легко измерить и выразить в деньгах!
Пользуйтесь, а я жду Вас снова на страницах моего блога – officelegko.com. Кстати, в следующей статье мы будем считать прибыль от депозита!
Через какую функцию просчитать сумму фиксированных платежей при сумме займа 100000, прцентной ставке 20% годовых и количестве периодов 24,53 месяца
Функция ОСПЛТ даёт разные платежи в каждом месяце
Тогда у меня ещё один вопрос можно ли вычислить количество периодов по выплате займа через подбор параметров, зная сумму займа(100000 руб), процентной ставке(20% годовых) и сумме ежемесячных платежей(5000). Как не производил подбор мешает дело. Через формулу всё просто =КПЕР(20%/12;-5000;100000). Через подбор вроде можно вычислить процентную ставку, а вот как сроки выплаты по кредиту не получается
Олег, здравствуйте. Отвечаю на два Ваших вопроса. В Экселе нет функции расчета постоянной выплаты, т.к. она и без того просто вычисляется: Плт = Пс * (Ставка + Ставка / (1 + Ставка)^Кпер — 1). Для Вашего примера и периода 24 мес. расчет будет таким: 100000*(1,67% + 1,67% / (1 + 1,67%)^24 — 1) = 5089,58. Здесь 1,67% = 20%/12. Правда, этой схемой пользуются редко, т.к. кредит получается дороже.
В связке с этой формулой работает и подбор параметра, в результате получаются указанные Вами 24,53 мес.
Добрый день!
как в excel рассчитать платеж по кредиту с разной процентной ставкой
если первый год 8%
остальные 19 лет 12%
Здравствуйте, Наталья. Видимо, речь идет о кредите с переменной ежемесячной платой (не аннуитет). Тогда Считаем так: = /240 + * ЕСЛИ(