HotXLS wertet die Anleihen- und Geldmarkt-Finanzfunktionen von Excel nativ in Delphi und C++Builder aus: PRICE, YIELD, DURATION, MDURATION, die sechs Kupontermin-Grundfunktionen, das Schatzwechsel-Trio, die Diskont-Familie und die französischen Abschreibungsfunktionen. Sie werden in der bibliothekseigenen Formel-Engine berechnet, sodass eine Arbeitsmappe, die ein Anleihenportfolio bewertet, auf einem Server neu berechnet, ohne dass Excel oder das Analysis-ToolPak-Add-in irgendwo in Sicht sind
Diese Funktionen steckten früher im Analysis-ToolPak, weshalb sie noch immer den Ruf haben, umständlich zu sein. Seit Excel 2007 sind sie Teil des Standard-Funktionssatzes und werden im Dateiformat unverblümt geschrieben, ohne das _xlfn.-Präfix, das genuin neuere Funktionen tragen. Eine Arbeitsmappe, die sie verwendet, ist eine gewöhnliche Arbeitsmappe; die einzige Frage ist, ob das, was sie neu berechnet, die Funktionen kennt
Was die Kupontermin-Engine richtig machen muss
Jede Anleihenberechnung ruht auf denselben Grundfunktionen: wie viele Tage seit dem letzten Kupon, wie viele Tage in der aktuellen Kuponperiode, wie viele Tage bis zum nächsten Kupon, wann diese Kupons fällig sind, und wie viele übrig bleiben. Das sind COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPPCD, COUPNCD und COUPNUM, und jede Preis- und Renditefunktion ruft sie auf
Was sie nichttrivial macht, ist das Basis-Argument. Fünf Zinstagemethoden sind im Spiel — 30/360 US, tatsächlich/tatsächlich, tatsächlich/360, tatsächlich/365 und 30/360 europäisch —, und sie widersprechen sich bei Fragen wie: Was passiert, wenn ein Abwicklungsdatum auf den 31. eines Monats fällt, oder wie sich ein Februar-Kupontermin in einem Schaltjahr verhält. Eine Methode richtig zu implementieren und die anderen zu approximieren erzeugt Ergebnisse, die plausibel aussehen und um ein paar Basispunkte falsch sind, was bei einer großen Position echtes Geld bedeutet
HotXLS implementiert alle fünf in einer in sich geschlossenen, verschachtelten Kupontermin-Engine, die sich jede Funktion teilt, die sie benötigt, sodass sich PRICE und DURATION nicht darüber uneinig sein können, wo die Kupontermine liegen
Preis und Rendite in einer Arbeitsmappe
Am aufrufenden Code ist nichts Besonderes: Formeln schreiben, neu berechnen, Werte lesen. Das ist der Punkt — die Arbeitsmappe ist das Modell, und Ihre Delphi-Anwendung ist der Rechner:
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); // Abwicklung
Sheet.Cells[2, 1].Value := EncodeDate(2034, 11, 15); // Fälligkeit
Sheet.Cells[3, 1].Value := 0.0425; // Kuponzins
Sheet.Cells[4, 1].Value := 0.0391; // geforderte Rendite
Sheet.Cells[5, 1].Value := 100; // Rückzahlung
Sheet.Cells[6, 1].Value := 2; // Häufigkeit
Sheet.Cells[7, 1].Value := 1; // Basis: tatsächlich/tatsächlich
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;Datumsangaben werden als echte Datumswerte übergeben, nicht als Strings. Ein Textdatum funktioniert in Excel, weil Excel es umwandelt, und diese Umwandlung hängt von der Locale ab, genau die Art von Abhängigkeit, die eine serverseitige Neuberechnung nicht haben sollte. Die darunterliegenden Regeln des Datumssystems sind unter Datums-Seriennummern und das 1904-System behandelt
Rendite hat keine geschlossene Form, und das hat Konsequenzen
PRICE ist eine direkte Berechnung: die Zahlungsströme diskontieren, summieren. YIELD ist die Umkehrung davon und hat für eine Anleihe mit mehr als einem verbleibenden Kupon keine geschlossene Lösung, muss also numerisch gelöst werden. HotXLS verwendet einen Bisektionslöser, der zuverlässig statt schnell konvergiert
Zwei praktische Konsequenzen folgen daraus. Rendite ist messbar teurer als Preis, sodass eine Portfolio-Neubewertung, die Renditen für zehntausend Positionen berechnet, spürbar mehr kostet als eine, die Preise berechnet, und es lohnt sich zu wissen, welche der beiden Ihr Modell tatsächlich benötigt. Und eine Rendite, die sich nicht eingrenzen lässt — weil die Eingaben eine Anleihe beschreiben, die keine Rendite zum gegebenen Wert bepreisen kann — liefert einen Fehler statt einer falschen Zahl, was das korrekte Verhalten ist und dasjenige, auf das man testen sollte
Schatzwechsel sind eine eigene Familie
Schatzwechsel sind Diskontinstrumente ohne Kupons, verwenden also TBILLPRICE, TBILLYIELD und TBILLEQ statt der Anleihenfunktionen. TBILLEQ rechnet einen Diskontsatz in eine anleihenäquivalente Rendite um und hat eine dokumentierte Eigenheit, die man kennen sollte: Bei Laufzeiten von mehr als 182 Tagen ist die Umrechnung quadratisch statt linear, da das Wertpapier mehr als eine Halbjahresperiode überspannt
Einen Schatzwechsel stattdessen mit Nullkupon und einer Häufigkeit in PRICE einzuspeisen, ergibt nicht dieselbe Zahl. Die Diskontinstrument-Familie — DISC, INTRATE, RECEIVED, PRICEDISC und YIELDDISC — existiert genau deshalb, weil diese Instrumente anders notiert und abgewickelt werden
Der Rest des Funktionssatzes, an einem Ort
Über Anleihen und Schatzwechsel hinaus deckt derselbe Funktionssatz die Funktionen ab, die sie in echten Modellen umgeben. PRICEMAT und YIELDMAT behandeln Wertpapiere, die bei Fälligkeit Zinsen zahlen. ACCRINTM berechnet die aufgelaufenen Zinsen dafür. EFFECT und NOMINAL rechnen zwischen effektiven und nominalen Jahreszinssätzen um, die Umrechnung, die von Hand am häufigsten falsch gemacht wird. CUMIPMT und CUMPRINC liefern kumulierte Zinsen und Tilgung über einen Zeitraum von Perioden. FVSCHEDULE wendet eine Reihe unterschiedlicher Zinssätze nacheinander an und nimmt entweder einen Bereich oder einen Skalar
DOLLARDE und DOLLARFR rechnen zwischen Dezimal- und Bruchnotierungen in Dollar um, was für jedes Instrument von Bedeutung ist, das noch in Zweiunddreißigstel notiert wird. Und AMORDEGRC und AMORLINC implementieren die französischen Abschreibungsmethoden, die von französischen Rechnungslegungsvorschriften vorgeschrieben sind und keine optionale Annehmlichkeit darstellen
// Eine Zinsumrechnung, die von Hand routinemäßig falsch gemacht wird
Sheet.Cells[1, 3].Formula := '=EFFECT(0.0425,12)'; // nominal zu effektiv
Sheet.Cells[2, 3].Formula := '=NOMINAL(0.043338,12)'; // und zurück
// Kumulierte Zinsen über die ersten zwei Jahre eines 25-jährigen Darlehens
Sheet.Cells[4, 3].Formula :=
'=CUMIPMT(0.0389/12,300,340000,1,24,0)';
// Eine Prognose mit variablem Zinssatz: 3 Prozent, dann 3,5, dann 4
Sheet.Cells[6, 3].Formula := '=FVSCHEDULE(10000,{0.03;0.035;0.04})';Eine Implementierung validieren, die Sie nicht selbst geschrieben haben
Finanzfunktionen sind der eine Bereich, in dem „sie liefert eine Zahl zurück“ kein Beweis für irgendetwas ist. Die Zahl ist konstruktionsbedingt plausibel, und ein Fehler in einer Zinstagemethode verschiebt Ergebnisse um einen Betrag, der zu klein ist, um aufzufallen, und zu groß, um akzeptiert zu werden
Der Test, der tatsächlich funktioniert, ist ein differenzieller. Bauen Sie eine Tabelle repräsentativer Instrumente — eine Anleihe pro Zinstagemethode, eine, die am Monatsende abgewickelt wird, eine, die einen Februar in einem Schaltjahr überspannt, ein Schatzwechsel unter 182 Tagen und einer darüber —, berechnen Sie die Werte einmal in Excel, speichern Sie sie als erwartete Ergebnisse und prüfen Sie nach jedem Bibliotheks-Upgrade dagegen. Positionen, die einen Kupontermin überspannen, und Abwicklungen am 31. sind Stellen, an denen Implementierungen auseinanderlaufen, also nehmen Sie beide bewusst mit auf
Für die Arbeitsmappen selbst sind die beiden Begleiter dieses Features die Berechnungs-Engine, die entscheidet, was neu zu berechnen ist, beschrieben unter inkrementelle Neuberechnung und der Abhängigkeitsgraph, und die statistische Funktionsfamilie, behandelt unter statistische Verteilungsfunktionen. Zusammen ermöglichen sie es, dass ein Finanzmodell unbeaufsichtigt auf einem Server läuft, was der ganze Grund war, sie nativ zu implementieren
Anleihenfinanzierung, Statistik, technische Funktionen und die Neuberechnungs-Engine werden in einer Bibliothek für Delphi und C++Builder ausgeliefert; die vollständige Funktionsliste finden Sie auf der HotXLS-Delphi-Tabellenkalkulationskomponentenseite