Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server Nelson Siegel coefficients for a zero coupon curve


NSCOEF

Updated: 29 Mar 2013


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:

XLeratorDB formula for NSCOEF function for Nelson Siegel coefficients calculations for SQL Server
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.
Syntax
SELECT * FROM [wctFinancial].[wct].[NSCOEF](
   <@YieldCurve_RangeQuery, nvarchar(max),>)
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
RETURNS TABLE (
      [B0] [float] NULL,
      [B1] [float] NULL,
      [B2] [float] NULL,
      [Tau] [float] NULL,
      [RMSE] [float] NULL
)

Column
Column Description
B0
The first coefficient
B1
The second coefficient
B2
The third coefficient
Tau
Tau
RMSE
Residual sum of squares. SQUARE(SUM(y – y))

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 NSCOEF2 for 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.
                B0                  B1                  B2    Tau                   RMSE
------------------ ------------------- ------------------- ------ ----------------------
0.0466701067626488 -0.0409398575156674 -0.0635075128703469 1.6545    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
---------- ----------- ----------------------
m                    0     0.0466701067626488
m                    1    -0.0409398575156674
m                    2    -0.0635075128703469
ss_resid          NULL    9.8912932859058E-07
 
 


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service