Excel формулы расчета аннуитетных платежей

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

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

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

Для того чтоб получить данные, будет нужно минимум инфы:

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

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

• КПЕР – размер кредита в рублях;
• СТАВКА – величина переплаты. Измеряется в процентах на определенный просвет времени. Почаще всего – за год;
• ПС – целевое назначение займа;
• ПЛТ – текущий взнос по кредиту.

Если ввести в программку верхние три пт, сервис в автоматическом режиме подсчитает крайний, 4-ый.

График выплат

Сохранить в pdf Сохранить в Excel Распечатать Cсылка на расчет

Дата платежа	Остаток задолженности, руб.	Начисленные %, руб.	Платеж в основной долг, руб.	Сумма платежа, руб.
08.10.2018	310 000,00	3 821,92	3 552,96	7 374,88
08.11.2018	306 447,04	3 904,05	3 470,83	7 374,88
08.12.2018	302 976,21	3 735,32	3 639,56	7 374,88
08.01.2019	299 336,66	3 813,47	3 561,41	7 374,88
08.02.2019	295 775,25	3 768,10	3 606,78	7 374,88
08.03.2019	292 168,46	3 361,94	4 012,94	7 374,88
08.04.2019	288 155,52	3 671,02	3 703,86	7 374,88
08.05.2019	284 451,67	3 506,94	3 867,94	7 374,88
08.06.2019	280 583,73	3 574,56	3 800,32	7 374,88
08.07.2019	276 783,41	3 412,40	3 962,48	7 374,88
08.08.2019	272 820,93	3 475,66	3 899,21	7 374,88
08.09.2019	268 921,71	3 425,99	3 948,89	7 374,88
08.10.2019	264 972,83	3 266,79	4 108,09	7 374,88
08.11.2019	260 864,74	3 323,35	4 051,53	7 374,88
08.12.2019	256 813,20	3 166,19	4 208,69	7 374,88
08.01.2020	252 604,51	3 218,11	4 156,77	7 374,88
08.02.2020	248 447,75	3 165,16	4 209,72	7 374,88
08.03.2020	244 238,03	2 910,78	4 464,10	7 374,88
08.04.2020	239 773,93	3 054,65	4 320,22	7 374,88
08.05.2020	235 453,71	2 902,85	4 472,02	7 374,88
08.06.2020	230 981,68	2 942,64	4 432,23	7 374,88
08.07.2020	226 549,45	2 793,08	4 581,80	7 374,88
08.08.2020	221 967,64	2 827,81	4 547,07	7 374,88
08.09.2020	217 420,57	2 769,88	4 605,00	7 374,88
08.10.2020	212 815,57	2 623,75	4 751,12	7 374,88
08.11.2020	208 064,45	2 650,68	4 724,19	7 374,88
08.12.2020	203 340,25	2 506,93	4 867,94	7 374,88
08.01.2021	198 472,31	2 528,48	4 846,40	7 374,88
08.02.2021	193 625,91	2 466,74	4 908,14	7 374,88
08.03.2021	188 717,78	2 171,55	5 203,33	7 374,88
08.04.2021	183 514,44	2 337,92	5 036,95	7 374,88
08.05.2021	178 477,49	2 200,41	5 174,47	7 374,88
08.06.2021	173 303,02	2 207,83	5 167,05	7 374,88
08.07.2021	168 135,97	2 072,91	5 301,97	7 374,88
08.08.2021	162 834,00	2 074,46	5 300,42	7 374,88
08.09.2021	157 533,59	2 006,93	5 367,94	7 374,88
08.10.2021	152 165,64	1 876,01	5 498,86	7 374,88
08.11.2021	146 666,78	1 868,49	5 506,38	7 374,88
08.12.2021	141 160,40	1 740,33	5 634,54	7 374,88
08.01.2022	135 525,85	1 726,56	5 648,32	7 374,88
08.02.2022	129 877,54	1 654,60	5 720,27	7 374,88
08.03.2022	124 157,26	1 428,66	5 946,22	7 374,88
08.04.2022	118 211,04	1 505,98	5 868,90	7 374,88
08.05.2022	112 342,14	1 385,04	5 989,84	7 374,88
08.06.2022	106 352,30	1 354,90	6 019,98	7 374,88
08.07.2022	100 332,32	1 236,97	6 137,90	7 374,88
08.08.2022	94 194,42	1 200,01	6 174,87	7 374,88
08.09.2022	88 019,55	1 121,34	6 253,53	7 374,88
08.10.2022	81 766,02	1 008,07	6 366,80	7 374,88
08.11.2022	75 399,21	960,57	6 414,31	7 374,88
08.12.2022	68 984,90	850,50	6 524,38	7 374,88
08.01.2023	62 460,52	795,73	6 579,15	7 374,88
08.02.2023	55 881,37	711,91	6 662,96	7 374,88
08.03.2023	49 218,41	566,35	6 808,53	7 374,88
08.04.2023	42 409,88	540,29	6 834,59	7 374,88
08.05.2023	35 575,29	438,60	6 936,28	7 374,88
08.06.2023	28 639,01	364,85	7 010,03	7 374,88
08.07.2023	21 628,99	266,66	7 108,22	7 374,88
08.08.2023	14 520,77	184,99	7 189,89	7 374,88
08.09.2023	7 330,88	93,39	7 330,88	7 424,27

Показать все

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

Пример 2. Вкладчику предложили сделать депозит в банк под 16% годовых (номинальная ставка), при этом расчете производится с использованием сложных процентов (эффективная ставка). По условиям договора вкладчик сможет снять только полученные проценты. Определить сумму к получению, если размер депозита – 1 млн. рублей, капитализация – ежемесячная.

Исходные данные:

Формула для расчета:

=ЭФФЕКТ(B3;B2)*B4

Описание аргументов:

• B2 – число периодов капитализации;
• B3 – номинальная ставка;
• B4 – сумма вклада.

Результат расчетов:

Для сравнения, доход от вклада при использовании простых процентов составил бы 1000000*0,16=160000 рублей, поэтому для вкладчика выгодно использовать предложенный вариант со сложными процентами.

Формула для расчета процентной части

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

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

В итоге по расчетам необходимо будет возвратить банкирам от полумиллиона 684881,67 руб. Соответственно разница – 184 881, 67 руб. Тогда действенная процентная ставка окажется на уровне 37%. Если же вести расчет без учета однопроцентной комиссии, то выйдем на эффективную процентную ставку в 13%.

Формы покрытия кредита

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

Существует всего две формы погашения займа:

• дифференцированный платёж;
• аннуитетный платёж.

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

его воспринимают главным в вопросе определения объёма переплаты.

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

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

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

Это интересно: Работа кредитным специалистом — что нужно знать

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

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

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

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

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

Алгоритм действий простой:

• выбираем тип платежей «Аннуитетный»;
• вносим желаемую сумму в ячейку «Размер кредита»;
• прописываем приемлемую процентную ставку в ячейке «Ставка по кредиту»;
• указываем период кредитования (лучше в месяцах);
• нажимаем кнопку «Рассчитать».

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

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

Калькулятор выдал сумму аннуитетного взноса в размере 664,29 руб. (второй столбец «Всего»).

1 год 0 месяц

-20000

0,0

0,0

20000

1 год 1 месяц

664,29

464,29

200,00

19 535,71

1 год 2 месяц

664,29

468,93

195,36

19 066,78

1 год 3 месяц

664,29

473,62

190,67

18 593,17

1 год 4 месяц

664,29

478,35

185,93

18 114,81

1 год 5 месяц

664,29

483,14

181,15

17 631,67

1 год 6 месяц

664,29

487,97

176,32

17 143,70

1 год 7 месяц

664,29

492,85

171,44

16 650,86

1 год 8 месяц

664,29

497,78

166,51

16 153,08

1 год 9 месяц

664,29

502,76

161,53

15 650,32

1 год 10 месяц

664,29

507,78

156,50

15 142,54

1 год 11 месяц

664,29

512,86

151,43

14 629,68

Все 3 способа дают один и тот же результат, но кредитный калькулятор — бесспорно, самый удобный.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как рассчитать аннуитетный платеж в Excel

Калькулятор по кредиту с нерегулярными оплатами

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

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

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

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

ПСК = i х ЧБП х 100, где

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

Базисный период рассчитывается последующим образом. По закону, это обычные временные рамки, почаще всего фигурирующие в типовых банковских графиках погашения задолженностей. Большая часть российских денежных учреждений используют 28 дневный срок. Таковым образом, базисный период равен 28. Как следует, ЧПБ = 365 (количество дней в году) : 28. Получаем 13. Броско, что этот показатель так же, в большинстве случаев, обычный.

Сейчас, имея на руках все нужные числа, можно подставлять их в формулу. К примеру, клиент берет в долг 400 000 рублей, сроком на 2 года (24 месяца), под ставку 22% годичных.

ПСК = 22/13 х 13 х 100 = 22%

Получаем, что при отсутствии доп либо укрытых комиссионных начислений, часто устанавливаемых банками, ПСК вполне совпала с величиной ставки.

Чтоб выяснить, сколько же всего нужно дать компании, чтоб кредит числился закрытым, необходимо к размеру взятых валютных средств прибавить ПСК – в нашем случае, это 22%.

В этом видео тщательно поведано о том, как верно подсчитать проценты по кредиту в таблице Эксель:

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

Задача2

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

Решение2 Ежеквартальный платеж может быть вычислен по формуле =ПЛТ(15%/12; 5*4; 100 000; -100 000*10%; 0) , результат -6 851,59р. Все параметры функции ПЛТ() выбираются аналогично предыдущей задаче, кроме значения БС, которое = -100000*10%=-10000р., и требует пояснения. Для этого вернемся к предыдущей задаче, где ПС = 100000, а БС=0. Найденное значение регулярного платежа обладает тем свойством, что сумма величин идущих на погашение тела кредита за все периоды выплат равна величине займа с противоположным знаком. Т.е. справедливо равенство: ПС+СУММ(долей ПЛТ, идущих на погашение тела кредита)+БС=0: 100000р.+(-100000р.)+0=0. То же самое и для второй задачи: 100000р.+(-90000р.)+БС=0, т.е. БС=-10000р.

Как быстро посчитать основные параметры кредита (ипотеки) в Excel, как быстро сформировать кредитный калькулятор и график платежей в Excel? Как посчитать параметры досрочного погашения кредита в Excel?

Если вам нужно быстро посчитать кредит в Excel, попробуйте наш простой калькулятор для расчета основных параметров кредита (ипотеки), который можно встроить непосредственно в ваш Excel!Калькулятор выводит основные параметры вашего кредита или может сформировать лист с кредитным калькулятором и графиком платежей. 

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

1. Формулы при аннуитетных (равномерных) платежах:

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

=ОСПЛТ(годовая проц.ставка/12;номер текущего месяца платежа;срок кредита (лет)*12;первоначальная сумма кредита)*-1

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

=ПРПЛТ(годовая проц.ставка/12;номер текущего месяца платежа;срок кредита (лет)*12;первоначальная сумма кредита)*-1

2. Формулы при полном возврате кредита в конце срока (используется редко):

Итоговая сумма к выплате в конце срока кредитования:

 =сумма кредита*(100%+годовая ставка)^срок кредита(лет)

Эти формулы не обязательно писать вручную! Вы можете добавить кредитный калькулятор в свой Excel с нашими надстройками PowerFin или PowerQuick, чтобы он, вместе с другими финансовыми инструментами, был под рукой в любую минуту!

Надстройки выводят экспресс-расчеты по заданным параметрам кредита:

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

Установите калькулятор с нашей надстройкой PowerFin Калькулятор устанавливается как отдельная вкладка в вашем Excel:

А если вы хотите больше функционала в вашем Excel – попробуйте нашу мощную универсальную надстройку PowerQuick!

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

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

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

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

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

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

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

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

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

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

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

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

Примеры расчетов регулярных платежей по аннуитетной схеме в Excel

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

Пример 1. Банк выдал кредит на сумму 10 000 руб. под 18% годовых сроком на 1 год. Был составлен график ежемесячных выплат. Определить, какую сумму тела кредита выплатит клиент в 3-1 месяц.

Вид таблицы данных:

Для расчета используем следующую функцию:

=ОСПЛТ(B3/12;3;B4;B5)

Описание аргументов:

• B3/12 – размер ставки, приведенной к числу периодов выплат (12 месяцев);
• 3 – номер периода, для которого выполняется расчет;
• B4 – общее число периодов (12 месяцев в году);
• B5 – сумма кредита по договору.

Результат вычислений:

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

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

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

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

Без недостатков не обошлось:

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

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

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

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

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

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

Формула расчета аннуитетных платежей

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

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

A = P * (1+P)N / ((1+P)N-1), где

A — коэффициент аннуитета;
P — процентная ставка выраженная десятичной дробью в расчете на период. Например, для случая 12% годовых и ежемесячной выплаты это составит 0.12/12 = 0.01;
N — число периодов гашения займа.

Sa = A * K, где

Sa — аннуитетный платеж;
A — коэффициент аннуитета;
K — кредит.

Формула общей суммы выплат

S = N * Sa = N * A * K, где

S — общая сумма выплат;
Sa — аннуитетный платеж;
N — число периодов гашения (обычно месяцев);
A — коэффициент аннуитета;
K — кредит.

Sp = S — K, где

S — общая сумма выплат;
K — кредит.

Пример 1

Ссуда 100 000 рублей на 5 лет под 12% годовых с ежемесячными выплатами.

В этом случае число периодов погашения N = 5*12 = 60,
процентная ставка на период P = 0.12 / 12 = 0.01.

Коэффициент аннуитета:

A = 0.01 * (1+0.01)60 / ((1+0.01)60-1) = 0.0222444

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

Sa = 0.0222444 * 100 000 = 2224.44 руб.

Всего выплат:

S = 60 * 2224.44 = 133 466.69 руб.

Сумма процентов (переплата):

Sp = 133 466.69 — 100 000 = 33 466.69 руб.

Пример 2

Ссуда 100 000 рублей на 3 года под 12% годовых с ежемесячными выплатами.

Число периодов гашения N = 3*12 = 36,ставка на период P = 0.12 / 12 = 0.01.

Коэффициент аннуитета:

A = 0.01 * (1+0.01)36 / ((1+0.01)36-1) = 0.0332143

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

Sa = 0.0332143 * 100 000 = 3321.43 руб.

Всего выплат:

S = 36 * 3321.43 = 119 571.52 руб.

Переплата:

Sp = 119 571.52 — 100 000 = 19 571.52 руб.

Выводы

Видим, что с уменьшением срока займа:

• Аннуитетный платеж растет
• Общая сумма выплат уменьшается
• Переплата уменьшается

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

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

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

Показывает какую долю от займа составляет переплата:

Pp = Sp / K * 100%, где   

Pp — процент переплаты;
Sp — переплата;
K — кредит;

Для приведенных выше примеров

Пример 1: Pp = 33.47%
Пример 2: Pp = 19.57%

Формула эффективной процентной ставки

Эффективная процентная ставка это процент переплаты, деленный на срок займа, выраженный в годах:

Pэф = Pp / (годы) = Pp / (N / 12)

Pэф — эффективная процентная ставка;
Pp — процент переплаты;
N — число месяцев гашения;

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

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

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

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

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

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

А = К * S

где:

• А – сумма платежа по кредиту;
• К – коэффициент аннуитетного платежа;
• 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, то скачайте следующие xls-файлы:

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

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

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

Умеет:

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

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

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

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

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

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

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

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

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

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

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

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

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

Какие формулы применять

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

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

В свою очередь, коэффициент получаем по формуле:

К = (i * (1 + i)^n) / ((1+i)^n-1), где:

• i – ставка в месяц. Она привязана к размеру годичный переплаты по процентам и составляет двенадцатую ее часть;
• n – период актуальности кредитного контракта. Данный просвет времени следует исчислять лишь в календарных месяцах.

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