NSCOEF2
Updated 2024-02-23 22:07:08.417000
Syntax
SELECT * FROM [westclintech].[wct].[NSCOEF2](
<@YieldCurve_RangeQuery, nvarchar(max),>
,<@NumSteps, int,>
,<@Tau_min, float,>
,<@Tau_max, float,>
,<@B0_min, float,>
,<@B0_max, float,>
,<@B1_min, float,>
,<@B1_max, float,>
,<@B2_min, float,>
,<@B2_max, float,>)
Description
Use the table-valued function NSCOEF2 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 λ which has the smallest residual sum of squares in the constraints defined by the input parameters.
Arguments
@NumSteps
an integer value that identifies the number of calculations to be done between @Tau_min and @Tau_max. @NumSteps is an expression of type int or of a type that can be implicitly converted to int.
@B2_max
the upper end of the range of permissible values for B2. @B2_max is an expression of type float or of a type that can be implicitly converted to float.
@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.
@B1_max
the upper end of the range of permissible values for B1. @B1_max is an expression of type float or of a type that can be implicitly converted to float.
@Tau_max
the upper end of the range of permissible values for tau. @Tau_max is an expression of type float or of a type that can be implicitly converted to float.
@B0_max
the upper end of the range of permissible values for B0. @B0_max is an expression of type float or of a type that can be implicitly converted to float.
@B1_min
the upper end of the range of permissible values for B0. @B0_max is an expression of type float or of a type that can be implicitly converted to float.
@B2_min
the lower end of the range of permissible values for B2. @B2_min is an expression of type float or of a type that can be implicitly converted to float.
@Tau_min
the lower end of the range of permissible values for tau. @Tau_min is an expression of type float or of a type that can be implicitly converted to float.
@B0_min
the lower end of the range of permissible values for B0. @B0_min is an expression of type float or of a type that can be implicitly converted to float.
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": "e927307c-d304-4f11-bc4a-45886d3e69ac", "colName": "B0", "colDatatype": "float", "colDesc": "The first coefficient"}, {"id": "3484f606-d791-42bf-a623-ab60db3ec90e", "colName": "B1", "colDatatype": "float", "colDesc": "The second coefficient"}, {"id": "0a930b1c-b3cc-49cb-8a39-c93c56bebb6a", "colName": "B2", "colDatatype": "float", "colDesc": "The third coefficient"}, {"id": "da51f381-142f-45c1-93ec-6263b62fa91a", "colName": "Tau", "colDatatype": "float", "colDesc": "Tau"}, {"id": "7751d5ac-7aee-4a1d-82d7-53402642aab7", "colName": "RMSE", "colDatatype": "float", "colDesc": "The 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 NELSONSIEGEL to calculate the interpolated values using the Nelson Siegel coefficients.
See NSCOEF for another way to calculate the Nelson Siegel coefficients.
If @NumSteps is NULL, @NumSteps = 50.
If @Tau_min is NULL, @Tau_min = 0.5
If @Tau_max is NULL, @Tau_max = 9.5
If @B0_min is NULL, @B0_min = -1.
If @B0_max is NULL, @B0_max = 1.
If @B1_min is NULL, @B0_min = -1.
If @B1_max is NULL, @B1_max = 1.
If @B2_min is NULL, @B2_min = -1.
If @B2_max is NULL, @B2_max = 1.
Examples
SELECT *
FROM wct.NSCOEF2(
'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',
900,
0.5,
9.5,
-0.5,
0.15,
-0.15,
0.30,
-0.30,
0.30
);
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.0466551017835633","B1":"-0.0408516979746398","B2":"-0.0637305177771845","Tau":"1.6500","RMSE":"9.89520927636627E-07"}]}
The following example is illustrative of the process used in the NSCOEF2 function. This example uses the LINEST_Q and SERIESFLOAT functions to do the same calculation.
SELECT TOP (1)
tau,
m0,
m1,
m2,
RMSE
FROM
(
SELECT k.seriesvalue as tau,
ISNULL(q.stat_name + cast(q.idx as CHAR(1)), 'RMSE') as lbl,
q.stat_val
FROM wct.SeriesFloat(0.5, 9.5, .01, NULL, NULL) k
CROSS APPLY 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(k.SeriesValue as varchar(max)
)
),
1,
'True'
) q
WHERE q.stat_name in ( 'm', 'ss_resid' )
) M
PIVOT
(
sum(stat_val)
FOR lbl in ([m0], [m1], [m2], [RMSE])
) AS PVT
ORDER BY RMSE ASC;
This produces the following result.
{"columns":[{"field":"tau","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"m0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"m1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"m2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"RMSE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"tau":"1.6500","m0":"0.0466551017835633","m1":"-0.0408516979746398","m2":"-0.0637305177771845","RMSE":"9.89520927636627E-07"}]}