Using XLeratorDB in your own SQL Server functions
Dec
15
Written by:
Charles Flock
12/15/2015 11:26 AM
The XLeratorDB financial library contains more than three dozen functions related to the calculation of price, yield, and accrued interest on bonds, bills, and notes. Recently, one of our customers came to us to help him solve for the yield on a bond which incorporated a tax rate. As with most requirements for financial calculations, he attached a very complicated spreadsheet which contained the underlying math used in the local market to calculate the price from yield for the bond, including the tax rate.
Generally when dealing with bond arithmetic, there is no closed solution for the yield on a bond when there is more than on coupon between the settlement date and the maturity date of the bond. Thus the yield is calculated as an iterative solution to the price function starting from an initial guess for the yield and refining that guess until the solution is within some acceptable tolerance of the supplied price. In other words, it's a non-trivial calculation and it makes a lot of sense to find a way to use existing functions (which have been tested millions of times) rather than writing a new one. And, did I mention, they needed a solution right away!
As far as our customer was concerned, the best solution would use the XLeratorDB YIELD function. So we analyzed the spreadsheet and realized that by adjusting inputs slightly and manipulating the output slightly, it was possible to produce the desired result. Given the following information:
DECLARE @mdate as datetime = '2026-01-31'
DECLARE @settdate as datetime = '2015-12-15'
DECLARE @couprate as float = .07
DECLARE @taxrate as float = 0.20
DECLARE @basis as int = 4
DECLARE @freq as int = 2
DECLARE @ytm as float = 0.075
We were able to determine that the price from yield could be calculated using the following SQL.
SELECT wct.PRICE(@settdate,@mdate,@couprate*(1-@taxrate),@ytm*(1-@taxrate),100,@freq,@basis)
Manipulating the coupon rate and the yield-to-maturity produced the correct price. It was then pretty straightforward to derive the calculation of the yield given the price.
SELECT n.price, wct.YIELD(@settdate,@mdate,@couprate*(1-@taxrate),n.price,100,@freq,@basis)/(1-@taxrate) as yield
FROM (SELECT wct.PRICE(@settdate,@mdate,@couprate*(1-@taxrate),@ytm*(1-@taxrate),100,@freq,@basis))n(price)
This produces the following result, which matched the calculations from the spreadsheet.
Having figured out the math, the only remaining issue was how to turn this into a function.
The XLeratorDB functions are all SQLCLR functions which can, in turn, be incorporated into Transact SQL user-defined functions. The following statement creates a new user-defined function which incorporates the tax calculation.
IF OBJECT_ID(N'dbo.YIELDTAX', N'FN') IS NOT NULL
DROP FUNCTION YIELDTAX;
GO
CREATE FUNCTION dbo.YIELDTAX(
@Settlement datetime,
@Maturity datetime,
@Rate float,
@Price float,
@Redemption float,
@Frequency float,
@Basis int,
@TaxRate float
)
RETURNS float
AS
BEGIN
DECLARE @result float;
SELECT @result = wct.YIELD(@Settlement,@Maturity,@Rate*(1-@TaxRate),@Price,@Redemption,@Frequency,@Basis)/(1-@TaxRate)
RETURN @result;
END;
GO
We can now use this function with our original input.
DECLARE @mdate as datetime = '2026-01-31'
DECLARE @settdate as datetime = '2015-12-15'
DECLARE @couprate as float = .07
DECLARE @taxrate as float = 0.20
DECLARE @basis as int = 4
DECLARE @freq as int = 2
DECLARE @ytm as float = 0.075
SELECT dbo.YIELDTAX(@settdate,@mdate,@couprate,96.9895507178116,100,@freq,@basis,@taxrate) as YIELD
This produces the following result.
Now all the logic associated with the tax calculation is embedded in the YIELDTAX function which can be called in place of the YIELD function.
XLeratorDB contains almost 900 functions which can turn your SQL Server database into an analytical engine. You should download the free 15-day trial today and try it out for yourself. If you have any questions or there are some functions you would like to see added to the library, just send us an e-mail at support@westclintech.com.