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

Вычисление процентной ставки за кредит

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

= RATE (Nper; pmt; present_value; ; )

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

Формула Excel, используемая для расчета ставки кредитования:

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)

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

Мы используем формулу = (1 + B5) составляет 12-1 ^ = (1 + 0. 294%) ^ 12-1 , чтобы получить годовую ставку нашего займа в размере 3. 58 %. Другими словами, чтобы занять 120 000 долларов США за 13 лет, чтобы ежемесячно выплачивать 960 долларов США, мы должны договориться о займе с максимальным курсом 3,8% годовых.

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

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

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

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

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

Первоначальные действия

1. В списке функций выбрать «ПЛТ» и нажать «ОК».

Выбор функции в специальном окне

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

Алгоритм действий по заполнению аргументов функции «ПЛТ»

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

Финальный результат вычислений

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

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

1. Составить исходную таблицу на 24 месяца.

Изначальный табличный массив

1. Поставить курсор в первую ячейку таблицы и вставить функцию «ОСПЛТ».

Выбор функции детализации платежей

1. Заполнить аргументы функции аналогичным образом.

Заполнение всех строк в окне аргументов оператора э

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

Заполнение аргумента «Период»

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

Заполнение оставшихся строчек

1. Выбрать функцию «ПРПЛТ» для заполнения второго столбца таблицы.
2. Заполнить все аргументы в открывшемся окошке в соответствии со скриншотом ниже.

Заполнение аргументов для оператора «ПРПЛТ»

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

Расчет ежемесячных взносов

1. Чтобы посчитать «Остаток к выплате», надо сложить процентную ставку с выплатой по телу кредита и растянуть до конца таблички, чтобы заполнить все месяцы кредитования.

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

Формула расчета платежа по ипотеке и калькулятор в excel

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

Порядок расчета займа с помощью калькулятора

Существует 2 варианта расчета займа
Первый — предварительный расчет, когда вы хотите взять наличные в кредит. Для данного расчета дата первого платежа не нужна. Ее можно оставить по умолчанию. Она не влияет на размер ежемесячного платежа.
Сумма займа — она прописана в кредитном договоре и берется без учета первоначального взноса на товар или услугу.
Процентная ставка — номинальная ставка по займу без учета комиссий и страховки. Берется из договора займа. Можно ввести 3 знака после запятой.
Выражается без деления на сто.
Срок — целое число месяцев на которе берется займ. Если у вас 2 года к примеру, то нужно ввести 24 месяца
Второй вариант — расчет существующего займа
Далее идет поле — дата первого платежа. Этот параметр уже важен когда вы взяли кредит
Для взятой ссуды важен расчет по дате. Т.е при построении графика указывается дата очередной выплаты — номер дня в месяце.
Расчет с учетом дат важен при досрочных погашениях. От даты досрочного внесения средств зависит то, в каком месяце будет новый уменьшенный платеж.

Результаты

Базовые условия

Срок кредитования	: 96 месяцев
Сумма кредита	: 400000 денежных единиц
Процентная ставка	: 14 % годовых (аннуитетная схема начисления)
Единоразовая комиссия	: %
Ежемесячная комиссия	: денежных единиц

Итоговые значения, денежных единиц

Ежемесячная сумма выплат	: 6948.60
Итого сумма выплат	: 667065.64
в том числе %	: 267065.64
тело кредита	: 400000.00
комиссии	: 0.00

Схема выплат по кредиту

Месяц	Задолженность по кредиту	Погашение кредита	Проценты по кредиту	Комиссии	Выплаты в месяц
1	400000.00	2281.93	4666.67	0.00	6948.60
2	397718.07	2308.56	4640.04	0.00	6948.60
3	395409.51	2335.49	4613.11	0.00	6948.60
4	393074.02	2362.74	4585.86	0.00	6948.60
5	390711.28	2390.30	4558.30	0.00	6948.60
6	388320.98	2418.19	4530.41	0.00	6948.60
7	385902.79	2446.40	4502.20	0.00	6948.60
8	383456.39	2474.94	4473.66	0.00	6948.60
9	380981.45	2503.82	4444.78	0.00	6948.60
10	378477.63	2533.03	4415.57	0.00	6948.60
11	375944.60	2562.58	4386.02	0.00	6948.60
12	373382.02	2592.48	4356.12	0.00	6948.60
13	370789.54	2622.72	4325.88	0.00	6948.60
14	368166.82	2653.32	4295.28	0.00	6948.60
15	365513.50	2684.28	4264.32	0.00	6948.60
16	362829.22	2715.59	4233.01	0.00	6948.60
17	360113.63	2747.27	4201.33	0.00	6948.60
18	357366.36	2779.33	4169.27	0.00	6948.60
19	354587.03	2811.75	4136.85	0.00	6948.60
20	351775.28	2844.56	4104.04	0.00	6948.60
21	348930.72	2877.74	4070.86	0.00	6948.60
22	346052.98	2911.32	4037.28	0.00	6948.60
23	343141.66	2945.28	4003.32	0.00	6948.60
24	340196.38	2979.64	3968.96	0.00	6948.60
25	337216.74	3014.40	3934.20	0.00	6948.60
26	334202.34	3049.57	3899.03	0.00	6948.60
27	331152.77	3085.15	3863.45	0.00	6948.60
28	328067.62	3121.14	3827.46	0.00	6948.60
29	324946.48	3157.56	3791.04	0.00	6948.60
30	321788.92	3194.40	3754.20	0.00	6948.60
31	318594.52	3231.66	3716.94	0.00	6948.60
32	315362.86	3269.37	3679.23	0.00	6948.60
33	312093.49	3307.51	3641.09	0.00	6948.60
34	308785.98	3346.10	3602.50	0.00	6948.60
35	305439.88	3385.13	3563.47	0.00	6948.60
36	302054.75	3424.63	3523.97	0.00	6948.60
37	298630.12	3464.58	3484.02	0.00	6948.60
38	295165.54	3505.00	3443.60	0.00	6948.60
39	291660.54	3545.89	3402.71	0.00	6948.60
40	288114.65	3587.26	3361.34	0.00	6948.60
41	284527.39	3629.11	3319.49	0.00	6948.60
42	280898.28	3671.45	3277.15	0.00	6948.60
43	277226.83	3714.29	3234.31	0.00	6948.60
44	273512.54	3757.62	3190.98	0.00	6948.60
45	269754.92	3801.46	3147.14	0.00	6948.60
46	265953.46	3845.81	3102.79	0.00	6948.60
47	262107.65	3890.68	3057.92	0.00	6948.60
48	258216.97	3936.07	3012.53	0.00	6948.60
49	254280.90	3981.99	2966.61	0.00	6948.60
50	250298.91	4028.45	2920.15	0.00	6948.60
51	246270.46	4075.44	2873.16	0.00	6948.60
52	242195.02	4122.99	2825.61	0.00	6948.60
53	238072.03	4171.09	2777.51	0.00	6948.60
54	233900.94	4219.76	2728.84	0.00	6948.60
55	229681.18	4268.99	2679.61	0.00	6948.60
56	225412.19	4318.79	2629.81	0.00	6948.60
57	221093.40	4369.18	2579.42	0.00	6948.60
58	216724.22	4420.15	2528.45	0.00	6948.60
59	212304.07	4471.72	2476.88	0.00	6948.60
60	207832.35	4523.89	2424.71	0.00	6948.60
61	203308.46	4576.67	2371.93	0.00	6948.60
62	198731.79	4630.06	2318.54	0.00	6948.60
63	194101.73	4684.08	2264.52	0.00	6948.60
64	189417.65	4738.73	2209.87	0.00	6948.60
65	184678.92	4794.01	2154.59	0.00	6948.60
66	179884.91	4849.94	2098.66	0.00	6948.60
67	175034.97	4906.53	2042.07	0.00	6948.60
68	170128.44	4963.77	1984.83	0.00	6948.60
69	165164.67	5021.68	1926.92	0.00	6948.60
70	160142.99	5080.27	1868.33	0.00	6948.60
71	155062.72	5139.53	1809.07	0.00	6948.60
72	149923.19	5199.50	1749.10	0.00	6948.60
73	144723.69	5260.16	1688.44	0.00	6948.60
74	139463.53	5321.53	1627.07	0.00	6948.60
75	134142.00	5383.61	1564.99	0.00	6948.60
76	128758.39	5446.42	1502.18	0.00	6948.60
77	123311.97	5509.96	1438.64	0.00	6948.60
78	117802.01	5574.24	1374.36	0.00	6948.60
79	112227.77	5639.28	1309.32	0.00	6948.60
80	106588.49	5705.07	1243.53	0.00	6948.60
81	100883.42	5771.63	1176.97	0.00	6948.60
82	95111.79	5838.96	1109.64	0.00	6948.60
83	89272.83	5907.08	1041.52	0.00	6948.60
84	83365.75	5976.00	972.60	0.00	6948.60
85	77389.75	6045.72	902.88	0.00	6948.60
86	71344.03	6116.25	832.35	0.00	6948.60
87	65227.78	6187.61	760.99	0.00	6948.60
88	59040.17	6259.80	688.80	0.00	6948.60
89	52780.37	6332.83	615.77	0.00	6948.60
90	46447.54	6406.71	541.89	0.00	6948.60
91	40040.83	6481.46	467.14	0.00	6948.60
92	33559.37	6557.07	391.53	0.00	6948.60
93	27002.30	6633.57	315.03	0.00	6948.60
94	20368.73	6710.96	237.64	0.00	6948.60
95	13657.77	6789.26	159.34	0.00	6948.60
96	6868.51	6868.51	80.13	0.00	6948.64
Итого		400000.00	267065.64	0.00	667065.64

Расчет параметров кредитов (процент, выплаты, сроки, сумма) в MS EXCEL

​ недоумение. С какой​ задолженности на начало​ разных периода и​Т.е. если номер периода​ ту сумму, которую​ что изложенный ниже​Можно пойти по уже​ – 1500 рублей.​Теперь можно найти процентную​ погашения кредита:​В программе Excel существует​ лишь решение конкретных​ или ОСПЛТ(), т.к.​ это деньги, которые​: Функция ПЛТ() входит​

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

1. ​ мы отдаем банку​
2. ​ материал вам не​ проторенному пути: рассчитать​
3. ​ Заемщик на руки​ ставку базового периода:​
4. ​Красная цифра – аннуитет​ специальная функция, которая​

​ контрольных заданий, а​ эти функции связаны​ мы​ в надстройку «Пакет​ пользование кредитом. Такой​ втором квартале не​Так же итоговая​ H8+I8. Сумма кредита,​ 2.​ мы вычисляем сумму​ за временно использование​ пригодится.​ внутреннюю ставку доходности,​

Задание 1

​ получит 148 500​У нас имеются все​ (брали 100 000​ считает аннуитетные платежи.​ основные разделы, посвященные​ и в любой​возвращаем банку​ анализа». Если данная​ равновеликий платеж называется​ гачился, то чистая​ сумма процентов получилась​ полученная в середине​

Задание 2

​Вот это действие​ выплат с помощью​ его денег. Это​Но если уж случится​ а потом умножить​ рублей.​ необходимые данные –​ руб.), черная –​

excel2.ru>

Дифференцированные платежи по кредиту в MS EXCEL

​ аннуитетных платежей по​ аннуитетный платеж –​ банку, терять.​

​ полгода пользования кредитом​за период​Составим в MS EXCEL​Программа Excel – это​Каждый из методов расчета​ кредитополучателей вне зависимости​ экономическое образование, чтобы​ и в любой​ставка /((1+ ставка)^​ случае за месяц.​ платеж включает:​ баланса раньше срока:​ПРПЛТ (IPMT)​ аргументы для расчета:​ кредиту используется простейшая​ проценты.​​ (см. условия задачи​; Период – номер​ график погашения кредита​ не просто большая​ при правильном применении​ от их финансового​ правильно произвести необходимые​ период: ПЛТ= ОСПЛТ​ Кпер -1)* Бс)*ЕСЛИ(Тип;1/(ставка​ Ставка =10%/12 (в​сумму в счет погашения​А в случае уменьшения​для вычисления процентной​Ставка​ функция ПЛТ. Как​Сумма процентов = остаток​

​Дифференцированный способ оплаты предполагает,​​ выше) = 150000*(12%/12)*6*(1-(6-1)/2/(2*12))=8062,50р.​ периода, для которого​ дифференцированными платежами.​ таблица. В ней​ даст точную цифру,​ положения;​ вычисления. Для облегчения​ + ПРПЛТ​ +1);1)​ году 12 месяцев).​ части ссудысумму для​ выплаты — заново​ части вводится аналогично.​- процентная ставка​ видите, дифференцированный способ​ долга * месячную​

График погашения кредита дифференцированными платежами

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

​Соотношение выплат основной суммы​Если процентная ставка =​Кпер – общее​ оплаты начисленных за​ пересчитывать ежемесячный взнос​ Осталось скопировать введенные​ по кредиту в​ погашения несколько сложнее.​ процентную ставку.​сумма основного долга распределена​​ будет выплачено =ПС*Ставка*(кпер+1)/2=18750р.​ начисленных процентов; Кпер​ кредита дифференцированными платежами​ количество вычислений, зная​ платежа. Поэтому, если​ поправкой на уровень​ все возможные способы​ долга и начисленных​ 0, то формула​ число периодов платежей​ прошедший период процентов​

​ начиная со следующего​ формулы вниз до​ пересчете на период​Кто как, а я​Остаток основного долга =​​ по периодам выплат​​Через функцию ПРОЦПЛАТ()​ — общее число​ сумма основного долга​ лишь, какие формулы​ есть сомнения в​ инфляции.​ для расчета аннуитетных​ процентов хорошо демонстрирует​ упростится до =(Пс​ по аннуитету, т.е.​ на остаток ссуды​ после досрочной выплаты​ последнего периода кредита​

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

​ график, приведенный в​​ + Бс)/Кпер​ 60 (12 мес.​;​

​ периода:​ и добавить столбцы​​ месяцы. Если годовая​​ Особенно потребительские. Кредиты​ – сумму основного​проценты по кредиту начисляются​ =СУММПРОИЗВ(ПРОЦПЛАТ(ставка;СТРОКА(ДВССЫЛ(«1:»&кпер))-1;кпер;-ПС))​ – приведенная стоимость​ части пропорционально сроку​ расчета аннуитетного платежа​ вычислений, можно произвести​ погашения кредита удобен​ которыми можно воспользоваться​ файле примера.​Если Тип=0 (выплата​ в году*5 лет)​​Сумма ежемесячного платежа (аннуитета)​Существуют варианты кредитов, где​ с простыми формулами​ ставка 12%, то​ для бизнеса -​ долга в предыдущем​ на остаток.​Excel – это универсальный​ на текущий момент​ кредитования. Регулярно, в​ в Excel есть​

Расчет суммарных процентов, уплаченных с даты выдачи кредита

​ проверку, рассчитав аннуитетный​ тем, что, зная​ для планирования собственного​Примечание​ в конце периода)​Пс — Приведенная​постоянна​ клиент может платить​ для вычисления общей​ на один месяц​​ другое дело, а​​ периоде.​​Формула расчета дифференцированного платежа:​​ аналитическо-вычислительный инструмент, который​​ (для кредита ПС​​ течение всего срока​ специальная функция –​ платеж другим возможным​ точный размер ежемесячного​​ бюджета.​. В статье Аннуитет.​​ и БС =0,​ стоимость всех денежных​и не меняется​ нерегулярно, в любые​ суммы ежемесячных выплат​ должно приходиться по​ для обычных людей​Опираясь на таблицу ежемесячных​​ДП = ОСЗ /​ часто используют кредиторы​ — это сумма​ погашения кредита, заемщик​ ПЛТ. Чтобы правильно​​ способом.​ взноса, проще планировать​​Перед практической частью изучения​ Расчет периодического платежа​ то Формула 2​

excel2.ru>

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

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

Мы сделаем не просто файлик с одной циферкой. Нет! Мы разработаем настоящий инструмент, с помощью которого вы сможете рассчитать аннуитетный платёж не только для себя, но и для соседа, который ставит свою машину на детской площадке; прыщавого студента, который сутками курит в вашем подъезде; тётки, которая выгуливает свою собаку прямо под вашими окнами – короче, для всех особо одарённых. Кстати, можете поставить где-нибудь возле монитора купюроприёмник и брать с этой публики деньги.

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

Итак, вы видите два блока. Один с исходными данными, а второй – с расчётами. Исходные данные (сумма кредита, годовая процентная ставка, срок кредитования) вы будете вводить вручную, а во втором блоке будут мгновенно появляться расчёты.

Начнём с расчёта ежемесячной суммы аннуитетного платежа. Для этого надо сделать активным окошко, в котором вы хотите видеть это значение (в нашем случае – это поле C11, на рисунке оно обведено и указано под номером 1). Далее слева от строки формул жмём на «fx» (на рисунке эта кнопка обведена и указана под номером 2). После этих действий у вас появится такая табличка:

Выбираем функцию «ПЛТ» и жмём «Ок». Перед вами появится таблица, в которую надо будет ввести исходные данные:

Здесь нам требуется заполнить три поля:

• «Ставка» – годовая процентная ставка по кредиту делённая на 12.
• «Кпер» – общий срок кредитования.
• «Пс» – сумма кредита (указывается со знаком минус).

Обратите внимание на то, что мы не вводим готовые цифры в эту таблицу, а указываем координаты ячеек нашего блока с исходными данными. Так, в поле «Ставка» мы указываем координаты ячейки, в которой будет вписываться вручную процентная ставка (C5) и делим её на 12; в поле «Кпер» указываются координаты ячейки, в которой будет вписываться срок кредитования (C6); в поле «Пс» – координаты ячейки в которой вписывается сумма кредита (C4). Так как сумма кредита у нас указывается со знаком минус, то перед координатой (C4) мы ставим знак минус

Так как сумма кредита у нас указывается со знаком минус, то перед координатой (C4) мы ставим знак минус.

После того как исходные данные будут введены, жмём кнопку «Ок». В результате мы видим в блоке расчетов точное значение ежемесячного аннуитетного платежа:

Итак, в данный момент сумма нашего аннуитетного платежа составляет 4680 руб (на рисунке он обведён и указан под номером 1). Если вы будете менять сумму кредита, процентную ставку и общий срок кредитования, то автоматически будет меняться значение вашего аннуитетного платежа.

Кстати, обратите внимание на значение функции, обозначенное на рисунке под номером 2: =ПЛТ(C5/12;C6;-C4). Да, да, это и есть те самые координаты, которые мы вводили в таблицу, выбрав функцию «ПЛТ». По сути, вы могли бы не проделывать всех тех сложных телодвижений, которые показаны на втором и третьем рисунках

Можно было просто вписать в строке формул то, что там сейчас вписано

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

Зная размер аннуитетного платежа несложно посчитать остальные значения нашего расчётного блока:

На рисунке наглядно показано, как рассчитана общая сумма выплат (обведена и указана под номером 1). Так как она равна сумме аннуитетного платежа (ячейка C11) умноженной на общее количество месяцев кредитования (ячейка C6), то мы и вписываем в строку формул следующую формулу: =C11*C6 (на рисунке она обведена и указана под номером 2). В результате мы получили значение 56 157 рублей.

Переплата по кредиту рассчитывается ещё проще. От общей суммы выплат (ячейка C12) надо отнять сумму кредита (ячейка C4). В строку вписываем такую формулу: =C12-C4. В нашем примере переплата равна: 6157 рублей.

Ну и последнее значение – эффективная процентная ставка (или полная стоимость кредита). Она рассчитывается так: общую сумму выплат (ячейка C12) делим на сумму кредита (ячейка C4), отнимаем единицу, затем делим всё это на срок кредитования в годах (ячейка C6 делённая на 12). В строке будет такая формула: =(C12/C4-1)/(C6/12). В нашем примере эффективная процентная ставка составляет 12,3%.

Всё! Вот таким нехитрым способом мы с вами составили в программе Microsoft Excel автоматический калькулятор расчета аннуитетных платежей по кредиту, скачать который можно ссылке ниже:

ипотечный калькулятор эксель, как делать расчеты по кредиту в Excel

О том как делать расчеты по ипотеке.

Каждый из нас рано или поздно подходил к тому, как же ему рассчитать свои платежи и т.п. Здесь
я расскажу о том как рассчитывать АННУИТЕТНЫЕ платежи в Excel. В Интернете
есть масса ипотечных калькуляторов. На сайте практически каждого банка есть свой ипотечный калькулятор.
Но у нас не всегда есть доступ к сети, поэтому научившись считать ипотеку в экселе вам будет
проще принимать решение.

Спасибо Биллу Гейтсу – у нас есть финансовые функции Excel! Так например, самая
полезная функция для расчета ипотечного платежа в экселе оказалась функция
ПЛТ() — платёж Объясняю на пальцах, просто в любой ячейке Excel пишем следующее:

=ПЛТ(ставка;периоды;сумма)

Причем, поскольку платежи в банк мы делаем ежемесячно, то всё должно быть указано в месяцах – и ставка, и количество периодов. Например, если мы берем ипотечный кредит на 10 лет (или 120 месяцев) под 12% (1% в месяц) годовых на сумму 1 000 000р, то в ячейку нужно вписать следущее:

=ПЛТ(1%;120;1000000)

или так:

=ПЛТ(12%/12;10*12;1000000)

Не обращайте внимание на то, что ответ (-14 347,09р) получается красного цвета и со знаком минус — это ведь Вам платить
банку и из своего кошелька, поэтому и минус. Другая полезная при ипотечных рассчетах функция:

Другая полезная при ипотечных рассчетах функция:

ПС(ставка;срок;платёж)

— расчёт максимального кредита (приведенная стоимость).

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

В нашем примере с 12% годовыми давайте предположим что мы бы хотели взять кредит на 20 лет,
то есть на 240 месяцев. Пусть ежемесячно мы хотим платить банку ровно 10 000р. Чтобы
узнать на какую сумму мы можем рассчитывать в Excel в любую ячейку пишем следущее:

=ПС(12%/12;240;10000)

или так:

=ПС(1%;10*12;10000)

Получается та сумма (-908 194,16р) на которую мы можем рассчитывать получить
от банка в качестве ипотечного кредита. Сумма по-прежнему указана красным
цветом со знаком минус, ведь это всё-таки размер вашего будущего долга =)

Если нам интересно сколько денег из нашего ежемесячного платежа идёт в счёт основного
долга, а сколько в счёт погашения процентов по ипотеке, то нужно использовать функции:

для расчёта процентов:=ПРПЛТ(ставка;номер_платежа;всего платежей;общая_сумма)

для расчёта осн. долга:=ОСПЛТ(ставка;номер_платежа;всего_платежей;общая_сумма)

Где, ставка — рассчитана ежемесячно. Т.е. в нашем примере с кредитом на 12% годовых для расчётов используем 1%.
номер_платежа — это порядковый номер, т.е. если мы будем делать наш первый платёж, то пишем 1, если уже пятый
платёж — то 5. всего_платежей — это общее количество выплат, или периодов выплат как это именует эксель.
Например, если кредит мы берем на 10 лет, то пишем 120, ведь в 10 годах содержатся 120 месяцев. Платим мы
ежемесячно, поэтому и количество платежей будет 120. Если например 20лет, то 240. общая_сумма — это
сумма кредита, который мы берем. Например 1 000 000р, т.е. пишем 1000000.

Пример расчёта второго платежа для ипотечного кредита под 14% годовых, на 25 лет, на 1 500 000р:

Проценты: =ПРПЛТ(14%/12;2;25*12;1500000) = -17 493,51р.

Осн. долг: =ОСПЛТ(14%/12;2;25*12;1500000) = -562,91р.

Платёж: =ПЛТ(14%/12;25*12;1500000) = -18 056,42р.

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

Поскольку ежемесячный платеж с каждой выплатой становится всё меньше, то
рассчет платежа у нас так сразу не получится. Для себя с просто сделал
небольшой файл Excel в котором делал рассчеты. Скачать мой
ипотечный калькулятор Excel можно здесь.
Поля зеленого цвета — это то что нужно ввести. Также скорей
всего придётся изменять срок кредита, для этого добавляйте-удаляйте
ячейки по своему усмотрению. Универсального ипотечного калькулятора
создавать я не стремился.

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

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

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

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

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

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

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

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

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

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