Vzorce pro dluhopisy a pokladniční poukázky v Delphi s HotXLS

HotXLS vyhodnocuje finanční funkce Excelu pro dluhopisy a pokladniční poukázky nativně v Delphi a C++Builder: PRICE, YIELD, DURATION, MDURATION, šest primitiv kupónových dat, trojici pro pokladniční poukázky, rodinu diskontních funkcí a francouzské funkce odpisů. Počítají se uvnitř vlastního výpočetního enginu knihovny, takže sešit, který oceňuje portfolio dluhopisů, se přepočítá na serveru bez Excelu a bez doplňku Analysis ToolPak kdekoli v dohledu

Tyto funkce dřív žily v doplňku Analysis ToolPak, což je důvod, proč si stále nesou pověst něčeho neohrabaného. Od Excelu 2007 jsou součástí standardní sady funkcí a v souborovém formátu jsou zapsané holé, bez prefixu _xlfn., který nesou skutečně novější funkce. Sešit, který je používá, je obyčejný sešit; jedinou otázkou je, zda ať už ho přepočítává cokoli, tyto funkce zná

Co musí engine kupónových dat zvládnout správně

Každý výpočet dluhopisu stojí na stejných primitivech: kolik dní od posledního kupónu, kolik dní má aktuální kupónové období, kolik dní zbývá do dalšího kupónu, kdy tyto kupóny připadají a kolik jich zbývá. Jsou to COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPPCD, COUPNCD a COUPNUM, a volá je každá funkce pro cenu i výnos

Netriviální je z toho argument báze. Ve hře je pět konvencí počítání dnů — 30/360 US, actual/actual, actual/360, actual/365 a 30/360 evropská — a ty se neshodnou na otázkách typu, co se stane, když datum vypořádání připadne na 31. den měsíce, nebo jak se chová únorové kupónové datum v přestupném roce. Zvládnout jednu konvenci správně a ostatní jen aproximovat vyprodukuje výsledky, které vypadají věrohodně a jsou špatné o pár bazických bodů, což je u velké pozice reálné peníze

HotXLS implementuje všech pět v samostatném vnořeném enginu kupónových dat sdíleném každou funkcí, která ho potřebuje, takže PRICE a DURATION se nemohou navzájem neshodnout na tom, kde kupónová data leží

Cena a výnos v sešitu

Na volajícím kódu není nic zvláštního: zapište vzorce, přepočítejte, čtěte hodnoty. V tom je smysl — sešit je model a vaše aplikace v Delphi je kalkulačka:

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);   // vypořádání
    Sheet.Cells[2, 1].Value := EncodeDate(2034, 11, 15);  // splatnost
    Sheet.Cells[3, 1].Value := 0.0425;                    // kupónová sazba
    Sheet.Cells[4, 1].Value := 0.0391;                    // požadovaný výnos
    Sheet.Cells[5, 1].Value := 100;                       // umoření
    Sheet.Cells[6, 1].Value := 2;                         // frekvence
    Sheet.Cells[7, 1].Value := 1;                         // báze: 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;

Data se vkládají jako skutečné datové hodnoty, ne jako řetězce. Textové datum funguje v Excelu, protože ho Excel převede, a převod závisí na lokalizaci, což je přesně ten druh závislosti, kterou by přepočet na straně serveru neměl mít. Pravidla datového systému, která pod tím vším leží, popisuje článek pořadová čísla dat a systém 1904

Výnos nemá uzavřený vzorec, a to má důsledky

PRICE je přímý výpočet: diskontujte peněžní toky, sečtěte je. YIELD je jeho inverzí a pro dluhopis s více než jedním zbývajícím kupónem nemá řešení v uzavřeném tvaru, takže se musí řešit numericky. HotXLS používá bisekční solver, který konverguje spolehlivě, ne rychle

Z toho plynou dva praktické důsledky. Výnos je měřitelně dražší než cena, takže přecenění portfolia, které počítá výnosy pro deset tisíc pozic, stojí citelně víc než to, které počítá ceny, a vyplatí se vědět, který ze dvou váš model skutečně potřebuje. A výnos, který nelze ohraničit — protože vstupy popisují dluhopis, který žádný výnos neocení na zadanou hodnotu — vrátí chybu místo špatného čísla, což je správné chování a to, které byste měli otestovat

Pokladniční poukázky jsou vlastní rodina

Poukázky jsou diskontní nástroje bez kupónů, takže používají TBILLPRICE, TBILLYIELD a TBILLEQ, ne funkce pro dluhopisy. TBILLEQ převádí diskontní sazbu na výnos ekvivalentní dluhopisu a má zdokumentovanou zvláštnost, o které stojí za to vědět: pro splatnosti delší než 182 dní je převod kvadratický, ne lineární, protože cenný papír přesahuje jedno pololetní období

Vložení poukázky do PRICE místo toho, s nulovým kupónem a frekvencí, nevyprodukuje stejné číslo. Rodina diskontních nástrojů — DISC, INTRATE, RECEIVED, PRICEDISC a YIELDDISC — existuje přesně proto, že tyto nástroje se kótují a vypořádávají jinak

Zbytek sady na jednom místě

Nad rámec dluhopisů a poukázek stejná dávka pokrývá funkce, které je v reálných modelech obklopují. PRICEMAT a YIELDMAT zpracovávají cenné papíry, které platí úrok při splatnosti. ACCRINTM pro ně počítá naběhlý úrok. EFFECT a NOMINAL převádějí mezi efektivní a nominální roční sazbou, což je převod, který se ručně nejčastěji dělá špatně. CUMIPMT a CUMPRINC dávají kumulativní úrok a jistinu za rozsah období. FVSCHEDULE aplikuje sekvenci různých sazeb v pořadí, přičemž přijímá jak oblast, tak skalár

DOLLARDE a DOLLARFR převádějí mezi desetinnou a zlomkovou dolarovou kotací, což má význam pro každý nástroj stále kótovaný v třicetinách. A AMORDEGRC a AMORLINC implementují francouzské metody odpisů, které vyžadují francouzská účetní pravidla, ne jen volitelné vymoženosti

// Převod sazby, který lidé ručně běžně pletou
Sheet.Cells[1, 3].Formula := '=EFFECT(0.0425,12)';   // nominální na efektivní
Sheet.Cells[2, 3].Formula := '=NOMINAL(0.043338,12)'; // a zpět

// Kumulativní úrok za první dva roky 25letého úvěru
Sheet.Cells[4, 3].Formula :=
  '=CUMIPMT(0.0389/12,300,340000,1,24,0)';

// Projekce s proměnlivou sazbou: 3 procenta, pak 3,5, pak 4
Sheet.Cells[6, 3].Formula := '=FVSCHEDULE(10000,{0.03;0.035;0.04})';

Ověřování implementace, kterou jste nenapsali sami

Finanční funkce jsou jedna oblast, kde "vrací to číslo" není důkazem ničeho. Číslo je věrohodné svou konstrukcí a chyba v konvenci počítání dnů posune výsledky o částku příliš malou na to, aby si jí kdokoli všiml, a příliš velkou na to, aby byla přijatelná

Test, který skutečně funguje, je diferenciální. Sestavte tabulku reprezentativních nástrojů — jeden dluhopis na každou bázi počítání dnů, jeden, který se vypořádává na konci měsíce, jeden, který přesahuje únor přestupného roku, jednu poukázku pod 182 dní a jednu nad — spočítejte hodnoty jednou v Excelu, uložte je jako očekávané výsledky a porovnávejte s nimi po každém upgradu knihovny. Pozice přesahující kupónové datum a vypořádání na 31. dni jsou místa, kde se implementace rozcházejí, takže obojí zahrňte záměrně

Pro samotné sešity jsou dva společníci této funkce výpočetní engine, který rozhoduje, co přepočítat, popsaný v článku inkrementální přepočet a graf závislostí, a statistická rodina popsaná v článku statistické distribuční funkce. Společně umožňují, aby finanční model běžel na serveru bez dozoru, což byl celý důvod pro jejich nativní implementaci

Dluhopisové finance, statistika, inženýrské funkce a přepočtový engine jsou dodávány v jedné knihovně pro Delphi a C++Builder; úplný seznam funkcí najdete na stránce komponenty HotXLS Delphi spreadsheet component