Ипотека от А до Я: считаем, сравниваем и управляем в Excel

Три жизненных сценария — покупка жилья, ускоренное погашение и финансовые трудности — и Excel-формулы для каждого

Покупка жилья — самое крупное финансовое решение в жизни большинства людей. Дороже только… ничего. При этом решение, которое определяет вашу финансовую реальность на 10–20–30 лет, чаще всего принимается удивительно неосознанно. Весь анализ обычно сводится к двум вопросам: «одобрит ли банк?» и «потянем ли платёж?»

Но одобрение банка — это не совет и не рекомендация. Это пороговая функция: банк проверяет, сможете ли вы технически обслуживать долг, не создавая ему проблем. Банку неважно, останется ли у вас запас на жизнь, отпуск, образование детей. Неважно, что через 20 лет вы заплатите за квартиру втрое больше её стоимости. Неважно, что существует вариант, при котором переплата на 5 миллионов меньше. Банк одобряет кредит — и всё. Дальше вы сами.

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

Сценарий 1: Покупаю квартиру — как выбрать ипотеку

Что нужно понять до визита в банк

Прежде чем сравнивать предложения банков, определите свои базовые параметры. Это не то, что спрашивает банк — это то, что вы должны спросить у себя:

Стоимость квартиры и первоначальный взнос. Чем больше взнос, тем меньше сумма кредита и переплата. Банки обычно требуют от 15% до 30% стоимости. При взносе менее 20% часто добавляется страховка, которая увеличивает эффективную ставку.

Ваш ежемесячный доход. Банки одобряют ипотеку, если платёж не превышает 40–50% дохода. Но комфортный уровень — не более 30%. Если платёж «съедает» половину зарплаты, любая непредвиденная ситуация превращается в кризис.

Горизонт планирования. Длинный срок (20–25 лет) снижает ежемесячный платёж, но многократно увеличивает переплату. Короткий срок (10–15 лет) — наоборот. Оптимальный подход: взять длинный срок для подстраховки, но гасить досрочно (об этом — в сценарии 2).

Математика ипотеки: два типа платежей

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

Аннуитетный платёж — одна и та же сумма каждый месяц. Внутри платежа соотношение процентов и погашения долга меняется: в первые годы вы платите в основном проценты, а тело кредита почти не уменьшается.

Формула:

P = S × r × (1 + r)^n / ((1 + r)^n − 1)

В Excel это функция ПЛТ (или PMT в английской версии):

=ОКРУГЛ(-ПЛТ(ставка/12; срок_лет*12; сумма_кредита); 0)

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

Как сравнить предложения банков в Excel

Вот что действительно нужно сравнивать. Не ставку саму по себе, а итоговую картину:

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

Шаг 2. Для каждого варианта рассчитайте:

• Ежемесячный платёж (ПЛТ)

• Общая переплата за весь срок (платёж × месяцы − сумма кредита)

• Эффективная стоимость — переплата + страховка за весь срок + комиссии

• Платёж / доход — процент от вашего дохода

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

Пример: разница в 0.5% ставки на деле

Квартира 8 000 000 ₽, первоначальный взнос 2 000 000 ₽, срок 20 лет:

Полпроцента ставки — это почти полмиллиона рублей переплаты на дистанции 20 лет. Вот почему стоит потратить вечер на расчёты.

Чек-лист перед подписанием ипотечного договора

Для каждого предложения проверьте и запишите:

1. Базовая ставка и условия её сохранения (зарплатный проект? страховка? электронная регистрация?)

2. Размер и условия страховки (жизнь, имущество, титул) — включите в расчёт стоимости

3. Есть ли комиссия за выдачу кредита

4. Условия досрочного погашения — нет ли моратория на первые месяцы

5. Можно ли менять дату платежа

6. Штрафы за просрочку

7. Проверьте, подходите ли вы под льготные программы: семейная ипотека (6%), IT-ипотека, сельская ипотека

8. Материнский капитал — можно ли использовать как часть первоначального взноса

Формулы Excel для этого сценария

Сценарий 2: Доход вырос — как погасить ипотеку быстрее

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

Два варианта досрочного погашения

При внесении дополнительной суммы банк предлагает выбор:

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

Уменьшить платёж — срок остаётся прежним, но ежемесячная нагрузка снижается. Экономия меньше, но вы получаете финансовый запас прямо сейчас.

Считаем оба варианта

Исходные данные: кредит 6 000 000 ₽ под 18% на 20 лет, платёж 92 579 ₽. Через 2 года (24 месяца) вносите 500 000 ₽ досрочно.

Остаток долга через 2 года — рассчитываем функцией БС (FV):

=БС(18%/12; 24; -92579; -6000000)

Остаток: около 5 870 000 ₽. После досрочного взноса: 5 370 000 ₽.

Вариант А — уменьшить срок. Рассчитываем новый срок функцией КПЕР (NPER):

=КПЕР(18%/12; -92579; 5370000)

Результат: примерно 195 месяцев вместо оставшихся 216. Вы закроете ипотеку на 21 месяц раньше.

Вариант Б — уменьшить платёж. Рассчитываем новый платёж:

=-ПЛТ(18%/12; 216; 5370000)

Результат: около 84 700 ₽ вместо 92 579 ₽. Экономия ~7 900 ₽ каждый месяц.

Сравнение сценариев

Уменьшение срока экономит примерно на 700 000 ₽ больше. Но у уменьшения платежа есть плюс: если в будущем доход упадёт, у вас будет ниже обязательный минимум.

Стратегия «лестница»

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

Регулярные досрочные платежи

Разовый взнос 500 000 ₽ — это хорошо. Но регулярные дополнительные платежи — ещё эффективнее. Если каждый месяц доплачивать хотя бы 10 000–20 000 ₽ сверх обязательного платежа, эффект накапливается за счёт сложного процента.

Рассчитайте в Excel: при той же ипотеке (6 млн, 18%, 20 лет) доплата 15 000 ₽/мес сокращает срок примерно на 7 лет и экономит около 6 миллионов рублей переплаты.

=КПЕР(18%/12; -(92579+15000); 6000000)

Формулы Excel для этого сценария

Сценарий 3: Финансовые трудности — что делать

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

Вариант 1: Ипотечные каникулы

По закону (ФЗ-76) заёмщик имеет право на отсрочку платежей до 6 месяцев, если:

• Доход снизился более чем на 30%

• Потеря работы

• Временная нетрудоспособность (больничный от 2 месяцев)

• Получение инвалидности I или II группы

• Увеличение количества иждивенцев при снижении дохода

Важно: каникулы — это отсрочка, а не прощение. Долг никуда не исчезает, срок кредита увеличивается, проценты продолжают начисляться. Но это законная передышка, чтобы встать на ноги.

Каникулы можно использовать один раз за весь срок ипотеки.

Вариант 2: Реструктуризация

Банк изменяет условия кредита. Типичные варианты:

• Увеличение срока → снижение ежемесячного платежа

• Временное снижение ставки на 1–2 года

• Изменение валюты кредита

• Временная выплата только процентов (без тела долга)

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

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

Рассчитайте в Excel, как изменится платёж при увеличении срока. Например, если остаток 5 000 000 ₽ при ставке 18% и осталось 15 лет (180 мес), увеличение срока до 25 лет (300 мес):

Текущий платёж:  =-ПЛТ(18%/12; 180; 5000000) → ~81 300 ₽
Новый платёж:    =-ПЛТ(18%/12; 300; 5000000) → ~76 300 ₽

Снижение небольшое (~5 000 ₽), потому что при высокой ставке увеличение срока мало влияет на платёж — основная часть и так уходит на проценты. Поэтому при реструктуризации эффективнее договариваться о временном снижении ставки.

Вариант 3: Рефинансирование

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

• Разница в ставке — более 2% (идеально 4%+)

• До конца ипотеки осталось больше половины срока

• Вы прошли минимум 6 месяцев с момента выдачи текущего кредита

В 2026 году рефинансирование особенно актуально для тех, кто брал ипотеку в пик ставок (28–29% в конце 2024 — начале 2025). Текущие рыночные ставки ~20%, а если в семье родился ребёнок — можно рефинансировать под семейную ипотеку (6%).

Рассчитайте экономию:

Старый платёж:   =-ПЛТ(28%/12; 240; 5000000) → ~117 200 ₽
Новый платёж:    =-ПЛТ(20%/12; 240; 5000000) → ~84 300 ₽
Экономия:        ~32 900 ₽/мес = ~395 000 ₽/год

Но учтите расходы на рефинансирование: оценка квартиры (~5 000 ₽), страховка в новом банке, возможно — регистрация нового залога. Обычно затраты окупаются за 2–3 месяца.

Вариант 4: Продажа квартиры

Крайний, но иногда самый разумный вариант. Если финансовая ситуация не временная и восстановления не видно, продажа позволяет:

• Погасить остаток долга

• Избежать судебных торгов (где квартиру продают с дисконтом 20–30%)

• Сохранить остаток средств, если рыночная цена выше долга

Для продажи ипотечной квартиры нужно согласие банка. Схема: покупатель вносит аванс → вы гасите остаток ипотеки → банк снимает обременение → оформляется сделка.

Рассчитайте в Excel:

Рыночная цена квартиры:        B1 (например, 9 000 000 ₽)
Остаток по ипотеке:            B2 (например, 5 500 000 ₽)
Расходы на сделку (~2%):       =B1*0.02
Остаток после продажи:         =B1-B2-B3

Чего делать нельзя

• Молчать и копить просрочку. После 90 дней просрочки банк начинает процедуру взыскания. После 180 дней — может обратиться в суд.

• Брать потребительский кредит, чтобы платить ипотеку. Ставки по потребительским кредитам ещё выше — это спираль долга.

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

Формулы Excel для этого сценария

Базовая теория: как устроена формула ПЛТ

Для тех, кто хочет понимать, что происходит «под капотом».

Функция ПЛТ (PMT) реализует формулу аннуитетного платежа:

P = S × r × (1 + r)^n / ((1 + r)^n − 1)

Где S — сумма кредита, r — месячная ставка (годовая ÷ 12), n — количество месяцев.

Что происходит внутри каждого платежа:

• Процентная часть = остаток долга × месячная ставка

• Основной долг = платёж − процентная часть

• Новый остаток = старый остаток − основной долг

В первый месяц при кредите 6 000 000 ₽ под 18%:

• Платёж: 92 579 ₽

• Проценты: 90 000 ₽ (6 000 000 × 1.5%)

• Основной долг: 2 579 ₽

Из 92 579 ₽ только 2 579 ₽ уменьшают ваш долг. Это 2.8% платежа. Остальные 97.2% — банку за пользование деньгами. Именно поэтому досрочное погашение в первые годы даёт максимальный эффект: каждый дополнительный рубль уменьшает базу, на которую начисляются проценты.

К середине срока соотношение выравнивается. К последним годам — почти весь платёж идёт на погашение долга.

Полный набор финансовых функций Excel

Готовый шаблон

Все расчёты из этой статьи мы собрали в один Excel-файл с пятью листами:

• Параметры — ввод данных, ключевые расчёты по обоим типам платежей, проверка доступности (платёж / доход)

• Аннуитет — полный график аннуитетных платежей на срок до 30 лет с разбивкой: проценты, основной долг, остаток, доля процентов в платеже

• Дифференцированный — полный график дифференцированных платежей для сравнения

• Досрочное погашение — два сценария (уменьшить срок / уменьшить платёж) с расчётом экономии

• Сравнение — сводная таблица: аннуитет vs дифференцированный по всем показателям

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

Скачать калькулятор ипотеки →