LБлог losLab по разработке программного обеспеченияИнженерные заметки для разработчиков PDF, Delphi и программного обеспечения для работы с документами.

Техническая статья

Формулы облигаций и казначейских векселей в Delphi с HotXLS

HotXLS Delphi Component

HotXLS вычисляет финансовые функции облигаций и казначейских векселей Excel нативно в Delphi и C++Builder: PRICE, YIELD, DURATION, MDURATION, шесть примитивов купонных дат, тройку казначейских векселей, семейство дисконтных функций и французские функции амортизации. Они вычисляются внутри собственного движка формул библиотеки, поэтому книга, оценивающая портфель облигаций, пересчитывается на сервере без Excel и без надстройки Analysis ToolPak где бы то ни было поблизости

Эти функции когда-то жили в Analysis ToolPak, поэтому за ними до сих пор тянется репутация неудобных. Начиная с Excel 2007 они входят в стандартный набор функций и записываются в формате файла напрямую, без префикса _xlfn., который несут по-настоящему новые функции. Книга, использующая их, — обычная книга; единственный вопрос в том, знает ли эти функции то, что её пересчитывает

Что обязан правильно делать движок купонных дат

Каждый расчёт облигации опирается на одни и те же примитивы: сколько дней прошло с последнего купона, сколько дней в текущем купонном периоде, сколько дней до следующего купона, когда наступают эти купоны и сколько их осталось. Это COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPPCD, COUPNCD и COUPNUM, и их вызывает каждая функция цены и доходности

Нетривиальными их делает аргумент базы расчёта. В игре пять соглашений о подсчёте дней — 30/360 US, actual/actual, actual/360, actual/365 и 30/360 European, — и они расходятся в таких вопросах, как поведение при дате расчёта, приходящейся на 31-е число месяца, или как ведёт себя купонная дата в феврале високосного года. Правильная реализация одного соглашения с приближённой реализацией остальных даёт результаты, которые выглядят правдоподобно и при этом ошибочны на несколько базисных пунктов, а на крупной позиции это реальные деньги

HotXLS реализует все пять баз в самодостаточном вложенном движке купонных дат, общем для каждой функции, которой он нужен, поэтому PRICE и DURATION не могут разойтись друг с другом в том, где находятся купонные даты

Движок купонных дат HotXLS для Delphi: входные данные облигации и выбранная база расчёта дней питают шесть примитивов COUP, общих для PRICE, YIELD, DURATION и MDURATION
Все пять баз расчёта дней питают один вложенный движок купонных дат, общий для каждой финансовой функции в HotXLS, поэтому PRICE и DURATION никогда не могут разойтись в том, где приходятся купонные даты

Расчёт цены и доходности в книге

В вызывающем коде нет ничего особенного: пишете формулы, пересчитываете, читаете значения. В этом и суть — книга является моделью, а ваше Delphi-приложение — калькулятором:

uses
  lxHandleX;

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Bond');
    Sheet.Cells[1, 1].Value := EncodeDate(2026, 3, 15);   // расчёт
    Sheet.Cells[2, 1].Value := EncodeDate(2034, 11, 15);  // погашение
    Sheet.Cells[3, 1].Value := 0.0425;                    // купонная ставка
    Sheet.Cells[4, 1].Value := 0.0391;                    // требуемая доходность
    Sheet.Cells[5, 1].Value := 100;                       // погашение (номинал)
    Sheet.Cells[6, 1].Value := 2;                         // частота
    Sheet.Cells[7, 1].Value := 1;                         // база: actual/actual

    Sheet.Cells[9, 1].Formula  := '=PRICE(A1,A2,A3,A4,A5,A6,A7)';
    Sheet.Cells[10, 1].Formula := '=DURATION(A1,A2,A3,A4,A6,A7)';
    Sheet.Cells[11, 1].Formula := '=MDURATION(A1,A2,A3,A4,A6,A7)';
    Sheet.Cells[12, 1].Formula := '=COUPNUM(A1,A2,A6,A7)';

    Book.Recalculate;

    Writeln(Format('price %.6f  Macaulay %.6f  modified %.6f  coupons %d',
      [Double(Sheet.Cells[9, 1].Value), Double(Sheet.Cells[10, 1].Value),
       Double(Sheet.Cells[11, 1].Value),
       Integer(Sheet.Cells[12, 1].Value)]));
  finally
    Book.Free;
  end;
end;

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

У доходности нет замкнутой формы, и это имеет последствия

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

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

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

Казначейские векселя — отдельное семейство

Векселя — это дисконтные инструменты без купонов, поэтому они используют TBILLPRICE, TBILLYIELD и TBILLEQ, а не функции облигаций. TBILLEQ преобразует ставку дисконта в доходность, эквивалентную облигационной, и у неё есть документированная особенность, которую стоит знать: для сроков погашения более 182 дней преобразование квадратичное, а не линейное, потому что бумага охватывает более одного полугодового периода

Если вместо этого подать вексель в PRICE с нулевым купоном и частотой, то же самое число не получится. Семейство дисконтных инструментов — DISC, INTRATE, RECEIVED, PRICEDISC и YIELDDISC — существует именно потому, что эти инструменты котируются и рассчитываются иначе

Остальная часть набора в одном месте

Помимо облигаций и векселей, тот же пакет охватывает функции, окружающие их в реальных моделях. PRICEMAT и YIELDMAT обрабатывают бумаги, выплачивающие проценты при погашении. ACCRINTM вычисляет для них накопленные проценты. EFFECT и NOMINAL преобразуют между эффективной и номинальной годовыми ставками — преобразование, которое чаще всего делают неправильно вручную. CUMIPMT и CUMPRINC дают накопленные проценты и основной долг за диапазон периодов. FVSCHEDULE применяет последовательность разных ставок подряд, принимая либо диапазон, либо скаляр

DOLLARDE и DOLLARFR преобразуют между десятичной и дробной долларовой котировкой, что важно для любого инструмента, до сих пор котируемого в тридцать вторых долях. А AMORDEGRC и AMORLINC реализуют французские методы амортизации, требуемые французскими правилами бухучёта, а не факультативные удобства

// Преобразование ставки, которое люди регулярно делают неправильно вручную
Sheet.Cells[1, 3].Formula := '=EFFECT(0.0425,12)';   // из номинальной в эффективную
Sheet.Cells[2, 3].Formula := '=NOMINAL(0.043338,12)'; // и обратно

// Накопленные проценты за первые два года 25-летнего кредита
Sheet.Cells[4, 3].Formula :=
  '=CUMIPMT(0.0389/12,300,340000,1,24,0)';

// Прогноз с переменной ставкой: 3 процента, затем 3,5, затем 4
Sheet.Cells[6, 3].Formula := '=FVSCHEDULE(10000,{0.03;0.035;0.04})';

Проверка реализации, которую писали не вы

Финансовые функции — та единственная область, где «функция возвращает число» ничего не доказывает. Число правдоподобно уже по построению, а ошибка в соглашении о подсчёте дней сдвигает результаты на величину слишком малую, чтобы её заметить, и слишком большую, чтобы её принять

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

Набор дифференциальной проверки для финансовых функций облигаций и казначейских векселей HotXLS в Delphi: представительные инструменты, включая случаи високосного года и конца месяца, вычисляются один раз в Excel и сверяются после каждого обновления библиотеки
Дифференциальный набор один раз оценивает представительные инструменты в Excel, сохраняет значения как ожидания и сверяет их после каждого обновления HotXLS, поэтому сдвиг на базисные пункты не может спрятаться за правдоподобным выводом

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

Финансы облигаций, статистика, инженерные функции и движок пересчёта поставляются в одной библиотеке для Delphi и C++Builder; полный список возможностей — на странице компонента HotXLS Delphi Spreadsheet Component