Formuły obligacji i bonów skarbowych w Delphi z HotXLS

HotXLS oblicza natywnie w Delphi i C++Builder finansowe funkcje obligacji i bonów skarbowych Excela: PRICE, YIELD, DURATION, MDURATION, sześć prymitywów dat kuponów, trójkę funkcji bonów skarbowych, rodzinę dyskontową i francuskie funkcje amortyzacji. Obliczają się one wewnątrz własnego silnika formuł biblioteki, więc skoroszyt wyceniający portfel obligacji przelicza się na serwerze bez Excela i bez dodatku Analysis ToolPak gdziekolwiek w zasięgu wzroku

Te funkcje kiedyś należały do Analysis ToolPak, dlatego wciąż mają opinię kłopotliwych. Od Excela 2007 są częścią standardowego zestawu funkcji i są zapisywane w formacie pliku bez ozdobników, bez prefiksu _xlfn., który niosą naprawdę nowsze funkcje. Skoroszyt ich używający jest zwykłym skoroszytem; jedyne pytanie brzmi, czy cokolwiek go przelicza, zna te funkcje

Co silnik dat kuponów musi zrobić poprawnie

Każde obliczenie obligacji opiera się na tych samych prymitywach: ile dni minęło od ostatniego kuponu, ile dni ma bieżący okres kuponowy, ile dni pozostało do następnego kuponu, kiedy przypadają te kupony i ile ich zostało. To COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPPCD, COUPNCD i COUPNUM, a wywołuje je każda funkcja ceny i rentowności

Nietrywialny czyni je argument bazy. W grze jest pięć konwencji liczenia dni — 30/360 US, actual/actual, actual/360, actual/365 i 30/360 European — a różnią się one w kwestiach takich jak to, co dzieje się, gdy data rozliczenia przypada na 31. dzień miesiąca, albo jak zachowuje się lutowa data kuponu w roku przestępnym. Poprawne zaimplementowanie jednej konwencji i przybliżenie pozostałych daje wyniki, które wyglądają wiarygodnie, a są błędne o kilka punktów bazowych, co przy dużej pozycji oznacza realne pieniądze

HotXLS implementuje wszystkie pięć w samodzielnym, zagnieżdżonym silniku dat kuponów, współdzielonym przez każdą funkcję, która go potrzebuje, dzięki czemu PRICE i DURATION nie mogą się między sobą różnić co do tego, gdzie leżą daty kuponów

Wycena i rentowność w skoroszycie

W kodzie wywołującym nie ma niczego szczególnego: zapisz formuły, przelicz, odczytaj wartości. O to właśnie chodzi — skoroszyt jest modelem, a twoja aplikacja Delphi jest kalkulatorem:

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);   // rozliczenie
    Sheet.Cells[2, 1].Value := EncodeDate(2034, 11, 15);  // termin wykupu
    Sheet.Cells[3, 1].Value := 0.0425;                    // stopa kuponu
    Sheet.Cells[4, 1].Value := 0.0391;                    // wymagana rentowność
    Sheet.Cells[5, 1].Value := 100;                       // wartość wykupu
    Sheet.Cells[6, 1].Value := 2;                         // częstotliwość
    Sheet.Cells[7, 1].Value := 1;                         // baza: 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;

Daty wchodzą jako prawdziwe wartości daty, a nie jako łańcuchy tekstowe. Data tekstowa działa w Excelu, ponieważ Excel ją wymusza, a to wymuszanie zależy od locale, co jest dokładnie takim rodzajem zależności, jakiej przeliczanie po stronie serwera nie powinno mieć. Reguły systemu dat leżące u podstaw opisano w numerach seryjnych dat i systemie 1904

Rentowność nie ma postaci zamkniętej, i to ma konsekwencje

PRICE to obliczenie bezpośrednie: zdyskontuj przepływy pieniężne, zsumuj je. YIELD jest jej odwrotnością i nie ma rozwiązania w postaci zamkniętej dla obligacji z więcej niż jednym pozostałym kuponem, więc trzeba je rozwiązać numerycznie. HotXLS używa solvera bisekcji, który zbiega niezawodnie, choć nie szybko

Wynikają z tego dwie praktyczne konsekwencje. Rentowność jest mierzalnie droższa niż cena, więc przewartościowanie portfela obliczające rentowności dla dziesięciu tysięcy pozycji kosztuje zauważalnie więcej niż obliczające ceny, i warto wiedzieć, którego z nich naprawdę potrzebuje twój model. A rentowność, której nie da się objąć nawiasem — ponieważ dane wejściowe opisują obligację, której żadna rentowność nie wyceni na daną wartość — zwraca błąd, a nie błędną liczbę, co jest poprawnym zachowaniem i tym, które warto testować

Bony skarbowe to osobna rodzina

Bony to instrumenty dyskontowe bez kuponów, więc korzystają z TBILLPRICE, TBILLYIELD i TBILLEQ, a nie z funkcji obligacji. TBILLEQ zamienia stopę dyskontową na rentowność ekwiwalentną obligacji i ma udokumentowaną osobliwość wartą poznania: dla terminów wykupu dłuższych niż 182 dni konwersja jest kwadratowa, a nie liniowa, ponieważ papier obejmuje więcej niż jeden półroczny okres

Podanie bonu zamiast tego do PRICE, z zerowym kuponem i częstotliwością, nie daje tej samej liczby. Rodzina instrumentów dyskontowych — DISC, INTRATE, RECEIVED, PRICEDISC i YIELDDISC — istnieje właśnie dlatego, że te instrumenty są kwotowane i rozliczane inaczej

Reszta zestawu, w jednym miejscu

Poza obligacjami i bonami ten sam pakiet obejmuje funkcje, które otaczają je w prawdziwych modelach. PRICEMAT i YIELDMAT obsługują papiery wypłacające odsetki przy wykupie. ACCRINTM oblicza dla nich narosłe odsetki. EFFECT i NOMINAL konwertują między efektywną a nominalną stopą roczną, co jest konwersją najczęściej błędnie liczoną ręcznie. CUMIPMT i CUMPRINC dają skumulowane odsetki i kapitał w zakresie okresów. FVSCHEDULE stosuje po kolei serię różnych stóp, przyjmując zakres albo wartość skalarną

DOLLARDE i DOLLARFR konwertują między dziesiętnymi a ułamkowymi notowaniami dolarowymi, co ma znaczenie dla każdego instrumentu wciąż kwotowanego w trzydziestych drugich. A AMORDEGRC i AMORLINC implementują francuskie metody amortyzacji, wymagane przez francuskie zasady rachunkowości, a nie opcjonalne udogodnienia

// Konwersja stopy, którą ludzie rutynowo liczą źle ręcznie
Sheet.Cells[1, 3].Formula := '=EFFECT(0.0425,12)';   // nominalna na efektywną
Sheet.Cells[2, 3].Formula := '=NOMINAL(0.043338,12)'; // i z powrotem

// Skumulowane odsetki za pierwsze dwa lata 25-letniego kredytu
Sheet.Cells[4, 3].Formula :=
  '=CUMIPMT(0.0389/12,300,340000,1,24,0)';

// Projekcja przy zmiennej stopie: 3 procent, potem 3,5, potem 4
Sheet.Cells[6, 3].Formula := '=FVSCHEDULE(10000,{0.03;0.035;0.04})';

Walidacja implementacji, której nie napisałeś

Funkcje finansowe to jeden obszar, w którym „zwraca liczbę” niczego nie dowodzi. Liczba jest z konstrukcji wiarygodna, a błąd w konwencji liczenia dni przesuwa wyniki o wartość zbyt małą, by ją zauważyć, i zbyt dużą, by ją zaakceptować

Test, który naprawdę działa, jest testem różnicowym. Zbuduj arkusz reprezentatywnych instrumentów — po jednej obligacji na każdą bazę liczenia dni, jedną rozliczaną na koniec miesiąca, jedną obejmującą przestępny luty, jeden bon poniżej 182 dni i jeden powyżej — oblicz wartości raz w Excelu, zapisz je jako oczekiwane wyniki i porównuj z nimi po każdej aktualizacji biblioteki. Pozycje przekraczające datę kuponu i rozliczenia na 31. dzień miesiąca to miejsca, w których implementacje się rozjeżdżają, więc celowo uwzględnij oba przypadki

Jeśli chodzi o same skoroszyty, dwoma towarzyszami tej funkcji są silnik obliczeniowy decydujący, co przeliczyć, opisany w przyrostowym przeliczaniu i grafie zależności, oraz rodzina statystyczna opisana w funkcjach rozkładów statystycznych. Razem to one pozwalają modelowi finansowemu działać bez nadzoru na serwerze, co było całym powodem ich natywnej implementacji

Finanse obligacji, statystyka, funkcje inżynierskie i silnik przeliczania są dostarczane w jednej bibliotece dla Delphi i C++Builder; pełną listę funkcji znajdziesz na stronie komponentu arkusza kalkulacyjnego HotXLS dla Delphi