Formules obligataires et du Trésor en Delphi avec HotXLS

HotXLS évalue nativement en Delphi et C++Builder les fonctions financières obligataires et du Trésor d'Excel : PRICE, YIELD, DURATION, MDURATION, les six primitives de date de coupon, le trio des bons du Trésor, la famille d'escompte et les fonctions françaises d'amortissement. Elles se calculent à l'intérieur du moteur de formules propre à la bibliothèque, si bien qu'un classeur qui valorise un portefeuille obligataire se recalcule sur un serveur sans Excel ni add-in Analysis ToolPak nulle part en vue

Ces fonctions vivaient autrefois dans l'Analysis ToolPak, ce qui explique qu'elles conservent une réputation de complexité. Depuis Excel 2007, elles font partie de l'ensemble de fonctions standard et sont écrites nues dans le format de fichier, sans le préfixe _xlfn. que portent les fonctions réellement plus récentes. Un classeur qui les utilise est un classeur ordinaire ; la seule question est de savoir si ce qui le recalcule connaît ces fonctions

Ce que le moteur de dates de coupon doit réussir

Chaque calcul obligataire repose sur les mêmes primitives : combien de jours depuis le dernier coupon, combien de jours dans la période de coupon en cours, combien de jours jusqu'au prochain coupon, quand ces coupons tombent, et combien il en reste. Ce sont COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPPCD, COUPNCD et COUPNUM, et chaque fonction de prix et de rendement les appelle

Ce qui les rend non triviales, c'est l'argument de base. Cinq conventions de comptage des jours entrent en jeu — 30/360 US, actuel/actuel, actuel/360, actuel/365 et 30/360 européen — et elles divergent sur des questions telles que ce qui se passe quand une date de règlement tombe le 31 d'un mois, ou comment se comporte une date de coupon de février lors d'une année bissextile. Bien traiter une convention et approximer les autres produit des résultats plausibles en apparence et faux de quelques points de base, ce qui représente de l'argent réel sur une position importante

HotXLS implémente les cinq dans un moteur de dates de coupon imbriqué et autonome, partagé par chaque fonction qui en a besoin, si bien que PRICE et DURATION ne peuvent pas se contredire quant à la position des dates de coupon

Prix et rendement dans un classeur

Rien dans le code d'appel n'a de spécial : écrivez les formules, recalculez, lisez les valeurs. C'est tout l'intérêt — le classeur est le modèle, et votre application Delphi est la calculatrice :

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);   // règlement
    Sheet.Cells[2, 1].Value := EncodeDate(2034, 11, 15);  // échéance
    Sheet.Cells[3, 1].Value := 0.0425;                    // taux de coupon
    Sheet.Cells[4, 1].Value := 0.0391;                    // rendement requis
    Sheet.Cells[5, 1].Value := 100;                       // remboursement
    Sheet.Cells[6, 1].Value := 2;                         // fréquence
    Sheet.Cells[7, 1].Value := 1;                         // base : actuel/actuel

    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;

Les dates entrent comme de vraies valeurs de date plutôt que comme des chaînes. Une date texte fonctionne dans Excel parce qu'Excel la convertit, et la conversion dépend de la locale, ce qui est exactement le genre de dépendance qu'un recalcul côté serveur ne devrait pas avoir. Les règles de système de dates qui sous-tendent tout cela sont traitées dans les numéros de série de date et le système 1904

Le rendement n'a pas de forme fermée, et cela a des conséquences

PRICE est un calcul direct : actualiser les flux de trésorerie, les additionner. YIELD en est l'inverse et n'a pas de solution sous forme fermée pour une obligation avec plus d'un coupon restant, si bien qu'elle doit être résolue numériquement. HotXLS utilise un solveur par bissection, qui converge de façon fiable plutôt que rapide

Deux implications pratiques en découlent. Le rendement est mesurablement plus coûteux que le prix, si bien qu'une revalorisation de portefeuille qui calcule les rendements de dix mille positions coûte sensiblement plus cher que celle qui calcule les prix, et il vaut la peine de savoir lequel des deux votre modèle a réellement besoin. Et un rendement qui ne peut pas être encadré — parce que les entrées décrivent une obligation qu'aucun rendement ne peut valoriser à la valeur donnée — retourne une erreur plutôt qu'un nombre faux, ce qui est le comportement correct et celui qu'il faut tester

Les bons du Trésor forment leur propre famille

Les bons sont des instruments d'escompte sans coupon, ils utilisent donc TBILLPRICE, TBILLYIELD et TBILLEQ plutôt que les fonctions obligataires. TBILLEQ convertit un taux d'escompte en rendement équivalent obligataire, et elle comporte une particularité documentée qu'il vaut la peine de connaître : pour des échéances de plus de 182 jours, la conversion est quadratique plutôt que linéaire, car le titre s'étend sur plus d'une période semestrielle

Faire passer un bon dans PRICE à la place, avec un coupon nul et une fréquence, ne produit pas le même nombre. La famille des instruments d'escompte — DISC, INTRATE, RECEIVED, PRICEDISC et YIELDDISC — existe précisément parce que ces instruments sont cotés et réglés différemment

Le reste de l'ensemble, réuni au même endroit

Au-delà des obligations et des bons, le même lot couvre les fonctions qui les entourent dans les modèles réels. PRICEMAT et YIELDMAT gèrent les titres qui paient l'intérêt à l'échéance. ACCRINTM calcule l'intérêt couru pour ces derniers. EFFECT et NOMINAL convertissent entre taux annuels effectifs et nominaux, la conversion la plus souvent mal faite à la main. CUMIPMT et CUMPRINC donnent l'intérêt et le principal cumulés sur une plage de périodes. FVSCHEDULE applique une série de taux différents en séquence, en prenant soit une plage soit un scalaire

DOLLARDE et DOLLARFR convertissent entre cotations en dollars décimales et fractionnaires, ce qui compte pour tout instrument encore coté en trente-deuxièmes. Et AMORDEGRC et AMORLINC implémentent les méthodes françaises d'amortissement, exigées par les règles comptables françaises plutôt que de simples commodités optionnelles

// Une conversion de taux que l'on se trompe couramment à la main
Sheet.Cells[1, 3].Formula := '=EFFECT(0.0425,12)';   // nominal vers effectif
Sheet.Cells[2, 3].Formula := '=NOMINAL(0.043338,12)'; // et retour

// Intérêt cumulé sur les deux premières années d'un prêt de 25 ans
Sheet.Cells[4, 3].Formula :=
  '=CUMIPMT(0.0389/12,300,340000,1,24,0)';

// Une projection à taux variable : 3 pour cent, puis 3,5, puis 4
Sheet.Cells[6, 3].Formula := '=FVSCHEDULE(10000,{0.03;0.035;0.04})';

Valider une implémentation que vous n'avez pas écrite

Les fonctions financières sont le seul domaine où « ça retourne un nombre » ne prouve rien. Le nombre est plausible par construction, et une erreur dans une convention de comptage des jours décale les résultats d'un montant trop petit pour être remarqué et trop grand pour être accepté

Le test qui fonctionne réellement est un test différentiel. Construisez une feuille de calcul d'instruments représentatifs — une obligation par base de comptage des jours, une qui se règle en fin de mois, une qui traverse un février bissextile, un bon de moins de 182 jours et un de plus — calculez les valeurs dans Excel une fois, stockez-les comme résultats attendus, et validez-les après chaque mise à jour de la bibliothèque. Les positions à cheval sur une date de coupon et les règlements le 31 sont l'endroit où les implémentations divergent, donc incluez les deux délibérément

Pour les classeurs eux-mêmes, les deux compagnons de cette fonctionnalité sont le moteur de calcul qui décide quoi recalculer, décrit dans le recalcul incrémental et le graphe de dépendances, et la famille statistique traitée dans les fonctions de distribution statistique. Ensemble, elles sont ce qui permet à un modèle financier de tourner sans surveillance sur un serveur, ce qui était toute la raison de les implémenter nativement

Les finances obligataires, les statistiques, les fonctions d'ingénierie et le moteur de recalcul sont livrés dans une seule bibliothèque pour Delphi et C++Builder ; la liste complète des fonctionnalités se trouve sur la page du composant tableur HotXLS Delphi