Автоматический подбор кредитов в моделях

Открыть эту статью в PDF

Назначение автоматического подбора

Автоматический подбор кредита (Debt Sculpting) — процедура расчета графика получения и погашения кредита в соответствии с денежными потоками проекта и заданными банком условиями.

Хотя автоматический подбор обычно охватывает и получение, и возврат кредита, главный элемент в этой задаче именно подбор графика возврата. Стандартные условия, определяющие график выплат:

• кредит должен быть выплачен как можно скорее;
• выплаты кредита не должны приводить к тому, что у компании образуется отрицательный остаток денег на счете;
• выплаты кредита должны обеспечивать значение показателя DSCR не ниже установленного банком предела.

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

Базовые формулы для подбора кредита

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

Например, мы видим, что в 2022 году для запуска проекта компании требуется 100 млн руб. Эти деньги мы можем получить под 10% годовых. Если компания возьмет кредит на 100 млн руб., то ее долг увеличится, понадобится заплатить проценты и окажется, что нам не хватает еще 10 млн руб. Но если взять кредит на 110 млн руб., то снова увеличатся проценты и так далее…

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

Расчет графика поступлений и погашения кредита, вне зависимости от того, какими методами он будет реализован, основан на следующем наборе формул:

где:
Proceeds и Repayment — суммы поступления и погашения кредита
Cash — остаток денег на счете
Outstanding Debt — остаток непогашенного долга
CFADS — денежный поток, доступный для обслуживания долга
DSCR — целевое значение коэффициента покрытия долга
Interest — проценты по кредиту

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

Основные способы подбора

Существует два основных подхода к автоматическому расчету графика кредита:

1. Формулы Excel с циклическими ссылками.

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

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

2. Макросы

В этом случае описанный выше алгоритм подбора реализуется в виде макроса VBA и вызывается по нажатию кнопки. Так, например, работает программа Альт-Инвест.

Подход на основе макросов работает стабильнее, позволяет по ходу подбора кредита сделать дополнительные проверки (например, отреагировать на ситуацию, когда после изменения проекта не удается выплатить весь кредит до его окончания), но имеет и свои недостатки:

• Вычисления выполняются не мгновенно при изменении любого параметра, а только по нажатию кнопки вызова макроса.
• В модели придется разрешить использование макросов, что иногда рассматривается как небезопасная практика.

Примеры реализации автоматического подбора

Мы подготовили небольшой пример того, как выглядит автоматический подбор кредита (без учета требования к DSCR):

Открыть видео на youtube

Более подробные версии автоматического подбора кредитов с учетом всех факторов мы рассматриваем на учебных семинарах, таких как «Финансовое моделирование инвестиционных проектов в Excel».