NSCOEF

Updated 2024-02-23 22:05:20.157000

Syntax

SELECT * FROM [westclintech].[wct].[NSCOEF](
   <@YieldCurve_RangeQuery, nvarchar(max),>)

Description

Use the table-valued function NSCOEF to calculate the Nelson Siegel coefficients for a zero coupon curve. Nelson and Siegel suggested calculating the yield curve at a point using this formula:

y_{\tau}=\beta_1+\beta_2\left(\frac{1-e^{-\lambda\tau}}{\lambda\tau}\right)+\beta_3\left(\frac{1-e^{-\lambda\tau}}{\lambda\tau}-e^{-\lambda\tau}\right)

To find the coefficients, the program uses ordinary least squares to calculate the values of B0, B1 and B2 for any value of λ and simply finds the value of λ (to 4 decimal places) which has the smallest r2. There may be more than solution; however the function only returns the first one that it finds.

Arguments

@YieldCurve_RangeQuery

a T-SQL statement, as a string, that specifies the maturities (as measured in years) and their zero coupon rates to be used as in calculating the Nelson Siegel coefficients.

Return Type

table

{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "a2f31c38-0d82-43bb-aecb-ee2d9f4899f1", "colName": "B0", "colDatatype": "float", "colDesc": "The first coefficient"}, {"id": "374c40e1-b67e-47aa-b456-964c34d1146a", "colName": "B1", "colDatatype": "float", "colDesc": "The second coefficient"}, {"id": "96aa9cd4-7e14-4bee-b7e1-4b2f474142d8", "colName": "B2", "colDatatype": "float", "colDesc": "The third coefficient"}, {"id": "4d6662ce-68c7-4bff-84e0-8b9b1c8c1233", "colName": "Tau", "colDatatype": "float", "colDesc": "Tau"}, {"id": "bf6fcb1f-2d3b-4103-8991-ccd211c93510", "colName": "RMSE", "colDatatype": "float", "colDesc": "Residual sum of squares. SQUARE(SUM(y \u2013 yhat))"}]}

Remarks

The function is insensitive to order; it does not matter what order the dates and rates are passed in.

See ee NELSONSIEGELto calculate the interpolated values using the Nelson Siegel coefficients.

See ee NSCOEF2for another way to calculate the Nelson Siegel coefficients.

Examples

SELECT *

FROM wct.NSCOEF('SELECT 1,0.0028 UNION ALL

SELECT 2,0.0056 UNION ALL

SELECT 3,0.0085 UNION ALL

SELECT 5,0.0164 UNION ALL

SELECT 7,0.0235 UNION ALL

SELECT 10,0.0299 UNION ALL

SELECT 20,0.0382 UNION ALL

SELECT 30,0.0406') k;

This produces the following result.

{"columns":[{"field":"B0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"B1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"B2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Tau","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"RMSE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"B0":"0.0466701067626488","B1":"-0.0409398575156674","B2":"-0.0635075128703469","Tau":"1.6545","RMSE":"9.8912932859058E-07"}]}

This example uses the LINEST_Q function to demonstrate the calculation of the coefficients when tau is equal to 1.6545.

DECLARE @tau as float = 1.6545;

SELECT *

FROM wct.LINEST_q(

                     REPLACE(

                                'SELECT zr

,(1 - EXP(-cast(mat as float) / @tau)) / (cast(mat as float) / @tau) as x1

,(1 - EXP(-cast(mat as float) / @tau)) / (cast(mat as float) / @tau) - EXP(-cast(

          mat as float) / @tau) as x2

FROM (

      SELECT 1,0.0028 UNION ALL

      SELECT 2,0.0056 UNION ALL

      SELECT 3,0.0085 UNION ALL

      SELECT 5,0.0164 UNION ALL

      SELECT 7,0.0235 UNION ALL

      SELECT 10,0.0299 UNION ALL

      SELECT 20,0.0382 UNION ALL

      SELECT 30,0.0406

      )n(mat,zr)',

                                '@tau',

                                CAST(@tau as varchar(max))

                            ),

                     1,

                     'True'

                 )

WHERE stat_name in ( 'm', 'ss_resid' );

This produces the following result.

stat_name          idx               stat_val col_name
---------- ----------- ---------------------- --------------------------------------------------------------------------------------------------------------------------------
m                    0     0.0466701067626488 Intercept
m                    1    -0.0409398575156675 x1
m                    2    -0.0635075128703467 x2
ss_resid          NULL   9.89129328590585E-07 NULL