Основные способы расчета кредитов в MS EXCEL

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

ВЫПУСК №12 (ДЕКАБРЬ, 2018)

 

Все финансовые функции можно найти в MS Excel в разделе главного меню «Формулы»-«Финансовые».

Предположим, есть кредит 300 000 на 24 месяца с номинальной годовой ставкой 16%*,ежемесячный платеж составляет 14 700.

Основные способы расчета кредитов в MS EXCEL

Рис. 1. Основные формулы для расчета параметров кредита

 

На цифрах примера (рис. 1) рассмотрим часто используемые формулы для просчета каждой кредитной переменной. В левой части таблицы с серой заливкой внесены исходные данные, в колонке С — результат расчета формулами, которые прописаны в колонке D. Таблица таким образом создана для удобного сравнения начальных и расчетных данных.

 

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

= СТАВКА (кпер; плт; пс; [бс]; [тип]; [предположение]).

Описание аргументов функции:

кпер — число периодов платежей по кредиту;

плт — платеж, производимый в каждый период, состоит из основного платежа и платежа по процентам;

пс — сумма на текущий момент;

бс — будущая стоимость;

тип — число 0 — оплата в конце периода, число 1 — оплата в начале периода (по умолчанию — 0).

 

Эти переменные могут быть положительными или отрицательными, в зависимости от того, получаем мы деньги или отдаем. Два последних аргумента бс и тип необязательны и мы их можем упускать.

 

В ячейке С1 введена формула = СТАВКА (B4; -B5; B3) х 12.

Сумма кредита или начальная стоимость займа/вклада:

= ПС (ставка; кпер; плт; [бс]; [тип]).

Аргументы:

ставка — процентная ставка.

 

В качестве аргумента в финансовых функциях Excel используется периодическая процентная ставка. Нужно понимать, о каких периодах идет речь в наших условиях. Если ставка годовая, а период займа в месяцах, то ставку нужно тоже разделить на 12 месяцев:

кпер — число периодов платежей по кредиту;

плт — платеж, производимый в каждый период, состоит из основного платежа и платежа по процентам;

бс — будущая стоимость;

тип — число 0 — оплата в конце периода, число 1 — оплата в начале периода (по умолчанию — 0).

 

В ячейке С2 внесена формула = ПС (B2 / 12; B4; -B5). Результат ее расчета по сравнению с начальными данными дает незначительное отклонение.

 

Для вычисления срока кредита в ячейке С4 вводим формулу = КПЕР (B2 / 12; -B5; B3):

= КПЕР (ставка; плт; пс; [бс]; [тип]). 

Описание аргументов функции:

ставка — процентная ставка;

плт — платеж, производимый в каждый период, состоит из основного платежа и платежа по процентам;

пс — сумма на текущий момент;

бс — будущая стоимость;

тип — число 0 — оплата в конце периода, число 1 — оплата в начале периода (по умолчанию — 0).

 

Аргумент ежемесячного платежа(плт) отрицательный, так как отдаем деньги.

Чтобы вычислить размер ежемесячного платежа, воспользуемся формулой ПЛТ:

= ПЛТ (ставка; кпер; пс; [бс]; [тип]).

 

Описание аргументов функции:

ставка — процентная ставка;

кпер — число периодов платежей по кредиту;

пс — сумма на текущий момент;

бс — будущая стоимость.

Тип — число 0 — оплата в конце периода, число 1 — оплата в начале периода (по умолчанию — 0).

В ячейке С5 вводим = ПЛТ (B2 / 12; B4; B3). Значение в результате отрицательное, так как эту сумму мы отдаем.

 

Формы и шаблоны для расчетов параметров займов

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

С помощью рассмотренных функций можно создать простой шаблон и проверять, реально ли закрыть кредит с предлагаемыми ежемесячными платежами банком и варианты его погашения. Для решения такой задачи используем КПЕР и ПТЛ, также понадобятся вспомогательные функции ЕСЛИ, ЕСЛИОШИБКА и ОКРУГЛВВЕРХ.

 

Функция ЕСЛИ — одна из самых популярных функций в Excel. Она позволяет выполнять логические сравнения значений и ожидаемых результатов.

= ЕСЛИ (лог_выражение;[значение_если_истина]; значение_если_ложь])

 

Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.

= ЕСЛИОШИБКА (значение;значение_если_ошибка).

Данная функция возвращает указанное значение, если вычисление по формуле вызывает ошибку; в противном случае функция возвращает результат формулы. Функция ЕСЛИОШИБКА позволяет перехватывать и обрабатывать ошибки в формуле. Возможны следующие типы ошибок: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО!:

= ОКРУГЛВВЕРХ (число; число_разрядов).

 

Приобрести полную версию статьи