Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server disambuguation function for Actual/Actual ISDA bonds


PRICEACTTV

Updated: 31 October 2014


Use the table-valued function PRICEACTTV to return the cash flows of a bond where coupon payments are calculated using the actual number of days in the coupon period and vary from period to period. PRICEACTTV also supports bonds with forced redemptions.
The first row in the resultant table is dated with settlement date passed into the function and is for the amount of the accrued interest. All the remaining rows are dated for the subsequent coupon dates and the amounts are the coupon amount plus any forced redemptions occurring on the coupon date. The row for the maturity date includes the coupon amount and the final redemption amount.
The resultant table also includes the discount factor for each period as will as the discounted cash flow value for each period. The sum of the discounted cash flow values across all the periods is equal to the clean price of the bond.
Syntax
SELECT * FROM [wct].[PRICEACTTV](
  <@Settlement, datetime,>
 ,<@Maturity, datetime,>
 ,<@Rate, float,>
 ,<@Par, float,>
 ,<@Yield, float,>
 ,<@Frequency, float,>
 ,<@Basis, nvarchar(4000),>
 ,<@Repayments, nvarchar(max),>)
Arguments
@Settlement
the settlement date of the security. @Settlement is an expression that returns a datetime or smalldatetime value, or a character string in date format. 
@Maturity
the maturity date of the security. @Maturity is an expression that returns a datetime or smalldatetime value, or a character string in date format. 
@Rate
the security’s annual coupon rate. @Rate is an expression of type float or of a type that can be implicitly converted to float.
@Par
the par value of the security. Any forced redemptions are subtracted from the par value on the redemption date and the adjusted balance is used in calculating the subsequent coupon interest. @Par is an expression of type float or of a type that can be implicitly converted to float.
@Yield
the security’s annual yield. @Yield is an expression of type float or of a type that can be implicitly converted to float.
@Frequency
the number of coupon payments per year. For annual payments, @Frequency = 1; for semi-annual, @Frequency = 2; for quarterly, @Frequency = 4; for bi-monthly, @Frequency = 6, for monthly, @Frequency = 12. @Frequency is an expression of type float or of a type that can be implicitly converted to float.
@Basis
is the type of day count to use. @Basis is an expression of the character string data type category.

@Basis
Day count basis
1, 'ACTUAL'
Actual/Actual
2, 'A360'
Actual/360
3, 'A365'
Actual/365
11, 'ACTUAL NON-EOM'
Actual/Actual non-end-of-month
12, 'A360 NON-EOM'
Actual/360 non-end-of-month
13, 'A365 NON-EOM'
Actual/365 non-end-of-month
 
 
 

@Repayments
a SELECT statement, as a string, which identifies the coupon dates and the forced redemption amounts to be used in the price calculation.
Return Type
RETURNS TABLE (
       [date_pmt] [datetime] NULL,
       [amt_prin] [float] NULL,
       [amt_coupon] [float] NULL,
       [amt_prinpay] [float] NULL,
       [amt_cashflow] [float] NULL,
       [DIP] [float] NULL,
       [DIY] [float] NULL,
       [t] [float] NULL,
       [DF] [float] NULL,
       [PVF] [float] NULL,
       [PVCF] [float] NULL,
       [cumPVCF] [float] NULL,
       [PVP] [float] NULL,
       [cumPVP] [float] NULL
)

Column
Description
date_pmt
Date of the cash flow.
amt_prin
Principal amount used in the calculation of amt_coupon.
amt_coupon
amt_prin * @Rate * DIP / DIY.
amt_prinpay
Principal payment amount.
amt_cashflow
amt_coup + amt_prinpay.
DIP
Number of days in the coupon period.
DIY
Number of days in the year.
t
DIP/DIY
DF
Discount factor for the period; POWER(1+@Yield/@Frequency, -t)
PVF
Product of DF for current and all preceding rows.
PVCF
PVF * amt_cashflow.
cumPVCF
Sum of the PVCF.
PVP
PVCF * @Par/amt_prin(@Settlement).
cumPVP
Sum of the PVP.

Remarks
·         If @Settlement is NULL then @Settlement = GETDATE().
·         If @Rate is NULL then @Rate = 0.
·         If @Redemption is NULL then @Par = 100.
·         If @Yield is NULL then @Yield = 0.
·         If @Frequency is NULL then @Frequency = 2.
·         If @Basis is NULL then @Basis = 1.
·         If @Maturity <= @Settlement then no rows are returned.
·         If @Frequency invalid PRICEACTTV returns an error.
·         If @Basis invalid (see above list), PRICEACTTV returns an error.
·         If @Maturity is NULL then no rows are returned.
Examples
In this example we generate the cash flow for a bond with a maturity on 2034-11-01 and with a coupon interest rate of 11.0% paying interest semi-annually. The bond is priced at a yield of 12.5% and is settling on 2014-10-29. The bond uses the actual/actual day-count convention.
SELECT
   *
FROM
   wct.PRICEACTTV(
        '2014-10-29' --@Settlement
       ,'2034-11-01' --@Maturity
       ,0.1100       --@Rate
       ,100          --@Par
       ,0.1250       --@Yield
       ,2            --@Frequency
       ,1            --@Basis
       ,NULL         --@Repayments
       )
This produces the following result (which has been reformatted for ease of viewing).

date_pmt
amt_prin
amt_coupon
amt_prinpay
amt_cashflow
DIP
DIY
t
2014-10-29
100
-5.454794521
0
-5.454794521
181
365
0
2014-11-01
100
5.545205479
0
5.545205479
3
365
0.016438356
2015-05-01
100
5.454794521
0
5.454794521
181
365
0.991780822
2015-11-01
100
5.545205479
0
5.545205479
184
365
1.008219178
2016-05-01
100
5.469945355
0
5.469945355
182
366
0.994535519
2016-11-01
100
5.530054645
0
5.530054645
184
366
1.005464481
2017-05-01
100
5.454794521
0
5.454794521
181
365
0.991780822
2017-11-01
100
5.545205479
0
5.545205479
184
365
1.008219178
2018-05-01
100
5.454794521
0
5.454794521
181
365
0.991780822
2018-11-01
100
5.545205479
0
5.545205479
184
365
1.008219178
2019-05-01
100
5.454794521
0
5.454794521
181
365
0.991780822
2019-11-01
100
5.545205479
0
5.545205479
184
365
1.008219178
2020-05-01
100
5.469945355
0
5.469945355
182
366
0.994535519
2020-11-01
100
5.530054645
0
5.530054645
184
366
1.005464481
2021-05-01
100
5.454794521
0
5.454794521
181
365
0.991780822
2021-11-01
100
5.545205479
0
5.545205479
184
365
1.008219178
2022-05-01
100
5.454794521
0
5.454794521
181
365
0.991780822
2022-11-01
100
5.545205479
0
5.545205479
184
365
1.008219178
2023-05-01
100
5.454794521
0
5.454794521
181
365
0.991780822
2023-11-01
100
5.545205479
0
5.545205479
184
365
1.008219178
2024-05-01
100
5.469945355
0
5.469945355
182
366
0.994535519
2024-11-01
100
5.530054645
0
5.530054645
184
366
1.005464481
2025-05-01
100
5.454794521
0
5.454794521
181
365
0.991780822
2025-11-01
100
5.545205479
0
5.545205479
184
365
1.008219178
2026-05-01
100
5.454794521
0
5.454794521
181
365
0.991780822
2026-11-01
100
5.545205479
0
5.545205479
184
365
1.008219178
2027-05-01
100
5.454794521
0
5.454794521
181
365
0.991780822
2027-11-01
100
5.545205479
0
5.545205479
184
365
1.008219178
2028-05-01
100
5.469945355
0
5.469945355
182
366
0.994535519
2028-11-01
100
5.530054645
0
5.530054645
184
366
1.005464481
2029-05-01
100
5.454794521
0
5.454794521
181
365
0.991780822
2029-11-01
100
5.545205479
0
5.545205479
184
365
1.008219178
2030-05-01
100
5.454794521
0
5.454794521
181
365
0.991780822
2030-11-01
100
5.545205479
0
5.545205479
184
365
1.008219178
2031-05-01
100
5.454794521
0
5.454794521
181
365
0.991780822
2031-11-01
100
5.545205479
0
5.545205479
184
365
1.008219178
2032-05-01
100
5.469945355
0
5.469945355
182
366
0.994535519
2032-11-01
100
5.530054645
0
5.530054645
184
366
1.005464481
2033-05-01
100
5.454794521
0
5.454794521
181
365
0.991780822
2033-11-01
100
5.545205479
0
5.545205479
184
365
1.008219178
2034-05-01
100
5.454794521
0
5.454794521
181
365
0.991780822
2034-11-01
100
5.545205479
100
105.5452055
184
365
1.008219178

 

date_pmt
DF
PVF
PVCF
cumPVCF
PVP
cumPVP
2014-10-29
1
1
-5.454794521
-5.454794521
-5.454794521
-5.454794521
2014-11-01
0.999003927
0.999003927
5.539682052
0.084887531
5.539682052
0.084887531
2015-05-01
0.941645561
0.940707614
5.131366737
5.216254268
5.131366737
5.216254268
2015-11-01
0.940707614
0.884930814
4.907123201
10.12337747
4.907123201
10.12337747
2016-05-01
0.941488317
0.833152023
4.55729604
14.68067351
4.55729604
14.68067351
2016-11-01
0.940864727
0.783883351
4.334917767
19.01559128
4.334917767
19.01559128
2017-05-01
0.941645561
0.738140278
4.026403545
23.04199482
4.026403545
23.04199482
2017-11-01
0.940707614
0.69437418
3.850447506
26.89244233
3.850447506
26.89244233
2018-05-01
0.941645561
0.653854364
3.566641202
30.45908353
3.566641202
30.45908353
2018-11-01
0.940707614
0.615085779
3.410777029
33.86986056
3.410777029
33.86986056
2019-05-01
0.941645561
0.579192793
3.159377674
37.02923823
3.159377674
37.02923823
2019-11-01
0.940707614
0.54485107
3.02131114
40.05054937
3.02131114
40.05054937
2020-05-01
0.941488317
0.512970917
2.805922886
42.85647226
2.805922886
42.85647226
2020-11-01
0.940864727
0.482636242
2.669004793
45.52547705
2.669004793
45.52547705
2021-05-01
0.941645561
0.454472275
2.479052876
48.00452993
2.479052876
48.00452993
2021-11-01
0.940707614
0.427525529
2.370716908
50.37524684
2.370716908
50.37524684
2022-05-01
0.941645561
0.402577517
2.195977634
52.57122447
2.195977634
52.57122447
2022-11-01
0.940707614
0.378707735
2.100012209
54.67123668
2.100012209
54.67123668
2023-05-01
0.941645561
0.356608458
1.945225863
56.61646254
1.945225863
56.61646254
2023-11-01
0.940707614
0.335464292
1.860218428
58.47668097
1.860218428
58.47668097
2024-05-01
0.941488317
0.315835711
1.727604082
60.20428505
1.727604082
60.20428505
2024-11-01
0.940864727
0.29715868
1.643303741
61.84758879
1.643303741
61.84758879
2025-05-01
0.941645561
0.279818152
1.526350524
63.37393932
1.526350524
63.37393932
2025-11-01
0.940707614
0.263227066
1.459648171
64.83358749
1.459648171
64.83358749
2026-05-01
0.941645561
0.247866599
1.352061364
66.18564885
1.352061364
66.18564885
2026-11-01
0.940707614
0.233169997
1.292975542
67.47862439
1.292975542
67.47862439
2027-05-01
0.941645561
0.219563492
1.197673734
68.67629813
1.197673734
68.67629813
2027-11-01
0.940707614
0.206545049
1.145334736
69.82163286
1.145334736
69.82163286
2028-05-01
0.941488317
0.19445975
1.063684209
70.88531707
1.063684209
70.88531707
2028-11-01
0.940864727
0.18296032
1.011780568
71.89709764
1.011780568
71.89709764
2029-05-01
0.941645561
0.172283773
0.939772582
72.83687022
0.939772582
72.83687022
2029-11-01
0.940707614
0.162068657
0.898704006
73.73557423
0.898704006
73.73557423
2030-05-01
0.941645561
0.152611232
0.83246291
74.56803714
0.83246291
74.56803714
2030-11-01
0.940707614
0.143562548
0.796083825
75.36412096
0.796083825
75.36412096
2031-05-01
0.941645561
0.135185036
0.737406592
76.10152755
0.737406592
76.10152755
2031-11-01
0.940707614
0.127169592
0.70518152
76.80670907
0.70518152
76.80670907
2032-05-01
0.941488317
0.119728685
0.654909367
77.46161844
0.654909367
77.46161844
2032-11-01
0.940864727
0.112648497
0.622952344
78.08457079
0.622952344
78.08457079
2033-05-01
0.941645561
0.106074957
0.578617095
78.66318788
0.578617095
78.66318788
2033-11-01
0.940707614
0.09978552
0.553331211
79.21651909
0.553331211
79.21651909
2034-05-01
0.941645561
0.093962592
0.512546631
79.72906572
0.512546631
79.72906572
2034-11-01
0.940707614
0.088391326
9.329280614
89.05834634
9.329280614
89.05834634

In this example we have a bond maturing on 2019-10-31 with a 12.5% coupon paid semi-annually. The bond has 16 equal forced redemptions starting with the 2012-04-30 coupon. The bond is priced at a yield of 12.5% settling on 2014-10-29.
SELECT
   *
FROM
   wct.PRICEACTTV(
        '2014-10-29' --@Settlement
       ,'2019-10-31' --@Maturity
       ,0.125        --@Rate
       ,100          --@Par
       ,0.125        --@Yield
       ,2            --@Frequency
       ,1            --@Basis
       ,'SELECT
          *
       FROM (VALUES
           (''2012-04-30'',6.25)
          ,(''2012-10-31'',6.25)
          ,(''2013-04-30'',6.25)
          ,(''2013-10-31'',6.25)
          ,(''2014-04-30'',6.25)
          ,(''2014-10-31'',6.25)
          ,(''2015-04-30'',6.25)
          ,(''2015-10-31'',6.25)
          ,(''2016-04-30'',6.25)
          ,(''2016-10-31'',6.25)
          ,(''2017-04-30'',6.25)
          ,(''2017-10-31'',6.25)
          ,(''2018-04-30'',6.25)
          ,(''2018-10-31'',6.25)
          ,(''2019-04-30'',6.25)
          ,(''2019-10-31'',6.25)
          )n(dt_ppay, amt_ppay)' --@Repayments
   )
This produces the following result

date_pmt
amt_prin
amt_coupon
amt_prinpay
amt_cashflow
DIP
DIY
t
2014-10-29
68.75
-4.28510274
0
-4.28510274
182
365
0
2014-10-31
68.75
4.332191781
6.25
10.58219178
2
365
0.010958904
2015-04-30
62.5
3.874143836
6.25
10.12414384
181
365
0.991780822
2015-10-31
56.25
3.544520548
6.25
9.794520548
184
365
1.008219178
2016-04-30
50
3.107923497
6.25
9.357923497
182
366
0.994535519
2016-10-31
43.75
2.74931694
6.25
8.99931694
184
366
1.005464481
2017-04-30
37.5
2.324486301
6.25
8.574486301
181
365
0.991780822
2017-10-31
31.25
1.969178082
6.25
8.219178082
184
365
1.008219178
2018-04-30
25
1.549657534
6.25
7.799657534
181
365
0.991780822
2018-10-31
18.75
1.181506849
6.25
7.431506849
184
365
1.008219178
2019-04-30
12.5
0.774828767
6.25
7.024828767
181
365
0.991780822
2019-10-31
6.25
0.393835616
6.25
6.643835616
184
365
1.008219178

 

date_pmt
DF
PVF
PVCF
cumPVCF
PVP
cumPVP
2014-10-29
1
1
-4.28510274
-4.28510274
-6.232876712
-6.232876712
2014-10-31
0.999335841
0.999335841
10.57516353
6.290060786
15.38205604
9.149179325
2015-04-30
0.941645561
0.941020159
9.527023442
15.81708423
13.85748864
23.00666797
2015-10-31
0.940707614
0.885224828
8.67035277
24.487437
12.61142221
35.61809018
2016-04-30
0.941488317
0.833428834
7.799163267
32.28660026
11.34423748
46.96232766
2016-10-31
0.940864727
0.784143792
7.056758515
39.34335878
10.26437602
57.22670368
2017-04-30
0.941645561
0.738385521
6.331276539
45.67463532
9.209129511
66.43583319
2017-10-31
0.940707614
0.694604882
5.709081221
51.38371654
8.30411814
74.73995133
2018-04-30
0.941645561
0.654071604
5.101534513
56.48525105
7.420413837
82.16036517
2018-10-31
0.940707614
0.615290138
4.572532872
61.05778392
6.650956905
88.81132207
2019-04-30
0.941645561
0.579385227
4.070082009
65.12786593
5.920119286
94.73144136
2019-10-31
0.940707614
0.545032094
3.62110364
68.74896957
5.26705984
99.9985012

 

 

See Also

 



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service