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

Кредитный калькулятор с убывающими платежами Excel

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

Шаг 1. Создание таблицы.

В новом документе Excel создаем таблицу с кредитными условиями:

Сумма кредита;
Процентная ставка;
Срок кредитования.

А также создаем таблицу с расчетами, которые будем производить:

Месяц;
Сумма процентов;
Сумма оплаты дол;
Сумма погашения кредита;
Платеж;
Остаток кредита.

В строке месяц в примере указано 120 месяцев (10 лет), при необходимости можно делать больше

Шаг 2. Рассчитываем первую строку по условиям.

Сумму процентов рассчитываем путем умножения годовой процентной ставки по кредиту деленной на 12 ( так как рассчитываем ежемесячный платеж) на сумму кредита.

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

Ежемесячный платеж равен сумме процентов и ежемесячного погашения основного долга.

Остаток по кредиту рассчитывает вычитанием из всей суммы кредита сумму месячного погашения кредита.

Шаг 3. Расчет второй строки по условиям.

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

В данном случае используем функцию «ЕСЛИ»:

Аргументы функции:

Лог_выражение — Остаток по кредиту больше значения «0»
Значение_если_истинна — расчет суммы процентов. Умножение месячной процентной ставки на остаток по кредиту. В формуле на картинке значение «C3» необходимо заключить в знак «$», для того чтобы при перемещении формулы значение процентной ставки бралось строго с обозначенной ячейки.
Значение_если_ложь — в противном случае, значение равно «0», то есть сумма процентов равна нулевому значению.

Расчет суммы погашения для основной суммы кредита производим также с использованием логической функции «ЕСЛИ».

Аргументы функции:

Лог_выражение — Остаток по кредиту больше значения «0»
Значение_если_истинна — расчет суммы погашения основного долга. Деление всей суммы кредита на срок кредита. В формуле на картинке значение «C2» и «C4» необходимо заключить в знак «$», для того чтобы при перемещении формулы значение процентной ставки бралось строго с обозначенной ячейки.
Значение_если_ложь — в противном случае, значение равно «0», то есть погашение по кредиту уже не производится.

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

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

Шаг 4. Оформление кредитного калькулятора.

После введения формул во второй строке с расчетами переносим их на все периоды, в файле примера 120 мес. После чего всем значениям устанавливаем формат «Финансовый».

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

wpcalc.com

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

Финансирование бизнеса » Анализ кредитных предложений банков »

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

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

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

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

Умеет:

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

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

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

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

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

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

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

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

Желаем успешного использования!

biznes-kredit.info

Формула расчета ипотеки: ипотечный калькулятор в excel

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

Параметры для расчета

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

Стоимость квартиры

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

Первоначальный взнос

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

Срок

Продолжительность погашения ссуды варьируется от одного года до 30 лет, минимальная — 1-3 года. С одной стороны, увеличенная длительность погашения гарантирует меньшие платежи, чем короткий срок займа, другой — повышается процент за ссуду денег. Основные заявители ипотеки делают акцент на 10-25 лет. Вот здесь и пригодится формула расчета платежа по ипотеке.

Платежеспособность

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

Процентная ставка

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

фиксированную;
плавающую.

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

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

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

Тип платежа

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

аннуитетную;
дифференцированную.

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

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

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

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

— данное выражение подскажет сумму оставшегося тела долга после каждой уплате;
ОСХ*ПрС*x/z — функция рассчитает количество денег для уплаты в конкретном случае.

Данные формулы используют:

ОСЗ — остаток ежемесячной кредитной линии;
ПрС — общая ставка процента по ипотечному договору;
y — количество календарных месяцев до полного погашения займа;
x — количество дней текущего месяца внесения взноса;
z — общее количество дней платежа в текущем году.

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

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

Формула расчеты под аннуитет

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

где Х — сумма взноса, которую нужно вносить ежемесячно;
S — общая сумма кредитной линии;
P — 1% от годовой ставки процента;
^ — производное число к степени;
M — общий ипотечный период в месяцах.

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

Калькулятор Excel

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

Основными достоинствами калькулятора считаются:

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

К сведению клиентов — калькулятор редактируется, производит вычисления под индивидуального пользователя, настраивается под разные типы расчета.

Сделать вычисление в Экселе вы можете, если скачаете этот ипотечный калькулятор. Там же сможете посмотреть формулу.

Заключение

Рассчитать ипотечный кредит в состоянии каждый потенциальный заявитель. Для этого ему предлагается калькулятор в excel, который поможет справиться с ежемесячными погашениями. Универсальное средство учитывает не только тело кредита, но и ставку процента.

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

Ждем ваших вопросов в комментариях.

Запись на бесплатную консультацию в специальной форме в углу.

Просьба оценить пост и нажать кнопки соцсетей.

ipotekaved.ru

Аннуитетные платежи

Ануитетные платежи применяются при кредитовании. Что означает термин “аннуитетный платеж”?

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

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

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

Размер ежемесячного аннуитетного платежа = ОСЗ х ПС / [1 - (1 + ПС)-ПП]

Применяемые в формуле обозначения:ОСЗ – остаток ссудной задолженности (суммы обязательства) на расчетную дату;ПС – месячная процентная ставка, равная 1/12 от годовой процентной ставки, установленной на сумму обязательств по кредиту и оговоренной в кредитном договоре;ПП – количество Процентных периодов(месяцев), оставшихся до окончательного погашения ссудной задолженности.

Приведу условный пример расчета аннуитетного платежа по кредиту со следующими параметрами:

сумма кредита – 1000 ед.(ОСЗ- начальная величина кредита или текущая на момент расчета величина кредита)
процентная ставка по кредиту - 20 % “годовых”
месячная процентная ставка кредита – 1,67 (ПС - месячная процентная ставка, 1/12 годовой)
заданный срок кредитования – 12 месяцев (n – количество процентных периодов - месяцев)

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

Сумма кредита (ОСЗ)Срок кредитования или количество процентных периодов (n)Процентная ставка кредита “годовых” – 1000,00 ед.– 12 мес.– 20 %№ платежа (пп) ОСЗ - Задолженность по кредиту Платеж по процентам Платеж по кредиту Аннуитетный платежИтого: 12процентных периодов 0,00 111,61 1000,00 1111,61

1	1000,00	16,67	75,97	92,63
2	924,03	15,40	77,23	92,63
3	846,80	14,11	78,52	92,63
4	768,28	12,80	79,83	92,63
5	688,45	11,47	81,16	92,63
6	604,29	10,12	82,51	92,63
7	524,77	8,75	83,89	92,63
8	440,89	7,35	85,29	92,63
9	355,60	5,93	86,71	92,63
10	268,89	4,48	88,15	92,63
11	180,74,	3,01	89,62	92,63
12	91,12	1,52	91,12	92,63

По приведенному условному примеру, ежемесячный аннуитетный платеж составит 92,63 единицы.

Проценты за кредит в данной таблице можно рассчитать по формуле: Сумма месячных процентов = ОСЗ * ПС / 100 %.

Из таблицы видно, что ежемесячная сумма платежа по процентам – из месяца в месяц уменьшается, а ежемесячная сумма платежа по кредиту - увеличивается. Всего за год банку будет выплачено процентов на 111,61 условных единиц. Это на 3,27 единицы больше обычного простого начисления 20 % “годовых” на остаток кредита при ежемесячном равновеликом погашении кредита. Вывод прост, удобство проведения платежей клиентами банков оплачивается за счет, как - бы “дополнительных” процентов.

Формула расчёта аннуитетного платежа заёмщику может понадобиться не часто, ну разве что для перепроверки банка или в момент планирования кредита. Надёжнее всего расчёт составлять на калькуляторе того банка, в котором кредит планируется оформлять. Так, например, на сайтах таких крупнейших банков как Сбербанк России, ВТБ24, и Альфа-Банк, описывающих кредиты обязательно присутствует калькулятор, по которому клиент может самостоятельно составить расчёт и определить размер ежемесячного аннуитетного платежа.

Все существующие методы расчета платежей по кредитам (с условными примерами) можно посмотреть в статье Цена кредита. Как составить расчет процентов за кредит .

bankirsha.com

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

Финансирование бизнеса » Анализ кредитных предложений банков »

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

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

Также будет продемонстрирована общая сумма выплат и размер переплаты в абсолютном выражении и в процентах.

Альтернатива этому on-line инструменту - расчеты в excel у себя на компьютере. По представленной ссылке вы сможете скачать xls-файл, в котором уже есть все необходимые формулы.

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

Особое внимание рекомендуем обратить на поле "переплата". Попробуйте забить в калькулятор разные условия (от разных предложений) и оценить, где вы переплачиваете минимум, а где - максимум.

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

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

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

biznes-kredit.info

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

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

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

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

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

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

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

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

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

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

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

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

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

=ЕСЛИ(A17>=$C$7;"";A17+1)

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

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

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

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

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

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

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

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

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

Предполагается что:

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