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