Bond Valuation and Duration

fn_CalBondDateFactor.sql

CREATE FUNCTION fn_CalBondDateFactor(@code VARCHAR(40), @settlement smalldatetime, @opt smallint)
RETURNS @payDays TABLE(payIntDays INT, unPayIntDays INT, payIntPeriodDays INT)
BEGIN
  --付息期間起迄日
  DECLARE @startPeriodDate smalldatetime, @endPeriodDate smalldatetime
  SELECT @startPeriodDate = startDay, @endPeriodDate = endDay
  FROM fn_GetBondReturnInterval(@code)
  WHERE @settlement BETWEEN startDay AND endDay

  --判斷是否月底付息
  DECLARE @isEOM BIT
  IF DAY(DATEADD(D, 2, @endPeriodDate)) = 1 SET @isEOM = 1
  ELSE SET @isEOM = 0

  DECLARE @times INT
  SET @times = dbo.fn_CalBondPayIntTimes(@code)

  IF @times = -1 SET @times = 1 --零息視作付息一次

  DECLARE @py int = YEAR(@startPeriodDate), @pm int = MONTH(@startPeriodDate), @pd int = DAY(@startPeriodDate),
          @sy int = YEAR(@settlement), @sm int = MONTH(@settlement), @sd int = DAY(@settlement)

  DECLARE @payIntDays INT, @payIntPeriodDays INT

  --US (NASD) 30/360
  IF @opt = 0
  BEGIN
    IF (@isEOM = 1
       AND DATEADD(D, 1, @startPeriodDate) = dbo.fn_datefromparts(@py, 3, 1)
       AND DATEADD(D, 1, @settlement) = dbo.fn_datefromparts(@sy, 3, 1))
    SET @sd = 30

    IF (@isEOM = 1
       AND DATEADD(D, 1, @startPeriodDate) = dbo.fn_datefromparts(@py, 3, 1))
    SET @pd = 30

    IF (@sd = 31
       AND (@pd = 30 OR @pd = 31))
    SET @sd  = 30

    IF @pd = 31
    SET @pd = 30

    SELECT @payIntDays = 360 * (@sy-@py) + 30 * (@sm-@pm) + @sd-@pd,
           @payIntPeriodDays = 360 / @times
  END

  --Actual/actual
  ELSE IF @opt = 1
  SELECT @payIntDays = DATEDIFF(D, @startPeriodDate, @settlement),
         @payIntPeriodDays = DATEDIFF(D, @startPeriodDate, @endPeriodDate)

  --Actual/360
  IF @opt = 2
  SELECT @payIntDays = DATEDIFF(D, @startPeriodDate, @settlement),
         @payIntPeriodDays = 360 / @times

  --Actual/365
  IF @opt = 3
  SELECT @payIntDays = DATEDIFF(D, @startPeriodDate, @settlement),
         @payIntPeriodDays = 365 / @times

  --European 30/360
  IF @opt = 4
  BEGIN
    IF @pd = 31 SET @pd = 30
    IF @sd = 31 SET @sd = 30
    SELECT @payIntDays = 360*(@sy-@py) + 30 * (@sm-@pm) + @sd-@pd,
           @payIntPeriodDays = 360 / @times
  END

  INSERT @payDays VALUES(@payIntDays, @payIntPeriodDays-@payIntDays, @payIntPeriodDays)
  RETURN
END

fn_CalBondHPMD.sql

CREATE FUNCTION fn_CalBondHPMD(@code VARCHAR(40), @settlement SMALLDATETIME, @yieldValue NUMERIC(8,5), @option INT)
RETURNS @HPMD TABLE(price NUMERIC(20, 5), duration NUMERIC(5,2))
BEGIN

  DECLARE @factor FLOAT
  SELECT @factor = unPayIntDays * 1.0 / payIntPeriodDays
  FROM fn_CalBondDateFactor(@code, @settlement, @option)

  DECLARE @calcTable TABLE(returnday SMALLDATETIME, period FLOAT, cashflow NUMERIC(20, 5, pv NUMERIC(20,5))

  INSERT @calcTable
  SELECT return_date, ROW_NUMBER() OVER(ORDER BY return_date) -1 + @factor, SUM(amount), 0
  FROM cmn_bond_return
  WHERE code = @code AND return_date > @settlement
  GROUP BY return_date;

  DECLARE @amt NUMERIC(20,5)
  SELECT @amt = SUM(amount)
  FROM cmn_bond_return
  WHERE code = @code AND
        return_date > @settlement AND
        tran_type = 4

  DECLARE @preint NUMERIC(20,5)
  SELECT @preint = cashflow * (1-@factor)
  FROM @calcTable
  WHERE period <= 1

  DECLARE @times INT
  SET @times = dbo.fn_CalBondPayIntTimes(@code)

  UPDATE @calcTable
  SET pv = cashflow / POWER(1 + @yieldValue/@times, period)

  DECLARE @sum NUMERIC(20,5)
  SELECT @sum = SUM(pv)
  FROM  @calcTable

  INSERT @HPMD
  SELECT (@sum-@preint)/@amt*100, SUM(pv * period/(@sum * @times * (1+@yieldValue/@times)))
  FROM @calcTable

  RETURN
END

fn_GetBondHPMD.sql

CREATE FUNCTION fn_GetBondHPMD(@day SMALLDATETIME, @option INT)
RETURNS @MDHP TABLE(code varchar(40), price NUMERIC(20, 5), duration numeric(5,2))
BEGIN
    
  DECLARE @bond TABLE(code varchar(40), yieldCurve VARCHAR(20), yieldValue NUMERIC(8,5), yearForMaturity INT, calcDay SMALLDATETIME)
 
  --預設 yield to maturity = coupon rate
  INSERT @bond
  SELECT code, '', rate/100.0, DATEDIFF(DAY, @day, redemptionDate), @day 
  FROM bond;
  
  --更新債券的殖利率曲線
  UPDATE @bond 
  SET yieldCurve = f.yieldCurve
  FROM @bond c, dbo.fn_yieldCurve(@day) f
  WHERE c.code = f.code;
  
  --更新未到期債券且有殖利率曲線的 yield to maturity
  UPDATE @bond 
  SET yieldValue = dbo.fn_CalcYield(yieldCurve, @day, yearForMaturity, 0)/100.0 
  WHERE yieldCurve <> '' AND yearForMaturity > 0;
  
  --如果交割日在起息日之前，將交割日改為起息日
  UPDATE @bond
  SET calcDay = (SELECT startCalcDate FROM fn_ResetBondCalcDate(code, calcDay, calcDay))
              
  DECLARE @code varchar(40), @yield float, @settlement smalldatetime

  DECLARE #c CURSOR FAST_FORWARD FOR (SELECT code, yieldValue, calcDay FROM @bond WHERE yearForMaturity > 0)
  OPEN #c
  FETCH NEXT FROM #c INTO @code, @yield, @settlement
  WHILE @@fetch_status = 0
  BEGIN
    DECLARE @perpetual BIT, @callable BIT, @rate numeric(8,5)
    SELECT @perpetual = perpetual, @callable = callable, @rate = rate FROM bond WHERE code = @code

    --永續債券
    IF @perpetual = 1
    INSERT @MDHP VALUES(@code, 100.0 * @rate / @yield, 1 + (1.0 / @yield))

    --可贖回債券
    /*
    ELSE IF @callable = 1
    BEGIN
    END
    */

    --零息債券
    ELSE IF @rate = 0
    BEGIN
      DECLARE @factor NUMERIC(8,5)
      SELECT @factor = unPayIntDays * 1.0 / payIntPeriodDays
      FROM fn_CalBondDateFactor(@code, @settlement, @option)

      INSERT @MDHP VALUES(@code, 100.0 / POWER(1+@yield, @factor), (SELECT yearForMaturity / 365.0 FROM @bond WHERE code = @code))
    END

    --一般債券
    ELSE
    BEGIN
      INSERT @MDHP SELECT @code, * FROM dbo.fn_CalBondHPMD(@code, @settlement, @yield, @option)
    END

    FETCH NEXT FROM #c INTO @code, @yield, @settlement
  END
  CLOSE #c
  DEALLOCATE #c
  RETURN
END