Почему ваши финмодели - бомба замедленного действия

Большинство excel-моделей содержат ошибки. Одна такая стоила JP Morgan 6.2 ярда, другая изменила экономическую политику стран. Ребята из Гарварда и Лондона остались без репутации, должности и денег. В том числе чужих. Глянем механику

Весной 2012 один из трейдеров JP Morgan Бруно Иксил, получил прозвище "Лондонский кит", а всё потому что его позиции в кредитных деривативах стали настолько велики, что двигали рынок. К декабрю JP зафиксировал убыток 6.2 ярда...

Что произошло было подробно описано на 130 страницах отчете JP Morgan Task Force в январе 2013. Модель Value-at-Risk работала через цепочку excel-файлов. При этом данные копировали вручную из таблицы в таблицу

Цитата из отчета гласит:

Elegant... Формула подразумевала деление на сумму вместо среднего. Как итог волатильность занизили вдвое. VaR показал риск ниже реального и трейдеры наращивали позиции

Маленькая excel-ошибка стала триггером. В отчете зафиксировали системные проблемы:

Последствия оказались жуткими 920 миллионов штрафов, зарплата Джейми Даймона (CEO) была поделена на 2 (символично), трейдеры узнали что такое УК. Карьеры, репутации, деньги... все пошло не по плану из-за одной формулы

Был такой профессор информационных систем в Гавайском университете - Рэй Панко (сейчас на пенсии). Он занимался исследованиями ошибок в электронных таблицах начиная с 1996 года. Его работы стали основой большинства цитат про "88% таблиц содержат ошибки", хотя обычно его цитируют неточно

Панко измерял Cell Error Rate (CER) - процент ячеек с формулами, содержащих ошибки. По мета-анализу 14 лабораторных исследований с 967 участниками диапазон в среднем составил от 1% до 5%, среднее 3.9%

Звучит терпимо, но есть ещё и Spreadsheet Error Rate - доля таблиц, содержащих хотя бы одну ошибку. Тут то и появляются 88%. Панко сказал: "88% of spreadsheets have 1% or more errors in formulas".

Типичная модель для M&A содержит от 2000 до 5000 формул. При CER 2% это от 40 до 100 ошибок (если повезло). Часть компенсируют друг друга. Часть не влияют на итог. Но одна ошибка в WACC или терминальной стоимости и оценка уезжает на десятки процентов. А за этой ошибкой стоят чьи-то деньги

Ошибки бывают механические (опечатки, кривые ссылки) и логические(формула работает, но считает не то). Логические намного хуже потому что их не видно

Получается так, что один проверяющий находит 20% ошибок, двое уже до 60%, но 100% не поймать и автотестов в Excel нет

Панко резюмировал это так:

В 2010 год Кармен Рейнхарт (Гарвард) и Кеннет Рогофф (бывший главный экономист МВФ) публикуют "Growth in a Time of Debt". Вывод их публикации гласил, что госдолг выше 90% ВВП роняет рост до минус 0.1%.

Бумага стала библией политики austerity. Пол Райан процитировал это в бюджете США, Джордж Осборн - в обосновании сокращений в Британии, Олли Рен - в Еврокомиссии

Кругман позже сказал: "Reinhart-Rogoff may have had more immediate influence on public policy debate than any previous paper in the history of economics."

В 2013 году Томас Херндон, аспирант University of Massachusetts Amherst попробовал воспроизвести результаты для семинара по эконометрике и surprise... не сошлось

Он запросил оригинальные excel-файлы у авторов и тут началось...

В соавторстве с Эшем и Поллином (Cambridge Journal of Economics, 2013), Томас выявляет 3 категории проблем:

1. Ошибка в формуле AVERAGE

Формула охватывала ячейки L30:L44, пропуская L45:L49. А в реальности это 5 стран с высоким долгом и положительным ростом, которые не попали в расчет среднего: Австралия, Австрия, Бельгия, Канада, Дания. Данные добавили, а диапазон не расширили

2. Селективное исключение данных

Обделили и Новую Зеландию включив данные с высоким долгом только за 1951 год (рост минус 7.6%), вместо 5 доступных лет (1946-1951). Авторы сослались на то, что это методология. Сомнительно, но ок

3. Нестандартное взвешивание

Вместо взвешивания по количеству "страно-лет" использовали взвешивание по странам.Как итог Британия с 19 годами высокого долга и Новая Зеландия с 1 годом весили одинаково

После исправлений минус 0.1% превратился в плюс 2.2%

Рейнхарт и Рогофф, конечно, признали ошибку, с поправкой на то, что вывод остался верным. Формально они правы. Но фраза "экономика падает" и "экономика растет" оказались разными аргументами для министра финансов...

European Spreadsheet Risks Interest Group коллекционирует такие истории с 1995 года. Среди особо интересных:

Fidelity Magellan в 1994. При переносе данных потеряли "-" перед убытком в 1.3 ярда. Убыток превращается в прибыль. Завышение чистых активов фонда стало 2.6 ярда. Повезло, что обнаружили ошибку до выплаты дивидендов

TransAlta в 2003. Copy-paste при подготовке заявки на аукцион превратился в завышенную цену, невыгодные контракты и 24 миллиона проблем за Ctrl+V.

MI5 в 2011. Ошибка форматирования таблицы с номерами телефонов. Последние три цифры обрезались, заменяясь на 000. В итоге слушали домохозяек вместо ожидаемых кадров. Хотя кто знает ошибка ли это...

AXA Rosenberg в 2011. В математической модели нашли ошибку, но решили промолчать. SEC узнал и за умение молчать выписал 242 миллиона штрафа. Дорогой урок

Public Health England в 2020. COVID-трекер, который использовал .xls формат (Excel 97-2003) с лимитом 65,536 строк. Если лимит превышали, то 16,000 положительных тестов не учитывались в статистике. Пришлось добавить Х в конце (.xlsx)

Mouchel (2011). Ошибка при расчете пенсионного фонда стоила 4.3 миллиона фунтов, отставки CEO, и падения акции на 30%

А объединяет их одно: тупая ошибка, сложная система, (около)нулевая проверка

Overconfidence bias

Исследования Панко говорят, что все думают, о том, что у них то точно нет ошибки. Фактически проверяли не более 40%, а остальные 60% оказывались сюрпризом.

Особенно остро это проявляется у опытных экспертов. Модели то усложняются, а процент ошибок не падает

Нет code review

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

Excel не поддерживает инструменты разработчиков. Нет diff, нет тестов, нет линтера, который поймал бы =SUM(B2:B10) когда данные идут до B15

Сроки

Отчет JP Morgan говорит о том, что разработчик VaR-модели запрашивал ресурсы и время на тестирование, но ему отказали и под давлением трейдеров ситуация усугубилась

"Ждем расчеты до 18:00" - эта фраза знакома всем в той или иной степени, но стоимость может быть катастрофической

Нет документации

Здесь как в коде. Если модель понятна автору, то для следующего в цепочке, она становится черным ящиком. Проверка деградирует, до "выглядит логично" (до поры до времени)

В программировании эти проблемы решают массой инструментов. В финмоделировании стандарты тоже есть: FAST (открытый, от F1F9), SMART (Mazars), ICAEW Code (британские бухгалтеры), Operis (проприетарный), но о них мало кто знает.

FAST является самым распространенным:
Flexible говорит о том, что модель адаптируется под новые данные без переписывания
Appropriate - это детализация по задаче. Главный принцип не делай сложнее, чем нужно
Structured говорит о том что всё должно быть везде одинаково и по одной строке на формулу: Вход -> Расчет -> Выход
Transparent про то, что формула должна быть понятна за секунды. Без вложенных IF на 200 зависимостей

Сделали в F1F9 в 1999м, а в 2011 передали под Creative Commons без привязки к вендору.

Ofwat (регулятор воды в UK) требует FAST-модели для расчета тарифов:

Эти ребята регулируют тарифы на воду для миллионов людей и им не до экспериментов

В общем проверяйте формулы. Или не проверяйте. Кто-то же должен пополнять коллекцию EuSpRIG

C++-разработчик. Инвестирую больше десяти лет. Пишу о пересечении кода и капитала.

Источники:

#финансовое_моделирование #Excel #FAST #риск_менеджмент #LAL_Research