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

Главное

• Для расчета платежей в Excel нужны: сумма кредита, ставка, срок, дата выдачи и тип платежа (аннуитетный или дифференцированный).
• Аннуитетный платеж в Excel считается функцией ПЛТ(ставка;кпер;пс), где ставка — месячная (годовая/12), кпер — число месяцев, пс — сумма кредита.
• Дифференцированный платеж в Excel считается вручную: тело кредита делится на срок, а проценты начисляются на остаток долга, поэтому платеж каждый месяц уменьшается.
• Переплату по кредиту в Excel можно посчитать функцией ОБЩПЛАТ, которая возвращает сумму процентов за выбранный период.
• Досрочное погашение в модели Excel снижает переплату за счет сокращения срока или уменьшения ежемесячного платежа — это видно при пересчете графика платежей.

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

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

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

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

Годовую ставку берите из договора, а не из рекламы банка. Обратите внимание на полную стоимость кредита (ПСК): по закону № 353-ФЗ она указывается на первой странице договора в правом верхнем углу в квадратной рамке — и в процентах годовых, и в рублях. ПСК включает не только проценты, но и страховки, комиссии и другие платежи, поэтому она почти всегда выше заявленной ставки. Если ПСК превышает среднерыночное значение, рассчитанное Центробанком, более чем на треть, такой договор заключать нельзя — это прямое нарушение закона.

Для расчёта в Excel достаточно номинальной ставки из договора — именно она участвует в формулах ПЛТ, ПРОЦПЛАТ и ОСПЛТ. Но для оценки реальной переплаты используйте ПСК: подставьте её в те же формулы, и вы увидите, сколько кредит стоит на самом деле. Также подготовьте данные о дополнительных расходах: комиссии за выдачу, страховые премии, плату за обслуживание счёта. В Excel удобно вести модель с отдельной строкой для каждого платежа, чтобы потом сравнить «чистые» проценты и полную стоимость.

Формула аннуитетного платежа: ПЛТ и её аргументы

Аннуитетный платёж — самый распространённый в потребительском кредитовании: сумма одинакова каждый месяц. В Excel для его расчёта есть функция ПЛТ. Синтаксис: ПЛТ(ставка; кпер; пс; [бс]; [тип]), где ставка — процентная ставка за период (годовая ÷ 12), кпер — число периодов (срок в месяцах), пс — текущая сумма кредита (с минусом, если вы хотите получить положительное значение платежа). Аргументы бс и тип обычно опускают: они нужны для расчёта будущей стоимости и платежей в начале периода соответственно.

Пример: кредит 1 000 000 ₽ на 12 месяцев под 12% годовых. Ставка за месяц = 12% ÷ 12 = 1%. Формула =ПЛТ(1%; 12; -1000000) вернёт ≈ 88 800 ₽ — это ежемесячный платёж. Обратите внимание: в формуле используется именно номинальная ставка, а не ПСК. Чтобы учесть реальную стоимость, подставьте ПСК вместо годовой ставки — результат покажет, каким был бы платёж, если бы все дополнительные расходы были включены в проценты. Разница между этими двумя цифрами — ваша «скрытая» переплата.

Функция ПЛТ удобна для быстрой оценки, но для построения графика платежей её недостаточно: нужно разбить платёж на части — проценты и основной долг. Для этого используйте пары функций ПРОЦПЛАТ и ОСПЛТ: первая возвращает сумму процентов за конкретный период, вторая — сумму, идущую на погашение тела кредита. Синтаксис обеих: (ставка; период; кпер; пс). Например, для первого месяца при тех же условиях: ПРОЦПЛАТ(1%; 1; 12; -1000000) = 10 000 ₽ процентов, ОСПЛТ(1%; 1; 12; -1000000) ≈ 78 800 ₽ — остальное. Сумма этих двух значений всегда равна аннуитетному платежу.

Расчет дифференцированного платежа в Excel

Дифференцированный платёж — это когда основной долг гасится равными долями, а проценты начисляются на остаток. С каждым месяцем платёж уменьшается. Такой график чаще встречается в ипотеке и реже — в потребительских кредитах. В Excel нет отдельной функции для дифференцированного платежа, но его легко рассчитать вручную: сумма погашения тела = сумма кредита ÷ срок в месяцах. Это постоянная величина, а проценты за период = остаток долга × ставка за месяц.

Постройте таблицу с колонками: номер месяца, остаток долга на начало периода, проценты, платёж по телу, итоговый платёж. Остаток на начало первого месяца равен сумме кредита. Проценты за первый месяц = остаток × (годовая ставка ÷ 12). Итоговый платёж = проценты + платёж по телу. Для второго месяца остаток уменьшите на платёж по телу и повторите расчёт. Формулы в Excel: для процентов — =остаток * ставка_мес, для платежа по телу — =сумма_кредита / срок_мес, для итогового — =проценты + платёж_тела.

Сравните с аннуитетом: при дифференцированной схеме первые платежи заметно выше, но общая переплата меньше, потому что проценты начисляются на быстро уменьшающийся остаток. В Excel легко посчитать суммарную переплату по обоим вариантам: сложите столбец процентов через функцию СУММ. Разница может составлять десятки тысяч рублей на сумме 1 млн ₽. Однако банки редко предлагают дифференцированные платежи по потребительским кредитам — это условие надо искать в ипотечных программах или договариваться индивидуально.

График платежей: как построить таблицу досрочного погашения

График платежей в Excel — это не просто список дат и сумм, а инструмент для анализа. Чтобы построить его корректно, создайте таблицу с колонками: дата платежа, номер периода, платёж, проценты, основной долг, остаток после платежа. Для аннуитета используйте функции ПРОЦПЛАТ и ОСПЛТ для каждого периода, как описано выше. Для дифференцированного — формулы из предыдущего раздела. В конце таблицы добавьте итоговые строки: сумма всех процентов (переплата), сумма всех платежей по телу (равна сумме кредита), общая сумма выплат.

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

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

Сколько процентов переплатите: формула ОБЩПЛАТ

Функция ОБЩПЛАТ в Excel позволяет одним действием посчитать сумму процентов, которую вы заплатите за весь срок или за его часть. Синтаксис: ОБЩПЛАТ(ставка; кпер; пс; нач_период; кон_период; тип). Аргументы нач_период и кон_период задают диапазон периодов: например, 1 и 12 — за первый год, 13 и 24 — за второй. Аргумент тип — 0, если платежи в конце периода, 1 — если в начале. Функция возвращает накопительную сумму процентов за выбранный диапазон.

Пример: кредит 1 000 000 ₽ на 12 месяцев под 12% годовых. Формула =ОБЩПЛАТ(1%; 12; 1000000; 1; 12; 0) вернёт ≈ 66 000 ₽ — это проценты за весь срок. Если взять диапазон 1–6, получите проценты за первое полугодие — они будут выше, чем за второе, потому что остаток долга в начале срока больше. Это удобно для планирования: вы видите, сколько процентов «сгорает» в первые месяцы, и понимаете, что досрочное погашение в начале срока даёт максимальную экономию.

Важно: ОБЩПЛАТ считает только проценты по номинальной ставке, без учёта страховок и комиссий. Для полной картины используйте ПСК: подставьте её в ту же формулу. Разница между результатами покажет, сколько вы переплачиваете сверх процентов из-за дополнительных услуг. Также помните: функция предполагает, что вы платите строго по графику и не допускаете просрочек. Любая задержка добавляет пени и неустойки, которые в формулу не входят — их придётся считать отдельно по условиям договора.

Как досрочное погашение меняет переплату в модели

Досрочное погашение в Excel моделируется пересчётом графика после каждого внесения дополнительной суммы. Логика простая: вы уменьшаете остаток долга, и проценты начисляются на меньшую базу. Чем раньше вы вносите досрочный платёж, тем больше экономия, потому что в начале срока доля процентов в платеже максимальна. Например, при аннуитете на 1 000 000 ₽ на 12 месяцев под 12% годовых досрочный платёж 100 000 ₽ в первый месяц сократит переплату примерно на 6 000 ₽, а в шестой — только на 3 000 ₽.

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

Чтобы пересчитать график, в Excel достаточно изменить остаток на дату досрочного платежа и заново протянуть формулы до конца срока. Убедитесь, что вы учли мораторий на досрочное погашение, если он есть в договоре (обычно 1–3 месяца с момента выдачи), и комиссию за пересчёт, если она предусмотрена. Также проверьте, как банк начисляет проценты за неполный месяц: некоторые кредиторы требуют оплатить проценты по день фактического погашения, другие — до конца месяца. Эти нюансы меняют итоговую экономию на несколько тысяч рублей, поэтому их стоит заложить в модель.

Что сделать, чтобы снизить переплату по кредиту

Модель в Excel — это не самоцель, а инструмент для принятия решений. Первое, что стоит сделать, — сравнить номинальную ставку и ПСК из договора. Если разница велика, попробуйте отказаться от навязанных страховок и дополнительных услуг: по закону вы вправе вернуть страховую премию в течение 14 дней с момента заключения договора, если услуга не была оказана. Это сразу снизит переплату.

Второй шаг — оценить свою долговую нагрузку. Банки обязаны рассчитывать показатель долговой нагрузки (ПДН) — отношение ежемесячных платежей по всем кредитам к вашему доходу. Если ПДН выше 50%, получить новый кредит или рефинансировать старый будет сложнее: Центробанк ограничивает выдачу закредитованным заёмщикам макропруденциальными лимитами. Если ваш ПДН высокий, сначала сократите текущие обязательства — например, закройте мелкие кредиты досрочно, а уже потом подавайте заявку на рефинансирование.

Третий шаг — используйте Excel для сравнения предложений. Постройте модель с одинаковыми условиями (сумма, срок), но разными ставками и комиссиями, и сравните суммарную переплату. Не гонитесь за низкой ставкой, если ПСК высокая из-за страховок. Также проверьте свою кредитную историю: запросите её через Госуслуги — Центральный каталог кредитных историй (ЦККИ) Банка России покажет, в каких бюро она хранится. Если в истории есть ошибки, исправьте их до подачи заявки — это может улучшить вашу кредитоспособность и снизить ставку. Наконец, рассмотрите досрочное погашение как стратегию: даже небольшие дополнительные платежи в первые месяцы заметно сокращают переплату, что вы легко увидите в своей модели.

Часто спрашивают

Как рассчитать проценты по кредиту в Excel?

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

Какая формула для расчета аннуитетного платежа в Excel?

Формула аннуитетного платежа в Excel — это функция ПЛТ(ставка; кпер; пс). В качестве аргументов укажите процентную ставку за период, общее количество периодов и сумму кредита.

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

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

Нужно ли учитывать ПСК при расчете в Excel?

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

Можно ли использовать Excel для модели досрочного погашения?

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

Информационный сервис. Не является финансовой рекомендацией. Окончательные условия уточняйте на сайте банка.