Fórmulas de bonos y letras del Tesoro en Delphi con HotXLS

HotXLS evalúa de forma nativa las funciones financieras de bonos y letras del Tesoro de Excel en Delphi y C++Builder: PRICE, YIELD, DURATION, MDURATION, las seis primitivas de fecha de cupón, el trío de letras del Tesoro, la familia de descuento y las funciones francesas de depreciación. Se calculan dentro del propio motor de fórmulas de la biblioteca, de modo que un libro que valora una cartera de bonos se recalcula en un servidor sin Excel ni el complemento Analysis ToolPak a la vista

Estas funciones vivían antes en el Analysis ToolPak, razón por la cual todavía tienen fama de ser incómodas. Desde Excel 2007 forman parte del conjunto de funciones estándar y se escriben desnudas en el formato de archivo, sin el prefijo _xlfn. que sí llevan las funciones genuinamente más recientes. Un libro que las usa es un libro ordinario; la única duda es si lo que sea que lo recalcule conoce las funciones

Qué tiene que acertar el motor de fechas de cupón

Todo cálculo de bonos descansa sobre las mismas primitivas: cuántos días han pasado desde el último cupón, cuántos días tiene el periodo de cupón actual, cuántos días faltan hasta el próximo cupón, cuándo caen esos cupones, y cuántos quedan. Esas son COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPPCD, COUPNCD y COUPNUM, y todas las funciones de precio y rentabilidad las llaman

Lo que las hace no triviales es el argumento de base. Hay cinco convenciones de recuento de días en juego — 30/360 US, actual/actual, actual/360, actual/365 y 30/360 europea — y discrepan en cuestiones como qué ocurre cuando una fecha de liquidación cae el 31 de un mes, o cómo se comporta una fecha de cupón de febrero en un año bisiesto. Acertar en una convención y aproximar las demás produce resultados que parecen plausibles y están equivocados por unos pocos puntos básicos, lo cual en una posición grande es dinero real

HotXLS implementa las cinco en un motor de fechas de cupón anidado y autocontenido, compartido por todas las funciones que lo necesitan, de modo que PRICE y DURATION no pueden discrepar entre sí sobre dónde están las fechas de cupón

Precio y rentabilidad en un libro

Nada en el código que llama a estas funciones es especial: escribe las fórmulas, recalcula, lee los valores. Esa es la clave — el libro es el modelo, y tu aplicación Delphi es la calculadora:

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);   // liquidación
    Sheet.Cells[2, 1].Value := EncodeDate(2034, 11, 15);  // vencimiento
    Sheet.Cells[3, 1].Value := 0.0425;                    // tipo de cupón
    Sheet.Cells[4, 1].Value := 0.0391;                    // rentabilidad exigida
    Sheet.Cells[5, 1].Value := 100;                       // valor de reembolso
    Sheet.Cells[6, 1].Value := 2;                         // frecuencia
    Sheet.Cells[7, 1].Value := 1;                         // base: 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;

Las fechas se introducen como valores de fecha reales y no como cadenas de texto. Una fecha en texto funciona en Excel porque Excel la convierte, y esa conversión depende de la configuración regional, que es exactamente el tipo de dependencia que un recálculo en servidor no debería tener. Las reglas del sistema de fechas subyacente se tratan en números de serie de fecha y el sistema 1904

La rentabilidad no tiene forma cerrada, y eso tiene consecuencias

PRICE es un cálculo directo: descuenta los flujos de caja, súmalos. YIELD es su inversa y no tiene solución de forma cerrada para un bono con más de un cupón pendiente, así que hay que resolverla numéricamente. HotXLS usa un solucionador por bisección, que converge de forma fiable en lugar de rápida

De ahí se derivan dos implicaciones prácticas. La rentabilidad es medible más costosa que el precio, así que una revaloración de cartera que calcula rentabilidades para diez mil posiciones cuesta notablemente más que una que calcula precios, y vale la pena saber cuál de las dos necesita realmente tu modelo. Y una rentabilidad que no se puede acotar — porque los datos de entrada describen un bono que ninguna rentabilidad puede fijar al precio dado — devuelve un error en lugar de un número incorrecto, que es el comportamiento correcto y el que hay que probar

Las letras del Tesoro son su propia familia

Las letras son instrumentos de descuento sin cupones, así que usan TBILLPRICE, TBILLYIELD y TBILLEQ en lugar de las funciones de bonos. TBILLEQ convierte una tasa de descuento en una rentabilidad equivalente a bono, y tiene una peculiaridad documentada que vale la pena conocer: para vencimientos de más de 182 días la conversión es cuadrática y no lineal, porque el instrumento abarca más de un periodo de medio año

Introducir una letra en PRICE en su lugar, con cupón cero y una frecuencia, no produce el mismo número. La familia de instrumentos de descuento — DISC, INTRATE, RECEIVED, PRICEDISC y YIELDDISC — existe precisamente porque estos instrumentos se cotizan y liquidan de forma distinta

El resto del conjunto, en un solo lugar

Más allá de los bonos y las letras, el mismo lote cubre las funciones que las rodean en los modelos reales. PRICEMAT y YIELDMAT manejan valores que pagan intereses al vencimiento. ACCRINTM calcula el interés devengado para esos. EFFECT y NOMINAL convierten entre tasas anuales efectivas y nominales, la conversión que con más frecuencia se hace mal a mano. CUMIPMT y CUMPRINC dan el interés y el principal acumulados a lo largo de un rango de periodos. FVSCHEDULE aplica una serie de tasas distintas en secuencia, tomando ya sea un rango o un escalar

DOLLARDE y DOLLARFR convierten entre cotizaciones en dólares decimales y fraccionarias, algo que importa para cualquier instrumento que todavía se cotice en treintaidosavos. Y AMORDEGRC y AMORLINC implementan los métodos franceses de depreciación, exigidos por las normas contables francesas y no simples comodidades opcionales

// Una conversión de tasa que la gente suele hacer mal a mano
Sheet.Cells[1, 3].Formula := '=EFFECT(0.0425,12)';   // nominal a efectiva
Sheet.Cells[2, 3].Formula := '=NOMINAL(0.043338,12)'; // y de vuelta

// Interés acumulado en los dos primeros años de un préstamo a 25 años
Sheet.Cells[4, 3].Formula :=
  '=CUMIPMT(0.0389/12,300,340000,1,24,0)';

// Una proyección a tasa variable: 3 por ciento, luego 3,5, luego 4
Sheet.Cells[6, 3].Formula := '=FVSCHEDULE(10000,{0.03;0.035;0.04})';

Validar una implementación que no escribiste tú

Las funciones financieras son el único terreno donde «devuelve un número» no es prueba de nada. El número es plausible por construcción, y un error en una convención de recuento de días desplaza los resultados en una cantidad demasiado pequeña para notarse y demasiado grande para aceptarse

La prueba que realmente funciona es diferencial. Construye una hoja de cálculo de instrumentos representativos — un bono por cada base de recuento de días, uno que liquide a fin de mes, uno que abarque un febrero bisiesto, una letra por debajo de 182 días y otra por encima —, calcula los valores en Excel una vez, guárdalos como resultados esperados, y compáralos tras cada actualización de la biblioteca. Las posiciones que abarcan una fecha de cupón y las liquidaciones el día 31 son donde las implementaciones divergen, así que incluye ambas deliberadamente

Para los propios libros, los dos complementos naturales de esta función son el motor de cálculo que decide qué recalcular, descrito en recálculo incremental y el grafo de dependencias, y la familia estadística tratada en funciones de distribución estadística. Juntas son lo que permite que un modelo financiero se ejecute sin supervisión en un servidor, que era precisamente la razón para implementarlas de forma nativa

Las finanzas de bonos, la estadística, las funciones de ingeniería y el motor de recálculo se distribuyen en una única biblioteca para Delphi y C++Builder; la lista completa de funciones está en la página del componente de hoja de cálculo HotXLS Delphi