PRICEACTTV
Updated 2023-10-05 13:19:05.863000
Syntax
Syntax
SELECT * FROM [westclintech].[wct].[PRICEACTTV](
<@Settlement, datetime,>
,<@Maturity, datetime,>
,<@Rate, float,>
,<@Par, float,>
,<@Yield, float,>
,<@Frequency, float,>
,<@Basis, nvarchar(4000),>
,<@Repayments, nvarchar(max),>)
Description
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.
Arguments
@Basis
is the type of day count to use. @Basis is an expression of the character string data type category.
{"columns":[{"field":"@Basis","width":305},{"field":"Day count basis","width":270}],"rows":[{"@Basis":"1 , 'ACTUAL'","Day count basis":"Actual/Actual"},{"@Basis":"2 , 'A360'","Day count basis":"Actual/360"},{"@Basis":"3 , 'A365'","Day count basis":"Actual/365"},{"@Basis":"11 , 'ACTUAL NON-EOM'","Day count basis":"Actual/Actual non-end-of-month"},{"@Basis":"12 , 'A360 NON-EOM'","Day count basis":"Actual/360 non-end-of-month"},{"@Basis":"13 , 'A365 NON-EOM'","Day count basis":"Actual/365 non-end-of-month"}]}
@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.
@Repayments
a SELECT statement, as a string, which identifies the coupon dates and the forced redemption amounts to be used in the price calculation.
@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.
@Yield
the security’s annual yield. @Yield 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.
@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 settlement date of the security. @Maturity is an expression that returns a datetime or smalldatetime value, or a character string in date format.
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": "c56decdd-b623-47b2-b0e5-a58b03e7d6e8", "colName": "date_pmt", "colDatatype": "datetime", "colDesc": "Date of the cash flow."}, {"id": "31a5505c-8349-4841-86ff-761943854a38", "colName": "amt_prin", "colDatatype": "float", "colDesc": "Principal amount used in the calculation of amt_coupon."}, {"id": "e27afa33-1655-45e4-980c-66fa31f4cb18", "colName": "amt_coupon", "colDatatype": "float", "colDesc": "amt_prin * @Rate * DIP / DIY."}, {"id": "de71693d-2c7e-4ca0-b38d-c98ef3d4af81", "colName": "amt_prinpay", "colDatatype": "float", "colDesc": "Principal payment amount."}, {"id": "0a3d6269-10d5-4903-99ee-5cd31c34952d", "colName": "amt_cashflow", "colDatatype": "float", "colDesc": "amt_coup + amt_prinpay."}, {"id": "cd33558e-8207-4ae6-8807-3724055f47f0", "colName": "DIP", "colDatatype": "float", "colDesc": "Number of days in the coupon period."}, {"id": "55c0850e-db2d-4567-b11b-563371093745", "colName": "DIY", "colDatatype": "float", "colDesc": "Number of days in the year."}, {"id": "5278d426-0cdc-4c64-b4e3-abbdc10c9875", "colName": "t", "colDatatype": "float", "colDesc": "DIP/DIY"}, {"id": "3ad0b929-32ab-4356-a435-dc8651c38515", "colName": "DF", "colDatatype": "float", "colDesc": "Discount factor for the period; POWER(1+@Yield/@Frequency, -t)"}, {"id": "d1b8fb5a-2c1c-4b44-af1a-fc8a35313d79", "colName": "PVF", "colDatatype": "float", "colDesc": "Product of DF for current and all preceding rows."}, {"id": "b1ecebe4-a02f-436d-b15b-2151ea39e819", "colName": "PVCF", "colDatatype": "float", "colDesc": "PVF * amt_cashflow."}, {"id": "272ba2aa-feb0-4f1e-b6d4-b3ca328b0e10", "colName": "cumPVCF", "colDatatype": "float", "colDesc": "Sum of the PVCF."}, {"id": "1bf25b63-7310-4b33-8b7c-1d7fd187335f", "colName": "PVP", "colDatatype": "float", "colDesc": "PVCF * @Par/amt_prin(@Settlement)."}, {"id": "e23654cb-faa5-4776-b831-0406d6883fb7", "colName": "cumPVP", "colDatatype": "float", "colDesc": "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 .
{"columns":[{"field":"date_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_coupon","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prinpay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_cashflow","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DIP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DIY","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"t","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DF","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PVF","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PVCF","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"cumPVCF","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PVP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"cumPVP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"date_pmt":"2014-10-29 00:00:00.000","amt_prin":"100","amt_coupon":"-5.45479452054794","amt_prinpay":"0","amt_cashflow":"-5.45479452054794","DIP":"181","DIY":"365","t":"0","DF":"1","PVF":"1","PVCF":"-5.45479452054794","cumPVCF":"-5.45479452054794","PVP":"-5.45479452054794","cumPVP":"-5.45479452054794"},{"date_pmt":"2014-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.54520547945206","amt_prinpay":"0","amt_cashflow":"5.54520547945206","DIP":"3","DIY":"365","t":"0.0164383561643836","DF":"0.999003927284346","PVF":"0.999003927284346","PVCF":"5.53968205157128","cumPVCF":"0.0848875310233312","PVP":"5.53968205157128","cumPVP":"0.0848875310233312"},{"date_pmt":"2015-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.45479452054795","amt_prinpay":"0","amt_cashflow":"5.45479452054795","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.940707613703775","PVCF":"5.13136673666909","cumPVCF":"5.21625426769242","PVP":"5.13136673666909","cumPVP":"5.21625426769242"},{"date_pmt":"2015-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.54520547945206","amt_prinpay":"0","amt_cashflow":"5.54520547945206","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.884930814480251","PVCF":"4.90712320139186","cumPVCF":"10.1233774690843","PVP":"4.90712320139186","cumPVP":"10.1233774690843"},{"date_pmt":"2016-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.46994535519126","amt_prinpay":"0","amt_cashflow":"5.46994535519126","DIP":"182","DIY":"366","t":"0.994535519125683","DF":"0.941488317144846","PVF":"0.833152023314629","PVCF":"4.55729604009805","cumPVCF":"14.6806735091823","PVP":"4.55729604009805","cumPVP":"14.6806735091823"},{"date_pmt":"2016-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.53005464480874","amt_prinpay":"0","amt_cashflow":"5.53005464480874","DIP":"184","DIY":"366","t":"1.00546448087432","DF":"0.940864727323692","PVF":"0.783883351235101","PVCF":"4.33491776748591","cumPVCF":"19.0155912766682","PVP":"4.33491776748591","cumPVP":"19.0155912766682"},{"date_pmt":"2017-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.45479452054795","amt_prinpay":"0","amt_cashflow":"5.45479452054795","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.738140278153885","PVCF":"4.02640354466955","cumPVCF":"23.0419948213378","PVP":"4.02640354466955","cumPVP":"23.0419948213378"},{"date_pmt":"2017-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.54520547945206","amt_prinpay":"0","amt_cashflow":"5.54520547945206","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.694374179640781","PVCF":"3.85044750573409","cumPVCF":"26.8924423270719","PVP":"3.85044750573409","cumPVP":"26.8924423270719"},{"date_pmt":"2018-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.45479452054795","amt_prinpay":"0","amt_cashflow":"5.45479452054795","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.653854364039427","PVCF":"3.56664120219863","cumPVCF":"30.4590835292705","PVP":"3.56664120219863","cumPVP":"30.4590835292705"},{"date_pmt":"2018-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.54520547945206","amt_prinpay":"0","amt_cashflow":"5.54520547945206","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.615085778505329","PVCF":"3.41077702930078","cumPVCF":"33.8698605585713","PVP":"3.41077702930078","cumPVP":"33.8698605585713"},{"date_pmt":"2019-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.45479452054795","amt_prinpay":"0","amt_cashflow":"5.45479452054795","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.579192793059146","PVCF":"3.15937767391989","cumPVCF":"37.0292382324912","PVP":"3.15937767391989","cumPVP":"37.0292382324912"},{"date_pmt":"2019-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.54520547945206","amt_prinpay":"0","amt_cashflow":"5.54520547945206","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.544851070233094","PVCF":"3.02131114014187","cumPVCF":"40.050549372633","PVP":"3.02131114014187","cumPVP":"40.050549372633"},{"date_pmt":"2020-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.46994535519126","amt_prinpay":"0","amt_cashflow":"5.46994535519126","DIP":"182","DIY":"366","t":"0.994535519125683","DF":"0.941488317144846","PVF":"0.512970917208324","PVCF":"2.80592288593187","cumPVCF":"42.8564722585649","PVP":"2.80592288593187","cumPVP":"42.8564722585649"},{"date_pmt":"2020-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.53005464480874","amt_prinpay":"0","amt_cashflow":"5.53005464480874","DIP":"184","DIY":"366","t":"1.00546448087432","DF":"0.940864727323692","PVF":"0.482636242144194","PVCF":"2.66900479262254","cumPVCF":"45.5254770511874","PVP":"2.66900479262254","cumPVP":"45.5254770511874"},{"date_pmt":"2021-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.45479452054795","amt_prinpay":"0","amt_cashflow":"5.45479452054795","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.454472275067638","PVCF":"2.47905287577991","cumPVCF":"48.0045299269674","PVP":"2.47905287577991","cumPVP":"48.0045299269674"},{"date_pmt":"2021-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.54520547945206","amt_prinpay":"0","amt_cashflow":"5.54520547945206","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.427525529373403","PVCF":"2.37071690808704","cumPVCF":"50.3752468350544","PVP":"2.37071690808704","cumPVP":"50.3752468350544"},{"date_pmt":"2022-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.45479452054795","amt_prinpay":"0","amt_cashflow":"5.45479452054795","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.402577517014932","PVCF":"2.19597763390885","cumPVCF":"52.5712244689632","PVP":"2.19597763390885","cumPVP":"52.5712244689632"},{"date_pmt":"2022-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.54520547945206","amt_prinpay":"0","amt_cashflow":"5.54520547945206","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.378707735361907","PVCF":"2.10001220923973","cumPVCF":"54.671236678203","PVP":"2.10001220923973","cumPVP":"54.671236678203"},{"date_pmt":"2023-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.45479452054795","amt_prinpay":"0","amt_cashflow":"5.45479452054795","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.356608457978625","PVCF":"1.94522586256285","cumPVCF":"56.6164625407658","PVP":"1.94522586256285","cumPVP":"56.6164625407658"},{"date_pmt":"2023-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.54520547945206","amt_prinpay":"0","amt_cashflow":"5.54520547945206","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.335464291531655","PVCF":"1.86021842756183","cumPVCF":"58.4766809683277","PVP":"1.86021842756183","cumPVP":"58.4766809683277"},{"date_pmt":"2024-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.46994535519126","amt_prinpay":"0","amt_cashflow":"5.46994535519126","DIP":"182","DIY":"366","t":"0.994535519125683","DF":"0.941488317144846","PVF":"0.315835711296326","PVCF":"1.72760408200886","cumPVCF":"60.2042850503365","PVP":"1.72760408200886","cumPVP":"60.2042850503365"},{"date_pmt":"2024-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.53005464480874","amt_prinpay":"0","amt_cashflow":"5.53005464480874","DIP":"184","DIY":"366","t":"1.00546448087432","DF":"0.940864727323692","PVF":"0.297158680387902","PVCF":"1.64330374072435","cumPVCF":"61.8475887910609","PVP":"1.64330374072435","cumPVP":"61.8475887910609"},{"date_pmt":"2025-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.45479452054795","amt_prinpay":"0","amt_cashflow":"5.45479452054795","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.279818152345963","PVCF":"1.52635052416661","cumPVCF":"63.3739393152275","PVP":"1.52635052416661","cumPVP":"63.3739393152275"},{"date_pmt":"2025-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.54520547945206","amt_prinpay":"0","amt_cashflow":"5.54520547945206","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.26322706636437","PVCF":"1.45964817074379","cumPVCF":"64.8335874859713","PVP":"1.45964817074379","cumPVP":"64.8335874859713"},{"date_pmt":"2026-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.45479452054795","amt_prinpay":"0","amt_cashflow":"5.45479452054795","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.247866598617877","PVCF":"1.35206136396765","cumPVCF":"66.1856488499389","PVP":"1.35206136396765","cumPVP":"66.1856488499389"},{"date_pmt":"2026-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.54520547945206","amt_prinpay":"0","amt_cashflow":"5.54520547945206","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.233169996502694","PVCF":"1.29297554225056","cumPVCF":"67.4786243921895","PVP":"1.29297554225056","cumPVP":"67.4786243921895"},{"date_pmt":"2027-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.45479452054795","amt_prinpay":"0","amt_cashflow":"5.45479452054795","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.219563492201303","PVCF":"1.19767373417204","cumPVCF":"68.6762981263615","PVP":"1.19767373417204","cumPVP":"68.6762981263615"},{"date_pmt":"2027-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.54520547945206","amt_prinpay":"0","amt_cashflow":"5.54520547945206","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.206545048805155","PVCF":"1.14533473638804","cumPVCF":"69.8216328627496","PVP":"1.14533473638804","cumPVP":"69.8216328627496"},{"date_pmt":"2028-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.46994535519126","amt_prinpay":"0","amt_cashflow":"5.46994535519126","DIP":"182","DIY":"366","t":"0.994535519125683","DF":"0.941488317144846","PVF":"0.194459750414165","PVCF":"1.06368420854961","cumPVCF":"70.8853170712992","PVP":"1.06368420854961","cumPVP":"70.8853170712992"},{"date_pmt":"2028-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.53005464480874","amt_prinpay":"0","amt_cashflow":"5.53005464480874","DIP":"184","DIY":"366","t":"1.00546448087432","DF":"0.940864727323692","PVF":"0.182960320048857","PVCF":"1.01178056770188","cumPVCF":"71.8970976390011","PVP":"1.01178056770188","cumPVP":"71.8970976390011"},{"date_pmt":"2029-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.45479452054795","amt_prinpay":"0","amt_cashflow":"5.45479452054795","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.172283773241515","PVCF":"0.939772582257141","cumPVCF":"72.8368702212582","PVP":"0.939772582257141","cumPVP":"72.8368702212582"},{"date_pmt":"2029-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.54520547945206","amt_prinpay":"0","amt_cashflow":"5.54520547945206","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.162068657205908","PVCF":"0.898704005985637","cumPVCF":"73.7355742272438","PVP":"0.898704005985637","cumPVP":"73.7355742272438"},{"date_pmt":"2030-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.45479452054795","amt_prinpay":"0","amt_cashflow":"5.45479452054795","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.152611231660304","PVCF":"0.832462910234699","cumPVCF":"74.5680371374785","PVP":"0.832462910234699","cumPVP":"74.5680371374785"},{"date_pmt":"2030-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.54520547945206","amt_prinpay":"0","amt_cashflow":"5.54520547945206","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.143562547559558","PVCF":"0.79608382537136","cumPVCF":"75.3641209628499","PVP":"0.79608382537136","cumPVP":"75.3641209628499"},{"date_pmt":"2031-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.45479452054795","amt_prinpay":"0","amt_cashflow":"5.45479452054795","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.13518503565757","PVCF":"0.737406591764993","cumPVCF":"76.1015275546149","PVP":"0.737406591764993","cumPVP":"76.1015275546149"},{"date_pmt":"2031-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.54520547945206","amt_prinpay":"0","amt_cashflow":"5.54520547945206","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.127169592301893","PVCF":"0.705181520052139","cumPVCF":"76.806709074667","PVP":"0.705181520052139","cumPVP":"76.806709074667"},{"date_pmt":"2032-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.46994535519126","amt_prinpay":"0","amt_cashflow":"5.46994535519126","DIP":"182","DIY":"366","t":"0.994535519125683","DF":"0.941488317144846","PVF":"0.119728685448305","PVCF":"0.654909366851111","cumPVCF":"77.4616184415181","PVP":"0.654909366851111","cumPVP":"77.4616184415181"},{"date_pmt":"2032-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.53005464480874","amt_prinpay":"0","amt_cashflow":"5.53005464480874","DIP":"184","DIY":"366","t":"1.00546448087432","DF":"0.940864727323692","PVF":"0.112648496987144","PVCF":"0.622952343994477","cumPVCF":"78.0845707855126","PVP":"0.622952343994477","cumPVP":"78.0845707855126"},{"date_pmt":"2033-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.45479452054795","amt_prinpay":"0","amt_cashflow":"5.45479452054795","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.106074957158733","PVCF":"0.578617095076814","cumPVCF":"78.6631878805894","PVP":"0.578617095076814","cumPVP":"78.6631878805894"},{"date_pmt":"2033-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.54520547945206","amt_prinpay":"0","amt_cashflow":"5.54520547945206","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.0997855198225217","PVCF":"0.553331211289819","cumPVCF":"79.2165190918792","PVP":"0.553331211289819","cumPVP":"79.2165190918792"},{"date_pmt":"2034-05-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.45479452054795","amt_prinpay":"0","amt_cashflow":"5.45479452054795","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.0939625918084276","PVCF":"0.512546630933094","cumPVCF":"79.7290657228123","PVP":"0.512546630933094","cumPVP":"79.7290657228123"},{"date_pmt":"2034-11-01 00:00:00.000","amt_prin":"100","amt_coupon":"5.54520547945206","amt_prinpay":"100","amt_cashflow":"105.545205479452","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.0883913255175278","PVCF":"9.32928061434861","cumPVCF":"89.0583463371609","PVP":"9.32928061434861","cumPVP":"89.0583463371609"}]}
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.
{"columns":[{"field":"date_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_coupon","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prinpay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_cashflow","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DIP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DIY","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"t","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"DF","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PVF","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PVCF","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"cumPVCF","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PVP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"cumPVP","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"date_pmt":"2014-10-29 00:00:00.000","amt_prin":"68.75","amt_coupon":"-4.28510273972603","amt_prinpay":"0","amt_cashflow":"-4.28510273972603","DIP":"182","DIY":"365","t":"0","DF":"1","PVF":"1","PVCF":"-4.28510273972603","cumPVCF":"-4.28510273972603","PVP":"-6.23287671232877","cumPVP":"-6.23287671232877"},{"date_pmt":"2014-10-31 00:00:00.000","amt_prin":"68.75","amt_coupon":"4.33219178082192","amt_prinpay":"6.25","amt_cashflow":"10.5821917808219","DIP":"2","DIY":"365","t":"0.010958904109589","DF":"0.99933584123397","PVF":"0.99933584123397","PVCF":"10.5751635253869","cumPVCF":"6.29006078566085","PVP":"15.3820560369264","cumPVP":"9.14917932459761"},{"date_pmt":"2015-04-30 00:00:00.000","amt_prin":"62.5","amt_coupon":"3.87414383561644","amt_prinpay":"6.25","amt_cashflow":"10.1241438356164","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.941020159001125","PVCF":"9.52702344194204","cumPVCF":"15.8170842276029","PVP":"13.8574886428248","cumPVP":"23.0066679674224"},{"date_pmt":"2015-10-31 00:00:00.000","amt_prin":"56.25","amt_coupon":"3.54452054794521","amt_prinpay":"6.25","amt_cashflow":"9.79452054794521","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.885224828221095","PVCF":"8.67035276956278","cumPVCF":"24.4874369971657","PVP":"12.6114222102731","cumPVP":"35.6180901776955"},{"date_pmt":"2016-04-30 00:00:00.000","amt_prin":"50","amt_coupon":"3.10792349726776","amt_prinpay":"6.25","amt_cashflow":"9.35792349726776","DIP":"182","DIY":"366","t":"0.994535519125683","DF":"0.941488317144846","PVF":"0.833428833816714","PVCF":"7.7991632672739","cumPVCF":"32.2866002644396","PVP":"11.3442374796711","cumPVP":"46.9623276573666"},{"date_pmt":"2016-10-31 00:00:00.000","amt_prin":"43.75","amt_coupon":"2.74931693989071","amt_prinpay":"6.25","amt_cashflow":"8.99931693989071","DIP":"184","DIY":"366","t":"1.00546448087432","DF":"0.940864727323692","PVF":"0.784143792472665","PVCF":"7.0567585149094","cumPVCF":"39.343358779349","PVP":"10.2643760216864","cumPVP":"57.226703679053"},{"date_pmt":"2017-04-30 00:00:00.000","amt_prin":"37.5","amt_coupon":"2.32448630136986","amt_prinpay":"6.25","amt_cashflow":"8.57448630136986","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.738385521489179","PVCF":"6.3312765391388","cumPVCF":"45.6746353184878","PVP":"9.20912951147463","cumPVP":"66.4358331905277"},{"date_pmt":"2017-10-31 00:00:00.000","amt_prin":"31.25","amt_coupon":"1.96917808219178","amt_prinpay":"6.25","amt_cashflow":"8.21917808219178","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.694604881913503","PVCF":"5.70908122120687","cumPVCF":"51.3837165396946","PVP":"8.30411813993727","cumPVP":"74.7399513304649"},{"date_pmt":"2018-04-30 00:00:00.000","amt_prin":"25","amt_coupon":"1.54965753424658","amt_prinpay":"6.25","amt_cashflow":"7.79965753424658","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.654071603810483","PVCF":"5.10153451259718","cumPVCF":"56.4852510522918","PVP":"7.42041383650499","cumPVP":"82.1603651669699"},{"date_pmt":"2018-10-31 00:00:00.000","amt_prin":"18.75","amt_coupon":"1.18150684931507","amt_prinpay":"6.25","amt_cashflow":"7.43150684931507","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.615290137611961","PVCF":"4.5725328719793","cumPVCF":"61.0577839242711","PVP":"6.65095690469716","cumPVP":"88.8113220716671"},{"date_pmt":"2019-04-30 00:00:00.000","amt_prin":"12.5","amt_coupon":"0.774828767123288","amt_prinpay":"6.25","amt_cashflow":"7.02482876712329","DIP":"181","DIY":"365","t":"0.991780821917808","DF":"0.941645561155059","PVF":"0.579385226904788","PVCF":"4.07008200920701","cumPVCF":"65.1278659334781","PVP":"5.92011928611929","cumPVP":"94.7314413577864"},{"date_pmt":"2019-10-31 00:00:00.000","amt_prin":"6.25","amt_coupon":"0.393835616438356","amt_prinpay":"6.25","amt_cashflow":"6.64383561643836","DIP":"184","DIY":"365","t":"1.00821917808219","DF":"0.940707613703775","PVF":"0.545032094216823","PVCF":"3.62110363965972","cumPVCF":"68.7489695731379","PVP":"5.26705983950504","cumPVP":"99.9985011972914"}]}
See Also
BONDCF - Cash flows for a bond paying regular periodic interest