Бесплатная горячая линия

8 800 700-88-16
Главная - Другое - Таблица эксель расчет по кредитам за год

Таблица эксель расчет по кредитам за год

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

Можно сделать расчет аннуитетного платежа и в Excel ,для чего здесь имеется специальная функция – ПЛТ. Нажав на значок «fx» который находится слева от поисковика, вы получите список функций в новом окне. Среди них будет и ПЛТ. Выберите ее и нажмите «ОК».

Перед вами появится другое окно, где Вам нужно будет заполнить следующие строки: «Ставка» – 22%/12; «Кпер» -24; «Пс» – -20000 (не забудьте, что число ставится с минусом!). Под строками сразу же появится число 1 037,56 – сумма, которую нужно будет вносить ежемесячно. Когда нажмете «ОК», то это же число появится в ячейке таблицы.

Скачать готовый Excel файл для расчета аннуитетного платежаДанные можно вносить не в строки окна, а в скобки в строке поисковика (возле «fx» – там появится ПЛТ(). В эти скобки нужно вставить следующее: 22%/12;24; – 20000, обязательно в таком же порядке и через точку с запятой, после чего, нажав «Энтер», вы получите тот же результат (он также появится в одной из ячеек таблицы) – 1 037,56 руб.Как видите, вычислить размер аннуитетного платежа самостоятельно – совсем несложно.

Но, отправляясь в банк, чтобы оформить кредит, нужно все-таки быть готовым к тому, что ежемесячный взнос будет несколько больше – за счет дополнительных комиссий.Источник: https://101.credit/articles/kredity/annuity/

Сложные проценты по кредиту

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

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

Эту систему можно встретить редко, но о ее существовании все же стоит знать.

Если кредитное учреждение хочет применить эту схему, с ним можно спорить. Ведь закон позволяет начислять только на основную сумму долга (ст. 317.1, 809 и 819 ГК). Схема «проценты на проценты» совсем не выгодна клиентам банка.
  • Рассчитайте выгодный график погашения кредита.
  • Как планировать платежи по кредиту с помощью Excel
  • Скачать шаблон в Excel

Посмотрим, как рассчитать по формуле сложные проценты по кредиту. Условно ее можно выразить так: Долг = Первоначальная сумма × (1 + Ставка за расчетный период/100%)^Количество расчетных периодов

Задача1

Определить величину ежемесячных равновеликих выплат по ссуде, размер которой составляет 100 000 руб., а процентная ставка составляет 10% годовых.

Ссуда взята на срок 5 лет. Разбираемся, какая информация содержится в задаче:

  • В конце срока задолженность должна быть равна 0 (БС=0).
  • Заемщик ежемесячно должен делать платеж банку. Этот платеж включает: сумму в счет погашения части ссуды и сумму для оплаты начисленных за прошедший период процентов на остаток ссуды ;
  • Как следствие п.3 и п.1, сумма, уплачиваемая в счет погашения основной суммы ссуды, увеличивается от месяца к месяцу.
  • Процент за пользование заемными средствами в месяц (за период) составляет 10%/12 (ставка);
  • Проценты начисляются в конце каждого периода (если не сказано обратное, то подразумевается именно это), т.е. аргумент Тип=0. Платеж должен производиться также в конце каждого периода;
  • Заемщик должен сделать 60 равновеликих платежей (12 мес. в году*5 лет), т.е. всего 60 периодов (Кпер);
  • Сумма для оплаты начисленных за прошедший период процентов уменьшается каждый период, т.к. проценты начисляются только на непогашенную часть ссуды;
  • Сумма ежемесячного платежа (аннуитета) постоянна и не меняется на протяжении всего срока, так же как и процентная ставка. Также не изменяется порядок платежей – 1 раз в месяц;

Расчет суммы выплаты по ссуде за один период, произведем сначала с помощью финансовой функции MS EXCEL ПЛТ() .

Примечание . Обзор всех функций аннуитета в статье . Эта функция имеет такой синтаксис: ПЛТ(ставка; кпер; пс; [бс]; [тип]) PMT(rate, nper, pv, [fv], [type]) – английский вариант. Примечание : Функция ПЛТ() входит в надстройку «Пакет анализа». Если данная функция недоступна или возвращает ошибку #ИМЯ?, то включите или установите и загрузите эту надстройку (в MS EXCEL 2007/2010 надстройка «Пакет анализа» включена по умолчанию).
Если данная функция недоступна или возвращает ошибку #ИМЯ?, то включите или установите и загрузите эту надстройку (в MS EXCEL 2007/2010 надстройка «Пакет анализа» включена по умолчанию). Первый аргумент – Ставка. Это процентная ставка именно за период, т.е.

в нашем случае за месяц. Ставка =10%/12 (в году 12 месяцев).

Кпер – общее число периодов платежей по аннуитету, т.е.

60 (12 мес. в году*5 лет) Пс — всех денежных потоков аннуитета.

В нашем случае, это сумма ссуды, т.е. 100 000. Бс — всех денежных потоков аннуитета в конце срока (по истечении числа периодов Кпер). В нашем случае Бс = 0, т.к. ссуда в конце срока должна быть полностью погашена.

Если этот параметр опущен, то он считается =0. Тип — число 0 или 1, обозначающее, когда должна производиться выплата.

0 – в конце периода, 1 – в начале. Если этот параметр опущен, то он считается =0 (наш случай).

Примечание : В нашем случае проценты начисляются в конце периода.

Например, по истечении первого месяца начисляется процент за пользование ссудой в размере (100 000*10%/12), до этого момента должен быть внесен первый ежемесячный платеж.

В случае начисления процентов в начале периода, в первом месяце % не начисляется, т.к. реального пользования средствами ссуды не было (грубо говоря % должен быть начислен за 0 дней пользования ссудой), а весь первый ежемесячный платеж идет в погашение ссуды (основной суммы долга). Решение1 Итак, ежемесячный платеж может быть вычислен по формуле =ПЛТ(10%/12; 5*12; 100 000; 0; 0) , результат -2 107,14р.

Решение1 Итак, ежемесячный платеж может быть вычислен по формуле =ПЛТ(10%/12; 5*12; 100 000; 0; 0) , результат -2 107,14р. Знак минус показывает, что мы имеем разнонаправленные денежные потоки: +100000 – это деньги, которые банк дал нам, -2107,14 – это деньги, которые мы возвращаем банку .

Альтернативная формула для расчета платежа (общий случай): =-(Пс*ставка*(1+ ставка)^ Кпер /((1+ ставка)^ Кпер -1)+ ставка /((1+ ставка)^ Кпер -1)* Бс)*ЕСЛИ(Тип;1/(ставка +1);1) Если процентная ставка = 0, то формула упростится до =(Пс + Бс)/Кпер Если Тип=0 (выплата в конце периода) и БС =0, то Формула 2 также упрощается: Вышеуказанную формулу часто называют формулой аннуитета (аннуитетного платежа) и записывают в виде А=К*S, где А — это аннуитетный платеж (т.е. ПЛТ), К — это коэффициент аннуитета, а S — это сумма кредита (т.е. ПС). K=-i/(1-(1+i)^(-n)) или K=(-i*(1+i)^n)/(((1+i)^n)-1), где i=ставка за период (т.е.

Ставка), n — количество периодов (т.е.

Кпер). Напоминаем, что выражение для K справедливо только при БС=0 (полное погашение кредита за число периодов Кпер) и Тип=0 (начисление процентов в конце периода).

Рекомендуем прочесть:  Ук сменилась долг остался

Таблица ежемесячных платежей Составим таблицу ежемесячных платежей для вышерассмотренной задачи. Для вычисления ежемесячных сумм идущих на погашение основной суммы долга используется функция ОСПЛТ(ставка; период; кпер; пс; [бс]; [тип]) практически с теми же аргументами, что и ПЛТ() (подробнее см.

статью ). Т.к. сумма идущая на погашение основной суммы долга изменяется от периода к периоду, то необходим еще один аргумент период , который определяет к какому периоду относится сумма. Для вычисления ежемесячных сумм идущих на погашение процентов за ссуду используется функция ПРПЛТ (ставка; период; кпер; пс; [бс]; [тип]) с теми же аргументами, что и ОСПЛТ() (подробнее см. статью ). Примечание . Для определения суммы переплаты по кредиту (общей суммы выплаченных процентов) используйте функцию ОБЩПЛАТ() , см.

. Конечно, для составления таблицы ежемесячных платежей можно воспользоваться либо ПРПЛТ() или ОСПЛТ() , т.к.

эти функции связаны и в любой период: ПЛТ= ОСПЛТ + ПРПЛТ Соотношение выплат основной суммы долга и начисленных процентов хорошо демонстрирует график, приведенный в файле примера .

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

График приведен в столбцах K:P файла примера лист Аннуитет (ПЛТ) , а также на листе Аннуитет (без ПЛТ) .

Также тело кредита на начало и конец периода можно рассчитать с помощью функции ПС и БС (см. файл примера лист Аннуитет (ПЛТ), столбцы H:I ).

Полезное по теме

  1. Правильный расчет ипотеки с материнским капиталом
  2. Калькулятор депозитов онлайн.
  3. Методика расчета страховки по ипотеке.

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

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

Пожалуйста оцените эту статью, поставьте оценку ниже.

Непонятно 12 Сойдет 8 Годнота 40

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

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

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

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

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

Также полезно будет прикинуть общий объем выплат и переплату, т.е.

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

В эксель надо подсчитать ежемесячную сумму платежа по кредиту. Как это сделать?

Анонимный вопрос · 17 декабря 20189,1 KИнтересно1 · 693ПодписатьсяЗдесь нужно учитывать тип платежа — аннуитетный или диффернецированный.

При первом варианте нужно величину займа умножить на коэффициент платежа и получим нужную нам сумму. При дифференцированном формула немного сложнее, нужно остаток займа разделить на сумму оставшихся платежей и произведения остатка займа и процентной ставки в месяц. Звучит сложновато, более подробно можно ознакомиться здесь 2 · Хороший ответ · 3,9 KКомментировать ответ.Ещё 2 ответа · 3,3 KМы смотрим на бизнес через цифры и знаем, как получить максимум пользы.

· ПодписатьсяДля подобных расчетов в Excel есть финансовые функции.

В частности, размер платежа можно подсчитать функцией ПЛТ. Как ею пользоваться — читайте .

3 · Хороший ответ · 1,8 KКомментировать ответ. · 3Просто чудиковатый парень.

Сисадмин, линуксоид, велосипедист и кое-что ещё ^-^ПодписатьсяПривет!

Я не специалист по эксели и кредитам. )) Но вот стандартная процедура:Выбираем ячейку, где будет результат: Ставим там знак «=»Затем, выбираем ячейки, которые хотим сложить или умножить. Их можно выбирать по одной мышкой, с зажатым ctrl или перетягивать область мышкой: Между номером каждой ячейки проставляем нужный знак плюс/минуc/умножение/деление.

Нажи. Читать далееХороший ответ3 · 1,3 KКомментировать ответ.Вы знаете ответ на этот вопрос?Поделитесь своим опытом и знаниямиЧитайте также · 3,3 KМы смотрим на бизнес через цифры и знаем, как получить максимум пользы. · Если надо просто быстро увидеть сумму по всему столбцу, то можно кликнуть на букву заголовка столбца (тогда он весь выделится) и внизу, в строке состояния (серая строка в нижней части окна программы) будет отображена сумма. Если надо вывести результат в ячейку, то пишете в пустой ячейке «=СУММ», открываете скобку, далее мышкой выделяете ячейки, сумму которых надо подсчитать, закрываете скобку и жмете Enter74 · Хороший ответ11 · 70,0 K · 3,3 KМы смотрим на бизнес через цифры и знаем, как получить максимум пользы.

· Используйте функцию ОСПЛТ для расчета размера основного долга и ПРПЛТ для расчета процентов. Например, если срок кредита указан в A1, ставка в A2, сумма в A3, а в A5 — номер периода оплаты, то формулы будут такие:=ОСПЛТ(A2;A5;A1;A3)=ПРПЛТ(A2;A5;A1;A3)Протяните формулы на все номера периодов — получите таблицу с графиком погашения2 · Хороший ответ · 927 · 7,8 KEngineer — programmer ⚡⚡ Разбираюсь в компьютерах, технике, электронике, интернете и. · Как правило, банки сразу указывают % по кредиту в годовых.

К примеру, кредит под 20% означает, что за 1 год использования денег банка, клиент уплачивает 20% от суммы кредита в год. Чтобы узнать ставку в месяц по кредиту, достаточно 20% разделить на число месяцев или 20/12 = 1,66% от суммы кредита в месяц.Это самый простой пример.

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

суммы, которую вы еще должны банку.58 · Хороший ответ29 · 76,4 K · 27Кредитные споры. Бесплатная консультация. Пишите https://vk.com/dapravo Добрый день!

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

Банк это делает в соответствии с условиями кредитного договора. Вы не можете изменить кредитный договор в одностороннем порядке. 2 · Хороший ответ · 488 · 16,4 KФизик по образованию, тестировщик по воле случая, блоггер по настроению, хоккеис.При прочих равных (например, при той же годовой ставке) справедливо простое заключение: чем короче срок, тем меньше переплата из-за начисленных процентов.

Если вы выплачиваете ипотеку за 15 лет, вы переплатите меньше, чем если ваша ипотека оформлена на 20 лет.Если же платёж уменьшить, то в этом случае не только увеличится общий срок выплаты (что очевидно), но и текущий остаток, на который и начисляются проценты, всегда будет выше, чем если бы платёж был больше — просто меньше бы оставалось в остатке, а значит и меньше начислялась бы сумма процентов.Но у этой проблемы есть и другая сторона — текущая платёжеспособность.

Например, может быть так, что вы точно знаете, что вы легко сможете отдавать десять тысяч в месяц на погашение ипотеки, но уже точно не сможете «потянуть» платежи больше двадцати. И тогда именно эта «вилка» (в купе с процентной ставкой, да) и определят в итоге размер вашей итоговой переплаты, потому что срок погашения будет целиком зависеть от того, какой платёж будет для вас приемлемым.6 · Хороший ответ · 21,4 K

Простая табличка для расчета платежей по кредиту

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

Теперь вам не придется идти в банк, чтобы: — посчитать платежи по кредиту после досрочных погашений; — узнать переплату по кредиту; — определить, в какой срок вы закроете «кредитку», если будете платить по 5/7/10 тысяч в месяц; — посмотреть, выгодно ли рефинансировать кредит; — выяснить, сколько вы сэкономите, если погасите долг «прямо сейчас»и т.д.Небольшая табличка в Экселе, пара несложных формуд – и вуаля, вы великолепны!

Готов примерный график погашения по любому кредиту или карте.Приступим?Шаг первый.

Делаем таблицу. Открываем Excel. «Обзываем» несколько столбцов:- Остаток долга на начало месяца:- Проценты за месяц;- Комиссии (если есть, конечно, и при этом они включаются в платеж. Разовые комиссии сюда не пишем – они только помешают расчетам):- Платеж по кредиту.Отмеряем вниз столько ячеек, сколько будет платежей (например, 12, как у меня в образце).Отдельную ячейку выделяем для процентной ставки по кредиту.Должно получиться что-то вроде этого:Получилось?

Отлично. Продолжим.Шаг второй.

Вносим формулы и значения.Нам известны только сумма кредита, комиссии и процентная ставка.

Например, мы берем 100 тысяч рублей под 30% годовых на 12 месяцев, комиссия составляет 1% в месяц от начальной суммы кредита (вообще-то эти самые комиссии давным-давно запретили, но это вовсе не значит, что все кредиторы прекратили эту порочную практику, их взимать. Так что создаем универсальную таблицу.

Нет комиссий? Отлично, просто не делайте этот столбец!)Итак, заполняем:- сумма на начало 1 месяца = сумме кредита (100 000);- процентная ставка – 30%/12 (ставка годовая, а начисляются проценты каждый месяц, а значит, всего 1/12 от общей ставки и на остаток долга!

Не упустите этот момент!);- проценты за 1 месяц – сумма на начало 1 месяца, умноженная на процентную ставку;- комиссия за 1 месяц – 1%, умноженный на начальную сумму кредита;- сумма на начало 2 месяца = сумма на начало 1-го + проценты за 1 месяц + комиссия за 1 месяц + платеж за месяц.- в графе «платеж за месяц» во втором месяце ставим абсолютную ссылку на эту же графу, но первый месяц. И тоже протягиваем до конца – это чтобы платежи в каждом месяце были равными.Обратите внимание!

Чтобы не вносить формулу в каждую ячейку отдельно (в последующие месяцы), а просто «протянуть» ее, мы ставим абсолютные ссылки на процентную ставку (в столбце «проценты») и на начальную сумму кредита (в столбце «комиссия»).Получаются вот такие формулы:Шаг третий.

Дозаполняем таблицу.- протягиваем до конца формулы в столбцах «сумма», «проценты» и «комиссия».- «Сумму» тянем на 1 лишний месяц вперед – отсюда мы будем «плясать» при определении ежемесячного взноса.Получилось вот что:Шаг четвертый. Считаем ежемесячный платеж.А теперь самое главное! Та самая «хитрая» формула, которая позволит нам, зная сумму и ставку по кредиту, узнать платеж.

Называется она «подбор параметра» или «что, если» и находится, как правило, в закладке «данные» главного меню Экселя. Если не можете найти эту возможность в вашей версии, воспользуйтесь справкой (вызывается через F1).

Гарантирую, она найдется.Итак, устанавливаем задание для Экселя – нам нужно, чтобы остаток по кредиту на начало 13 месяца (то есть после выплаты кредита полностью) был равен нулю, и для этого нам нужно подобрать значение ежемесячного платежа.Вот так:Вуаля! При наших исходных данных сумма платежа составляет 10748,71 рублей.

Если мы будем платить именно столько в месяц, то кредит «закроется» через год.Если вы освоите эту функцию, то жизнь с кредитами заметно упростится – ведь теперь вы сами в любой момент сможете рассчитать, по сколько платить и что вам за это будет. Я рекомендую вести такие таблички по всем оформленным кредитам и картам, чтобы ничего не упустить.Надеюсь, эта информация оказалась вам полезной. Хотите продолжения? Ставьте «палец вверх» и подписывайтесь, я постараюсь в будущем рассказать еще больше нужного о кредитах, картах и финансах.Искренне ваша, Lonika

Формула определения суммы процентов по взятому кредиту

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

Ее аргументы не отличаются от необходимых в ОСПЛТ:

  • Тип – срок выплаты (0 – конец периода, 1 – начало периода).
  • за период (годовые, поделенные на 12).
  • Период – от первого до какого-либо нужного периода.
  • Приведенная стоимость, которая равна совокупности будущих платежей на данный момент.
  • Общее количество периодов платежей по кредиту.
  • Требуемое значение будущей стоимости, то есть остатка после последних выплат (если же этот аргумент вообще не указывать, то будет предполагаться, что он приравнен к нулю).

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

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

Выводы.

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

Последние новости по теме статьи

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

Поэтому, для вас работают бесплатные эксперты-консультанты!

Расскажите о вашей проблеме, и мы поможем ее решить! Задайте вопрос прямо сейчас!

  • Анонимно
  • Профессионально

Задайте вопрос нашему юристу!

Расскажите о вашей проблеме и мы поможем ее решить!

+