В этом посте мы хотим поделиться нашими мыслями относительно наиболее дебатируемой в финансовом моделировании темы – расчете процентов к уплате и циклических ссылках в Excel. Как знают все специалисты по моделированию, для идеального расчета процентов к уплате в финансовой модели требуются данные о долге на начало и конец периода: средний долг * ставка процента = проценты начисленные.

Однако эта формула по определения является циклической: чтобы вычислить долг на конец периода надо знать, сколько процентов уплачено в течение периода. К счастью, Microsoft Excel прекрасно справляется с этой задачей. Разрешите итеративные вычисления в настройках Excel и — пожалуйста.

Тем не менее, большинство наших коллег сказали бы на это большое НЕТ. Почему? Разрешение итераций в моделях опасно. Причина в том, что Excel не может определить, какая циклическая ссылка присутствует согласно замыслу пользователя, а какая по ошибке, когда пользователь случайно «зациклил» произвольные ячейки: например, в случае A1 = A9 Excel будет использовать последнее значение, введенное или в A1 или A9. Причем, в режиме итерационных вычислений предупреждения об ошибке не появится.

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

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

Есть ли решение лучше? Мы думаем есть. Наши модели работают в двух режимах – с циклической ссылкой расчете процентов и без. Как Универсальная, так и Сценарная модели оснащены кнопкой «Циклический расчет процентов» как показано ниже (лист «Финансовые отчеты» («Financials»).

Установите переключатель на «Да» («Yes») и ваша Модель станет работать итерационно. Не забудьте одновременно разрешить циклические вычисления в настройках программы «Excel». Если в Excel установлены линейные вычисления, то вы немедленно увидите предупреждение, что в модели есть циклические ссылки. Не обращайте внимание. Смените настройки Excel. Предупреждения исчезнут.

Вы хотите проверить нет ли ошибочных зацикливаний в модели? Установите переключатель на «Нет» («No») и отключите итерации в настройках Excel. Если не возникает никаких предупреждений о циклических ссылках, то модель чиста от каких-либо случайных циклов.

Все. Никаких дополнительных программ. Никаких сложных процедур расчета процентов. Прямо и просто. Используйте на здоровье!