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

23.03.2026

Три жизненных сценария — покупка жилья, ускоренное погашение и финансовые трудности — и 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. Создайте таблицу параметров. Для каждого банка — отдельный столбец:

|---|---|---|---|

| Ставка, % | 18.5 | 17.9 | 18.2 |

| Срок, лет | 20 | 25 | 20 |

| Первонач. взнос, % | 20 | 15 | 20 |

| Страховка в год, ₽ | 0 | 35 000 | 15 000 |

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

Ежемесячный платёж (ПЛТ)
Общая переплата за весь срок (платёж × месяцы − сумма кредита)
Эффективная стоимость — переплата + страховка за весь срок + комиссии
Платёж / доход — процент от вашего дохода

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

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

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

|---|---|---|---|

| Ежемесячный платёж | 92 579 ₽ | 94 419 ₽ | +1 840 ₽/мес |

| Общая переплата | 16 218 960 ₽ | 16 660 560 ₽ | +441 600 ₽ |

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

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

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

Базовая ставка и условия её сохранения (зарплатный проект? страховка? электронная регистрация?)
Размер и условия страховки (жизнь, имущество, титул) — включите в расчёт стоимости
Есть ли комиссия за выдачу кредита
Условия досрочного погашения — нет ли моратория на первые месяцы
Можно ли менять дату платежа
Штрафы за просрочку
Проверьте, подходите ли вы под льготные программы: семейная ипотека (6%), IT-ипотека, сельская ипотека
Материнский капитал — можно ли использовать как часть первоначального взноса

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

| Что считаем | Формула | Пример |

|---|---|---|

| Ежемесячный платёж | =-ПЛТ(ставка/12; срок*12; кредит) | =-ПЛТ(18%/12; 240; 6000000) |

| Общая переплата | =платёжсрок12 - кредит | |

| Эффективная стоимость | =переплата + страховка*срок | |

| Доля платежа в доходе | =платёж/доход*100 | |

| Сумма процентов за 1 год | =ОБЩПЛАТ(ставка/12; срок*12; кредит; 1; 12; 0) | |

---

Сценарий 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 ₽ каждый месяц.

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

| | Уменьшить срок | Уменьшить платёж |

|---|---|---|

| Платёж | 92 579 ₽ (без изменений) | ~84 700 ₽ |

| Оставшийся срок | ~195 мес | 216 мес (без изменений) |

| Общая переплата | ~14 400 000 ₽ | ~15 100 000 ₽ |

| Экономия vs без досрочного | ~1 800 000 ₽ | ~1 100 000 ₽ |

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

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

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

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

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

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

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

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

| Что считаем | Формула |

|---|---|

| Остаток долга через N месяцев | =БС(ставка/12; N; -платёж; -кредит) |

| Новый срок (при уменьшении срока) | =КПЕР(ставка/12; -платёж; новый_остаток) |

| Новый платёж (при уменьшении платежа) | =-ПЛТ(ставка/12; оставшиеся_мес; новый_остаток) |

| Проценты за конкретный месяц | =ПРПЛТ(ставка/12; номер_мес; всего_мес; -кредит) |

| Тело долга за конкретный месяц | =ОСПЛТ(ставка/12; номер_мес; всего_мес; -кредит) |

---

Сценарий 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 для этого сценария

| Что считаем | Формула |

|---|---|

| Новый платёж при увеличении срока | =-ПЛТ(ставка/12; новый_срок_мес; остаток) |

| Экономия при рефинансировании | =(старый_платёж - новый_платёж) * оставшиеся_мес |

| Остаток после продажи | =цена_квартиры - долг - расходы_на_сделку |

| Накопленная просрочка | =платёж кол-во_пропущенных_мес (1 + штраф%) |

---

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

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

Функция ПЛТ (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

| Функция | Что делает | Пример |

|---|---|---|

| ПЛТ (PMT) | Ежемесячный аннуитетный платёж | =-ПЛТ(18%/12; 240; 6000000) |

| КПЕР (NPER) | Количество платежей до погашения | =КПЕР(18%/12; -92579; 6000000) |

| БС (FV) | Остаток долга через N месяцев | =БС(18%/12; 24; -92579; -6000000) |

| ПРПЛТ (IPMT) | Проценты за конкретный месяц | =-ПРПЛТ(18%/12; 1; 240; 6000000) |

| ОСПЛТ (PPMT) | Основной долг за конкретный месяц | =-ОСПЛТ(18%/12; 1; 240; 6000000) |

| ОБЩПЛАТ (CUMIPMT) | Сумма процентов за период | =-ОБЩПЛАТ(18%/12; 240; 6000000; 1; 12; 0) |

| ОБЩДОХОД (CUMPRINC) | Сумма погашения долга за период | =-ОБЩДОХОД(18%/12; 240; 6000000; 1; 12; 0) |

| СТАВКА (RATE) | Обратный расчёт ставки по платежу | =СТАВКА(240; -92579; 6000000)*12 |

---

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

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

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

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

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