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


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

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

Итак, любой кредит имеет 4 основных параметра:

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

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

Если вы знаете 3 показателя, то сможете подобрать четвертый.

Мы сделаем сначала калькулятор. За расчет всех четырех показателей отвечают эти функции:

- Срок – Функция ПС()

- Сумма – Функция КПЕР()

- Ставка – Функция СТАВКА()

- Ежемесячный платеж – Функция ПЛТ()

Параметры функций одни и те же – знаете три из 4-х показателей, соответствующая функция выдаст 4-й. Нагляднее смотрите первый лист файла.

Чтобы подготовить график платежей, нам понадобится дата выдачи кредита.

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

Переходим ко второму листу.

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

На второй строке –

  1. Дата – определяется как то же число, что и выдача кредита, но следующего месяца. Используем функцию ДАТА, где год и число те же, что и в предыдущем периоде, а месяц на один больше. Но есть нюанс – банк ведь не примет платеж в выходной день. Поэтому делаем корректировку числа с помощью функции ДЕНЬНЕД. Важно: дату можно корректировать вручную, на следующую дату влияния не окажет.
  2. Сумма ежемесячного платежа (которая определяется по функции ПЛТ).
  3. Сумма погашения процентов как умножение величины прошедшего периода на соответствующий процент. Используется функция ДОЛЯГОДА, чтобы убрать последствия високосности. Банки скрупулезно подходят к расчетам, поэтому период считается в днях, иначе можно было бы сделать проще – взять годовой процент, поделить на 12 месяцев и умножить на сумму.
  4. Сумма погашения основного долга – берется как разница ежемесячного платежа и суммы погашения процентов.
  5. Досрочное погашения и его дата ставятся произвольно. Единственное условие – ставится в тот период, где дата или меньше или совпадает с датой досрочного погашения.
  6. Сумма долга после платежа определяется как сумма предыдущего периода за вычетом погашения основной части и суммы досрочного погашения.

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

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

Меняем две формулы:

1) Сумму погашения основного долга. Она будет неизменной - сумма долга разделить на количество периодов (месяцев).

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

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

Какие еще можно вытащить показатели, которые важны нам, но не учитываются в доступных калькуляторах?

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

Если кому нужно более наглядно, то вот здесь видео, где этот файл и формировался.

Эксель Практик

«Глаза боятся, а руки делают»

P.S. Я веду рассылку, где идет немного другой формат подачи информации, поэтому если интересно - подписывайтесь на рассылку, форма в правой части страницы. Тем более, что сразу после подписки вы получите бонус - мой видеокурс о 10 полезных инструментах Excel, которые не всякому известны.

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

excelpractic.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

wpcalc.com

Что выгоднее лизинг или кредит: сравнительный калькулятор Excel

Rating: 5.0. From 2 votes.

Please wait...

 

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

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

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

 

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

Вы можете скачать расчетную модель в формате MS Excel нажатием на СКАЧАТЬ ФАЙЛ. Модель дает возможность анализа эффективности каждого из способов приобретения имущества для организаций, использующих общую и упрощенную системы налогообложения, а также с учетом применения таких специальных налогов как налог на имущество и транспортный налог.

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

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

 

Общая информация и понятия кредита и лизинг

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

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

  1. Компания, предоставляющая данный сервис (лизингодатель), покупает дорогостоящую технику или оборудование у производителя. Также предметом отношений лизинга могут выступать торговые площади, здания и проч., кроме участков и природных объектов.
  2. Предприниматель либо представитель организации обращается в лизинговую компанию, собрав необходимые документы. Обязательным условием является первый взнос (аванс), эта цифра составит от 5 до 20% от полной стоимости.
  3. Сделка завершается подписанием арендного договора с указанием процента вознаграждения и срока действия. Предмет договора передается в пользование арендатору на оговоренный период времени.

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

 

Достоинства лизинга перед кредитом

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

Условия предоставления

Кредитная деятельность банковской системы регламентируется условиями предоставления крупных займов предпринимателям и ООО. Так, чтобы взять в кредит сумму, требуемую для осуществления закупки имущества, необходимо подать в банк пакет документов, балансовые показатели, причем копии должны быть заверены у нотариуса. В итоге только сбор данных и нотариальное заверение займет минимум неделю. Рассмотрение документации банком занимает 2-3 недели, в течение которых тщательно изучаются экономические показатели, официальные отчеты и бумаги.

Лизинговые компании также требуют подтвердить платежеспособность, однако здесь к клиенту относятся более лояльно. Лизингодатель рассматривает не только “сухие” документы, при изучении данных клиента учитываются все положительные моменты, а в том числе неофициальный доход. Это связано с тем, что предмет лизинга всегда ликвиден для страховой компании, т.к. обычно она специализируется на приобретении таких объектов. Это означает, что лизинговая компания меньше опасается дефолта заемщика.

Подтверждение передачи имущества в финансовую аренду осуществляется в срок до 1 недели в 90 % сделок с малым процентом отказов.

 

Залоговое обеспечение

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

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

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

 

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

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

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

  • страхование предмета договора, рисков заёмщика, жизни и трудоспособности ИП и т.д., данные платежи составят 1-2% от суммы кредита в месяц;
  • единоразовая комиссия за предоставление кредита, которая входит в общую сумму и погашается с “телом” займа.

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

Для банка такой подход – практически исключен.

 

Комплексное обслуживание

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

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

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

 

Ускоренная амортизация и снижение налога на прибыль

Срок амортизации грузового и специализированного транспорта с объемом двигателя свыше 3,5 л при лизинге по сравнению с условиями стандартных программ кредитования снижается с 5-7 лет до 21 мес. и 7-10 лет до 29 мес. соответственно.

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

Что касается кредита, затраты на его погашение частично покрываются начислением амортизации. В отличие от лизинга, платежи по кредитам нельзя в полном объеме отнести к затратам, часть процентов переносится на прибыль. Данная сумма рассчитывается как разница между банковским процентом и ставкой рефинансирования ЦБ РФ, умноженной на максимальный коэффициент 1,8 (в иностранной валюте – 0,8).

 

Уплата НДС при лизинге – налоговая оптимизация на ОСН

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

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

 

Скидки от поставщика

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

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

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

 

Недостатки лизинга

Стоимость финансирования

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

 

Начисление НДС на УСН

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

 

Предмет лизинга – собственность лизинговой компании

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

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

 

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

  Это может быть полезным:

calculator24.ru

Кредитный калькулятор в Excel | Компьютер для чайников

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

Расчет суммы ежемесячных выплат

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

  1. Откройте программу Microsoft Excel и введите в столбик А описание исходных данных: сумма кредита, годовая ставка, срок кредита (в месяцах), а также строку результата – сумма ежемесячных выплат. В столбик В мы будем заносить соответствующие значения.
  2. Ставим курсор на ячейку результата (в нашем примере это ячейка В5) и в меню «Вставка» находим пункт «функция». Откроется окно Мастера функций
  3. Выберите категорию «финансовые» и в окне функций выделите функцию ПЛТ
  4. Далее необходимо указать ячейки, которые будут служить аргументами функции. Первый аргумент Ставка. Чтобы не вводить название ячейки вручную, окно мастера можно свернуть кнопкой в конце строки.
  5. Сверните окно и выделите ячейку, в которой будете указывать ставку (в нашем примере это ячейка В2).
  6. Нажмите на кнопку в конце строки аргумента, чтобы вернуться в окно мастера.
  7. Так как ставку вы буде указывать годовую, а результат выплат нужно получить помесячный, то в строке аргумента заданное значение нужно разделить на 12. Дополните строку аргумента «/12»
  8. Следующий аргумент – количество периодов. Таким же образом сверните окно и укажите ячейку значения «срок кредита» (у нас это ячейка В3)
  9. Аргумент ПС означает сумму кредита, сверните окно мастера и укажите соответствующую ячейку (в нашем примере В1)
  10. Аргумент БС выражает конечный баланс. Очевидно, что наша задача погасить кредит полностью, поэтому введите в строку значение аргумента «0». Аргумент Тип указывает на способ учета зачисления выплат – в начале периода (месяца) или в конце. Большинство кредитов выдается по второму типу учета, поэтому определите значение аргумента равным «0»
  11. Нажмите кнопку ОК и формула готова. Введите исходные значения, и в результативной ячейке вы найдете сумму ежемесячных выплат. Она будет со знаком минус. Это означает, что деньги вам нужно отдавать…

Дополнительные расчеты

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

  1. Всего выплат по кредиту (за весь период) – значение выражается формулой, умножьте сумму ежемесячных выплат на количество месяцев. В нашем примере В5*В3.
  2. Сумма переплаты – сколько денег вы заплатите за пользование кредитными средствами. Для этого нужно сложить сумму всех выплат (эта величина у нас со знаком минус!) и сумму кредита. Формула в нашем случае: В6+В1
  3. Просчитывать разные варианты и сравнивать предложения по кредитованию с таким калькулятором очень легко.

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

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

Загрузка... Опубликовано: 12.08.2015

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

dontfear.ru


Смотрите также