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.

Archive

Monthly

Go

| |||||||||

Sun | Mon | Tue | Wed | Thu | Fri | Sat | |||
---|---|---|---|---|---|---|---|---|---|

30 | 31 | 1 | 2 | 3 | 4 | 5 | |||

6 | 7 | 8 | 9 | 10 | 11 | 12 | |||

13 | 14 | 15 | 16 | 17 | 18 | 19 | |||

20 | 21 | 22 | 23 | 24 | 25 | 26 | |||

27 | 28 | 29 | 30 | 31 | 1 | 2 | |||

3 | 4 | 5 | 6 | 7 | 8 | 9 |

Go