用 HotXLS 在 Delphi 中实现债券与国库券公式

HotXLS 在 Delphi 与 C++Builder 中原生计算 Excel 的债券与国库券金融函数：PRICE、YIELD、DURATION、MDURATION、六个票息日期基础函数、国库券三件套、贴现类函数，以及法式折旧函数。它们在库自身的公式引擎内部计算，因此一份为债券组合定价的工作簿，可以在一台既没有 Excel、也没有任何 Analysis ToolPak 加载项的服务器上重算

这些函数曾经属于 Analysis ToolPak，这也是它们至今仍带着"不好用"这个名声的原因。自 Excel 2007 起，它们已经成为标准函数集的一部分，并且在文件格式中以裸函数名写出，不带那些真正的新函数才有的 _xlfn. 前缀。使用它们的工作簿是一份普通的工作簿，唯一的问题在于负责重算它的那个引擎是否认识这些函数

票息日期引擎必须做对的事

每一个债券计算都依赖同一组基础量：距上一次付息过了多少天，本次付息周期共有多少天，距下一次付息还有多少天，这些付息日分别落在哪一天，以及还剩多少次付息。这些分别是 COUPDAYBS、COUPDAYS、COUPDAYSNC、COUPPCD、COUPNCD 和 COUPNUM，而每一个定价与收益率函数都会调用它们

让它们变得不简单的是基准参数。这里涉及五种计日惯例——30/360（美国）、实际/实际、实际/360、实际/365 和 30/360（欧洲）——它们在诸如"结算日落在某月 31 日会发生什么"或者"闰年里二月的付息日该如何处理"这类问题上给出不同答案。只把一种惯例做对、而对其余惯例做近似处理，得到的结果看起来合理，实际却偏差了几个基点，而在一笔大额头寸上，这就是真金白银

HotXLS 用一个自包含的嵌套票息日期引擎实现了全部五种惯例，由每一个需要它的函数共享，因此 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);   // 结算日
    Sheet.Cells[2, 1].Value := EncodeDate(2034, 11, 15);  // 到期日
    Sheet.Cells[3, 1].Value := 0.0425;                    // 票息利率
    Sheet.Cells[4, 1].Value := 0.0391;                    // 要求收益率
    Sheet.Cells[5, 1].Value := 100;                       // 赎回价
    Sheet.Cells[6, 1].Value := 2;                         // 付息频率
    Sheet.Cells[7, 1].Value := 1;                         // 基准：实际/实际

    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 中能用，是因为 Excel 会做类型强制转换，而这种转换依赖于区域设置，这正是服务端重算不应该有的一种依赖。底层的日期系统规则见 日期序列号与 1904 系统

收益率没有解析解，这带来了一些后果

PRICE 是一次直接计算：把各期现金流贴现后求和即可。YIELD 是它的逆运算，对于剩余付息次数超过一次的债券没有解析解，因此必须用数值方法求解。HotXLS 使用二分法求解器，它收敛得可靠，但不算快

由此带来两个实际影响。计算收益率明显比计算价格更昂贵，因此一次对一万个头寸计算收益率的组合重估，成本会明显高于只计算价格的重估，值得先弄清楚你的模型实际需要哪一种。另外，一个无法被区间夹逼的收益率——因为输入描述的这只债券在给定价格下根本不存在对应的收益率——会返回一个错误而不是一个错误的数字，这才是正确的行为，也是应该测试到的情形

国库券是自成一体的一类

国库券是没有票息的贴现工具，因此使用 TBILLPRICE、TBILLYIELD 和 TBILLEQ，而不是债券函数。TBILLEQ 把贴现率转换为债券等价收益率，它有一个值得了解的既定怪癖：对于期限超过 182 天的情形，这种转换是二次的而不是线性的，因为该证券跨越了不止一个半年期

如果改用零票息、带频率参数的方式把一张国库券喂给 PRICE，得到的数字并不相同。贴现工具家族——DISC、INTRATE、RECEIVED、PRICEDISC 和 YIELDDISC——正是因为这些工具的报价和结算方式不同才存在

其余函数，一并列出

除了债券和国库券之外，同一批函数还覆盖了真实模型中围绕它们的其他函数。PRICEMAT 和 YIELDMAT 处理到期一次性付息的证券。ACCRINTM 计算这类证券的应计利息。EFFECT 和 NOMINAL 在实际年利率和名义年利率之间转换，这是手工计算时最常出错的一种换算。CUMIPMT 和 CUMPRINC 给出某一期间范围内的累计利息与累计本金。FVSCHEDULE 依次应用一系列不同的利率，参数可以是一个区域，也可以是一个标量

DOLLARDE 和 DOLLARFR 在小数和分数形式的美元报价之间转换，这对任何仍以三十二分之一为单位报价的工具都很重要。而 AMORDEGRC 和 AMORLINC 实现了法式折旧方法，这是法国会计准则的强制要求，而不是可有可无的便利功能

// 一种人们手工计算时经常出错的利率换算
Sheet.Cells[1, 3].Formula := '=EFFECT(0.0425,12)';   // 名义利率转实际利率
Sheet.Cells[2, 3].Formula := '=NOMINAL(0.043338,12)'; // 再转换回去

// 一笔 25 年期贷款前两年的累计利息
Sheet.Cells[4, 3].Formula :=
  '=CUMIPMT(0.0389/12,300,340000,1,24,0)';

// 浮动利率预测：先 3%，再 3.5%，再 4%
Sheet.Cells[6, 3].Formula := '=FVSCHEDULE(10000,{0.03;0.035;0.04})';

校验一份非自己所写的实现

金融函数是"能返回一个数字"完全不能说明任何问题的一个领域。这个数字从构造上看就是合理的，而计日惯例上的一处错误，会让结果偏移一个小到不易察觉、却大到不可接受的幅度

真正有效的测试是差分测试。搭建一张具有代表性的工具清单——每种计日基准配一只债券、一只在月末结算的债券、一只跨越闰年二月的债券、一只 182 天以内和一只 182 天以上的国库券——先在 Excel 里计算一次数值，把它们存为期望结果，此后每次库升级后都拿它们做断言。横跨某个付息日的头寸和 31 日结算的情形，正是各实现容易出现分歧的地方，因此要刻意把两者都纳入

就工作簿本身而言，与这项特性相配的另外两块内容分别是决定重算范围的计算引擎，见 增量重算与依赖图，以及统计函数家族，见 统计分布函数。二者结合起来，正是让一个金融模型能够在服务器上无人值守运行的关键，这也是从一开始就要原生实现它们的全部理由

债券金融函数、统计函数、工程函数和重算引擎，都打包在同一个面向 Delphi 与 C++Builder 的库中；完整功能列表见 HotXLS Delphi 电子表格组件页面