HotXLS evaluates the bond and treasury finance functions of Excel natively in Delphi and C++Builder: PRICE, YIELD, DURATION, MDURATION, the six coupon-date primitives, the treasury-bill trio, the discount family and the French depreciation functions. They compute inside the library's own formula engine, so a workbook that prices a bond portfolio recalculates on a server with no Excel and no Analysis ToolPak add-in anywhere in sight
These functions used to live in the Analysis ToolPak, which is why they still carry a reputation for being awkward. Since Excel 2007 they have been part of the standard function set and are written bare in the file format, without the _xlfn. prefix that genuinely newer functions carry. A workbook using them is an ordinary workbook; the only question is whether whatever recalculates it knows the functions
What the coupon-date engine has to get right
Every bond calculation rests on the same primitives: how many days since the last coupon, how many days in the current coupon period, how many days until the next coupon, when those coupons fall, and how many remain. Those are COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPPCD, COUPNCD and COUPNUM, and every price and yield function calls them
What makes them non-trivial is the basis argument. Five day-count conventions are in play — 30/360 US, actual/actual, actual/360, actual/365 and 30/360 European — and they disagree on questions like what happens when a settlement date falls on the 31st of a month, or how a February coupon date behaves in a leap year. Getting one convention right and approximating the others produces results that look plausible and are wrong by a few basis points, which on a large position is real money
HotXLS implements all five in a self-contained nested coupon-date engine shared by every function that needs it, so PRICE and DURATION cannot disagree with each other about where the coupon dates are
Pricing and yield in a workbook
Nothing about the calling code is special: write the formulas, recalculate, read the values. That is the point — the workbook is the model, and your Delphi application is the calculator:
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;Dates go in as real date values rather than as strings. A text date works in Excel because Excel coerces it, and coercion depends on locale, which is exactly the kind of dependency a server-side recalculation should not have. The date system rules that sit underneath are covered in date serial numbers and the 1904 system
Yield has no closed form, and that has consequences
PRICE is a direct calculation: discount the cash flows, sum them. YIELD is its inverse and has no closed-form solution for a bond with more than one coupon remaining, so it must be solved numerically. HotXLS uses a bisection solver, which converges reliably rather than quickly
Two practical implications follow. Yield is measurably more expensive than price, so a portfolio revaluation that computes yields for ten thousand positions costs noticeably more than one that computes prices, and it is worth knowing which of the two your model actually needs. And a yield that cannot be bracketed — because the inputs describe a bond that no yield can price at the given value — returns an error rather than a wrong number, which is the correct behavior and the one to test for
Treasury bills are their own family
Bills are discount instruments with no coupons, so they use TBILLPRICE, TBILLYIELD and TBILLEQ rather than the bond functions. TBILLEQ converts a discount rate to a bond-equivalent yield, and it has a documented quirk worth knowing: for maturities of more than 182 days the conversion is quadratic rather than linear, because the security spans more than one half-year period
Feeding a bill into PRICE instead, with a zero coupon and a frequency, does not produce the same number. The discount-instrument family — DISC, INTRATE, RECEIVED, PRICEDISC and YIELDDISC — exists precisely because these instruments are quoted and settled differently
The rest of the set, in one place
Beyond bonds and bills, the same batch covers the functions that surround them in real models. PRICEMAT and YIELDMAT handle securities that pay interest at maturity. ACCRINTM computes accrued interest for those. EFFECT and NOMINAL convert between effective and nominal annual rates, which is the conversion most commonly done wrong by hand. CUMIPMT and CUMPRINC give cumulative interest and principal over a range of periods. FVSCHEDULE applies a series of different rates in sequence, taking either a range or a scalar
DOLLARDE and DOLLARFR convert between decimal and fractional dollar quotations, which matters for any instrument still quoted in thirty-seconds. And AMORDEGRC and AMORLINC implement the French depreciation methods, which are required by French accounting rules rather than optional conveniences
// A rate conversion that people routinely get wrong by hand
Sheet.Cells[1, 3].Formula := '=EFFECT(0.0425,12)'; // nominal to effective
Sheet.Cells[2, 3].Formula := '=NOMINAL(0.043338,12)'; // and back
// Cumulative interest over the first two years of a 25-year loan
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})';Validating an implementation you did not write
Financial functions are the one area where "it returns a number" is not evidence of anything. The number is plausible by construction, and an error in a day-count convention shifts results by an amount too small to notice and too large to accept
The test that actually works is a differential one. Build a spreadsheet of representative instruments — one bond per day-count basis, one that settles on a month end, one that spans a leap-year February, one bill under 182 days and one over — compute the values in Excel once, store them as expected results, and assert against them after every library upgrade. Positions that straddle a coupon date and settlements on the 31st are where implementations diverge, so include both deliberately
For the workbooks themselves, the two companions to this feature are the calculation engine that decides what to recalculate, described in incremental recalculation and the dependency graph, and the statistical family covered in statistical distribution functions. Together they are what lets a financial model run unattended on a server, which was the whole reason for implementing them natively
Bond finance, statistics, engineering functions and the recalculation engine ship in one library for Delphi and C++Builder; the complete feature list is on the HotXLS Delphi spreadsheet component page