Obligations- og statsgældsformler i Delphi med HotXLS

HotXLS evaluerer Excels obligations- og statsgældsfinansfunktioner nativt i Delphi og C++Builder: PRICE, YIELD, DURATION, MDURATION, de seks kupondato-primitiver, statsgældsbevis-trioen, discount-familien og de franske afskrivningsfunktioner. De beregnes inde i bibliotekets egen formelmotor, så en projektmappe, der prisfastsætter en obligationsportefølje, genberegner på en server uden Excel og uden Analysis ToolPak-tilføjelsesprogrammet nogen steder i syne

Disse funktioner boede tidligere i Analysis ToolPak, hvilket er grunden til, at de stadig bærer et ry for at være besværlige. Siden Excel 2007 har de været en del af standardfunktionssættet og skrives ubeskyttet i filformatet, uden det _xlfn.-præfiks, som genuint nyere funktioner bærer. En projektmappe, der bruger dem, er en almindelig projektmappe; det eneste spørgsmål er, om hvad end der genberegner den, kender funktionerne

Hvad kupondato-motoren skal have ret i

Enhver obligationsberegning hviler på de samme primitiver: hvor mange dage siden sidste kupon, hvor mange dage i den aktuelle kuponperiode, hvor mange dage til næste kupon, hvornår de kuponer falder, og hvor mange der er tilbage. Det er COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPPCD, COUPNCD og COUPNUM, og hver pris- og yield-funktion kalder dem

Det, der gør dem ikke-trivielle, er basis-argumentet. Fem day-count-konventioner er i spil — 30/360 US, faktisk/faktisk, faktisk/360, faktisk/365 og 30/360 europæisk — og de er uenige om spørgsmål som, hvad der sker, når en afregningsdato falder på den 31. i en måned, eller hvordan en februar-kupondato opfører sig i et skudår. At få én konvention rigtig og tilnærme de andre giver resultater, der ser plausible ud og er forkerte med nogle få basispoint, hvilket på en stor position er rigtige penge

HotXLS implementerer alle fem i en selvstændig, indlejret kupondato-motor, som deles af hver funktion, der har brug for den, så PRICE og DURATION ikke kan være uenige om, hvor kupondatoerne ligger

Prisfastsættelse og yield i en projektmappe

Der er intet specielt ved den kaldende kode: skriv formlerne, genberegn, læs værdierne. Det er hele pointen — projektmappen er modellen, og din Delphi-applikation er lommeregneren:

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);   // afregning
    Sheet.Cells[2, 1].Value := EncodeDate(2034, 11, 15);  // udløb
    Sheet.Cells[3, 1].Value := 0.0425;                    // kuponrente
    Sheet.Cells[4, 1].Value := 0.0391;                    // krævet yield
    Sheet.Cells[5, 1].Value := 100;                       // indfrielseskurs
    Sheet.Cells[6, 1].Value := 2;                         // frekvens
    Sheet.Cells[7, 1].Value := 1;                         // basis: faktisk/faktisk

    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;

Datoer indsættes som rigtige datoværdier frem for som strenge. En tekstdato virker i Excel, fordi Excel konverterer den, og konverteringen afhænger af locale, hvilket er præcis den slags afhængighed, en server-side genberegning ikke bør have. De underliggende datosystemregler er dækket i dato-serienumre og 1904-systemet

Yield har ingen lukket løsningsformel, og det har konsekvenser

PRICE er en direkte beregning: diskontér cash flowene, læg dem sammen. YIELD er dens inverse og har ingen lukket løsningsformel for en obligation med mere end én tilbageværende kupon, så den skal løses numerisk. HotXLS bruger en bisektions-solver, som konvergerer pålideligt snarere end hurtigt

To praktiske konsekvenser følger. Yield er målbart dyrere end pris, så en porteføljerevaluering, der beregner yields for ti tusind positioner, koster mærkbart mere end én, der beregner priser, og det er værd at vide, hvilken af de to din model faktisk har brug for. Og en yield, der ikke kan indrammes — fordi inputtene beskriver en obligation, ingen yield kan prisfastsætte til den givne værdi — returnerer en fejl frem for et forkert tal, hvilket er den korrekte adfærd og den, du bør teste for

Statsgældsbeviser er deres egen familie

Statsgældsbeviser er diskontoinstrumenter uden kuponer, så de bruger TBILLPRICE, TBILLYIELD og TBILLEQ frem for obligationsfunktionerne. TBILLEQ konverterer en diskonteringsrente til en obligations-ækvivalent yield, og den har en dokumenteret finesse, det er værd at kende: for løbetider over 182 dage er konverteringen kvadratisk frem for lineær, fordi papiret spænder over mere end én halvårsperiode

At sende et statsgældsbevis ind i PRICE i stedet, med en nulkupon og en frekvens, giver ikke det samme tal. Discount-instrument-familien — DISC, INTRATE, RECEIVED, PRICEDISC og YIELDDISC — findes netop, fordi disse papirer noteres og afregnes anderledes

Resten af sættet, samlet ét sted

Ud over obligationer og statsgældsbeviser dækker den samme batch de funktioner, der omgiver dem i virkelige modeller. PRICEMAT og YIELDMAT håndterer papirer, der betaler rente ved udløb. ACCRINTM beregner påløben rente for disse. EFFECT og NOMINAL konverterer mellem effektiv og nominel årlig rente, hvilket er den konvertering, der oftest regnes forkert i hånden. CUMIPMT og CUMPRINC giver kumuleret rente og afdrag over en periode. FVSCHEDULE anvender en serie forskellige renter i rækkefølge og tager enten et område eller en skalar

DOLLARDE og DOLLARFR konverterer mellem decimale og brøkbaserede dollarnoteringer, hvilket betyder noget for ethvert papir, der stadig noteres i toogtredivedele. Og AMORDEGRC og AMORLINC implementerer de franske afskrivningsmetoder, som er krævet af franske regnskabsregler frem for valgfrie bekvemmeligheder

// En rentekonvertering, folk rutinemæssigt regner forkert i hånden
Sheet.Cells[1, 3].Formula := '=EFFECT(0.0425,12)';   // nominel til effektiv
Sheet.Cells[2, 3].Formula := '=NOMINAL(0.043338,12)'; // og tilbage

// Kumuleret rente over de første to år af et 25-årigt lån
Sheet.Cells[4, 3].Formula :=
  '=CUMIPMT(0.0389/12,300,340000,1,24,0)';

// En variabel rente-projektion: 3 procent, så 3,5, så 4
Sheet.Cells[6, 3].Formula := '=FVSCHEDULE(10000,{0.03;0.035;0.04})';

Validering af en implementering, du ikke selv har skrevet

Finansfunktioner er det ene område, hvor "den returnerer et tal" ikke er bevis for noget som helst. Tallet er plausibelt af konstruktion, og en fejl i en day-count-konvention forskyder resultater med et beløb, der er for lille til at bemærke og for stort til at acceptere

Den test, der reelt virker, er en differentiel test. Byg et regneark med repræsentative papirer — én obligation pr. day-count-basis, én der afregnes ved en månedsafslutning, én der spænder over en skudårs-februar, ét statsgældsbevis under 182 dage og ét over — beregn værdierne i Excel én gang, gem dem som forventede resultater, og assertér mod dem efter hver biblioteksopgradering. Positioner, der overspænder en kupondato, og afregninger på den 31. er, hvor implementeringer divergerer, så inkludér begge bevidst

For selve projektmapperne er de to følgesvende til denne funktion beregningsmotoren, der afgør, hvad der skal genberegnes, beskrevet i inkrementel genberegning og afhængighedsgrafen, og den statistiske familie, dækket i statistiske fordelingsfunktioner. Sammen er de, hvad der lader en finansmodel køre uovervåget på en server, hvilket var hele grunden til at implementere dem nativt

Obligationsfinans, statistik, tekniske funktioner og genberegningsmotoren leveres i ét bibliotek til Delphi og C++Builder; den komplette funktionsliste findes på HotXLS Delphi spreadsheet component-siden