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

HotXLS evalúa de forma nativa en Delphi y C++Builder las funciones financieras de bonos y letras del tesoro de Excel: 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 de depreciación francesas. 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 antes vivían en el Analysis ToolPak, razón por la cual todavía cargan con la fama de ser incómodas. Desde Excel 2007 forman parte del conjunto estándar de funciones y se escriben sin adornos en el formato de archivo, sin el prefijo _xlfn. que sí llevan las funciones genuinamente más nuevas. Un libro que las usa es un libro ordinario; la única pregunta es si lo que sea que lo recalcule conoce esas funciones

Lo que el motor de fechas de cupón tiene que acertar

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 período 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 toda función de precio y de rendimiento las llama

Lo que las hace no triviales es el argumento de base. Hay cinco convenciones de conteo de días en juego — 30/360 US, actual/actual, actual/360, actual/365 y 30/360 europea — y difieren en preguntas como qué pasa cuando una fecha de liquidación cae el día 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 se ven plausibles y que 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 autocontenido y compartido por cada función que lo necesita, de modo que PRICE y DURATION no pueden estar en desacuerdo entre sí sobre dónde caen las fechas de cupón

Precio y rendimiento dentro de un libro

No hay nada especial en el código que hace la llamada: escriba las fórmulas, recalcule, lea los valores. Ese es justamente el punto — el libro es el modelo, y su 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;                    // tasa de cupón
    Sheet.Cells[4, 1].Value := 0.0391;                    // rendimiento requerido
    Sheet.Cells[5, 1].Value := 100;                       // redención
    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 ingresan como valores de fecha reales y no como cadenas de texto. Una fecha en texto funciona en Excel porque Excel la coacciona, y esa coacción depende del locale, que es exactamente el tipo de dependencia que un recálculo del lado del servidor no debería tener. Las reglas del sistema de fechas que subyacen a esto se cubren en números de serie de fecha y el sistema 1904

El rendimiento no tiene forma cerrada, y eso tiene consecuencias

PRICE es un cálculo directo: descuente los flujos de caja, súmelos. YIELD es su inverso y no tiene una solución de forma cerrada para un bono al que le queda más de un cupón, así que debe resolverse numéricamente. HotXLS usa un solucionador por bisección, que converge de forma confiable, aunque no rápida

De ahí se siguen dos implicaciones prácticas. Calcular el rendimiento resulta, de forma medible, más costoso que calcular el precio, de modo que una revaloración de cartera que calcula rendimientos para diez mil posiciones cuesta notablemente más que una que calcula precios, y vale la pena saber cuál de los dos necesita realmente su modelo. Y un rendimiento que no se puede acotar — porque los datos de entrada describen un bono que ningún rendimiento puede valorar al precio dado — devuelve un error en lugar de un número equivocado, 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 un rendimiento equivalente a bono, y tiene una particularidad 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 período semestral

Si en cambio se alimenta una letra a PRICE, con un cupón de cero y una frecuencia, no se obtiene el mismo número. La familia de instrumentos de descuento — DISC, INTRATE, RECEIVED, PRICEDISC y YIELDDISC — existe precisamente porque estos instrumentos se cotizan y se liquidan de forma distinta

El resto del conjunto, en un solo lugar

Más allá de bonos y 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 acumulado para esos casos. 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 sobre un rango de períodos. FVSCHEDULE aplica una serie de tasas distintas en secuencia, y toma tanto un rango como 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 de depreciación franceses, que exigen las normas contables francesas y no son una comodidad opcional

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

// Interés acumulado durante los primeros dos 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 usted no escribió

Las funciones financieras son la única área donde "devuelve un número" no es evidencia de nada. El número es plausible por construcción, y un error en una convención de conteo de días desplaza los resultados en una cantidad demasiado pequeña para notarla y demasiado grande para aceptarla

La prueba que realmente funciona es una diferencial. Construya una hoja de cálculo con instrumentos representativos — un bono por cada base de conteo de días, uno que liquide a fin de mes, uno que abarque un febrero bisiesto, una letra de menos de 182 días y otra de más — calcule los valores en Excel una vez, guárdelos como resultados esperados, y compárelos contra ellos después de cada actualización de la biblioteca. Las posiciones que caen justo a caballo de una fecha de cupón y las liquidaciones el día 31 son donde las implementaciones divergen, así que inclúyalas deliberadamente

Para los propios libros, los dos complementos 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 cubierta en funciones de distribución estadística. Juntos son lo que permite que un modelo financiero corra sin supervisión en un servidor, que era toda 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 entregan en una sola biblioteca para Delphi y C++Builder; la lista completa de funciones está en la página del componente de hoja de cálculo para Delphi de HotXLS