В своей простейшей форме план погашения кредита отслеживает платежи (разделенные на основную часть и погашение процентов) и баланс по конкретному займу. В этом разделе приведено несколько примеров планов погашения кредита, реализуемых с помощью Excel.
Простой план погашения кредита
В этом примере использован простой план, демонстрирующий основные концепции, включаемые в создание динамического плана. Обратимся к таблице на рисунке. Строки с 19 по 369 скрыты, поэтому в окне видны только первые пять и последние пять платежей.
Раздел пользовательского ввода
Область над планом погашения содержит ячейки, предназначенные для ввода пользователем исходных параметров, а также для промежуточных вычислений. Ячейки, предназначенные для ввода пользователя, отмечены другим цветом фона, поэтому хорошо видно, что может быть изменено и где содержатся формулы.
Пользователь может вводить стоимость покупки и размер авансового платежа. Объем финансирования вычисляется для дальнейшего использования в расчетах погашения кредита. Формула в ячейке В5 имеет следующий вид:
=Цена_покупки-Аванс
Чтобы сделать формулы более понятными, в них использованы описательные имена диапазонов.
Все остальные вычисления, необходимые для заполнения плана погашения, предназначены для получения сумм ежемесячных платежей. Формула в ячейке В9 следующая:
=-ОКРУГЛ(ПЛТ(Ставка/12;Срок*12;Объем_финансирования);2)
Функция ПЛТ используется для вычисления суммы ежемесячных платежей. Диапазон Ставка (ячейка В7) делится на 12, а диапазон Срок (ячейка В8) умножается на то же число. Таким образом, все аргументы приводятся к месячному базису, что гарантирует получение результата функции тоже на основе месячного базиса.
Функция ОКРУГЛ округляет результат функции ПЛТ до двух десятичных знаков. Конечно, можно не округлять результат до копеек или центов, чтобы достичь большей точности, однако вряд ли какой-либо банк примет у вас платеж в десятых долях копеек. Следовательно, эти десятые доли нужно исключить из плана погашения.
Сводная информация
В первой строке плана, непосредственно под заголовком, содержатся сводные формулы. В данном примере видны только общие итоговые суммы, однако при желании можно включить итоги по годам, кварталам и любым нужным периодам. Формула, введенная в ячейке В13 и размноженная на всю строку, имеет следующий вид:
=СУММ(В14:В381)
Помещение итоговой информации над детальным планом исключает необходимость прокрутки вниз в конец рабочего листа для доступа к этой информации.
План погашения кредита
Сам план погашения кредита начинается со строки 14, где в качестве начального баланса отображается сумма финансирования. Первый платеж должен быть внесен ровно через месяц после получения займа. Строка первого платежа (15-я) и все следующие строки содержат одни и те же формулы, приведенные ниже. Формула в ячейке Е14 следующая:
=Объем_финансирования
Для приращения дат платежей используется функция ДАТА. Формула в ячейке А15 имеет следующий вид:
=ДАТА(ГОД(А14);МЕСЯЦ(А14)+1;ДЕНЬ(А14))
Функция ДАТА формирует дату из аргументов года, месяца и дня. Аргументы основаны на ячейке, находящейся непосредственно выше, при этом месяц увеличивается на единицу.
Столбец платежей ссылается на функцию ПЛТ из раздела пользовательского ввода. Так как результат этой формулы уже округлен, дальнейшее округление не требуется.
Столбец процентной составляющей платежа формирует результат на основе предыдущего баланса. Формула в ячейке С15 следующая:
=ОКРУГЛ(Е14*Ставка/12;2)
Предыдущий баланс, содержащийся в ячейке Е14, умножается на годовую процентную ставку, деленную на 12. Годовая процентная ставка находится в ячейке В7 с именем Ставка. Баланс каждого месяца должен быть округлен до копейки (цента), это также касается и процентной составляющей платежа.
Та часть платежа, которая превысила процентную составляющую, формирует основную составляющую платежа. Формула в ячейке D15 следующая:
=В15-С15
В заключение обновляется баланс, чтобы отразить основную часть платежа. Формула в ячейке Е15 следующая:
=E14-D15
Планы погашения проверяют сами себя. Если все настроено правильно, заключительный баланс в последнем периоде будет равен нулю (или близок к нулю, учитывая ошибки округления).
Еще одна проверка добавлена в компонент основной составляющей платежа. Сумма всех этих значений должна равняться исходной сумме займа.
Ограничения
Этот тип плана погашения кредита отлично подходит для займов, не изменяющихся во времени. Он настраивается один раз и действует в течение всего срока займа. В дальнейшем он может быть скопирован для создания нового плана, в который нужно внести всего несколько изменений.
Наверное, вы заметили, что баланс в конце срока займа составляет не нуль, а -4,07. Причиной этого являются ошибки округления, накопившиеся при вычислении всех основных и процентных составляющих платежа. Поскольку все эти округления жизненно необходимы, более гибкий план позволит скорректировать заключительный платеж так, чтобы баланс был нулевым.
Приведенный выше план погашения кредита лишен гибкости и в других аспектах.
• Платеж вычисляется и применяется каждый месяц, но не учитывает возможность досрочных и просроченных платежей.
• Многие займы имеют переменные процентные ставки, а данный план не позволяет скорректировать процентную ставку в конкретном периоде.
• Несмотря на то, что пользователю предоставлена возможность определить срок займа, количество строк в плане фиксировано. Определение более короткого или более продолжительного срока займа потребует удаления или добавления формул.
Динамический план погашения кредита
Пример этого раздела основан на предыдущем. На рисунке показан план погашения займа, позволяющий вычислить параметры, отличные от объемов, процентной ставки и срока займа. Обратите внимание на то, что строки с 22 по 114 скрыты.
Первое заметное отличие состоит в том, что в плане больше затененных ячеек, т.е. тех, в которые пользователь может вносить изменения. Также был добавлен столбец годовой процентной ставки, которая может быть определена для каждого периода.
Раздел пользовательского ввода
В этом разделе изменений не так много. Процентная ставка теперь получила название начальной, а платеж – вычисляемого. Это указывает на то, что данные значения могут динамически изменяться.
Сводная информация
Теперь пользователь может изменять срок займа, процентную ставку и суммы платежей, которые влияют на срок погашения займа. В сводной информации суммируются только допустимые строки. Формула в ячейке С13 следующая:
=СУММЕСЛИ($G15:$G374;“>=0”;С15:СЗ74)
После того как баланс в столбце G становится нулевым, вычисление прекращается. Функция СУММЕСЛИ используется для суммирования значений столбца только до этой точки. Эта формула копируется в столбцы процентной и основной составляющей платежа. Абсолютная ссылка гарантирует, что новые формулы также будут указывать на столбец G.
План погашения кредита
Когда план погашения содержит много полей, изменяемых пользователем, многие из формул должны изменяться в соответствии с разными условиями. Такой план имеет два типа вводимых данных:
• данные, изменяющие только один платеж;
• данные, которые изменяют все последующие платежи.
Когда процентная ставка изменяется для одного платежа, она оказывает влияние на все последующие платежи. На предыдущие платежи никакого влияния новая процентная ставка не оказывает. По этой причине ячейки столбца Ставка ссылаются только на данные, находящиеся выше. Формула в столбце В15 наследует начальную процентную ставку из раздела пользовательского ввода. Формула в ячейке В16, копируемая в нижние ячейки, просто повторяет процентную ставку предыдущего месяца.
=В15
Это дает возможность пользователю ввести новую процентную ставку только в одну ячейку, после чего она будет размножена на все последующие месяцы. Таким образом, она будет распространяться до тех пор, пока снова не будет изменена вручную. В рассматриваемом примере банк информировал, что начиная с пятого платежа (строка 19) процентная ставка понижается до 4,8%. Эта ставка вводится в ячейку В19, и все ячейки под ней наследуют данное изменение.
Изменение даты платежа оказывает влияние только на свою строку. Если платеж запоздал, это не значит, что и все последующие платежи будут осуществлены позже. В данном примере третий платеж (строка 17) был осуществлен десятью днями позднее. Это не повлияло на следующий платеж, который был сделан уже вовремя. Для этого типа данных приращение даты должно выполняться относительно даты займа, которая остается неизменной. Формула в ячейке А15 следующая:
=ДАТА(ГОД(Дата_3айма);МЕСЯЦ(Дата_займа)+СТРОКА()-14;ДЕНЬ(Дата_займа))
Эта формула копируется вниз во все строки. В отличие от предыдущего примера, формула не ссылается на ячейку выше; вместо этого она ссылается на базу Дата_займа. Так как платежи начинаются только с 15-й строки, текущая строка за вычетом четырнадцатой используется для вычисления приращений месяца.
Ключевым моментом этой формулы является то, что пользователь может заменить ее в некоторой ячейке константой, что не окажет влияния на следующие строки. В ячейке А17 дата платежа была изменена, что повлияло на вычисление текущего платежа, но никак не отразилось на следующих платежах.
Так как для дополнительных платежей был выделен дополнительный столбец, общая сумма платежа не изменяется, за исключением того, что нужно учитывать ошибки округления предыдущего платежа. Формула в ячейке С15 следующая:
=ЕСЛИ(G14+Е15-Месячный_платеж-Б15<5;G14+E15-D15;Месячный_платеж)
Если оставшийся баланс меньше обычного платежа, оплачивается только баланс (плюс проценты). Однако в данном примере мы налагаем запрет на платежи с суммой до пяти долларов. Если обычный платеж оставляет после себя такой небольшой баланс, этот остаток просто добавляется к платежу. Вообще-то в последних платежах нет ничего предосудительного, и если вас они устраивают, вы можете упростить предыдущую формулу следующим образом:
=ЕСЛИ(G14+Е15<Месячный_платеж+D15;G14+E15-D15;Месячный_платеж)
Вычисление процентной составляющей должно теперь учитывать тот факт, что платеж был сделан раньше или позже. Вместо деления процентной ставки на 12, как в предыдущем примере, процентная ставка умножается на частное количества дней по отношению к 365. Формула в ячейке Е15 следующая:
=ОКРУГЛ(G14*B15*(А15-А14)/365;2)
Формула в столбце основной части платежа практически не изменилась, но теперь включает в себя дополнительные платежи. Формула в ячейке F15 следующая:
=C15+D15-Е15
Баланс вычисляется в результате вычитания основной составляющей текущего платежа из предыдущего баланса – точно так же, как и в предыдущем примере.
Заключительные штрихи
Как следует из рисунка выше (на этом рисунке скрыта масса средних строк, так что можно увидеть последние), заключительный платеж содержится в строке 127; вычисления ниже этой строки не осуществляются. Однако это еще не все. Все ячейки в плане погашения кредита, начиная с пятнадцатой, имеют условное форматирование. Если значение столбца G предыдущей строки меньше или равно нулю, цвет фона и текста становятся белыми, поэтому их содержимое невидимо.
Для применения условного форматирования выделите диапазон ячеек А15:G374, после чего выберите команду Главная→Стили→Условное форматирование→Создать правило. В качестве формулы введите следующее правило:
=$G14<=0
Абсолютная ссылка на столбец гарантирует, что все столбцы выделенного диапазона будут ссылаться на столбец G. Относительная ссылка на строку 14 обеспечит всем строкам ссылку на предыдущую строку.
Формулы, находящиеся под строкой 127 (вплоть до 360 месяцев), скрыты с помощью условного форматирования. Таким образом, применение условного форматирования позволяет динамически изменять размеры таблицы.
Использование таблиц платежей и процентных ставок
В предыдущем примере пользователь мог вводить данные непосредственно в план погашения. Такой подход обеспечивает максимальную гибкость; кроме того, он интуитивно понятен. Однако в зависимости от квалификации пользователя такая свобода может стать источником ошибок. В частности, замена формулы константой не позволит скорректировать ошибку, если пользователь не знаком с внутренней структурой рабочего листа. И такие константы могут появиться повсеместно; при этом пользователь даже не будет знать, что отображаются на самом деле не результаты вычисления формул.
Существует еще один метод, обеспечивающий такую же гибкость работы пользователя с таблицей выплат. Он заключается в полном отделении области ввода пользователя от области расчетов и отчетности. В этом случае проще определить, что ввел пользователь, и какие данные отсутствуют.
В следующем примере используются те же данные, что и в предыдущих двух, однако добавлены дополнительные таблицы – платежей, процентных ставок и платежей, выполненных не в срок. Соответствующим образом изменены и формулы. На рисунке показана область пользовательского ввода гибкого плана погашения.
В плане погашения кредита ничто не может быть изменено пользователем. Изменения в таблице платежей выполняются как реакция на значения в столбце В или значения в одной из трех таблиц, расположенных правее. Формулы, которые не были изменены, не упоминаются в этом разделе.
Даты
=ДАТА(ГОД(Дата_займа2);МЕСЯЦ(Дата_займа2)+СТРОКА()-14;
ДЕНЬ(Дата_займа2))+ЕСЛИОШИБКА(ПРОСМОТР(ДАТА(ГОД(
Дата_займа2);МЕСЯЦ(Дата_займа2)+СТРОКА()-14;ДЕНЬ(
Дата_займа2));tblLate;Задержка2);0)
Эта формула выглядит несколько загроможденной, однако она не так плоха. Начинается она все с той же формулы, которая была использована в предыдущем примере и в которую добавлено количество дней опоздания из таблицы tblLate. Функция ПРОСМОТР ищет точное соответствие в первом столбце таблицы tblLate, извлекает значение из ее второго столбца (положительное или отрицательное), которое затем добавляется к дате положенного платежа. Функция ЕСЛИОШИБКА используется для возвращения нуля, если соответствие не найдено (в данной ситуации сохраняется исходная вычисленная дата).
Процент
Таблица tblRate содержит список изменений процентной ставки. Функция ПРОСМОТР использована с отсутствующим четвертым аргументом, так что изменение ставки сохраняется, пока ее значение не будет изменено снова. Таким образом, даты в таблице tblRate должны быть отсортированы.
Если искомое значение в таблице не найдено, функция ЕСЛИОШИБКА возвращает начальную ставку.
=ЕСЛИОШИБКА(ПРОСМОТР(А15;tblRate;2);Ставка2)
Дополнительные платежи
В таблице tblAdd перечислены дополнительные платежи, а также даты их начала и окончания. Для добавления единоразового платежа пользователь может записать одинаковые даты начала и конца платежа. Однако если дополнительные платежи вносятся в течение длительного промежутка времени, такой подход упрощает пользователю задачу. Формула СУММЕСЛИМН добавляет сумму дополнительного платежа в каждую строку плана погашения кредита между датами начала и конца платежа. Это значит, что даже если в одну дату было внесено несколько дополнительных платежей, все они будут учтены.
=СУММЕСЛИМН(tblAdd[Дополн.];tblAdd[Начало];“<=”&А15;tblAdd[Конец];“>=”&А15)
Расчеты по кредитной карточке
Последним типом планов погашения кредита являются расчеты по кредитной карточке. Кредитные карточки имеют свои особенности, поскольку минимальный платеж варьируется в зависимости от внешнего баланса. Для карточки можно использовать описанный выше метод таблицы платежей, однако количество строк в нем ограничено, что не подходит для большинства приложений. Также можно использовать описанный ранее метод, где платежи вводились непосредственно в план погашения. Однако когда платежи отличаются, план теряет свой смысл как основа прогнозирования или планирования. Необходимо создать такой план, который позволил бы прогнозировать платежи по займу с помощью кредитной карточки.
Расчеты по кредитной карточке связаны с несколькими нестандартными проблемами. Дело в том, что финансовые функции Excel (такие как ПС, БС и КПЕР) предполагают регулярные платежи с одной и той же суммой. Функция ПЛТ также возвращает один уровень платежей. При использовании функций ЧПС и ВСД пользователь может вставить в денежные потоки разные платежи.
Банки, выпускающие кредитные карточки, обычно требуют выполнения стандартного набора правил.
• Сумма платежа ограничена снизу. К примеру, счет кредитной карточки может требовать минимальный платеж в 25 долларов.
• Платеж должен быть равен, по крайней мере, процентам от просроченной суммы. Обычно платеж рассчитывается как процент остаточного баланса, но не меньше указанной суммы.
• Платеж округляется (обычно до ближайших пяти центов).
• Ставка неизменна на протяжении некоторого периода (обычно месяца).
На рисунке показан рабочий лист, настроенный для расчета платежей по кредитной карточке.
Формула расчета минимального платежа достаточно сложна, равно как и сам принцип расчета. В данном примере используется минимальный платеж в 25 долларов или 3 процентов баланса, что может намного увеличить время погашения кредита. Если кредитор надеется погасить кредит за разумный промежуток времени, ему потребуются дополнительные столбцы расчетов.
Формула минимального платежа (ячейка В13) имеет следующий вид:
=МИН(F12+D13;ОКРУГЛ(МАКС($В$7;ОКРУГЛ($B$6*F12;2));$В$8))
По этой формуле вычисляется минимум суммы баланса или минимальный процент, а затем результат округляется до ближайших пяти центов. Это округленное значение затем сравнивается с просроченным балансом, после чего используется меньшее из двух значений.
Все становится значительно сложнее, если вносятся дополнительные платежи. В этом случае формулы должны учитывать “благоприятные периоды” для покупок. Дополнительную путаницу вносит то, что в подобных случаях ставка рассчитывается на ежедневной основе и берет в расчет дневной эффективный эквивалент процентной ставки.