Formule per obbligazioni e titoli di stato in Delphi con HotXLS

HotXLS valuta nativamente in Delphi e C++Builder le funzioni finanziarie di Excel per obbligazioni e titoli di stato: PRICE, YIELD, DURATION, MDURATION, le sei primitive delle date cedola, il trio dei buoni del tesoro, la famiglia degli sconti e le funzioni di ammortamento francesi. Vengono calcolate all'interno del motore di formule proprio della libreria, così una cartella di lavoro che valuta un portafoglio obbligazionario ricalcola su un server senza Excel e senza alcun add-in Analysis ToolPak in vista

Queste funzioni un tempo vivevano nell'Analysis ToolPak, motivo per cui portano ancora la reputazione di essere scomode. Dalla versione Excel 2007 fanno parte del set standard di funzioni e vengono scritte nude nel formato file, senza il prefisso _xlfn. che le funzioni genuinamente più recenti portano. Una cartella di lavoro che le usa è una cartella di lavoro ordinaria; l'unica domanda è se qualunque cosa la ricalcoli conosca le funzioni

Cosa deve fare correttamente il motore delle date cedola

Ogni calcolo obbligazionario si basa sulle stesse primitive: quanti giorni dall'ultima cedola, quanti giorni nel periodo cedolare corrente, quanti giorni fino alla prossima cedola, quando cadono quelle cedole, e quante ne rimangono. Sono COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPPCD, COUPNCD e COUPNUM, e ogni funzione di prezzo e rendimento le richiama

Ciò che le rende non banali è l'argomento della base. Sono in gioco cinque convenzioni di conteggio giorni — 30/360 statunitense, effettivo/effettivo, effettivo/360, effettivo/365 e 30/360 europea — e sono in disaccordo su questioni come cosa succede quando una data di regolamento cade il 31 del mese, o come si comporta una data cedola di febbraio in un anno bisestile. Implementare correttamente una convenzione e approssimare le altre produce risultati che sembrano plausibili e sono sbagliati di qualche punto base, il che su una posizione grande equivale a denaro reale

HotXLS implementa tutte e cinque in un motore delle date cedola annidato e autonomo, condiviso da ogni funzione che ne ha bisogno, così PRICE e DURATION non possono essere in disaccordo tra loro su dove si trovino le date cedola

Prezzo e rendimento in una cartella di lavoro

Non c'è nulla di speciale nel codice chiamante: scrivete le formule, ricalcolate, leggete i valori. È proprio questo il punto — la cartella di lavoro è il modello, e la vostra applicazione Delphi è la calcolatrice:

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);   // regolamento
    Sheet.Cells[2, 1].Value := EncodeDate(2034, 11, 15);  // scadenza
    Sheet.Cells[3, 1].Value := 0.0425;                    // tasso cedolare
    Sheet.Cells[4, 1].Value := 0.0391;                    // rendimento richiesto
    Sheet.Cells[5, 1].Value := 100;                       // rimborso
    Sheet.Cells[6, 1].Value := 2;                         // frequenza
    Sheet.Cells[7, 1].Value := 1;                         // base: effettivo/effettivo

    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;

Le date vengono inserite come valori data reali anziché come stringhe. Una data testuale funziona in Excel perché Excel la coercizza, e la coercizione dipende dalla locale, che è esattamente il tipo di dipendenza che un ricalcolo lato server non dovrebbe avere. Le regole del sistema di date che stanno alla base sono trattate in numeri seriali di data e il sistema 1904

Il rendimento non ha forma chiusa, e questo ha conseguenze

PRICE è un calcolo diretto: scontare i flussi di cassa, sommarli. YIELD è il suo inverso e non ha una soluzione in forma chiusa per un'obbligazione con più di una cedola rimanente, quindi deve essere risolto numericamente. HotXLS usa un solutore per bisezione, che converge in modo affidabile piuttosto che rapido

Ne seguono due implicazioni pratiche. Il rendimento è misurabilmente più costoso del prezzo, quindi una rivalutazione di portafoglio che calcola rendimenti per diecimila posizioni costa sensibilmente di più di una che calcola prezzi, e vale la pena sapere quale dei due il vostro modello richieda realmente. E un rendimento che non può essere delimitato — perché gli input descrivono un'obbligazione che nessun rendimento può valorizzare al dato valore — restituisce un errore anziché un numero sbagliato, il che è il comportamento corretto e quello da testare

I buoni del tesoro sono una famiglia a sé

I buoni sono strumenti a sconto senza cedole, quindi usano TBILLPRICE, TBILLYIELD e TBILLEQ piuttosto che le funzioni obbligazionarie. TBILLEQ converte un tasso di sconto in un rendimento equivalente obbligazionario, e ha una particolarità documentata che vale la pena conoscere: per scadenze superiori a 182 giorni la conversione è quadratica anziché lineare, perché il titolo copre più di un periodo semestrale

Passare invece un buono a PRICE, con cedola zero e una frequenza, non produce lo stesso numero. La famiglia degli strumenti a sconto — DISC, INTRATE, RECEIVED, PRICEDISC e YIELDDISC — esiste proprio perché questi strumenti vengono quotati e regolati in modo diverso

Il resto del set, in un unico posto

Oltre a obbligazioni e buoni, lo stesso gruppo copre le funzioni che le circondano nei modelli reali. PRICEMAT e YIELDMAT gestiscono titoli che pagano interessi a scadenza. ACCRINTM calcola gli interessi maturati per quelli. EFFECT e NOMINAL convertono tra tassi annui effettivi e nominali, la conversione più comunemente sbagliata a mano. CUMIPMT e CUMPRINC forniscono interessi e capitale cumulativi su un intervallo di periodi. FVSCHEDULE applica in sequenza una serie di tassi diversi, accettando sia un intervallo sia uno scalare

DOLLARDE e DOLLARFR convertono tra quotazioni in dollari decimali e frazionarie, il che conta per qualsiasi strumento ancora quotato in trentaduesimi. E AMORDEGRC e AMORLINC implementano i metodi di ammortamento francesi, che sono richiesti dalle norme contabili francesi piuttosto che comodità opzionali

// Una conversione di tasso che le persone sbagliano regolarmente a mano
Sheet.Cells[1, 3].Formula := '=EFFECT(0.0425,12)';   // da nominale a effettivo
Sheet.Cells[2, 3].Formula := '=NOMINAL(0.043338,12)'; // e viceversa

// Interessi cumulativi sui primi due anni di un mutuo a 25 anni
Sheet.Cells[4, 3].Formula :=
  '=CUMIPMT(0.0389/12,300,340000,1,24,0)';

// Una proiezione a tasso variabile: 3 percento, poi 3,5, poi 4
Sheet.Cells[6, 3].Formula := '=FVSCHEDULE(10000,{0.03;0.035;0.04})';

Validare un'implementazione che non avete scritto voi

Le funzioni finanziarie sono l'unica area in cui "restituisce un numero" non è prova di nulla. Il numero è plausibile per costruzione, e un errore in una convenzione di conteggio giorni sposta i risultati di una quantità troppo piccola da notare e troppo grande da accettare

Il test che funziona davvero è di tipo differenziale. Costruite un foglio di calcolo di strumenti rappresentativi — un'obbligazione per ogni base di conteggio giorni, una che si regola a fine mese, una che copre un febbraio bisestile, un buono sotto i 182 giorni e uno sopra — calcolate i valori in Excel una volta, memorizzateli come risultati attesi, e verificateli dopo ogni aggiornamento della libreria. Le posizioni a cavallo di una data cedola e i regolamenti al 31 sono i punti in cui le implementazioni divergono, quindi includete entrambi deliberatamente

Per le cartelle di lavoro stesse, i due complementi di questa funzionalità sono il motore di calcolo che decide cosa ricalcolare, descritto in ricalcolo incrementale e grafo delle dipendenze, e la famiglia statistica trattata in funzioni di distribuzione statistica. Insieme sono ciò che permette a un modello finanziario di girare senza supervisione su un server, che era proprio il motivo per implementarle nativamente

La finanza obbligazionaria, le statistiche, le funzioni ingegneristiche e il motore di ricalcolo sono forniti in un'unica libreria per Delphi e C++Builder; l'elenco completo delle funzionalità è disponibile sulla pagina del componente foglio di calcolo HotXLS Delphi