График погашения и кредитный портфель компании
Разбираем ключевой элемент кредитной финансовой модели. Из чего состоит и как моделируется график погашения долга, а также кредитный портфель и кредиты и займы в Балансе.
Привет!
Если вы строите финансовую модель для получения кредита в банке, LBO-сделки или любого проекта с долговым финансированием, то ключевым элементом этой модели является График погашения долга. Это не просто важная, а необходимая часть такой модели. Звучит довольно просто, но на практике его создание может вызывать сложности.
В этом материале мы разберем, из чего состоит график погашения и как его построить в финансовой модели, с формулами и примерами.
Эта тема — справочное пособие по моделированию графика погашения кредитов и немного кредитного портфеля. Вы узнаете:
Поехали!
Если вы строите финансовую модель для получения кредита в банке, LBO-сделки или любого проекта с долговым финансированием, то ключевым элементом этой модели является График погашения долга. Это не просто важная, а необходимая часть такой модели. Звучит довольно просто, но на практике его создание может вызывать сложности.
В этом материале мы разберем, из чего состоит график погашения и как его построить в финансовой модели, с формулами и примерами.
Эта тема — справочное пособие по моделированию графика погашения кредитов и немного кредитного портфеля. Вы узнаете:
Поехали!
Что такое график погашения и зачем он нужен
В двух словах график погашения (Debt Schedule в англоязычной терминологии) — это детальная таблица, которая показывает, каким образом и почему будет меняться остаток долга компании с течением времени.
Для финансовой модели, целью которой является привлечение заемного финансирования, это ключевой элемент. По сути это «сердце» всей долговой части вашей финансовой модели. Именно в графике погашения рассчитываются процентные платежи для P&L, темп погашения основного долга (тела кредита) для ДДС и остаток долга для Баланса.
Зачем он нужен? В первую очередь, он демонстрирует кредитору (банку) в явном, наглядном виде, как будут выглядеть платежи компании этому самому кредитору. То есть для кредитора (с его перспективы) это его входящий денежный поток или возвраты. Также, без корректно построенного графика погашения долга невозможно рассчитать ни один релевантный показатель, будь то DSCR или LLCR, которые могут выступать ковенантами в кредитном договоре. Наконец, график погашения (в скорректированном или неизменном виде) является частью кредитного договора, то есть после подписания договора это задокументированное обязательство компании по срокам и размерам выплат кредита (долга).
Для финансовой модели, целью которой является привлечение заемного финансирования, это ключевой элемент. По сути это «сердце» всей долговой части вашей финансовой модели. Именно в графике погашения рассчитываются процентные платежи для P&L, темп погашения основного долга (тела кредита) для ДДС и остаток долга для Баланса.
Зачем он нужен? В первую очередь, он демонстрирует кредитору (банку) в явном, наглядном виде, как будут выглядеть платежи компании этому самому кредитору. То есть для кредитора (с его перспективы) это его входящий денежный поток или возвраты. Также, без корректно построенного графика погашения долга невозможно рассчитать ни один релевантный показатель, будь то DSCR или LLCR, которые могут выступать ковенантами в кредитном договоре. Наконец, график погашения (в скорректированном или неизменном виде) является частью кредитного договора, то есть после подписания договора это задокументированное обязательство компании по срокам и размерам выплат кредита (долга).
Как выглядит график погашения
Прежде чем рисовать таблицу в Excel или Google Sheets и писать формулы, давайте разберемся, как выглядит график погашения и из чего он состоит.
Стандартный, минималистичный график погашения для финансовой модели состоит из 4 показателей. Такой график предполагает, что это один кредит (один транш), начисление и погашение процентов происходит синхронно. Вот эти показатели:
1. Начальный остаток долга.
Сумма долга на начало отчетного периода (месяца, квартала, года). Для первого периода года это сумма полученного кредита, для последующих периодов этот показатель равен конечному остатку предыдущего года. Как с остатком денежных средств на начало и конец периода в ДДС.
2. Платеж по основному долгу (возврат).
Сумма, которую компания должна погасить по кредитному договору в этом периоде в соответствии с условиями этого договора. В случае, если договора еще нет, а есть только модель или прогноз, то это фактически предполагаемая сумма для погашения в периоде.
3. Проценты.
Сумма процентов за пользование кредитными средствами, начисленных за период и подлежащих уплате в этом же периоде (обычно в конце периода).
4. Конечный остаток долга.
Сумма долга на конец периода, которая равна разнице между начальным остатком долга и платежом по основному долгу.
Стандартный, минималистичный график погашения для финансовой модели состоит из 4 показателей. Такой график предполагает, что это один кредит (один транш), начисление и погашение процентов происходит синхронно. Вот эти показатели:
1. Начальный остаток долга.
Сумма долга на начало отчетного периода (месяца, квартала, года). Для первого периода года это сумма полученного кредита, для последующих периодов этот показатель равен конечному остатку предыдущего года. Как с остатком денежных средств на начало и конец периода в ДДС.
2. Платеж по основному долгу (возврат).
Сумма, которую компания должна погасить по кредитному договору в этом периоде в соответствии с условиями этого договора. В случае, если договора еще нет, а есть только модель или прогноз, то это фактически предполагаемая сумма для погашения в периоде.
3. Проценты.
Сумма процентов за пользование кредитными средствами, начисленных за период и подлежащих уплате в этом же периоде (обычно в конце периода).
4. Конечный остаток долга.
Сумма долга на конец периода, которая равна разнице между начальным остатком долга и платежом по основному долгу.
Однако, не все кредитные продукты имеют такую структуру. В общем случае начисление и уплата процентов могут быть разнесены во времени. А для кредитных линий, и тем более возобновляемых кредитных линий, есть еще и возможность выборки, т.е. получения заемных средств в рамках установленного лимита. То есть, к стандартным пунктам выше добавятся: выборка (получение траншей), начальный и конечный остатки начисленных и неуплаченных процентов; а сами проценты разделятся на две составляющие: начисление процентов и уплата процентов.
Дополнительно в финансовой модели в блок с графиком погашения можно включить показатель CFADS (Cash Flow Available for Debt Service) — денежные средства доступные для обслуживания долга. Это наглядно покажет, что выплаты по долгу не превышают доступных для этого средств. Ведь Excel все стерпит, и можно смоделировать график погашения таким образом, что деньги будут браться «из воздуха». Такие модели иногда встречаются, но они, к сожалению, оторваны от реальности, и кредитор такую модель всерьез рассматривать не будет.
Когда график погашения переносится в кредитный договор, то он, наоборот, может упрощаться до погашения основного долга. Это связано с тем, что в кредитном договоре условия начисления и погашения процентов описаны корректными юридическими формулировками, и дублировать информацию не нужно. Так может выглядеть график погашения в кредитном договоре: открыть изображение. А, например, шаблон для графика погашения основного долга можно посмотреть и скачать здесь. Такой график не подойдет для финансовой модели, но подойдет для диалога с банком.
Дополнительно в финансовой модели в блок с графиком погашения можно включить показатель CFADS (Cash Flow Available for Debt Service) — денежные средства доступные для обслуживания долга. Это наглядно покажет, что выплаты по долгу не превышают доступных для этого средств. Ведь Excel все стерпит, и можно смоделировать график погашения таким образом, что деньги будут браться «из воздуха». Такие модели иногда встречаются, но они, к сожалению, оторваны от реальности, и кредитор такую модель всерьез рассматривать не будет.
Когда график погашения переносится в кредитный договор, то он, наоборот, может упрощаться до погашения основного долга. Это связано с тем, что в кредитном договоре условия начисления и погашения процентов описаны корректными юридическими формулировками, и дублировать информацию не нужно. Так может выглядеть график погашения в кредитном договоре: открыть изображение. А, например, шаблон для графика погашения основного долга можно посмотреть и скачать здесь. Такой график не подойдет для финансовой модели, но подойдет для диалога с банком.
Как построить график погашения в Excel пошагово
Давайте построим простой график погашения для проекта, чтобы наглядно показать процесс создания этого инструмента. Предположим, что у нас есть инвестиционный проект, для реализации которого мы хотим привлечь кредит в банке. Параметры кредита таковы:
- Сумма кредита: 100 млн руб.; её мы получаем сразу одним траншем.
- Срок: 5 лет
- Процентная ставка: 18% годовых. Проценты начисляются на конечный остаток долга предыдущего периода (или начальный остаток долга текущего периода) и выплачиваются в текущем периоде.
- Обязательный годовой платеж по основному долгу: 20 млн руб.
Давайте посмотрим, что нужно пошагово сделать для создания простого графика погашения.
Шаг 1. Создаем структуру таблицы (пустографку).
Выносим параметры кредита в шапку, формируем таблицу с 4 минимально необходимыми показателями, о которых рассказывали выше, и 5 годами по сроку кредита.
Шаг 2. Заполняем остатки.
Начальный остаток долга для 1-го года будет равен сумме кредита. Начальный остаток долга для 2-5 периодов будет равен конечному остатку предыдущего периода. Конечный остаток долга каждого периода равен начальному остатку того же периода за вычетом платежа по основному догу (ОД).
Шаг 3. Рассчитываем проценты простым способом.
Для простоты расчета процентов используем надежный (но не самый точный) метод — расчет по начальному остатку. Проценты = Начальный остаток долга * 18% (здесь лучше поставить ссылку на ставку в шапке).
Шаг 4. Вносим в таблицу платеж по основному долгу (ОД).
Можно это сделать, прямо указав в каждом периоде на сумму платежа по ОД, зафиксированную в условии (в шапке). Но лучше предусмотреть предохранитель на случай, если условия кредита не будут такими удачными, как в нашем примере. Компания не может заплатить больше денег, чем остаток по кредиту. Чтобы учесть это условие, нам нужно использовать несложную формулу: =МИН(Платеж по ОД из шапки; Начальный остаток долга). Она никогда не позволит нам платит больше, чем мы были должны на начало периода. Это не универсальная формула, но в данном случае она делает свое дело.
В финансовой модели такой график выносится на отдельный лист, и информация из него транслируется в три формы финансовой отчетности. Об этом чуть ниже.
Сложный график погашения с разделением основного долга и процентов создается примерно так же, но с чуть большим количеством показателей.
Шаг 1. Создаем структуру таблицы (пустографку).
Выносим параметры кредита в шапку, формируем таблицу с 4 минимально необходимыми показателями, о которых рассказывали выше, и 5 годами по сроку кредита.
Шаг 2. Заполняем остатки.
Начальный остаток долга для 1-го года будет равен сумме кредита. Начальный остаток долга для 2-5 периодов будет равен конечному остатку предыдущего периода. Конечный остаток долга каждого периода равен начальному остатку того же периода за вычетом платежа по основному догу (ОД).
Шаг 3. Рассчитываем проценты простым способом.
Для простоты расчета процентов используем надежный (но не самый точный) метод — расчет по начальному остатку. Проценты = Начальный остаток долга * 18% (здесь лучше поставить ссылку на ставку в шапке).
Шаг 4. Вносим в таблицу платеж по основному долгу (ОД).
Можно это сделать, прямо указав в каждом периоде на сумму платежа по ОД, зафиксированную в условии (в шапке). Но лучше предусмотреть предохранитель на случай, если условия кредита не будут такими удачными, как в нашем примере. Компания не может заплатить больше денег, чем остаток по кредиту. Чтобы учесть это условие, нам нужно использовать несложную формулу: =МИН(Платеж по ОД из шапки; Начальный остаток долга). Она никогда не позволит нам платит больше, чем мы были должны на начало периода. Это не универсальная формула, но в данном случае она делает свое дело.
В финансовой модели такой график выносится на отдельный лист, и информация из него транслируется в три формы финансовой отчетности. Об этом чуть ниже.
Сложный график погашения с разделением основного долга и процентов создается примерно так же, но с чуть большим количеством показателей.
Откройте пример в Google Sheets, чтобы наглядно его изучить. В файле вы найдете простой и сложный графики погашения.
Как рассчитать проценты по кредиту в модели
Расчет процентов — самый сложный момент в моделировании графика погашения и кредитов в финансовой модели. Это напрямую влияет на всю модель: на P&L, на ДДС и, иногда, на Баланс, а также по цепочке влияет на расчет некоторых показателей.
Существует два основных подхода.
1. Расчет по начальному остатку.
Простой, консервативный метод, в основе которого лежит начисление процентов на начальный остаток долга, т.е. на остаток долга на начало периода. Формула для расчета процентов выглядит минималистично: =Начальный остаток * Ставка (годовая, квартальная или месячная, будьте внимательны). Такой расчет удобен тем, что не создает циклических ссылок. Но его недостаток заключается в том, что он никак не учитывает движения по основному долгу (телу кредита) в расчетном периоде. Это снижает точность моделирования. Реализацию этого метода можно увидеть в простом графике погашения из примера выше.
2. Расчет по среднему остатку.
Это более точный с точки зрения моделирования метод, но его главным недостатком является то, что он может создавать циклические ссылки. Это не критично, и как с этим работать, можно посмотреть в статье про циклические ссылки в блоге. Вкратце, в Excel и Google Sheets предусмотрен инструмент итеративных вычислений, который легко решает проблему циклических ссылок для этого метода расчета процентов. Метод предполагает, что компания каким-то образом брала или погашала кредит в течение периода, и это происходило в среднем равномерно. Проценты в таком случае можно начислять на среднее значение остатка долга на начало и конец периода. Формула: =СРЗНАЧ(Начальный остаток долга; Конечный остаток долга) * Ставка. Вы можете наглядно посмотреть на этот метод, скачав пример по ссылке перед этим разделом.
Существует два основных подхода.
1. Расчет по начальному остатку.
Простой, консервативный метод, в основе которого лежит начисление процентов на начальный остаток долга, т.е. на остаток долга на начало периода. Формула для расчета процентов выглядит минималистично: =Начальный остаток * Ставка (годовая, квартальная или месячная, будьте внимательны). Такой расчет удобен тем, что не создает циклических ссылок. Но его недостаток заключается в том, что он никак не учитывает движения по основному долгу (телу кредита) в расчетном периоде. Это снижает точность моделирования. Реализацию этого метода можно увидеть в простом графике погашения из примера выше.
2. Расчет по среднему остатку.
Это более точный с точки зрения моделирования метод, но его главным недостатком является то, что он может создавать циклические ссылки. Это не критично, и как с этим работать, можно посмотреть в статье про циклические ссылки в блоге. Вкратце, в Excel и Google Sheets предусмотрен инструмент итеративных вычислений, который легко решает проблему циклических ссылок для этого метода расчета процентов. Метод предполагает, что компания каким-то образом брала или погашала кредит в течение периода, и это происходило в среднем равномерно. Проценты в таком случае можно начислять на среднее значение остатка долга на начало и конец периода. Формула: =СРЗНАЧ(Начальный остаток долга; Конечный остаток долга) * Ставка. Вы можете наглядно посмотреть на этот метод, скачав пример по ссылке перед этим разделом.
Кредитный портфель или несколько траншей
То, о чем мы говорили выше, преимущественно относится к одному кредиту или, в лучшем случае, к одной кредитной линии. Одна фиксированная ставка и один остаток долга. Но в реальности компании зачастую имеют несколько кредитных продуктов или кредитуются в разных банках. А, это автоматически означает наличие разных кредитных ставок, разных сроков кредитования и погашения, разных темпов погашения, условий и ковенантов.
Как моделировать график погашения в этом случае?
Задача чаще всего сводится к моделированию кредитного портфеля. Главная ошибка, которую вы можете допустить — пытаться смешать все в одной таблице. Правильный подход — разделение.
1. Создайте отдельные графики погашения для каждого кредита и транша.
Для одного кредита свой график со своей ставкой и сроком, для другого — свой, для кредитной линии — третий. Даже в рамках одной кредитной линии (если она невозобновляемая) разделите транши. Это позволяет сохранить прозрачность и гибкость.
2. Создайте сводный, консолидированный блок.
Это таблица, которая агрегирует показатели индивидуальных графиков. Простое суммирование. Простое «итого».
3. Свяжите с формами отчетности только итоговые цифры из сводного блока.
Именно итоговые значения отправляются в P&L, ДДС и Баланс. Об этом ниже.
Такой подход делает вашу модель прозрачной, легко читаемой, легко настраиваемой, проверяемой, масштабируемой и еще много полезных прилагательных.
Как моделировать график погашения в этом случае?
Задача чаще всего сводится к моделированию кредитного портфеля. Главная ошибка, которую вы можете допустить — пытаться смешать все в одной таблице. Правильный подход — разделение.
1. Создайте отдельные графики погашения для каждого кредита и транша.
Для одного кредита свой график со своей ставкой и сроком, для другого — свой, для кредитной линии — третий. Даже в рамках одной кредитной линии (если она невозобновляемая) разделите транши. Это позволяет сохранить прозрачность и гибкость.
2. Создайте сводный, консолидированный блок.
Это таблица, которая агрегирует показатели индивидуальных графиков. Простое суммирование. Простое «итого».
3. Свяжите с формами отчетности только итоговые цифры из сводного блока.
Именно итоговые значения отправляются в P&L, ДДС и Баланс. Об этом ниже.
Такой подход делает вашу модель прозрачной, легко читаемой, легко настраиваемой, проверяемой, масштабируемой и еще много полезных прилагательных.
Шаблон модульного кредитного портфеля можно посмотреть в Google Sheets и скачать в Excel.
Связка графика погашения с формами отчетности
Здесь без сложностей. Если вы знакомы с нашим материалом про моделирование прогнозного Баланса, то понимаете, как связаны проценты, получение и погашение кредита и остаток долга с тремя формами отчетности.
Кратко напомним и заодно дадим алгоритм переноса данных из графика погашения в P&L, ДДС и Баланс:
1. Итоговые начисленные проценты → отправляются в P&L в строку «Проценты» («Расходы по процентам»).
2. Итоговый платеж по ОД и получение траншей → отправляются в ДДС в раздел «Финансовый поток».
3. Конечный остаток долга → отправляется в Баланс в «Кредиты и займы» раздела «Обязательства», а далее в зависимости от срока кредита эта сумма может оказаться в долгосрочных или краткосрочных обязательствах. Если в вашей модели такое разделение в Балансе предусмотрено, то можно и в модуле кредитного портфеля заранее рассчитать, какая часть итоговой суммы долга попадет в краткосрочные, а какая в долгосрочные.
Если вы разделяете во времени начисленные и уплаченные проценты, то начисленные оправляются в P&L, уплаченные отправляются в ДДС, при этом построенный косвенным методом ДДС должен учитывать временную разницу этих процентов, а остаток начисленных и неуплаченных процентов отправляется в Баланс.
При корректном переносе Баланс сойдется, а вы фактически подключите модуль расчета кредитного портфеля или графика погашения к трем формам финансовой отчетности в основе вашей модели.
Кратко напомним и заодно дадим алгоритм переноса данных из графика погашения в P&L, ДДС и Баланс:
1. Итоговые начисленные проценты → отправляются в P&L в строку «Проценты» («Расходы по процентам»).
2. Итоговый платеж по ОД и получение траншей → отправляются в ДДС в раздел «Финансовый поток».
3. Конечный остаток долга → отправляется в Баланс в «Кредиты и займы» раздела «Обязательства», а далее в зависимости от срока кредита эта сумма может оказаться в долгосрочных или краткосрочных обязательствах. Если в вашей модели такое разделение в Балансе предусмотрено, то можно и в модуле кредитного портфеля заранее рассчитать, какая часть итоговой суммы долга попадет в краткосрочные, а какая в долгосрочные.
Если вы разделяете во времени начисленные и уплаченные проценты, то начисленные оправляются в P&L, уплаченные отправляются в ДДС, при этом построенный косвенным методом ДДС должен учитывать временную разницу этих процентов, а остаток начисленных и неуплаченных процентов отправляется в Баланс.
При корректном переносе Баланс сойдется, а вы фактически подключите модуль расчета кредитного портфеля или графика погашения к трем формам финансовой отчетности в основе вашей модели.
Резюмируем
1. График погашения — обязательная часть любой кредитной финансовой модели.
2. График состоит, как минимум, из 4 показателей: начального и конечного остатков долга, платежей по ОД, процентов.
3. Проценты — сложная часть моделирования графика и могут быть рассчитаны разными методами. Выбор метода расчета влияет на точность, но в случае расчета по среднему остатку могут появляться циклические ссылки.
4. Для моделирования кредитного портфеля можно использовать разделение (модульный подход): отдельные графики для каждого кредита и сводный блок, из которого агрегированные цифры транслируются в формы отчетности.
2. График состоит, как минимум, из 4 показателей: начального и конечного остатков долга, платежей по ОД, процентов.
3. Проценты — сложная часть моделирования графика и могут быть рассчитаны разными методами. Выбор метода расчета влияет на точность, но в случае расчета по среднему остатку могут появляться циклические ссылки.
4. Для моделирования кредитного портфеля можно использовать разделение (модульный подход): отдельные графики для каждого кредита и сводный блок, из которого агрегированные цифры транслируются в формы отчетности.
Тренажер (практическое задание)
...скоро будет
Предыдущая
Кредитная финансовая модель