Obligatie- en treasuryformules in Delphi met HotXLS

HotXLS evalueert de obligatie- en treasury-financefuncties van Excel native in Delphi en C++Builder: PRICE, YIELD, DURATION, MDURATION, de zes coupondatum-primitieven, het treasury-billtrio, de discount-familie en de Franse afschrijvingsfuncties. Ze rekenen binnen de eigen formule-engine van de library, zodat een werkmap die een obligatieportefeuille prijst, opnieuw kan worden berekend op een server zonder Excel en zonder Analysis ToolPak-invoegtoepassing in zicht

Deze functies zaten vroeger in de Analysis ToolPak, wat verklaart waarom ze nog steeds de reputatie hebben lastig te zijn. Sinds Excel 2007 maken ze deel uit van de standaardfunctieset en worden ze kaal geschreven in het bestandsformaat, zonder het _xlfn.-voorvoegsel dat werkelijk nieuwere functies dragen. Een werkmap die ze gebruikt, is een gewone werkmap; de enige vraag is of wat hem ook maar herberekent, de functies kent

Wat de coupondatum-engine correct moet doen

Elke obligatieberekening berust op dezelfde primitieven: hoeveel dagen sinds de laatste coupon, hoeveel dagen in de huidige couponperiode, hoeveel dagen tot de volgende coupon, wanneer die coupons vallen, en hoeveel er nog resteren. Dat zijn COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPPCD, COUPNCD en COUPNUM, en elke prijs- en rendementsfunctie roept ze aan

Wat ze niet-triviaal maakt, is het basis-argument. Er spelen vijf day-count-conventies mee — 30/360 US, actual/actual, actual/360, actual/365 en 30/360 Europees — en ze zijn het oneens over vragen zoals wat er gebeurt wanneer een settlementdatum op de 31e van een maand valt, of hoe een coupondatum in februari zich gedraagt in een schrikkeljaar. Eén conventie correct implementeren en de andere benaderen levert resultaten op die plausibel lijken en enkele basispunten fout zitten, wat bij een grote positie echt geld is

HotXLS implementeert alle vijf in een zelfstandige, geneste coupondatum-engine die wordt gedeeld door elke functie die hem nodig heeft, zodat PRICE en DURATION het onderling niet oneens kunnen zijn over waar de coupondatums liggen

Prijsstelling en rendement in een werkmap

Er is niets bijzonders aan de aanroepende code: schrijf de formules, herbereken, lees de waarden. Dat is precies het punt — de werkmap is het model, en uw Delphi-toepassing is de rekenmachine:

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);   // settlement
    Sheet.Cells[2, 1].Value := EncodeDate(2034, 11, 15);  // vervaldatum
    Sheet.Cells[3, 1].Value := 0.0425;                    // couponrente
    Sheet.Cells[4, 1].Value := 0.0391;                    // vereist rendement
    Sheet.Cells[5, 1].Value := 100;                       // aflossing
    Sheet.Cells[6, 1].Value := 2;                         // frequentie
    Sheet.Cells[7, 1].Value := 1;                         // basis: 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;

Datums gaan erin als echte datumwaarden in plaats van als strings. Een tekstdatum werkt in Excel omdat Excel deze afdwingt, en die afdwinging is afhankelijk van de locale, wat precies het soort afhankelijkheid is die een server-side herberekening niet zou moeten hebben. De onderliggende datumsysteemregels worden behandeld in datumserienummers en het 1904-systeem

Yield heeft geen gesloten vorm, en dat heeft gevolgen

PRICE is een directe berekening: verdisconteer de cashflows, tel ze op. YIELD is de inverse ervan en heeft geen gesloten-vorm-oplossing voor een obligatie met meer dan één resterende coupon, dus moet het numeriek worden opgelost. HotXLS gebruikt een bisectie-oplosser, die betrouwbaar convergeert in plaats van snel

Daaruit volgen twee praktische gevolgen. Yield is meetbaar duurder dan price, dus een portefeuilleherwaardering die yields berekent voor tienduizend posities kost merkbaar meer dan één die prices berekent, en het is de moeite waard te weten welke van de twee uw model daadwerkelijk nodig heeft. En een yield die niet kan worden ingesloten — omdat de inputs een obligatie beschrijven die door geen enkele yield tegen de gegeven waarde kan worden geprijsd — retourneert een fout in plaats van een fout getal, wat het juiste gedrag is en waarop u moet testen

Treasury bills vormen hun eigen familie

Bills zijn discount-instrumenten zonder coupons, dus gebruiken ze TBILLPRICE, TBILLYIELD en TBILLEQ in plaats van de obligatiefuncties. TBILLEQ zet een discountrente om naar een bond-equivalent rendement, en het heeft een gedocumenteerde eigenaardigheid die het waard is te kennen: voor looptijden van meer dan 182 dagen is de omzetting kwadratisch in plaats van lineair, omdat het effect meer dan één halfjaarperiode overspant

Een bill in plaats daarvan invoeren in PRICE, met een nulcoupon en een frequentie, levert niet hetzelfde getal op. De discount-instrumentfamilie — DISC, INTRATE, RECEIVED, PRICEDISC en YIELDDISC — bestaat juist omdat deze instrumenten anders worden genoteerd en afgewikkeld

De rest van de set, op één plek

Naast obligaties en bills dekt dezelfde batch de functies die er in echte modellen omheen zitten. PRICEMAT en YIELDMAT behandelen effecten die rente uitkeren bij vervaldatum. ACCRINTM berekent de opgelopen rente daarvoor. EFFECT en NOMINAL zetten om tussen effectieve en nominale jaarlijkse rentes, wat de omzetting is die het vaakst met de hand fout wordt gedaan. CUMIPMT en CUMPRINC geven cumulatieve rente en aflossing over een reeks periodes. FVSCHEDULE past een reeks verschillende rentes achtereenvolgens toe, en neemt daarbij een bereik of een scalar

DOLLARDE en DOLLARFR zetten om tussen decimale en gebroken dollarnoteringen, wat van belang is voor elk instrument dat nog in tweeëndertigsten wordt genoteerd. En AMORDEGRC en AMORLINC implementeren de Franse afschrijvingsmethoden, die verplicht zijn volgens de Franse boekhoudregels en geen optioneel gemak zijn

// Een rente-omzetting die mensen routinematig met de hand fout doen
Sheet.Cells[1, 3].Formula := '=EFFECT(0.0425,12)';   // nominaal naar effectief
Sheet.Cells[2, 3].Formula := '=NOMINAL(0.043338,12)'; // en terug

// Cumulatieve rente over de eerste twee jaar van een lening van 25 jaar
Sheet.Cells[4, 3].Formula :=
  '=CUMIPMT(0.0389/12,300,340000,1,24,0)';

// Een projectie met variabele rente: 3 procent, dan 3,5, dan 4
Sheet.Cells[6, 3].Formula := '=FVSCHEDULE(10000,{0.03;0.035;0.04})';

Een implementatie valideren die u niet zelf hebt geschreven

Financiële functies zijn het ene gebied waar "hij geeft een getal terug" geen enkel bewijs is. Het getal is door constructie plausibel, en een fout in een day-count-conventie verschuift resultaten met een bedrag dat te klein is om op te merken en te groot om te accepteren

De test die daadwerkelijk werkt, is een differentiële. Bouw een spreadsheet met representatieve instrumenten — één obligatie per day-count-basis, één die op een maandeinde afwikkelt, één die een schrikkeljaarfebruari overspant, één bill onder de 182 dagen en één erboven — bereken de waarden één keer in Excel, bewaar ze als verwachte resultaten, en toets eraan na elke library-upgrade. Posities die over een coupondatum heen lopen en afwikkelingen op de 31e zijn waar implementaties uiteenlopen, dus neem beide bewust op

Voor de werkmappen zelf zijn de twee metgezellen van deze functie de rekenengine die beslist wat opnieuw moet worden berekend, beschreven in incrementele herberekening en de afhankelijkheidsgraaf, en de statistische familie behandeld in statistische verdelingsfuncties. Samen zijn ze wat een financieel model onbeheerd op een server laat draaien, wat de hele reden was om ze native te implementeren

Obligatiefinanciering, statistiek, technische functies en de herberekeningsengine worden geleverd in één library voor Delphi en C++Builder; de volledige functielijst staat op de HotXLS Delphi spreadsheetcomponent-pagina