LlosLabソフトウェア開発ブログPDF、Delphi、およびドキュメントソフトウェア開発者向けのエンジニアリングノート。

技術記事

HotXLSによるDelphiでの債券・財務省証券の数式

HotXLS Delphi Component

HotXLSはExcelの債券・財務省証券向けファイナンス関数をDelphiおよびC++Builderでネイティブに評価する：PRICE、YIELD、DURATION、MDURATION、6つのクーポン日付プリミティブ、財務省短期証券の3関数、割引関連の関数群、そしてフランス式減価償却関数である。これらはライブラリ自身の数式エンジン内で計算されるため、債券ポートフォリオの価格を算出するワークブックは、Excelも、Analysis ToolPakアドインも一切ないサーバー上で再計算できる

これらの関数はかつてAnalysis ToolPakにあったため、今も扱いにくいという評判を引きずっている。しかしExcel 2007以降は標準の関数セットの一部となり、本当に新しい関数が持つ_xlfn.接頭辞を伴わず、ファイルフォーマット中に素のまま書かれる。これらを使うワークブックは通常のワークブックにすぎない。唯一の問題は、それを再計算する側がその関数を知っているかどうかである

クーポン日付エンジンが正確に処理すべきこと

すべての債券計算は同じプリミティブに立脚している：直近のクーポンから何日経過したか、現在のクーポン期間が何日か、次のクーポンまで何日か、それらのクーポンがいつ発生するか、そして残りがいくつか。それがCOUPDAYBS、COUPDAYS、COUPDAYSNC、COUPPCD、COUPNCD、COUPNUMであり、価格・利回り関連のすべての関数がこれらを呼び出す

これらを単純ではないものにしているのはbasis引数である。5つの日数計算規則——30/360 US、actual/actual、actual/360、actual/365、30/360 European——が関わっており、これらは決済日が月末31日にあたる場合に何が起きるか、あるいは2月のクーポン日がうるう年でどう振る舞うかといった問題について食い違う。1つの規則だけを正確に実装し他を近似すると、もっともらしく見えて数ベーシスポイント分だけ誤った結果を生み、大きなポジションではそれは実際の金額になる

HotXLSはこの5つすべてを、必要とするすべての関数が共有する自己完結型のネストしたクーポン日付エンジンとして実装しているため、PRICEとDURATIONがクーポン日付の位置について互いに食い違うことはない

Delphi向けHotXLSのクーポン日付エンジン：債券の入力値と選択された日数計算基準が、PRICE・YIELD・DURATION・MDURATIONで共有される6つのCOUPプリミティブへ供給される様子を示す図
5つの日数計算基準すべてが、HotXLSのすべてのファイナンス関数で共有される1つのネストしたクーポン日付エンジンへ供給されるため、PRICEとDURATIONがクーポン日付の位置について食い違うことは決してない

ワークブックでの価格算出と利回り算出

呼び出し側のコードに特別な点は何もない：数式を書き、再計算し、値を読む。それがまさに要点である——ワークブックがモデルであり、Delphiアプリケーションは計算機である

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);   // settlement
    Sheet.Cells[2, 1].Value := EncodeDate(2034, 11, 15);  // maturity
    Sheet.Cells[3, 1].Value := 0.0425;                    // coupon rate
    Sheet.Cells[4, 1].Value := 0.0391;                    // required yield
    Sheet.Cells[5, 1].Value := 100;                       // redemption
    Sheet.Cells[6, 1].Value := 2;                         // frequency
    Sheet.Cells[7, 1].Value := 1;                         // basis: 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;

日付は文字列としてではなく実際の日付値として入力する。テキストの日付はExcelがそれを型強制するために動作するが、その型強制はロケールに依存する。これはまさに、サーバーサイドの再計算が持つべきではない種類の依存である。その根底にある日付システムの規則については日付シリアル値と1904システムで扱われている

YIELDには閉じた形の解がなく、それには帰結がある

PRICEは直接的な計算である：キャッシュフローを割り引き、合計する。YIELDはその逆であり、残存クーポンが2回以上ある債券に対しては閉じた形の解を持たないため、数値的に解く必要がある。HotXLSは二分法ソルバーを使用しており、速さよりも確実な収束を優先している

これには2つの実務上の意味がある。YIELDはPRICEより明らかにコストが高いため、1万ポジションの利回りを計算するポートフォリオ再評価は、価格を計算するものよりも顕著にコストがかかる。自分のモデルが実際にどちらを必要としているかを知っておく価値がある。そして、区間で挟むことができない利回り——所与の値では入力が記述する債券をいかなる利回りでも価格付けできない場合——はエラーを返し、誤った数値を返すことはない。これは正しい挙動であり、テストすべき対象である

DelphiのHotXLSにおいて、PRICEは割引キャッシュフローの直接的な1パスであるのに対し、YIELDは閉じた形の解を持たず、区間が存在しない場合にエラーを返す二分法ループで解かれる様子を示す図
PRICEは割引キャッシュフローに対する1回の直接的なパスであるのに対し、YIELDは閉じた形の解を持たず区間を挟んで二分法で解かれ、入力を価格付けできない場合は誤った数値ではなくエラーを返す

財務省短期証券は独自の系統を持つ

短期証券はクーポンのない割引商品であるため、債券関数ではなくTBILLPRICE、TBILLYIELD、TBILLEQを使う。TBILLEQは割引率を債券換算利回りに変換するが、知っておく価値のある文書化された癖がある：満期が182日を超える場合、証券が半年を超える期間にまたがるため、変換は線形ではなく二次式になる

代わりに短期証券をゼロクーポン・所定の頻度でPRICEに入力しても、同じ数値にはならない。割引商品の系統——DISC、INTRATE、RECEIVED、PRICEDISC、YIELDDISC——が存在するのは、まさにこれらの商品が異なる建値と決済のされ方をするからである

その他一式、まとめて

債券と短期証券を超えて、同じ関数群には、実際のモデルでそれらを取り巻く関数も含まれる。PRICEMATとYIELDMATは満期時に利息を支払う証券を扱う。ACCRINTMはそれらの未収利息を計算する。EFFECTとNOMINALは実効年利と名目年利の間で変換を行い、これは手計算で最もよく間違えられる変換である。CUMIPMTとCUMPRINCは期間の範囲にわたる累積利息と累積元金を返す。FVSCHEDULEは範囲またはスカラーのいずれかを受け取り、一連の異なる利率を順に適用する

DOLLARDEとDOLLARFRは、小数と分数のドル建値の間で変換を行い、今も32分の1単位で建値される商品にとって重要である。そしてAMORDEGRCとAMORLINCはフランス式減価償却方式を実装しており、これは任意の便宜機能ではなくフランスの会計規則が要求するものである

// 手作業でよく間違えるrate conversion
Sheet.Cells[1, 3].Formula := '=EFFECT(0.0425,12)';   // nominalからeffectiveへ
Sheet.Cells[2, 3].Formula := '=NOMINAL(0.043338,12)'; // and back

// 25年loanの最初の2年間のcumulative interest
Sheet.Cells[4, 3].Formula :=
  '=CUMIPMT(0.0389/12,300,340000,1,24,0)';

// A variable-rate projection: 3 percent, then 3.5, then 4
Sheet.Cells[6, 3].Formula := '=FVSCHEDULE(10000,{0.03;0.035;0.04})';

自分で書いていない実装を検証する

ファイナンス関数は「数値が返ってくる」ことが何の証拠にもならない唯一の領域である。その数値は構造上もっともらしく見え、日数計算規則の誤りは、気づかれないほど小さく、かつ受け入れられないほど大きい量だけ結果をずらす

実際に機能するテストは差分テストである。代表的な商品のスプレッドシートを構築する——日数計算基準ごとに1つの債券、月末に決済されるもの1つ、うるう年の2月をまたぐもの1つ、182日未満の短期証券1つと182日を超えるもの1つ——それらの値をExcelで一度計算し、期待結果として保存し、ライブラリのアップグレードのたびにそれと照合する。クーポン日をまたぐポジションと31日決済は実装が食い違いやすい箇所であるため、意図的に両方とも含めること

DelphiのHotXLS債券・財務省証券関数向けの差分検証セット：うるう年および月末のケースを含む代表的な商品をExcelで一度計算し、ライブラリのアップグレードのたびに照合する様子を示す図
差分テストスイートは代表的な商品をExcelで一度価格付けし、その値を期待結果として保存し、HotXLSのアップグレードのたびに照合する。そのためベーシスポイント単位のずれがもっともらしい出力の陰に隠れることはない

ワークブック自体については、この機能に付随する2つの要素がある。何を再計算するかを決める計算エンジンは増分再計算と依存関係グラフで説明されており、統計関数の系統は統計分布関数で扱われている。この2つが揃って初めて、財務モデルがサーバー上で無人稼働できるようになる。それこそが、これらをネイティブに実装した理由のすべてである

債券金融、統計、工学関数、そして再計算エンジンは、DelphiおよびC++Builder向けの1つのライブラリにまとめられている。機能一覧の全体はHotXLS Delphiスプレッドシートコンポーネントページにある