Obligations- och statspappersformler i Delphi med HotXLS

HotXLS beräknar Excels finansfunktioner för obligationer och statspapper direkt i Delphi och C++Builder: PRICE, YIELD, DURATION, MDURATION, de sex kupongdatumsprimitiven, statsskuldväxeltrion, diskonteringsfamiljen och de franska avskrivningsfunktionerna. De beräknas inuti bibliotekets egen formelmotor, så en arbetsbok som prissätter en obligationsportfölj omberäknas på en server utan att vare sig Excel eller tillägget Analysis ToolPak syns till någonstans

Dessa funktioner bodde tidigare i Analysis ToolPak, vilket är varför de fortfarande har rykte om sig att vara besvärliga. Sedan Excel 2007 har de varit en del av standardfunktionsuppsättningen och skrivs rent i filformatet, utan det _xlfn.-prefix som genuint nyare funktioner bär. En arbetsbok som använder dem är en helt vanlig arbetsbok; den enda frågan är om det som omberäknar den känner till funktionerna

Vad kupongdatummotorn måste få rätt

Varje obligationsberäkning vilar på samma primitiv: hur många dagar sedan senaste kupong, hur många dagar i den aktuella kupongperioden, hur många dagar till nästa kupong, när dessa kuponger infaller, och hur många som återstår. Det är COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPPCD, COUPNCD och COUPNUM, och varje pris- och avkastningsfunktion anropar dem

Det som gör dem icke-triviala är basargumentet. Fem dagräkningskonventioner är i spel — 30/360 US, verklig/verklig, verklig/360, verklig/365 och 30/360 europeisk — och de är oense om frågor som vad som händer när ett likviddatum infaller på den 31:a i en månad, eller hur ett februarikupongdatum beter sig ett skottår. Att få en konvention rätt och approximera de andra ger resultat som ser rimliga ut och är fel med några punkter, vilket på en stor position är riktiga pengar

HotXLS implementerar alla fem i en självständig, nästlad kupongdatummotor som delas av varje funktion som behöver den, så PRICE och DURATION kan aldrig vara oense om var kupongdatumen ligger

HotXLS kupongdatummotor för Delphi: obligationsindata och den valda dagräkningsbasen matar sex COUP-primitiv som delas av PRICE, YIELD, DURATION och MDURATION — Alla fem dagräkningsbaser matar en enda nästlad kupongdatummotor som delas av varje finansfunktion i HotXLS, så PRICE och DURATION kan aldrig vara oense om var kupongdatumen infaller

Prissättning och avkastning i en arbetsbok

Inget med den anropande koden är speciellt: skriv formlerna, omberäkna, läs värdena. Det är poängen — arbetsboken är modellen, och din Delphi-applikation är räknaren:

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);   // likviddag
    Sheet.Cells[2, 1].Value := EncodeDate(2034, 11, 15);  // förfallodag
    Sheet.Cells[3, 1].Value := 0.0425;                    // kupongränta
    Sheet.Cells[4, 1].Value := 0.0391;                    // begärd avkastning
    Sheet.Cells[5, 1].Value := 100;                       // inlösenvärde
    Sheet.Cells[6, 1].Value := 2;                         // frekvens
    Sheet.Cells[7, 1].Value := 1;                         // bas: verklig/verklig

    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;

Datum matas in som riktiga datumvärden snarare än som strängar. Ett textdatum fungerar i Excel eftersom Excel tvångskonverterar det, och tvångskonvertering beror på lokalinställning, vilket är exakt den typen av beroende en serverside-omberäkning inte bör ha. Datumsystemreglerna som ligger under täcks i datumserienummer och 1904-systemet

Avkastning saknar sluten form, och det får konsekvenser

PRICE är en direkt beräkning: diskontera kassaflödena, summera dem. YIELD är dess invers och saknar en sluten lösning för en obligation med mer än en återstående kupong, så den måste lösas numeriskt. HotXLS använder en halveringsmetod (bisektion), som konvergerar tillförlitligt snarare än snabbt

Två praktiska konsekvenser följer. Avkastning är mätbart dyrare att beräkna än pris, så en portföljomvärdering som beräknar avkastning för tio tusen positioner kostar märkbart mer än en som beräknar priser, och det är värt att veta vilken av de två din modell faktiskt behöver. Och en avkastning som inte kan inringas — eftersom indata beskriver en obligation som ingen avkastning kan prissätta till det angivna värdet — returnerar ett fel snarare än ett felaktigt tal, vilket är det korrekta beteendet och det som är värt att testa för

I HotXLS på Delphi är PRICE en direkt beräkning av diskonterade kassaflöden i en passering, medan YIELD saknar sluten form och löses av en bisektionsloop som returnerar ett fel när inget intervall kan hittas — PRICE är en direkt passering över de diskonterade kassaflödena, medan YIELD saknar sluten form och inringas och bisekteras, och returnerar ett fel snarare än ett felaktigt tal när indata inte kan prissättas

Statsskuldväxlar är en egen familj

Statsskuldväxlar är diskonteringsinstrument utan kuponger, så de använder TBILLPRICE, TBILLYIELD och TBILLEQ snarare än obligationsfunktionerna. TBILLEQ konverterar en diskonteringsränta till en obligationsekvivalent avkastning, och den har en dokumenterad egenhet värd att känna till: för löptider över 182 dagar är konverteringen kvadratisk snarare än linjär, eftersom värdepapperet sträcker sig över mer än en halvårsperiod

Att istället mata en statsskuldväxel in i PRICE, med noll kupong och en frekvens, ger inte samma tal. Diskonteringsinstrumentfamiljen — DISC, INTRATE, RECEIVED, PRICEDISC och YIELDDISC — finns just för att dessa instrument noteras och avvecklas annorlunda

Resten av uppsättningen, på ett ställe

Utöver obligationer och statsskuldväxlar täcker samma sats de funktioner som omger dem i verkliga modeller. PRICEMAT och YIELDMAT hanterar värdepapper som betalar ränta vid förfall. ACCRINTM beräknar upplupen ränta för dessa. EFFECT och NOMINAL konverterar mellan effektiv och nominell årsränta, vilket är den konvertering som oftast görs fel för hand. CUMIPMT och CUMPRINC ger kumulativ ränta och amortering över ett intervall av perioder. FVSCHEDULE tillämpar en serie olika räntor i följd, och tar antingen ett intervall eller ett skalärt värde

DOLLARDE och DOLLARFR konverterar mellan decimala och bråkdelsbaserade dollarnoteringar, vilket spelar roll för alla instrument som fortfarande noteras i trettioandelar. Och AMORDEGRC och AMORLINC implementerar de franska avskrivningsmetoderna, vilka krävs enligt fransk redovisningslagstiftning snarare än är valfria bekvämligheter

// En räntekonvertering folk rutinmässigt gör fel för hand
Sheet.Cells[1, 3].Formula := '=EFFECT(0.0425,12)';   // nominell till effektiv
Sheet.Cells[2, 3].Formula := '=NOMINAL(0.043338,12)'; // och tillbaka

// Kumulativ ränta under de första två åren av ett 25-årigt lån
Sheet.Cells[4, 3].Formula :=
  '=CUMIPMT(0.0389/12,300,340000,1,24,0)';

// En prognos med rörlig ränta: 3 procent, sedan 3,5, sedan 4
Sheet.Cells[6, 3].Formula := '=FVSCHEDULE(10000,{0.03;0.035;0.04})';

Att validera en implementation du inte själv skrev

Finansfunktioner är det ena området där "den returnerar ett tal" inte är belägg för någonting. Talet är rimligt till sin konstruktion, och ett fel i en dagräkningskonvention förskjuter resultat med ett belopp för litet för att märkas och för stort för att accepteras

Testet som faktiskt fungerar är ett differentiellt. Bygg ett kalkylblad med representativa instrument — en obligation per dagräkningsbas, en som avvecklas vid en månads slut, en som sträcker sig över en skottårsfebruar, en statsskuldväxel under 182 dagar och en över — beräkna värdena i Excel en gång, lagra dem som förväntade resultat, och jämför mot dem efter varje biblioteksuppgradering. Positioner som spänner över ett kupongdatum och likvider på den 31:a är där implementationer skiljer sig åt, så inkludera båda medvetet

Differentiell valideringsuppsättning för HotXLS obligations- och statspappersfunktioner i Delphi: representativa instrument, inklusive skottårs- och månadsslutsfall, beräknas en gång i Excel och jämförs efter varje biblioteksuppgradering — En differentiell testsvit prissätter representativa instrument en gång i Excel, lagrar värdena som förväntningar och jämför dem efter varje HotXLS-uppgradering, så att avvikelser på punktnivå inte kan gömma sig bakom rimlig utdata

För själva arbetsböckerna är de två följeslagarna till den här funktionen beräkningsmotorn som avgör vad som ska omberäknas, beskriven i inkrementell omberäkning och beroendegrafen, och den statistiska familjen som täcks i statistiska fördelningsfunktioner. Tillsammans är de vad som låter en finansiell modell köra obevakad på en server, vilket var hela anledningen till att implementera dem direkt

Obligationsfinans, statistik, ingenjörsfunktioner och omberäkningsmotorn levereras i ett bibliotek för Delphi och C++Builder; den fullständiga funktionslistan finns på HotXLS Delphi kalkylbladskomponentsidan