Таблица «Эксель» для расчёта ипотеки.

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

Какие данные нужны для расчёта

Для начала подготовьте основные параметры:

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

Сумма кредита определяется так: стоимость недвижимости минус первоначальный взнос. Например, если квартира стоит 6 000 000 рублей, а первоначальный взнос составляет 1 500 000 рублей, ориентировочная сумма займа равна 4 500 000 рублей.

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

Как устроена таблица ипотеки в Excel

Простая таблица ипотеки может выглядеть так:

Показатель	Значение
Стоимость недвижимости	6 000 000
Первоначальный взнос	1 500 000
Сумма кредита	4 500 000
Годовая ставка	12%
Срок кредита	20 лет
Количество платежей	240

В отдельной ячейке можно рассчитать сумму кредита формулой:

`=Стоимость_недвижимости-Первоначальный_взнос`

Если значения находятся, например, в ячейках B2 и B3, формула будет выглядеть так: `=B2-B3`.

Количество платежей рассчитывают умножением срока в годах на 12:

`=Срок_в_годах*12`

Если срок указан в ячейке B6, формула может быть такой: `=B6*12`.

Расчёт аннуитетного платежа

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

В Excel для расчёта используют функцию ПЛТ. В англоязычной версии программы она называется PMT:

`=ПЛТ(Годовая_ставка/12;Срок_в_месяцах;-Сумма_кредита)`

Если сумма кредита находится в B4, ставка — в B5, а срок в месяцах — в B6, формула будет такой:

`=ПЛТ(B5/12;B6;-B4)`

Минус перед суммой кредита нужен для того, чтобы Excel вывел платёж положительным числом. Если его не поставить, результат может отображаться со знаком минус из-за правил расчёта денежных потоков.

Для примера возьмём условные параметры: кредит 4 500 000 рублей, ставка 12% годовых и срок 20 лет. Полученный в Excel платёж будет ориентировочным. Он не учитывает страхование, комиссии, платные сервисы и другие расходы, если они не включены отдельно.

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

График погашения по месяцам

Чтобы таблица ипотеки в Excel показывала не только размер платежа, но и уменьшение долга, создайте график с такими столбцами:

Месяц	Долг на начало	Платёж	Проценты	Основной долг	Долг на конец
1	Сумма кредита	Ежемесячный платёж	Долг на начало × ставка/12	Платёж − проценты	Долг на начало − основной долг
2	Долг на конец из строки 1	Ежемесячный платёж	Долг на начало × ставка/12	Платёж − проценты	Долг на начало − основной долг

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

• проценты за месяц: `=Долг_на_начало*Годовая_ставка/12`;
• погашение основного долга: `=Платёж-Проценты`;
• остаток долга: `=Долг_на_начало-Основной_долг`.

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

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

Как посчитать переплату

Если ежемесячный платёж одинаковый, ориентировочную сумму выплат можно определить так:

`=Ежемесячный_платёж*Количество_платежей`

Переплата по процентам без учёта дополнительных расходов:

`=Общая_сумма_платежей-Сумма_кредита`

Например, в расчёт можно добавить отдельные строки:

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

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

Аннуитетные и дифференцированные платежи

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

Для расчёта дифференцированного графика нужно определить:

1. Основной долг в каждом месяце: сумма кредита разделить на количество платежей.
2. Проценты: остаток долга на начало месяца умножить на годовую ставку и разделить на 12.
3. Общий платёж: часть основного долга плюс начисленные проценты.
4. Остаток: долг на начало месяца минус погашенная часть основного долга.

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

Как учесть досрочное погашение

В таблице можно добавить столбец «Досрочный платёж». После каждого месяца нужно уменьшать остаток долга на сумму, направленную на досрочное погашение.

Отдельно проверьте, как банк пересчитывает график:

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

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

Что Excel не учитывает автоматически

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

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

Рекламная ставка может зависеть от выполнения дополнительных условий. Например, её могут связывать с подтверждением дохода, использованием определённого способа погашения или оформлением страхового продукта. Эти условия нужно проверять в индивидуальном предложении и документах банка.

Как проверить свою таблицу

После заполнения эксель-таблицы ипотеки проверьте несколько показателей:

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

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

Кому нужна таблица ипотеки в Excel

Самостоятельный расчёт удобен, если нужно:

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

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

Частые вопросы