Эксель ипотека как составить таблицу

Создание и форматирование таблиц

Создайте и отформатируйте таблицу для наглядной группировки и анализа данных.

Примечание: Таблицы Excel не следует путать с таблицами данных (эта функция входит в набор команд «Анализ «что если»» на вкладке Данные в группе Прогноз). Дополнительные сведения см. в статье Введение в анализ «что если».

Проверьте, как это работает!

1. Выберите ячейку данных.
2. На вкладке Главная выберите команду Форматировать как таблицу.
3. Выберите стиль таблицы.
4. В диалоговом окне Создание таблицы укажите диапазон ячеек.
5. Если таблица содержит заголовки, установите соответствующий флажок.
6. Нажмите кнопку ОК.

1. Вставьте таблицу на лист. Дополнительные сведения см. в статье Общие сведения о таблицах Excel.
2. Выберите ячейку данных.
3. На вкладке Главная выберите команду Форматировать как таблицу.
4. Выберите стиль таблицы.
5. В диалоговом окне Создание таблицы укажите диапазон ячеек.
6. Если таблица содержит заголовки, установите соответствующий флажок.
7. Нажмите кнопку ОК.

Чтобы добавить пустую таблицу, выберите ячейки, которые нужно включить в таблицу, и последовательно выберите пункты Вставка > Таблица.

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

1. Выберите ячейки с данными.
2. Нажмите Главная >Таблица >Форматировать как таблицу.
3. Если не установить флажок Таблица с заголовками, то Excel в Интернетедобавляет заголовки с именами по умолчанию, такими как «Столбец1» и «Столбец2» над данными. Чтобы переименовать заголовок, дважды щелкните на нем и введите новое имя.

Примечание: В Excel в Интернете невозможно изменить форматирование таблицы по умолчанию.

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

Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.п.) и заемщики (предприниматели, компании, частные лица и т.д.).

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

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

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

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

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

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

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

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

Формула коэффициента аннуитета:

К = (i * (1 + i)^n) / ((1+i)^n-1)

• где i – процентная ставка за месяц, результат деления годовой ставки на 12;
• n – срок кредита в месяцах.

В программе Excel существует специальная функция, которая считает аннуитетные платежи. Это ПЛТ:

1. Заполним входные данные для расчета ежемесячных платежей по кредиту. Это сумма займа, проценты и срок.
2. Составим график погашения кредита. Пока пустой.
3. В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в Excel: =ПЛТ($B$3/12; $B$4; $B$2). Чтобы закрепить ячейки, используем абсолютные ссылки. Можно вводить в формулу непосредственно числа, а не ссылки на ячейки с данными. Тогда она примет следующий вид: =ПЛТ(18%/12; 36; 100000).

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

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

Дифференцированный способ оплаты предполагает, что:

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

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

ДП = ОСЗ / (ПП + ОСЗ * ПС)

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

Составим график погашения предыдущего кредита по дифференцированной схеме.

Входные данные те же:

Составим график погашения займа:

Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B$2. Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10 – номер текущего периода, В4 – срок кредита; Е9 – остаток по кредиту в предыдущем периоде; G9 – сумма основного долга в предыдущем периоде.

Выплата процентов: остаток по кредиту в текущем периоде умножить на месячную процентную ставку, которая разделена на 12 месяцев: =E9*($B$3/12).

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9 Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.

Внесем формулы в соответствующие столбцы. Скопируем их на всю таблицу.

Сравним переплату при аннуитетной и дифференцированной схеме погашения кредита:

Красная цифра – аннуитет (брали 100 000 руб.), черная – дифференцированный способ.

Формула расчета процентов по кредиту в Excel

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

Рассчитаем ежемесячную процентную ставку и платежи по кредиту:

Заполним таблицу вида:

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

Сумма основного долга = аннуитетный платеж – проценты.

Сумма процентов = остаток долга * месячную процентную ставку.

Остаток основного долга = остаток предыдущего периода – сумму основного долга в предыдущем периоде.

Опираясь на таблицу ежемесячных платежей, рассчитаем эффективную процентную ставку:

• взяли кредит 500 000 руб.;
• вернули в банк – 684 881,67 руб. (сумма всех платежей по кредиту);
• переплата составила 184 881, 67 руб.;
• процентная ставка – 184 881, 67 / 500 000 * 100, или 37%.
• Безобидная комиссия в 1 % обошлась кредитополучателю очень дорого.

Эффективная процентная ставка кредита без комиссии составит 13%. Подсчет ведется по той же схеме.

Расчет полной стоимости кредита в Excel

Согласно Закону о потребительском кредите для расчета полной стоимости кредита (ПСК) теперь применяется новая формула. ПСК определяется в процентах с точностью до третьего знака после запятой по следующей формуле:

• ПСК = i * ЧБП * 100;
• где i – процентная ставка базового периода;
• ЧБП – число базовых периодов в календарном году.

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

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

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

Далее находим ЧБП: 365 / 28 = 13.

Теперь можно найти процентную ставку базового периода:

У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8

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

ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

График платежей по ипотеке – как посмотреть, расчет в excel, с досрочным погашением

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

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

График внесения платежей: знакомство

Как выглядит график платежей по ипотеке? Стандартный вид — таблица из пяти колонок, в которых указана информация:

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

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

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

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

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

Расчет ипотеки с графиком платежей проводит кредитующий банк. Для этого:

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

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

График погашения платежей по ипотеке: сколько и когда платить

Узнать график платежей по ипотеке можно двумя способами:

• открыв договор с банком, где таблица транзакций оформлена приложением и скреплена подписями сторон;
• в Личном кабинете банка-онлайн.

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

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

• входят в Личный кабинет онлайн-банка;
• выбирают вкладку Кредиты, если соглашений несколько — устанавливают указатель на интересующем, ипотечном;
• на мониторе по умолчанию появляется детальная информация по займу: сумма, сроки, схема начисления — дифференцированная или аннуитетные, очередной взнос и когда вносить, оставшийся к погашению долг.

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

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

Платежеспособность: как определить самостоятельно

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

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

В некоторых банках онлайн график платежей по ипотеке реализован достаточно гибко:

• ВТБ (скачать общие условия), сервис позволяет провести исчисления двух видов — от стоимости недвижимости и от семейного дохода;
• Сбербанк России (скачать общий договор) — калькулятор ипотеки с графиком платежей существует как самостоятельная программа, без привязки к сайту. Находят через любой интернет-поисковик, задав соответствующий запрос;
• Газпромбанк (скачать общую информацию) — кроме обычного калькулятора, есть специальная программа-калькулятор, разработанная для кредитования по направлению реновации жилья.

Как правильно рассчитать ипотеку на калькуляторе с графиком платежей? Использовать сервис просто — вносят основные параметры ипотечного кредита:

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

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

ВТБ-банк: изучаем онлайн-калькулятор

Открыв на сайте банка соответствующую вкладку меню, можно рассчитать ипотеку ВТБ с графиком платежей. На какие моменты пользователю следует обратить внимание:

• требуется установить регион расположения приобретаемой недвижимости;
• выбрать цель кредитования — под новое строительство или покупку готового жилья;
• вносят параметры будущего обязательства – стоимость квартиры или дома, первичный взнос, ежемесячный доход;
• устанавливают «флажки», чтобы отметить преимущества кредитуемого объекта и особенности заемщика. Например, если покупаемая квартира превышает 100 кв.м или получатель относится к категории люди дела, предполагается оформление по двум документам, заем планируется для клиента-зарплатника;
• отметка-флажок возле пункта материнский капитал внесет корректировки в график платежей по ипотеке ВТБ. Подробнее о том, Как рассчитать ипотеку с материнским капиталом в ВТБ на калькуляторе онлайн — читайте в другой статье.

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

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

Распечатать документ можно нажатием одной кнопки. Выглядит расчет просто — на печать выводится текст, который уже есть на экране. Калькулятор и график платежей по ипотеке ВТБ учет маткапитала проводит упрощенно — господдержка вычитается из доступного займа.

Рекомендуемая статья: Зачем делать оценку квартиры при ипотеке

Обзор ипотечных калькуляторов

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

Сайт Альфа-Банка (скачать общие условия ипотеки) также предлагает воспользоваться калькулятором для определения кредитоспособности. Для этого вносят информацию о цели кредитования, сроках, объемах планируемого кредита и первоначального взноса. Программа учитывает материнский капитал и участие заемщика в зарплатном проекте. Результат расчетов пользователь видит на мониторе, вывод графика платежей по ипотеке Альфа Банка на печать не предлагают. Как рассчитать ипотеку Альфа-банка на калькуляторе онлайн

Банк Открытие (скачать общие условия ипотеки) рекомендует оценить кредитоспособность с помощью собственного калькулятора. Структура и возможности программного сервиса аналогичны представленным ранее. Расчет проводится от стоимости недвижимости и имеющихся средств на первоначальный взнос. Выведение на печать графика платежей по ипотеке в Открытие также не предусмотрено. Условия ипотеки банка Открытие

Военная ипотека: калькулятор и план выплат

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

При нажатии на Посмотреть график платежей отражается график

Этот калькулятор позволяет внести сведения о досрочном гашении. Нажмите на Как погасить досрочно справа снизу в графике и выберите вариант (снижение платежа, сокращение срока), месяц погашения и сумму. График изменится следующим образом:

Но во всех банковских калькуляторах есть такие возможности, поэтому стоит отметить: если отдельного расчета для военнослужащих вашим банком не предусмотрено. Тогда где найти, и как узнать график платежей по военной ипотеке? Ответ простой — на сайте Росвоенипотеки размещен калькулятор. Параметры кредита зависят от участия военнослужащего в НИС и накоплений, аккумулированных на его лицевом счете (117-ФЗ от 20 августа 2004 г, ст. 4 (скачать)). Для работы сервиса потребуется внести:

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

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

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

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

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

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

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

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

Рекомендуемая статья: Вопросы при оформлении ипотеки

Считаем в Эксель

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

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

Для расчета графика платежей по ипотеке в Эксель воспользуемся простым примером: 15 мая 2020-го планируется выдача займа молодой семье на 850 тыс. руб., на 20 лет под 6,0% годовых. Вносим информацию:

Сумма кредита, рос.руб.	850000
Ставка, % в год	6,0
Срок, месяцы	240
Дата выдачи	15.05.2020

Сформируется расчет по двум схемам — с аннуитетными платежами и дифференцированными. По первому варианту придется погашать ежемесячно 6089,67 руб., по второму — стартовый платеж 7791,67 руб., с постепенным уменьшением за счет процентов.

Досрочное погашение: используем Эксель-таблицу

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

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

В нашем примере можно более подробно разобрать график платежей по ипотеке с досрочным погашением. Пусть к январю 2021 будет оформлен материнский капитал на первого ребенка в объеме 466 617 руб. Тогда следующий платеж составит:

• по аннуитету — 2694,99 руб., но сроки останутся без изменений — 20 лет;
• по дифференцированной схеме — ежемесячное погашение основного долга останется таким же, но при этом график покажет уменьшение %-ого платежа и кредитных сроков.

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

Заключение

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

Вычисление аннуитетного платежа в Microsoft Excel

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

Расчет оплаты

Прежде всего, нужно сказать, что существует два вида кредитных платежей:

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

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

Этап 1: расчет ежемесячного взноса

Для расчета ежемесячного взноса при использовании аннуитетной схемы в Экселе существует специальная функция – ПЛТ. Она относится к категории финансовых операторов. Формула этой функции выглядит следующим образом:

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

Аргумент «Ставка» указывает на процентную ставку за конкретный период. Если, например, используется годовая ставка, но платеж по займу производится ежемесячно, то годовую ставку нужно разделить на 12 и полученный результат использовать в качестве аргумента. Если применяется ежеквартальный вид оплаты, то в этом случае годовую ставку нужно разделить на 4 и т.д.

«Кпер» обозначает общее количество периодов выплат по кредиту. То есть, если заём берется на один год с ежемесячной оплатой, то число периодов считается 12, если на два года, то число периодов – 24. Если кредит берется на два года с ежеквартальной оплатой, то число периодов равно 8.

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

«Бс» — это будущая стоимость. Эта величина, которую будет составлять тело займа на момент завершения кредитного договора. В большинстве случаев данный аргумент равен «0», так как заемщик на конец срока кредитования должен полностью рассчитаться с кредитором. Указанный аргумент не является обязательным. Поэтому, если он опускается, то считается равным нулю.

Аргумент «Тип» определяет время расчета: в конце или в начале периода. В первом случае он принимает значение «0», а во втором – «1». Большинство банковских учреждений используют именно вариант с оплатой в конце периода. Этот аргумент тоже является необязательным, и если его опустить считается, что он равен нулю.

Теперь настало время перейти к конкретному примеру расчета ежемесячного взноса при помощи функции ПЛТ. Для расчета используем таблицу с исходными данными, где указана процентная ставка по кредиту (12%), величина займа (500000 рублей) и срок кредита (24 месяца). При этом оплата производится ежемесячно в конце каждого периода.

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

В поле «Кпер» устанавливается срок кредитования. Он у нас равен 24 месяцам. Можно занести в поле число 24 вручную, но мы, как и в предыдущем случае, указываем ссылку на месторасположение данного показателя в исходной таблице.

В поле «Пс» указывается первоначальная величина займа. Она равна 500000 рублей. Как и в предыдущих случаях, указываем ссылку на элемент листа, в котором содержится данный показатель.

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

В поле «Тип» указываем в начале или в конце месяца производится оплата. У нас, как и в большинстве случаев, она производится в конце месяца. Поэтому устанавливаем число «0». Как и в случае с предыдущим аргументом, в данное поле можно ничего не вводить, тогда программа по умолчанию будет считать, что в нем расположено значение равное нулю.

Этап 2: детализация платежей

Для определения величины оплаты по телу займа используем функцию ОСПЛТ, которая как раз предназначена для этих целей. Устанавливаем курсор в ячейку, которая находится в строке «1» и в столбце «Выплата по телу кредита». Жмем на кнопку «Вставить функцию».

Как видим, аргументы данной функции почти полностью совпадают с аргументами оператора ПЛТ, только вместо необязательного аргумента «Тип» добавлен обязательный аргумент «Период». Он указывает на номер периода выплаты, а в нашем конкретном случае на номер месяца.

Мы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12742 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

[expert_bq для кредита, оформленного сроком на 1 год со ставкой 23 и ежемесячными платежами аргументы ставка и кпер функции ОСПЛТ должны быть заданы как 0,23 12 и 1 12 соответственно. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] В итоге по расчетам нужно будет вернуть банкирам от полумиллиона 684881,67 руб. Соответственно разница – 184 881, 67 руб. Тогда эффективная процентная ставка окажется на уровне 37%. Если же вести расчет без учета однопроцентной комиссии, то выйдем на эффективную процентную ставку в 13%.

Как рассчитать кредит в Excel — Как рассчитать ежемесячный платеж по кредиту? Формула расчета платежей в excel. Онлайн калькулятор. Как в офисе.

Чтобы не утруждать себя расчетами вручную, попробуйте сделать это при помощи таблицы Excel. Там есть специальная функция под названием ПЛТ. Для расчетов следует создать новую таблицу и ввести строку в любой ячейке. Если вам выдали кредит в сумме 30000 руб., под 18% годовых на 36 месяцев, необходимо ввести в ячейку вот такое выражение.