Делаем график платежей в Excel
Если вы хотя бы немного умеете управляться с Excel, мои вам поздравления! Теперь вам не придется идти в банк, чтобы:
- посчитать платежи по кредиту после досрочных погашений;
- узнать переплату по кредиту;
- определить, в какой срок вы закроете «кредитку», если будете платить по 5/7/10 тысяч в месяц;
- посмотреть, выгодно ли рефинансировать кредит;
- выяснить, сколько вы сэкономите, если погасите долг «прямо сейчас»
и т.д.
Небольшая табличка в Экселе, пара несложных формуд – и вуаля, вы великолепны! Готов примерный график погашения по любому кредиту или карте.
Приступим?
Шаг первый. Делаем таблицу.
Открываем Excel. «Обзываем» несколько столбцов:
- Остаток долга на начало месяца:
- Проценты за месяц;
- Комиссии (если есть, конечно, и при этом они включаются в платеж. Разовые комиссии сюда не пишем – они только помешают расчетам):
- Платеж по кредиту.
Отмеряем вниз столько ячеек, сколько будет платежей (например, 12, как у меня в образце).
Отдельную ячейку выделяем для процентной ставки по кредиту.
Должно получиться что-то вроде этого:
Получилось? Отлично. Продолжим.
Шаг второй. Вносим формулы и значения.
Нам известны только сумма кредита, комиссии и процентная ставка. Например, мы берем 100 тысяч рублей под 30% годовых на 12 месяцев, комиссия составляет 1% в месяц от начальной суммы кредита (вообще-то эти самые комиссии давным-давно запретили, но это вовсе не значит, что все кредиторы прекратили эту порочную практику, их взимать. Так что создаем универсальную таблицу. Нет комиссий? Отлично, просто не делайте этот столбец!)
Итак, заполняем:
- сумма на начало 1 месяца = сумме кредита (100 000);
- процентная ставка – 30%/12 (ставка годовая, а начисляются проценты каждый месяц, а значит, всего 1/12 от общей ставки и на остаток долга! Не упустите этот момент!);
- проценты за 1 месяц – сумма на начало 1 месяца, умноженная на процентную ставку;
- комиссия за 1 месяц – 1%, умноженный на начальную сумму кредита;
- сумма на начало 2 месяца = сумма на начало 1-го + проценты за 1 месяц + комиссия за 1 месяц + платеж за месяц.
- в графе «платеж за месяц» во втором месяце ставим абсолютную ссылку на эту же графу, но первый месяц. И тоже протягиваем до конца – это чтобы платежи в каждом месяце были равными.
Обратите внимание! Чтобы не вносить формулу в каждую ячейку отдельно (в последующие месяцы), а просто «протянуть» ее, мы ставим абсолютные ссылки на процентную ставку (в столбце «проценты») и на начальную сумму кредита (в столбце «комиссия»).
Получаются вот такие формулы:
Шаг третий. Дозаполняем таблицу.
- протягиваем до конца формулы в столбцах «сумма», «проценты» и «комиссия».
- «Сумму» тянем на 1 лишний месяц вперед – отсюда мы будем «плясать» при определении ежемесячного взноса.
Получилось вот что:
Шаг четвертый. Считаем ежемесячный платеж.
А теперь самое главное! Та самая «хитрая» формула, которая позволит нам, зная сумму и ставку по кредиту, узнать платеж. Называется она «подбор параметра» или «что, если» и находится, как правило, в закладке «данные» главного меню Экселя. Если не можете найти эту возможность в вашей версии, воспользуйтесь справкой (вызывается через F1). Гарантирую, она найдется.
Итак, устанавливаем задание для Экселя – нам нужно, чтобы остаток по кредиту на начало 13 месяца (то есть после выплаты кредита полностью) был равен нулю, и для этого нам нужно подобрать значение ежемесячного платежа.
Вот так:
Вуаля! При наших исходных данных сумма платежа составляет 10748,71 рублей. Если мы будем платить именно столько в месяц, то кредит «закроется» через год.
Если вы освоите эту функцию, то жизнь с кредитами заметно упростится – ведь теперь вы сами в любой момент сможете рассчитать, по сколько платить и что вам за это будет.
Я рекомендую вести такие таблички по всем оформленным кредитам и картам, чтобы ничего не упустить.
Надеюсь, эта информация оказалась вам полезной. Хотите продолжения? Ставьте «палец вверх» и подписывайтесь, я постараюсь в будущем рассказать еще больше нужного о кредитах, картах и финансах.
Искренне ваша, Lonika