XLeratorDLL vs WorksheetFunction: YIELD

In this article we compare the XLeratorDLL/financial YIELD function to Worksheetfunction YIELD where we encounter a few surprises and some things that made me scratch my head.

The calculation of yield is an essential part of the math associated with the pricing of a bond. Given the yield, you can calculate the price of the bond for any settlement date. Conversely, given the price you should be able to calculate the yield for a settlement date.
The yield calculation is much more involved than the price calculation since there is no closed-form solution if the settlement occurs before the last coupon period.
And the YIELD function in Excel works fine, though it has limitations. The cause for head-scratching is that it is not a member of Worksheetfunction.

I have the following statements at the top of my code.

Option Explicit On

Option Strict On

Imports System.Runtime.InteropServices

Imports System.Text

Imports Excel = Microsoft.Office.Interop.Excel

Imports wct = XLeratorDLL_financial.XLeratorDLL_financial

Then I create a very simple form which consists of a single button and a text box where the results are displayed.

But that code won't run because YIELD is not a method in Worksheetfunction. After digging around a little bit, I come to the conclusion that if I use the Evaluate function and construct a properly formatted string, I might be able to get a result.

In order to evaluate the relative performance we need to incorporate the overhead of constructing a new string every time the Excel version is invoked. In the interest of keeping things relatively simple, this code will randomly generate 10,000 values for Settlement, Maturity, Rate, and Price while holding Frequency, Redemption and Basis constant. We then build a new string every time the function is invoked.

The XLDLL function is slightly more than 19 times faster than using the Excel version.

In terms of industry practice the Excel YIELD function also has some problems, most notably that it does not accept negative values for rate (the Bank of Japan, the European Central Bank, and several European agencies now have negative rates). In Excel, you will get NUM#! in the cell when you try this. Let's see what happens.

What's up with the Excel calculation? After a little bit of digging it turns out that that -2146826252 is actually equivalent to NUM#! in the COM world. This means that Try…Catch is not going to work and if we were going to do this in Excel, we would need to incorporate something like this.

In addition to these aforementioned problems, the Excel YIELD does not handle monthly coupons, or coupons every 28 days, has no provision for end of month rules (where a bonds pays it's coupons on the 30th of the month not the last day of the month), and only supports a few day count conventions (as opposed to the 2 dozen in XLeratorDLL and Bloomberg).
There is also no built-in capability to calculate the dirty price of a bond, which is needed to calculate the settlement amount of the bond, and there is no way to calculate ex-dividend bonds all of which can be accommodated in XLDLL.