Заёмное финансирование

Вводная / Варианты погашения

Разделяем получение долга и его обслуживание

Продолжаем рабочую копию «М04_Контроль.xlsx». На листе «Расчеты» строка 51 уже содержит фактическое финансирование инвестиций долгом. Теперь рассчитываем получение и погашение долга, а также проценты для нескольких вариантов.

Рассчитываем пять вариантов обслуживания долга: аннуитетные платежи, дифференцированные платежи, облигационный заём, «Всё на долг» и ковенантные платежи. В последнем варианте связываем выплаты с заданным финансовым условием — коэффициентом покрытия обслуживания долга.

В этом уроке подготавливаем общий календарь и ставки. Затем последовательно рассчитываем первые четыре схемы. Ковенантная схема разбирается отдельно; её показатели пока не включаем в сравнение.

Перед вводом формул выбираем «Формулы → Параметры вычислений → Автоматически». Многострочную формулу вставляем через F2 или строку формул. В русской записи разделитель аргументов — точка с запятой; внутри кавычек в формулах календаря сохранён один пробел.

Пример / Периодичность

Сравниваем частоту платежей при одинаковой ставке

Для отдельного примера берём долг 7000 рублей, срок 7 лет и годовую номинальную ставку 15%. Постоянный аннуитет зависит от числа платежей в году: ставку делим на это число, а срок в годах умножаем на него.

Разница между ежегодными и ежемесячными выплатами составляет около 431,17 рубля: 6,16% от долга 7000 или 4,31% от инвестиций 10 000. Итоги рассчитаны до округления отдельного платежа.

Изменение единицы измерения не меняет процент экономии.

Это отдельная иллюстрация с выплатами в течение всех семи лет. В основной модели ниже есть льготная фаза и только 20 выплат; результаты двух примеров не смешиваем.

Шаг 01 / Условия примера

Фиксируем календарь и границы расчёта

В основной модели сохраняем 36 месячных периодов, затем кварталы; долг начинается 01.11.2015 и заканчивается 31.10.2022. Доли капитала и долга — 30/70, ставка — условные 15%.

В основном варианте выплаты происходят в конце квартала. Последняя долговая фаза завершается внутри четвёртого квартала 2022 года, поэтому расчёт дней ограничен 31 октября. Альтернатива — облигационный заём с регулярным купоном 90 дней.

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

Купонная таблица рассчитана на 20 фактических выплат; следующая строка показывает «СТОП». Эта схема не поддерживает произвольные купонные периоды, например 60 или 180 дней. Не меняем периодичность и дату эксплуатации в ходе сравнения схем.

Шаг 02 / Банковские периоды

Рассчитываем дни и флаги выплат

На «Исходные данные» вводим B243 «График погашения заемного финансирования», C244 «Периодичность выплат», D245 «для аннуитетных / дифференцированных платежей». В G246 — «Количество дней для начисления процентов», G247 — «График погашения заемного финансирования».

=ЕСЛИ(P12>O12;P116;0)*(P22=1)*(P18=1)-
ЕСЛИ(И(Дата_конца_заемного_финансирования<=P$6;
Дата_конца_заемного_финансирования>=P$5);P6-
Дата_конца_заемного_финансирования;0)

=ЕСЛИ(P246>0;1;0)

=СУММ(P246:EE246)

=СУММ(P247:EE247)

Заполняем строки 246 и 247 до EE. Строка 246 считает дни платёжного периода и ограничивает последний период датой окончания долга. Строка 247 возвращает флаг 1, если число дней положительное. Итоги должны дать 1765 дней и 20 выплат.

К P246:EE246 применяем «Обычный» с учётным числовым форматом; к P247:EE247 — «Флаг». К итогам O246:O247 применяем «СУММА». Для длинных подписей G включаем перенос текста и подбираем высоту строк.

Шаг 02 / Банковские периоды / Сверка в Excel

Календарь банковских выплат

Сопоставляем адреса и значения со своей рабочей книгой. Это результат предыдущего шага.

Шаг 03 / Купонные условия

Вводим даты и продолжительность купона

В D249 вводим «для облигационного займа». Подготавливаем подписи G и параметр J250:

=Дата_начала_заемного_финансирования

=Дата_конца_заемного_финансирования

=I251

=J255-J253

=Дата_начала_эксплуатации-1

=ОКРУГЛВВЕРХ((I252-J255)/J250;0)

К I251:I252, J253 и J255 применяем «Дата», к J250 — «Допущение», к J254:J256, кроме J255, — «Расчеты». Для J250/J254 сохраняем формат с подписью «дней». Получится 01.11.2015, 31.10.2022, конец льготы 31.12.2017, 791 день и 20 выплат. Округление вверх в J256 оставляет место для последнего короткого купона.

Шаг 03 / Купонные условия / Сверка в Excel

Параметры купонного календаря

Сопоставляем адреса и значения со своей рабочей книгой. Это результат предыдущего шага.

Шаг 04 / Даты купонов

Строим последовательность выплат

В G258 вводим «Дата конца льготного периода». Ниже в G259:G279 размещаем подписи «Дата платежа (купона) №1» … «Дата платежа (купона) №21».

=$J$255

=ЕСЛИ(И(J258+$J$250<=$I$252;J258<>0);J258+$J$250;(ЕСЛИ($I$252=J258;
"СТОП";$I$252)))

Заполняем J259 вниз до J279. К J258:J279 применяем «Дата». Формула прибавляет 90 дней, пока очередная дата не выходит за дату окончания долга; последнюю фактическую выплату назначает на 31.10.2022. После неё появляется текст «СТОП».

Проверяем: первая дата J259 — 31.03.2018, последняя фактическая J278 — 31.10.2022; J279 содержит «СТОП». Это не двадцать первая выплата, а видимый признак лишней строки.

Шаг 05 / Распределение дат

Относим каждую дату к периоду модели

=ЕСЛИ(И(P$5<=$J259;P$6>=$J259);1;0)

=СУММ(P259:EE259)

=СУММ(P259:P279)

=СУММ(P281:EE281)

=O281-J256

1. Вводим P259 и заполняем вправо до EE259, затем вниз до строки 279. Итоговая матрица — P259:EE279; в нижней правой ячейке проверяется дата J279.
2. Вводим O259 и заполняем вниз до O279: каждая фактическая дата должна попасть ровно в один период. У строки «СТОП» итог 0.
3. В G281 вводим «График погашения заемного финансирования». Заполняем P281:EE281 и рассчитываем O281 и K281.

Ожидаемые значения: O281 = 20, K281 = 0. К O259:O279 и O281 применяем «СУММА», к K281 — «ТРЕВОГА». К P259:EE279 и P281:EE281 применяем «Флаг».

Шаг 05 / Распределение дат / Сверка в Excel

Купонные флаги и их итог

Сопоставляем адреса и значения со своей рабочей книгой. Это результат предыдущего шага.

Шаг 06 / Длительность купонов

Считаем дни каждого купона

В L258 вводим «Кол-во дней». Длительность купона — разница между текущей и предыдущей датами:

=J259-J258

=СУММ(L259:L278)

Заполняем L259 вниз только до L278. В L279 находится итог, а не разность с текстом «СТОП». Регулярные купонные периоды длятся 90 дней, последний — 55 дней. Сумма L279 должна быть 1765.

К L259:L279 применяем «Обычный» с учётным числовым форматом, как в соседней проверке дней. Короткий последний купон возникает из-за установленной даты окончания долга, а не из-за изменения J250.

Шаг 07 / Проверка дней

Два способа подсчёта дней

В L250 вводим «Кол-во дней». Сопоставляем весь срок, льготную фазу и дни купонов:

=I252-I251

=J254+L279

=L251-L252

=J150-J254-СУММ(L259:L278)-1

Оба значения L251 и L252 равны 2556: 791 + 1765. В исходном календаре J150 содержит 2557 — длительность фазы с включением обеих крайних дат. Поэтому контроль K279 дополнительно вычитает единицу. K252 и K279 должны быть нулевыми; применяем к ним «ТРЕВОГА».

Шаг 08 / Дни в периодах

Переносим длительности купонов в календарь

В G283 вводим «Количество дней для начисления процентов (купонный период)». В G284:G304 — «Дата платежа (купона) №1» … «Дата платежа (купона) №21», в G305 — «Итого».

=ЕСЛИ(P259=1;$J259-$J258;0)

=СУММ(P284:EE284)

=СУММ(P284:P304)

=СУММ(P305:EE305)

=O305-L279

Заполняем P284 вправо до EE284 и вниз до строки 304. Заполняем O284:O304 и строку P305:EE305. Формула возвращает длительность только в тот период, где стоит соответствующий флаг из строк 259:279; когда флаг равен нулю, формула возвращает 0 и не вычитает дату из текста «СТОП».

O305 = 1765, K305 = 0. Последний фактический купон даёт 55 дней. К суммам O применяем «СУММА», к K305 — «ТРЕВОГА», к P284:EE304 применяем «Обычный», к P305:EE305 — «Л_ИТОГО», сохранив учётный числовой формат.

Шаг 08 / Дни в периодах / Сверка в Excel

Купонные дни по периодам

Сопоставляем адреса и значения со своей рабочей книгой. Это результат предыдущего шага.

Шаг 09 / Расчётный лист

Связываем два календаря выплат

На «Расчеты» в B56 вводим «График погашения заемного финансирования», в C57 — «Периоды выплат по заемному финансированию». В G58 — «для аннуитетных / дифференцированных платежей», в G59 — «для облигационного займа».

='Исходные данные'!P247

='Исходные данные'!P281

=СУММ(P58:EE58)

=СУММ(P59:EE59)

Заполняем обе строки до EE. К связанным строкам P58:EE59 применяем «Флаг», к O58:O59 — «СУММА». Исходные строки P247:EE247 и P281:EE281 сохраняют стиль «Флаг». O58 и O59 должны равняться 20.

В базовом сценарии оба календаря дают одну выплату за квартал, но это разные ряды: банковский и купонный. Их совпадение по количеству не означает поддержки произвольной купонной периодичности.

Шаг 10 / Общие условия

Подготавливаем годовую ставку и ставки за период

В C60 вводим «Общие условия». Подписи G61:G66:

='Исходные данные'!$J$223

=$I$61*(P58=1)*(P18=1)

Заполняем P61:EE61. I61 ссылается на согласованную ставку J223 исходных данных — 15%. Годовая ставка в строке 61 появляется только при банковском флаге 1 в фазе эксплуатации. Строка 62 остаётся подзаголовком, а строка 59 сохраняет купонные флаги.

Шаг 11 / Ставка аннуитета

Преобразуем годовую ставку в квартальную

=I61/(ЕСЛИ('Исходные данные'!J101="месяц";Число_месяцев_в_году;0)+
ЕСЛИ('Исходные данные'!J101="квартал";Число_кварталов_в_году;0)+
ЕСЛИ('Исходные данные'!J101="год";1;0)+
ЕСЛИ('Исходные данные'!J101="полугодие";Число_месяцев_в_году/6;0))

=$L$63*(P58=1)*(P18=1)

Заполняем P63:EE63. В принятой модели J101 = «квартал», поэтому L63 = 15% / 4 = 3,75%. Аннуитет использует постоянную квартальную ставку; длительность конкретного квартала здесь не меняет ставку.

К I61, P61:EE61 и P63:EE65 применяем «Процент_0,00%». L63 оформляем стилем «Расчеты» и числовым форматом 0,00%, чтобы видеть 3,75%. Тип основного периода, дата эксплуатации и календарь выплат — связанные параметры. В этих уроках сохраняем проверенную квартальную схему.

Шаг 12 / Ставки по дням

Рассчитываем ставки только для действующих периодов

=ЕСЛИ(P$5=" ";" ";P61*'Исходные данные'!P246/'Исходные данные'!P117*
(P58=1)*(P18=1))

=ЕСЛИ(P$5=" ";" ";P61*'Исходные данные'!P305/'Исходные данные'!P117*
(P58=1)*(P18=1))

Заполняем обе строки до EE. В строке 64 годовая ставка умножается на дни начисления банковских процентов из строки 246 на листе «Исходные данные» и делится на число дней года из строки 117. В строке 65 используется сумма купонных дней из строки 305. В обеих формулах используем банковский флаг из строки 58 и флаг эксплуатации из строки 18.

Внешняя функция ЕСЛИ проверяет, есть ли дата периода, до выполнения деления. После конца календаря она возвращает один пробел. Простое умножение на нулевой флаг не предотвращает деление на нулевое число дней года; ошибки активного периода здесь не скрываются.

Здесь используются разные способы расчёта ставки: аннуитет — 15%/4, остальные рассматриваемые расчёты — дни/год. Они сохранены намеренно. Купонная ставка в этой конструкции по-прежнему связана с банковским календарём, поэтому произвольные сроки купонов не поддерживаются.

Шаг 13 / Номер выплаты

Проверяем количество платёжных периодов

=СУММ($P$18:P18)*(P58=1)

=МАКС(P66:EE66)

=O58-O66

Заполняем P66:EE66. Накопительная сумма флагов эксплуатации даёт номер выплаты в выбранном календаре; банковский флаг оставляет его только в платёжных периодах. Здесь эксплуатация начинается, когда модель уже использует квартальные периоды.

O66 = 20 и K66 = 0. К P66:EE66 и O66 применяем «Обычный» с учётным числовым форматом, к K66 — «ТРЕВОГА». В EE66 сумма должна охватывать диапазон от $P$18 до EE18 и использовать флаг EE58. Начало суммирования остаётся фиксированным и не сдвигается к началу каждого года.

Шаг 13 / Номер выплаты / Сверка в Excel

Ставки и номера выплат: подписи

Сопоставляем адреса и значения со своей рабочей книгой. Это результат предыдущего шага.

Шаг 13 / Номер выплаты / Сверка в Excel

Ставки и номера платежей

Первые и последние кварталы выплат.

Оформление / Подписи и единицы

Сохраняем различие параметров, флагов и проверок

На «Исходные данные» используем «Заголовок1(к)» для B243, «Заголовок3» для C244, «Заголовок4» для D245/D249. На «Расчеты»: B56 — «Заголовок1(к)», C57/C60 — «Заголовок3». Обычные подписи G оформляем «Обычный».

К заполненным ячейкам единиц измерения в столбце H применяем «ед_изм». На исходном листе H246/H250/H254 — «дней», H247 — «Флаг», H251:H253 и H255 — «дата», H256 — «число», H258:H279 и H281 — «Флаг», H284:H305 — «дней». Подпись «Флаг» относится к матрице календаря; даты самих купонов находятся в J.

На «Расчеты» H58:H59 — «Флаг», H61 — «% в год», H63:H65 — «% за период», H66 — «Число». L251:L252 и L259:L279 сохраняют «Обычный» с учётным числовым форматом. Нулевой контроль оформляется «ТРЕВОГА» и отображается как Ok.

Для флагов создаём условное форматирование: «Главная → Условное форматирование → Правила выделения ячеек → Равно», значение 1, затем «Пользовательский формат». Задаём шрифт и цвет фона #4EA72E (RGB 78, 167, 46), белый цвет узора #FFFFFF и восходящую диагональную штриховку. Это оформление действует только на единицы; именованный стиль «Флаг» сам по себе такого правила не создаёт.

Создаём по одному правилу для каждой строки таблицы. Для двух несмежных областей выбираем первую, затем вторую с Ctrl. В «Управление правилами» проверяем условие «значение ячейки равно 1» и поле «Применяется к». Существующие правила верхнего календаря сохраняются.

Самопроверка / Платёжный календарь

Сохраняем общую основу расчёта долга

1. Проверяем 01.11.2015 → 31.10.2022, 791 день без начисления, 20 выплат и последний купон 55 дней.
2. Сопоставляем O247, O281, O58, O59 и O66: каждое значение равно 20. K252, K279, K281, K305 и K66 должны быть нулевыми.
3. Проверяем 791 + 1765 = 2556 и отдельное значение J150 = 2557, которое включает обе крайние даты.
4. После конца активного проекта, в CB64:EE65, не должно быть деления на ноль. Формулы этих строк должны возвращать один пробел.
5. На «Исходные данные» группируем созданные строки 244:305. На «Расчеты» сначала группируем 57:66, затем создаём внутри неё отдельные группы 57:59 и 61:66. Будущие строки не группируем.
6. Для G243:G305 и G56:G66 включаем перенос текста и подбираем высоту; сохраняем читаемые единицы в H. Убеждаемся, что ширины столбцов позволяют видеть даты, ставки и итоги.

Сохраняем результат как «М05_После_урока_01.xlsx». Новых именованных диапазонов не требуется: используем существующие даты, количество месяцев/кварталов в году и другие имена предыдущих модулей.

1 / Каркас

Строим блок аннуитетных платежей

Продолжаем свою книгу после урока 1 на листе «Расчеты». Общая сумма привлечения — 7 000 руб. Основной долг и проценты начинаем выплачивать с начала эксплуатации. Первые 791 день проценты по условиям примера не начисляются.

В H70:H73, H76 и H79:H81 указываем «руб.». Для C68 применяем стиль «Заголовок3», для D69, D75 и D78 — «Заголовок4». Подписи G оформляем стилем «Обычный», единицы H — «ед_изм». Для длинных подписей включаем перенос текста и автоподбор высоты строк.

2 / Остаток и проценты

Связываем получение долга и начисление процентов

В O73 ничего не вводим: эта пустая ячейка задаёт начальный остаток первого периода. Формула P70 берёт остаток из предыдущего столбца; P71 получает привлечение из строки 51. Проценты P76 считаются на задолженность в начале периода.

=O73

=P51

=ЕСЛИ(P$5=" ";" ";P70*P63*(P58=1))

Каждую формулу столбца P протягиваем вправо до EE: выделяем P70:EE70, затем P71:EE71 и P76:EE76 и для каждого диапазона нажимаем Ctrl+R. В формулах с проверкой даты между кавычками стоит один пробел. До заполнения строки 73 расчёт остатков не завершён. Проверяем результат, когда заполним весь блок.

=СУММ(P71:EE71)

=СУММ(P76:EE76)

В строке 63 используется постоянная квартальная ставка 15% / 4 = 3,75%. В этой схеме она применяется и к последнему укороченному периоду. Это условие выбранного аннуитетного расчёта; расчёт процентов по фактическим дням будет в других схемах.

3 / Погашение

Рассчитываем погашение основного долга

Аннуитетный платёж состоит из процентов и погашения основного долга. В P72 рассчитывается постоянная сумма платежа, из неё вычитаются проценты P76. Минус показывает отток денежных средств. Число платежей берётся из O66: в текущем примере их 20.

=-ЕСЛИ(P66>=1;$O$71*(P63*(1+P63)^$O$66)/(((1+P63)^$O$66)-1)-P76;0)

=СУММ(P70:P72)

Протягиваем P72:EE72 и P73:EE73 вправо сочетанием Ctrl+R. Строка 73 складывает начальную задолженность, получение средств и погашение. Не включаем в сумму ячейку конечного остатка, чтобы формула не ссылалась сама на себя.

=СУММ(P72:EE72)

=ОКРУГЛ(O71+O72;2)

Итог O72 отрицательный. Контроль K73 должен дать 0: сумма привлечения и погашения равна нулю с точностью до копеек. O73 оставляем пустой и не вводим в неё итог строки 73. Формула аннуитета рассчитана на положительную ставку: подстановка 0% в эту запись потребует отдельного случая и сейчас не входит в пример.

3 / Погашение / Сверка в Excel

Сверка остатка аннуитетного долга

Сопоставляем адреса и значения со своей рабочей книгой. Это результат предыдущего шага.

4 / Обслуживание

Собираем суммарные выплаты

В итоговом блоке выплаты показываем положительными числами. Поэтому погашение долга в строке 79 меняет знак строки 72; строка 80 берёт проценты из строки 76.

=-P72

=P76

=СУММ(P79:P80)

Протягиваем каждую из трёх формул до столбца EE. В O79:O81 считаем суммы по всей временной шкале:

=СУММ(P79:EE79)

=СУММ(P80:EE80)

=СУММ(P81:EE81)

O81 показывает основной долг вместе с процентами, а O80 — только проценты. Эти показатели учитывают разные выплаты. При сравнении схем используем один и тот же состав выплат.

5 / Оформление

Оформляем новый блок

Для денежных ячеек применяем учётный числовой формат с двумя десятичными знаками. В P70:EE72, P76:EE76 и P79:EE80 используем стиль «Обычный»; в P73:EE73 — «Л_Баланс»; в P81:EE81 — «Л_ИТОГО».

Для O71:O72, O76 и O79:O81 применяем стиль «СУММА», для K73 — «ТРЕВОГА». Этот стиль отображает нулевой контроль как «Ok» и ненулевой как «ОШИБКА!». Сохраняем пустую O73. При переносе форматов не копируем в неё сумму из соседней итоговой строки.

Сначала выделяем строки 67:81 и выполняем «Данные → Группировать → Строки»: это продолжение внешней группы готового расчёта долга. Затем группируем внутри неё 69:81. Заголовок C68 остаётся за пределами вложенной группы. Все формулы по периодам должны доходить до EE, хотя после завершения проекта активных дат уже нет.

Проверка / Аннуитет

Проверяем аннуитетную схему

В каждом платёжном периоде общая выплата составляет около 503,73 руб. В первом из них основной долг уменьшается примерно на 241,23 руб., в последнем — на 485,53 руб. Последний остаток равен нулю с точностью до копеек; небольшая погрешность вычислений не означает, что часть долга не погашена.

Сохраняем свою рабочую книгу. Для сверки этого этапа предназначен файл «М05_После_урока_02.xlsx». Следующий урок добавит отдельный вариант в той же книге; аннуитетный блок сохраняется.

Проверка / Аннуитет / Сверка в Excel

Сверка аннуитетных выплат

Сопоставляем адреса и значения со своей рабочей книгой. Это результат предыдущего шага.

1 / Новый вариант

Копируем каркас и задаём равные доли

Продолжаем лист «Расчеты». В C83 вводим «Дифференцированные платежи» и применяем стиль «Заголовок3». Копируем A69:EE81, выбираем A84 и вставляем обычным Ctrl+V. Получится блок строк 84:96. Не вставляем новые строки: адреса следующих разделов должны сохраниться.

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

В H85:H88, H91 и H94:H96 должны остаться единицы «руб.». O88 оставляем пустой: она задаёт начальный остаток первого периода. После копирования обязательно заменяем все формулы ниже: автоматическое смещение ссылок ещё не даёт готового расчёта нового варианта.

2 / Основной долг

Распределяем долг на двадцать платежей

=O88

=P51

=-ЕСЛИ(P$66>=1;$O$86/$O$66;0)

=СУММ(P85:P87)

Протягиваем каждую формулу до EE. В строке 86 снова используем фактическое получение из строки 51, а не случайно смещённую ссылку после копирования. В строке 87 общий долг O86 делится на число платежей O66: 7 000 / 20 = 350 руб. основного долга в каждом платёжном периоде.

=СУММ(P86:EE86)

=СУММ(P87:EE87)

=ОКРУГЛ(O86+O87;2)

O86 — вся сумма получения, O87 — вся сумма погашения с минусом. K88 должен равняться нулю. O88 не заполняем суммой: эта пустая ячейка задаёт начальный остаток в P85.

2 / Основной долг / Сверка в Excel

Сверка дифференцированного долга

Сопоставляем адреса и значения со своей рабочей книгой. Это результат предыдущего шага.

3 / Проценты

Начисляем проценты по числу дней

=ЕСЛИ(P$5=" ";" ";P85*P64*(P58=1))

=СУММ(P91:EE91)

Протягиваем P91:EE91. Проценты считаются на начальную задолженность строки 85 по ставке строки 64. В отличие от аннуитетного варианта, строка 64 учитывает фактические дни расчётного периода и число дней в соответствующем году. Банковский флаг строки 58 ограничивает начисление платёжными периодами.

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

4 / Итоги

Собираем выплаты по дифференцированной схеме

=-P87

=P91

=СУММ(P94:P95)

Протягиваем P94:EE94, P95:EE95 и P96:EE96. В итоговом блоке основной долг и проценты показываем как положительные выплаты.

=СУММ(P94:EE94)

=СУММ(P95:EE95)

=СУММ(P96:EE96)

После копирования сохраняем учётный формат с двумя десятичными знаками, стиль «Л_Баланс» в P88:EE88, «Л_ИТОГО» в P96:EE96, «СУММА» в O86:O87, O91 и O94:O96, «ТРЕВОГА» в K88. Остальные денежные строки оформлены стилем «Обычный».

Сначала группируем строки 82:96 через «Данные → Группировать → Строки», продолжая внешнюю группу готового расчёта долга. Затем создаём вложенную группу 84:96. Заголовок C83 оставляем вне вложенной группы.

Проверка / Дифференцированные платежи

Проверяем долг, проценты и конечный остаток

Проверяем двадцать погашений по 350 руб. и нулевой остаток в BS88 с точностью до копеек. Сумма O96 меньше аннуитетной O81 в этом примере. Схемы различаются как порядком погашения, так и способом определения процентной ставки за период; поэтому полученная разница не означает, что результат будет таким же для любого кредитного договора.

Сохраняем свою книгу. Файл для сверки — «М05_После_урока_03.xlsx». В следующем варианте сохраняем равные доли погашения и рассчитываем проценты по купонному календарю.

Проверка / Дифференцированные платежи / Сверка в Excel

Сверка дифференцированных выплат

Сопоставляем адреса и значения со своей рабочей книгой. Это результат предыдущего шага.

1 / Купонная схема

Создаём отдельный облигационный вариант

В C98 листа «Расчеты» вводим «Облигационный займ» и применяем стиль «Заголовок3». Копируем A84:EE96, выбираем A99 и вставляем Ctrl+V. Получатся строки 99:111. Не вставляем новые строки и не ограничиваем копирование строкой 94: нужны также проценты и итоговые выплаты.

В этом примере облигационный долг погашается равными долями, а не единой суммой номинала в конце срока. Меняем календарь начисления процентов: используем купоны по 90 дней и 20 выплат, подготовленные в уроке 1. Эта схема не поддерживает произвольную смену купонного периода на 60 или 180 дней.

O103 должна остаться пустой. Единицы в H100:H103, H106 и H109:H111 — «руб.». Ниже заменяем все формулы в скопированном блоке, затем протягиваем строки с расчётами по периодам до EE.

2 / Основной долг

Сохраняем равные доли погашения

=O103

=P51

=-ЕСЛИ(P$66>=1;$O$86/$O$66;0)

=СУММ(P100:P102)

Протягиваем каждую формулу до EE. В P102 ссылка $O$86 сохранена намеренно: в исходной конструкции это общий объём долга 7 000 руб. из дифференцированного блока. И O86, и новое O101 суммируют один график получения из строки 51. При одинаковом привлечении обе ячейки содержат одну и ту же сумму долга. Схема пока не предназначена для независимой суммы долга по каждому варианту.

=СУММ(P101:EE101)

=СУММ(P102:EE102)

=ОКРУГЛ(O101+O102;2)

Проверяем O101 = 7 000 руб. и O102 = −7 000 руб. Контроль находится в K103, а не в K88 предыдущего блока. O103 оставляем пустой: она задаёт начальный остаток первого периода.

2 / Основной долг / Сверка в Excel

Сверка облигационного долга

Сопоставляем адреса и значения со своей рабочей книгой. Это результат предыдущего шага.

3 / Купонные проценты

Применяем купонную ставку и флаг

=ЕСЛИ(P$5=" ";" ";P100*P65*(P59=1))

=СУММ(P106:EE106)

Протягиваем P106:EE106. Начальный остаток строки 100 умножается на купонную ставку строки 65; флаг строки 59 определяет период выплаты купона. В текущей модели на каждый платёжный период приходится один купон. Последний купон укорочен до даты конца долга 31.10.2022.

Ставка строки 65 рассчитывается через число купонных дней и число дней в году. Она отличается от постоянной квартальной ставки аннуитета и от ставки по дням банковского периода. Льготная фаза по-прежнему означает отсутствие начисления процентов в первые 791 день.

4 / Итоги

Собираем выплаты по облигационному займу

=-P102

=P106

=СУММ(P109:P110)

Протягиваем формулы до EE и рассчитываем итоговые суммы:

=СУММ(P109:EE109)

=СУММ(P110:EE110)

=СУММ(P111:EE111)

Сохраняем скопированные стили: P103:EE103 — «Л_Баланс», P111:EE111 — «Л_ИТОГО», O101:O102, O106 и O109:O111 — «СУММА», K103 — «ТРЕВОГА». Денежный формат — учётный с двумя десятичными знаками.

Сначала группируем строки 97:111, продолжая внешнюю группу готового расчёта долга. Затем группируем внутри неё 99:111. Заголовок C98 остаётся вне вложенной группы.

Проверка / Три схемы

Сопоставляем результаты при одинаковом долге

Сравниваем итоговые выплаты: аннуитет O81 — 10 074,69 руб.; дифференцированная схема O96 — 9 745,18 руб.; облигационный вариант O111 — 9 711,97 руб. Разница между аннуитетом и облигационным вариантом составляет 362,72 руб., или около 5,18% от долга 7 000 руб.

Сохраняем книгу. Файл для сверки — «М05_После_урока_04.xlsx». Далее рассчитываем ускоренное погашение за счёт доступного денежного потока.

Проверка / Три схемы / Сверка в Excel

Сверка купонных выплат

Сопоставляем адреса и значения со своей рабочей книгой. Это результат предыдущего шага.

1 / Доступный поток

Создаём вариант «Все на долг»

На листе «Расчеты» в C113 вводим «Все на долг», в D114 — «Расчет свободного денежного потока для обслуживания долга». Применяем стили «Заголовок3» и «Заголовок4» соответственно. Вводим подписи:

В H115:H120 указываем «руб.», применяем стиль «ед_изм». Для G используем «Обычный», перенос текста и автоподбор высоты строк.

Копируем A99:EE111, выбираем A122 и вставляем Ctrl+V без вставки новых строк. Получится блок 122:134: баланс долга, проценты и суммарные выплаты. O126 оставляем пустой — эта ячейка задаёт начальный остаток первого периода. Подписи и единицы скопированного блока сохраняются, а все формулы заменяем.

Здесь поток упрощён: выручка минус расходы, проценты и налог. Такое упрощение помогает разобрать механизм погашения; полный расчёт свободного потока для практического применения здесь не рассматривается. Оборотный капитал и резервные счета в этой части ещё не рассчитаны.

2 / Операционная основа

Рассчитываем валовую прибыль

=P30

=ЕСЛИ(P$5=" ";" ";-P35)

=СУММ(P115:P116)

Протягиваем каждую формулу до EE. Выручка поступает из строки 30, расходы из строки 35 показываются с минусом. В строке 117 складываем выручку и отрицательные расходы.

=СУММ(P115:EE115)

=СУММ(P116:EE116)

=СУММ(P117:EE117)

В P115:EE116 применяем стиль «Обычный», в P117:EE117 — «Л_ИТОГО», в O115:O117 — «СУММА». Для денежных значений нужен учётный формат с двумя десятичными знаками. Заполняем весь блок, прежде чем проверять результат: проценты ниже будут зависеть от нового остатка долга.

3 / Проценты и налог

Получаем поток после процентов и налога

=ЕСЛИ(P$5=" ";" ";-P123*P64*(P58=1))

=-СУММ(P117:P118)*НП

=СУММ(P117:P119)

Протягиваем строки 118:120 до EE. Проценты P118 берут начальный остаток из P123, а не из строки 122 с заголовком. Отрицательное значение означает расход. Налог рассчитываем с прибыли после вычета процентов, используя существующее имя НП: в учебной модели это условные 20%.

=СУММ(P118:EE118)

=СУММ(P119:EE119)

=СУММ(P120:EE120)

В P118:EE119 используем «Обычный», в P120:EE120 — «Л_ВСЕГО», в O118:O120 — «СУММА», учётный формат с двумя знаками. В строке 120 получаем доступный поток после процентов и налога, который далее направим на основной долг.

При отрицательной прибыли формула строки 119 даёт положительную сумму налога. Эту строку используем только при положительной прибыли, как в данном примере.

3 / Проценты и налог / Сверка в Excel

Свободный поток после процентов и налога

Сопоставляем адреса и значения со своей рабочей книгой. Это результат предыдущего шага.

4 / Ускоренное погашение

Ограничиваем погашение потоком и остатком

=O126

=P51

=-ЕСЛИ(P$66>=1;МИН(P120;P123);0)

=СУММ(P123:P125)

Протягиваем каждую формулу до EE. Получение берётся из строки 51. В платёжном периоде функция МИН выбирает меньшую из двух величин: доступный поток P120 и начальный остаток P123. Поэтому последний платёж не превышает остаток основного долга. Знак минус отражает выплату.

=СУММ(P124:EE124)

=СУММ(P125:EE125)

=ОКРУГЛ(O124+O125;2)

O126 оставляем пустой. K126 должен равняться нулю с точностью до копеек. В P126:EE126 сохраняем «Л_Баланс», в O124:O125 — «СУММА», в K126 — «ТРЕВОГА».

При отрицательном P120 исходная запись МИН не задаёт корректную схему погашения: она могла бы увеличить задолженность. Проверяем, что поток P120 неотрицательный.

4 / Ускоренное погашение / Сверка в Excel

Сверка ускоренного погашения

Сопоставляем адреса и значения со своей рабочей книгой. Это результат предыдущего шага.

5 / Выплаченные проценты

Показываем процентные выплаты отдельно

=ЕСЛИ(P$5=" ";" ";P123*P64*(P58=1))

=СУММ(P129:EE129)

Протягиваем P129:EE129. Эта строка показывает проценты положительным числом для итогового отчёта; строка 118 учитывает те же проценты с минусом при расчёте доступного потока. Начальный остаток в обеих формулах — строка 123, ставка за период — строка 64.

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

Для P129:EE129 используем стиль «Обычный», для O129 — «СУММА», учётный формат с двумя десятичными знаками.

6 / Обслуживание

Собираем суммарные выплаты

=-P125

=P129

=СУММ(P132:P133)

Протягиваем все три строки до EE. Погашение основного долга и проценты показываем положительными числами:

=СУММ(P132:EE132)

=СУММ(P133:EE133)

=СУММ(P134:EE134)

Сохраняем «Обычный» в P132:EE133, «Л_ИТОГО» в P134:EE134 и «СУММА» в O132:O134. Денежные значения — в учётном формате с двумя знаками. Проверяем, что в столбце H всех новых денежных строк указаны и видны единицы «руб.».

Сначала группируем строки 112:134, продолжая общую внешнюю группу готового расчёта долга. Затем группируем 114:134 внутри неё. Заголовок C113 остаётся вне вложенной группы.

Проверка / Ускоренное погашение

Проверяем срок и сумму выплат

В текущем примере долг погашен в IV квартале 2019 года вместо конца октября 2022 года — примерно на 2 года 10 месяцев раньше. Общая выплата меньше облигационного варианта на 1 370,83 руб., или около 19,58% от первоначального долга 7 000 руб. Это результат заданного потока и условий примера.

Сохраняем свою книгу. Файл для сверки — «М05_После_урока_05.xlsx». Расчёт этого этапа заканчивается строкой 134; остальные условия и ранее построенные варианты сохраняются.

Проверка / Ускоренное погашение / Сверка в Excel

Сверка выплат «Всё на долг»

Сопоставляем адреса и значения со своей рабочей книгой. Это результат предыдущего шага.

Оценка / Допущения

Условия ускоренного погашения

В этом варианте весь рассчитанный поток после процентов и налога направляем на погашение долга. Задолженность покупателей, резервы и запасы здесь отдельно не учитываем. Итоговые выплаты 8 341,15 руб. относятся к этим условиям.

1 / Условный пример

Задаём коэффициент покрытия долга

Продолжаем рабочую книгу после урока 5. Ковенант — условие финансирования, проверяемое по заданным показателям. Принимаем DSCR = 2: выбранный поток должен вдвое превышать выплаты основного долга и процентов.

В строку 137 переносим строку 120 схемы «Всё на долг» — поток после процентов и налога этой схемы. Используем этот поток для сравнения вариантов. Собственный поток ковенантной схемы до обслуживания долга здесь не рассчитываем. Резервы отдельно не формируем.

2 / Параметр DSCR

Вводим коэффициент и подготавливаем каркас

На листе «Исходные данные» в C307 вводим «Коэффициент покрытия обслуживания долга (DSCR)», применяем стиль «Заголовок3». В J307 вводим число 2 и применяем стиль «Коэффициенты»: оно отображается с двумя десятичными знаками и обозначением x. Вводим число, без буквенного суффикса. Новое имя диапазона не требуется.

Возвращаемся на «Расчеты». В C136 вводим «Ковенантные платежи (под DSCR)» со стилем «Заголовок3». В G137:G139 добавляем подписи:

Для G используем «Обычный», перенос текста и автоподбор высоты. В H137 указываем «руб.», в H138 — «x»; применяем «ед_изм». H139 оставляем пустой. Срок долговой фазы 01.11.2015–31.10.2022, 36 помесячных периодов и условные налоговые ставки модели остаются прежними.

3 / Бюджет выплат

Связываем выбранный поток и коэффициент

=P120

='Исходные данные'!J307

=$I$138

=P137/P138*P22

Заполняем P137:EE137, P138:EE138 и P139:EE139 вправо сочетанием Ctrl+R. I138 содержит ссылку на параметр, а строка 138 повторяет его по всей шкале. Строка 139 делит поток из схемы «Всё на долг» на DSCR и умножает результат на флаг долга строки 22.

=СУММ(P137:EE137)

=СУММ(P139:EE139)

В P137:EE137 и P139:EE139 применяем «Обычный» с учётным форматом и двумя знаками; для O137/O139 — «СУММА». I138 и Q138:EE138 оформляем стилем «Коэффициенты». В P138 сохраняем исходный «Обычный» с учётным форматом: значение остаётся 2,00. Строка 139 задаёт сумму, доступную для обслуживания долга. Далее разделяем её на проценты и погашение основного долга.

3 / Бюджет выплат / Сверка в Excel

Поток и бюджет обслуживания долга

Сопоставляем адреса, даты и значения со своей рабочей книгой. Это результат предыдущего шага.

4 / Каркас долга

Переносим график и рассчитываем собственные проценты

Копируем A122:EE134, выбираем A141 и вставляем Ctrl+V без вставки новых строк. Сохраняем подписи и денежные единицы; новый блок занимает 141:153. O145 оставляем пустой — эта ячейка задаёт начальный остаток первого периода. Заменяем все формулы нового блока, даже если после копирования они выглядят правдоподобно.

=O145

=P51

=ЕСЛИ(P$5=" ";" ";P142*P63*(P58=1))

Протягиваем каждую формулу до EE. Строка 142 берёт предыдущий конечный остаток, строка 143 — получение из строки 51. Проценты строки 148 считаются на собственный начальный остаток ковенантного варианта.

=СУММ(P143:EE143)

=СУММ(P148:EE148)

Ставку для этой формулы задаёт строка 63: 15% / 4 = 3,75%, как в аннуитетном расчёте. Подставлять вместо неё ставку по дням из строки 64 не нужно. Первые 791 день по условию модели проценты не начисляются. Расчёт нового блока завершаем после заполнения строк погашения и конечного остатка.

5 / Погашение

Вычитаем проценты из бюджета и ограничиваем выплату остатком

=-ЕСЛИ(P$66>=1;МИН(P142;(P139-P148));0)

=СУММ(P142:P144)

Протягиваем P144:EE144 и P145:EE145. В платёжном периоде из бюджета строки 139 вычитаются собственные проценты строки 148. Функция МИН выбирает меньшую величину: сумму после вычета процентов или начальную задолженность строки 142. Минус показывает погашение основного долга.

=СУММ(P144:EE144)

=ОКРУГЛ(O143+O144;2)

Контроль K145 проверяет возврат всей полученной суммы с точностью до копеек. O145 должна остаться пустой; не ставим туда сумму конечных остатков. В P145:EE145 используем «Л_Баланс», в O143:O144 — «СУММА», в K145 — «ТРЕВОГА». Остальные денежные строки сохраняют «Обычный» и учётный формат с двумя знаками.

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

5 / Погашение / Сверка в Excel

Остаток ковенантного долга

Сопоставляем адреса, даты и значения со своей рабочей книгой. Это результат предыдущего шага.

6 / Итоговые выплаты

Сохраняем отдельный итог основного долга и процентов

Не пропускаем строки 151:153, полученные при копировании. Они нужны для сравнения общей суммы выплат по долгу с бюджетом и для итоговой таблицы всех вариантов.

=-P144

=P148

=СУММ(P151:P152)

Протягиваем каждую строку до EE и заполняем суммы:

=СУММ(P151:EE151)

=СУММ(P152:EE152)

=СУММ(P153:EE153)

В P151:EE152 применяем «Обычный», в P153:EE153 — «Л_ИТОГО», в O151:O153 — «СУММА». H151:H153 сохраняют «руб.». Денежные значения показываем в учётном формате с двумя десятичными знаками.

7 / Проверка бюджета

Поясняем ненулевую разницу последнего платежа

В G155 вводим «Проверка», в H155 — «руб.». Заполняем:

=ЕСЛИ(P144<>0;P153-P139;0)

=СУММ(P155:EE155)

Протягиваем P155:EE155. Формула сравнивает общую выплату строки 153 с бюджетом строки 139, если основной долг действительно погашается. В сравнении участвуют и основной долг, и проценты, а не только погашение основного долга из строки 144. Для P155:EE155 используем «Обычный», для O155 — «СУММА», денежный учётный формат. Это не нулевой контроль со стилем «ТРЕВОГА».

В последнем платеже, BP — I квартал 2022 года, бюджет равен примерно 759,49 руб. Осталось погасить 345,58 руб. основного долга и заплатить 12,96 руб. процентов. Общая выплата — около 358,54 руб.; неиспользованный бюджет даёт разницу −400,95 руб. В O155 будет та же сумма. Она ожидаема из-за ограничения остатком долга и не требует исправления на ноль.

После полного погашения значение строки 144 равно нулю: строка 155 возвращает 0, даже если расчётный бюджет ещё есть. Поэтому O155 суммирует разницы только в периоды погашения и не равна простому вычитанию O153 − O139 за весь проект.

7 / Проверка бюджета / Сверка в Excel

Выплаты и неиспользованный бюджет

Сопоставляем адреса, даты и значения со своей рабочей книгой. Это результат предыдущего шага.

Проверка / Ковенантный вариант

Проверяем расчёт при принятых условных правилах

Во всех предшествующих периодах с выплатами отношение выбранного потока строки 137 к выплате строки 153 равно 2. При последнем погашении оно выше, поскольку весь бюджет уже не нужен. Так проверяем арифметику этой схемы. Поток в числителе по-прежнему зависит от процентов и налога варианта «Все на долг».

8 / Сравнение вариантов

Собираем общую таблицу выплат по периодам

В C157 вводим «Сравнение итоговых выплат по заемному финансированию» со стилем «Заголовок3». В G158:G162 вводим: «Аннуитетные платежи», «Дифференцированные платежи», «Облигационный займ», «Все на долг», «Ковенантные платежи (под DSCR)». В H158:H162 указываем «руб.».

Для каждого варианта берём уже рассчитанную сумму основного долга и процентов. Формулы связывают сравнение с рабочими расчётами, поэтому не вводим итоговые числа вручную:

=P81

=P96

=P111

=P134

=P153

Протягиваем каждую из пяти строк до EE. Для G и денежных ячеек по периодам используем «Обычный», для H — «ед_изм». Включаем перенос длинных подписей и автоподбор высоты. Числовой формат — учётный с двумя знаками.

9 / Общие суммы

Считаем итоги пяти вариантов

=СУММ(P158:EE158)

=СУММ(P159:EE159)

=СУММ(P160:EE160)

=СУММ(P161:EE161)

=СУММ(P162:EE162)

Применяем к O158:O162 стиль «СУММА» и учётный формат с двумя десятичными знаками.

В условиях этого примера «Все на долг» даёт наименьшую общую выплату, аннуитет — наибольшую. Сравниваем сумму выплат без дисконтирования; варианты отличаются темпом погашения и способом расчёта процентов. Ковенантный результат дополнительно зависит от выбранного общего потока. Учитываем эти особенности при сравнении результатов в таблице.

9 / Общие суммы / Сверка в Excel

Сравнение пяти вариантов в Excel

Сопоставляем адреса, даты и значения со своей рабочей книгой. Это результат предыдущего шага.

Завершение / Группы и переход

Сохраняем сравнение и границы расчёта

На «Исходные данные» группируем строки 306:307, продолжая ранее созданную внешнюю группу исходных условий. На «Расчеты» сначала группируем 135:162, продолжая внешнюю группу готового расчёта долга. Затем создаём вложенные группы 137:155 и 158:162. Заголовки C136 и C157 остаются вне вложенных групп.

Проверяем пустую O145, нулевой K145, ненулевую ожидаемую O155 = −400,95 руб. и совпадение O158:O162 с итогами пяти вариантов. Все новые формулы по периодам должны доходить до EE. Сохраняем рабочую книгу; файл для сверки — «М05_Контроль.xlsx».

В следующем модуле рассчитываем оборотный капитал. Резервы отдельно не рассчитываем. Итоговая таблица выплат остаётся в строках 158:162.