ConstantCashFlowFR
Updated 2024-02-22 16:59:48.163000
Syntax
SELECT * FROM [westclintech].[wct].[ConstantCashFlowFR](
<@OutstandingAmount, float,>
,<@LastPrinPayAmount, float,>
,<@PaymentFrequency, int,>
,<@MaturityDate, datetime,>
,<@ReferenceDate, datetime,>
,<@PrevPayDate, datetime,>
,<@StartDate, datetime,>
,<@FirstPayDate, datetime,>
,<@GracePeriodStartDate, datetime,>
,<@GracePeriodEndDate, datetime,>
,<@FutureRates, nvarchar(max),>)
Description
Use the table-valued function ConstantCashFlowFR to return the cash flow schedule for a loan with a fixed maturity date and annuity-style payments using a table of forward rates to calculate each periodic payment. ConstantCashFlowFR computes the periodic interest and principal amount through to the maturity date.
The payment frequency is entered into ConstantCashFlowFR as the number of months between payments. For example, a loan with monthly payments would have a frequency of 1. A loan with quarterly payments would have a frequency of 3. A loan with annual payments would have a frequency of 12.
ConstantCashFlowFR supports both an initial grace period and an additional grace period during the life of the loan. All payments and payment dates are calculated with respect to the reference date supplied to the function (which should not be confused with the start date). If an initial grace period is entered in ConstantCashFlowFR and it is greater than the reference date then it becomes the first payment date and subsequent payments are calculated from that date forward.
If any payment would otherwise occur in the specified grace period, then that payment is moved to the end of the grace period and all remaining payments are calculated from the end of the grace period.
If no initial grace period is specified then that first payment date is calculated using the payment frequency. If the start date has been entered and the number of months between the start date and the reference date is less than the frequency, then the first payment date is calculated by adding the frequency (as a number of months) to the start date.
If no start date has been entered but a previous payment date has been entered and the number of months between the previous payment date and the reference date is less than the frequency then the first payment date is calculated by adding the frequency (as a number of months) to the previous payment date.
If there is no start date and no previous payment date or the number of months between those dates and the reference date is greater than the frequency the first payment date is calculated by adding the frequency (as a number of months) to the reference date.
All payments in the resultant table are moved to the end of the month and interest is calculated using these end-of-month dates.
The periodic interest payment is calculated as:
\mathrm{I_p=C_{p-1}\times\left[\left(\left(1+\frac{R_p\times{F}}{12}\right)^{12\slash{F}}\right)^{\left(N_p-N_{p-1}\right)\slash{12}}-1\right]}
Where:Ip = Interest payment in period pCp-1 = Capital Amount In Debt in period p-1Rp = Interest rate in period pF = @FrequencyNp = Number of Month in period pNp-1 = Number of Month in period p-1
If the period is irregular and is longer than the regular period then the interest amount is broken out into a regular interest amount and a 'grace' interest amount. The regular interest amount is simply the normal periodic interest and the grace interest is the difference between the amount calculated using the above-formula and the normal periodic interest.
The principal payment is calculated as:
PPAYp = -PPMT(Rp * F/12,1,N-(P-1),Cp-1,FV,0)
Where:
PPAYp = Principal payment in period pPPMT = PPMT functionCp-1 = Capital Amount In Debt in period p-1Rp = Interest rate in period pF = @FrequencyP-1 = Period – 1FV = @LastPrinPayAmount
Arguments
@FirstPayDate
the first payment date of the loan if other than a regular periodic payment. @FirstPayDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@LastPrinPayAmount
the amount of principal to be paid off on the maturity date. @OutstandingAmount is an expression of type float or of a type that can be implicitly converted to float.
@OutstandingAmount
the principal amount of the loan. @OutstandingAmount is an expression of type float or of a type that can be implicitly converted to float.
@FutureRates
a SELECT statement, as a string, which identifies the forward dates and rates to be used in the calculation. The resultant table should consist of 2 columns: a date and a rate. The rate is decimal format such that 1% = .01.
@MaturityDate
the last payment date of the loan. @MaturityDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@GracePeriodStartDate
the date on which the (interim) grace period commences. @GracePeriodStartDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@PaymentFrequency
the number of months in a regular interest payment. @PaymentFrequency is an expression of type int or of a type that can be implicitly converted to int.
@PrevPayDate
the last interest payment date prior to the reference date. @PrevPayDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@ReferenceDate
the starting date for the number of months with respect to all other dates. @ReferenceDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@StartDate
the start date of the loan. @StartDate is an expression that returns a datetime or smalldatetime value, or a character string in date format.
@GracePeriodEndDate
the date on which the (interim) grace period concludes. @GracePeriodEndDate 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": "b9653eee-e37f-4b2e-9ad7-b0dd851129ec", "colName": "Period", "colDatatype": "int", "colDesc": "A reference number uniquely identifying a row in the resultant table."}, {"id": "4055bcfb-9d1c-4a05-8087-550051f135de", "colName": "PrincipalPayment", "colDatatype": "float", "colDesc": "The amount of the principal payment."}, {"id": "5ee2970f-2f8f-4632-af66-1ad1901ff42a", "colName": "InterestPayment", "colDatatype": "float", "colDesc": "The amount of the regular interest payment."}, {"id": "8b614ad4-9128-4473-b12d-fd0d4a5f3350", "colName": "CashFlow", "colDatatype": "float", "colDesc": "PrincipalPayment + InterestPayment + GraceInterest."}, {"id": "4fa3e7a1-66f7-42bb-801d-f382017ac4a5", "colName": "OutstandingExposure", "colDatatype": "float", "colDesc": "When Period = 0 then @OutstandingAmount. For Period > 0 then OutstandingExposure(Period-1) + InterestPayment."}, {"id": "d7d466fc-73ad-4790-9dd0-edcccc951337", "colName": "CapitalAmountInDebt", "colDatatype": "float", "colDesc": "When Period = 0, @OutstandingAmount. For Period > 0 then CapitalAmountInDebt(Period-1) \u2013 PrincipalPayment"}, {"id": "885bdbca-4b9f-4c92-86db-9e76ce951ef4", "colName": "TotalExposure", "colDatatype": "float", "colDesc": "When Period = 0, @OutstandingAmount. For Period > 0 "}, {"id": "361b7e10-4c63-4756-b3b7-7ba057105f01", "colName": "NumberOfMonth", "colDatatype": "int", "colDesc": "The number of months between the @ReferenceDate and the PaymentDate."}, {"id": "5c72f35a-9b8d-4c1d-ad1c-3fd8fb48e282", "colName": "PaymentDate", "colDatatype": "datetime", "colDesc": "The end-of-month date of the payment."}, {"id": "fb63d252-373a-46ce-8f0d-f22dc1bd4c47", "colName": "GraceInterest", "colDatatype": "float", "colDesc": "The amount of the grace interest"}, {"id": "0b2f9a34-8d65-4051-b2a1-442d662fda0e", "colName": "InterestRate", "colDatatype": "float", "colDesc": "The Interest Rate used in the calculation of the interest and principal payment"}]}
Remarks
The PaymentDate for all rows is generated as the last day of the month.
For Period = 0, PrincipalPayment, InterestPayment, CashFlow, NumberOfMonth, GraceInterest, and InterestRate are set to 0.
If @Frequency is NULL then @Frequency = 1.
If @ReferenceDate is NULL then @ReferenceDate = GETDATE().
GraceInterest is only calculated on @FirstPayDate and @GracePeriodEndDate.
GraceInterest is only calculated if NumberOfMonth – NumberOfMonth(Period-1) > @PaymentFrequency.
GraceInterest is the difference between the interest for the period from the previous row to the current row minus the interest that would have been calculated for a period with length equal to @PaymentFrequency.
The final payment is adjusted for CapitalAmountInDebt(Period-1) and the length of the period if it is less than @PaymentFrequency.
The last row returned will always be for the maturity date and may be shorter than a regular period depending on the combination of dates and @PaymentFrequency.
The interest rate used in the calculation comes from the @FutureRates resultant table. The rate value for the maximum date less than or equal to the PaymentDate is used.
Examples
Use the following forward rates, which have been inserted into a #fwdrates table, for all the examples.
SELECT CAST(dt as datetime) as dt,
rate
INTO #fwdrates
FROM
(
VALUES
('2014-12-31', 0.025),
('2015-01-31', 0.025102),
('2015-02-28', 0.025205),
('2015-03-31', 0.025308),
('2015-04-30', 0.02541),
('2015-05-31', 0.025513),
('2015-06-30', 0.025615),
('2015-07-31', 0.025718),
('2015-08-31', 0.02582),
('2015-09-30', 0.025923),
('2015-10-31', 0.026025),
('2015-11-30', 0.026128),
('2015-12-31', 0.026231),
('2016-01-31', 0.026333),
('2016-02-29', 0.026436),
('2016-03-31', 0.026539),
('2016-04-30', 0.026641),
('2016-05-31', 0.026744),
('2016-06-30', 0.026847),
('2016-07-31', 0.026949),
('2016-08-31', 0.027052),
('2016-09-30', 0.027155),
('2016-10-31', 0.027257),
('2016-11-30', 0.02736),
('2016-12-31', 0.027463),
('2017-01-31', 0.027566),
('2017-02-28', 0.027668),
('2017-03-31', 0.027771),
('2017-04-30', 0.027874),
('2017-05-31', 0.027977),
('2017-06-30', 0.028079),
('2017-07-31', 0.028182),
('2017-08-31', 0.028285),
('2017-09-30', 0.028388),
('2017-10-31', 0.028491),
('2017-11-30', 0.028594),
('2017-12-31', 0.028696),
('2018-01-31', 0.028799),
('2018-02-28', 0.028902),
('2018-03-31', 0.029005),
('2018-04-30', 0.029108),
('2018-05-31', 0.029211),
('2018-06-30', 0.029314),
('2018-07-31', 0.029417),
('2018-08-31', 0.02952),
('2018-09-30', 0.029623),
('2018-10-31', 0.029726),
('2018-11-30', 0.029829),
('2018-12-31', 0.029932),
('2019-01-31', 0.030035),
('2019-02-28', 0.030138),
('2019-03-31', 0.030241),
('2019-04-30', 0.030344),
('2019-05-31', 0.030447),
('2019-06-30', 0.03055),
('2019-07-31', 0.030653),
('2019-08-31', 0.030756),
('2019-09-30', 0.030859),
('2019-10-31', 0.030962),
('2019-11-30', 0.031065),
('2019-12-31', 0.031168)
) n (dt, rate);
This is a simple 300,000 loan with quarterly payments with the interest rates coming from #fwdrates .
SELECT *
FROM wct.ConstantCashFlowFR( 300000, --@OutstandingAmount
0, --@LastPrinPayAmount
3, --@PaymentFrequency
'2019-12-15', --@MaturityDate
'2014-12-15', --@ReferenceDate
NULL, --@PrevPayDate
NULL, --@StartDate
NULL, --@FirstPayDate
NULL,
--@GracePeriodStartDate
NULL, --@GracePeriodEndDate,
'SELECT * FROM #fwdrates' --@FutureRates
);
This produces the following result.
{"columns":[{"field":"Period","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PrincipalPayment","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"InterestPayment","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CashFlow","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"OutstandingExposure","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CapitalAmountInDebt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"TotalExposure","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"NumberOfMonth","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PaymentDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"GraceInterest","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"InterestRate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Period":"0","PrincipalPayment":"0","InterestPayment":"0","CashFlow":"0","OutstandingExposure":"300000","CapitalAmountInDebt":"300000","TotalExposure":"300000","NumberOfMonth":"0","PaymentDate":"2014-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0"},{"Period":"1","PrincipalPayment":"14118.2997411431","InterestPayment":"1898.1","CashFlow":"16016.3997411431","OutstandingExposure":"301898.1","CapitalAmountInDebt":"285881.700258857","TotalExposure":"301898.1","NumberOfMonth":"3","PaymentDate":"2015-03-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.006327"},{"Period":"2","PrincipalPayment":"14197.6716609513","InterestPayment":"1830.71493803265","CashFlow":"16028.386598984","OutstandingExposure":"303728.814938033","CapitalAmountInDebt":"271684.028597906","TotalExposure":"287712.41519689","NumberOfMonth":"6","PaymentDate":"2015-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00640375"},{"Period":"3","PrincipalPayment":"14279.1122136908","InterestPayment":"1760.71626833588","CashFlow":"16039.8284820267","OutstandingExposure":"305489.531206369","CapitalAmountInDebt":"257404.916384215","TotalExposure":"273444.744866241","NumberOfMonth":"9","PaymentDate":"2015-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00648075"},{"Period":"4","PrincipalPayment":"14362.6873415711","InterestPayment":"1687.99709041858","CashFlow":"16050.6844319897","OutstandingExposure":"307177.528296787","CapitalAmountInDebt":"243042.229042644","TotalExposure":"259092.913474633","NumberOfMonth":"12","PaymentDate":"2015-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00655775"},{"Period":"5","PrincipalPayment":"14448.4281134272","InterestPayment":"1612.52442914068","CashFlow":"16060.9525425679","OutstandingExposure":"308790.052725928","CapitalAmountInDebt":"228593.800929216","TotalExposure":"244654.753471784","NumberOfMonth":"15","PaymentDate":"2016-03-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00663475"},{"Period":"6","PrincipalPayment":"14536.3664798309","InterestPayment":"1534.26444338667","CashFlow":"16070.6309232175","OutstandingExposure":"310324.317169314","CapitalAmountInDebt":"214057.434449386","TotalExposure":"230128.065372603","NumberOfMonth":"18","PaymentDate":"2016-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00671175"},{"Period":"7","PrincipalPayment":"14626.5352913931","InterestPayment":"1453.18240811827","CashFlow":"16079.7176995114","OutstandingExposure":"311777.499577433","CapitalAmountInDebt":"199430.899157992","TotalExposure":"215510.616857504","NumberOfMonth":"21","PaymentDate":"2016-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00678875"},{"Period":"8","PrincipalPayment":"14718.9683176065","InterestPayment":"1369.24269589399","CashFlow":"16088.2110135004","OutstandingExposure":"313146.742273327","CapitalAmountInDebt":"184711.930840386","TotalExposure":"200800.141853886","NumberOfMonth":"24","PaymentDate":"2016-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00686575"},{"Period":"9","PrincipalPayment":"14813.7002662586","InterestPayment":"1282.40875784209","CashFlow":"16096.1090241007","OutstandingExposure":"314429.151031169","CapitalAmountInDebt":"169898.230574127","TotalExposure":"185994.339598228","NumberOfMonth":"27","PaymentDate":"2017-03-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00694275"},{"Period":"10","PrincipalPayment":"14910.7668034178","InterestPayment":"1192.64310407273","CashFlow":"16103.4099074905","OutstandingExposure":"315621.794135242","CapitalAmountInDebt":"154987.46377071","TotalExposure":"171090.8736782","NumberOfMonth":"30","PaymentDate":"2017-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00701975"},{"Period":"11","PrincipalPayment":"15010.1875891643","InterestPayment":"1099.94603038073","CashFlow":"16110.133619545","OutstandingExposure":"316721.740165622","CapitalAmountInDebt":"139977.276181545","TotalExposure":"156087.40980109","NumberOfMonth":"33","PaymentDate":"2017-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.007097"},{"Period":"12","PrincipalPayment":"15112.0378735513","InterestPayment":"1004.19697932641","CashFlow":"16116.2348528777","OutstandingExposure":"317725.937144949","CapitalAmountInDebt":"124865.238307994","TotalExposure":"140981.473160872","NumberOfMonth":"36","PaymentDate":"2017-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.007174"},{"Period":"13","PrincipalPayment":"15216.3223904572","InterestPayment":"905.429059280842","CashFlow":"16121.7514497381","OutstandingExposure":"318631.36620423","CapitalAmountInDebt":"109648.915917537","TotalExposure":"125770.667367275","NumberOfMonth":"39","PaymentDate":"2018-03-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00725125"},{"Period":"14","PrincipalPayment":"15323.0997654969","InterestPayment":"803.562080301669","CashFlow":"16126.6618457985","OutstandingExposure":"319434.928284531","CapitalAmountInDebt":"94325.8161520399","TotalExposure":"110452.477997838","NumberOfMonth":"42","PaymentDate":"2018-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.0073285"},{"Period":"15","PrincipalPayment":"15432.4108906194","InterestPayment":"698.55341296797","CashFlow":"16130.9643035874","OutstandingExposure":"320133.481697499","CapitalAmountInDebt":"78893.4052614205","TotalExposure":"95024.3695650079","NumberOfMonth":"45","PaymentDate":"2018-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00740575"},{"Period":"16","PrincipalPayment":"15544.2977541982","InterestPayment":"590.35935157121","CashFlow":"16134.6571057694","OutstandingExposure":"320723.84104907","CapitalAmountInDebt":"63349.1075072223","TotalExposure":"79483.7646129917","NumberOfMonth":"48","PaymentDate":"2018-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.007483"},{"Period":"17","PrincipalPayment":"15658.8034656363","InterestPayment":"478.935090031477","CashFlow":"16137.7385556678","OutstandingExposure":"321202.776139102","CapitalAmountInDebt":"47690.304041586","TotalExposure":"63828.0425972538","NumberOfMonth":"51","PaymentDate":"2019-03-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00756025"},{"Period":"18","PrincipalPayment":"15775.9722806856","InterestPayment":"364.234697117613","CashFlow":"16140.2069778033","OutstandingExposure":"321567.010836219","CapitalAmountInDebt":"31914.3317609003","TotalExposure":"48054.5387387036","NumberOfMonth":"54","PaymentDate":"2019-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.0076375"},{"Period":"19","PrincipalPayment":"15895.849627493","InterestPayment":"246.211090952406","CashFlow":"16142.0607184454","OutstandingExposure":"321813.221927172","CapitalAmountInDebt":"16018.4821334073","TotalExposure":"32160.5428518527","NumberOfMonth":"57","PaymentDate":"2019-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00771475"},{"Period":"20","PrincipalPayment":"16018.4821334073","InterestPayment":"124.81601278351","CashFlow":"16143.2981461908","OutstandingExposure":"321938.037939955","CapitalAmountInDebt":"0","TotalExposure":"16143.2981461908","NumberOfMonth":"60","PaymentDate":"2019-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00779200000000002"}]}
In this example the SQL reflects a final principal payment of 172,000.
SELECT *
FROM wct.ConstantCashFlowFR( 300000, --@OutstandingAmount
172000, --@LastPrinPayAmount
3, --@PaymentFrequency
'2019-12-15', --@MaturityDate
'2014-12-15', --@ReferenceDate
NULL, --@PrevPayDate
NULL, --@StartDate
NULL, --@FirstPayDate
NULL,
--@GracePeriodStartDate
NULL, --@GracePeriodEndDate,
'SELECT * FROM #fwdrates' --@FutureRates
);
This produces the following result.
{"columns":[{"field":"Period","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PrincipalPayment","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"InterestPayment","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CashFlow","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"OutstandingExposure","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CapitalAmountInDebt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"TotalExposure","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"NumberOfMonth","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PaymentDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"GraceInterest","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"InterestRate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Period":"0","PrincipalPayment":"0","InterestPayment":"0","CashFlow":"0","OutstandingExposure":"300000","CapitalAmountInDebt":"300000","TotalExposure":"300000","NumberOfMonth":"0","PaymentDate":"2014-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0"},{"Period":"1","PrincipalPayment":"6023.80788955439","InterestPayment":"1898.1","CashFlow":"7921.90788955439","OutstandingExposure":"301898.1","CapitalAmountInDebt":"293976.192110446","TotalExposure":"301898.1","NumberOfMonth":"3","PaymentDate":"2015-03-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.006327"},{"Period":"2","PrincipalPayment":"6057.6732420059","InterestPayment":"1882.55004022727","CashFlow":"7940.22328223317","OutstandingExposure":"303780.650040227","CapitalAmountInDebt":"287918.51886844","TotalExposure":"295858.742150673","NumberOfMonth":"6","PaymentDate":"2015-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00640375"},{"Period":"3","PrincipalPayment":"6092.42121117475","InterestPayment":"1865.92794115664","CashFlow":"7958.3491523314","OutstandingExposure":"305646.577981384","CapitalAmountInDebt":"281826.097657265","TotalExposure":"289784.446809596","NumberOfMonth":"9","PaymentDate":"2015-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00648075"},{"Period":"4","PrincipalPayment":"6128.07993240369","InterestPayment":"1848.14509191193","CashFlow":"7976.22502431562","OutstandingExposure":"307494.723073296","CapitalAmountInDebt":"275698.017724861","TotalExposure":"283674.242749177","NumberOfMonth":"12","PaymentDate":"2015-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00655775"},{"Period":"5","PrincipalPayment":"6164.66266172893","InterestPayment":"1829.18742310002","CashFlow":"7993.85008482895","OutstandingExposure":"309323.910496396","CapitalAmountInDebt":"269533.355063132","TotalExposure":"277527.205147961","NumberOfMonth":"15","PaymentDate":"2016-03-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00663475"},{"Period":"6","PrincipalPayment":"6202.1830313945","InterestPayment":"1809.04049584498","CashFlow":"8011.22352723947","OutstandingExposure":"311132.950992241","CapitalAmountInDebt":"263331.172031738","TotalExposure":"271342.395558977","NumberOfMonth":"18","PaymentDate":"2016-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00671175"},{"Period":"7","PrincipalPayment":"6240.65505766106","InterestPayment":"1787.68949413046","CashFlow":"8028.34455179152","OutstandingExposure":"312920.640486371","CapitalAmountInDebt":"257090.516974077","TotalExposure":"265118.861525868","NumberOfMonth":"21","PaymentDate":"2016-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00678875"},{"Period":"8","PrincipalPayment":"6280.09314884542","InterestPayment":"1765.11921691477","CashFlow":"8045.21236576019","OutstandingExposure":"314685.759703286","CapitalAmountInDebt":"250810.423825231","TotalExposure":"258855.636190991","NumberOfMonth":"24","PaymentDate":"2016-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00686575"},{"Period":"9","PrincipalPayment":"6320.51211360369","InterestPayment":"1741.31407001262","CashFlow":"8061.82618361631","OutstandingExposure":"316427.073773299","CapitalAmountInDebt":"244489.911711628","TotalExposure":"252551.737895244","NumberOfMonth":"27","PaymentDate":"2017-03-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00694275"},{"Period":"10","PrincipalPayment":"6361.92716945824","InterestPayment":"1716.2580577377","CashFlow":"8078.18522719594","OutstandingExposure":"318143.331831036","CapitalAmountInDebt":"238127.984542169","TotalExposure":"246206.169769365","NumberOfMonth":"30","PaymentDate":"2017-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00701975"},{"Period":"11","PrincipalPayment":"6404.34670471009","InterestPayment":"1689.99430629578","CashFlow":"8094.34101100587","OutstandingExposure":"319833.326137332","CapitalAmountInDebt":"231723.637837459","TotalExposure":"239817.978848465","NumberOfMonth":"33","PaymentDate":"2017-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.007097"},{"Period":"12","PrincipalPayment":"6447.80282604854","InterestPayment":"1662.38537784593","CashFlow":"8110.18820389447","OutstandingExposure":"321495.711515178","CapitalAmountInDebt":"225275.835011411","TotalExposure":"233386.023215305","NumberOfMonth":"36","PaymentDate":"2017-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.007174"},{"Period":"13","PrincipalPayment":"6492.29755326174","InterestPayment":"1633.53139862649","CashFlow":"8125.82895188823","OutstandingExposure":"323129.242913805","CapitalAmountInDebt":"218783.537458149","TotalExposure":"226909.366410037","NumberOfMonth":"39","PaymentDate":"2018-03-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00725125"},{"Period":"14","PrincipalPayment":"6537.85589994533","InterestPayment":"1603.35515426205","CashFlow":"8141.21105420738","OutstandingExposure":"324732.598068067","CapitalAmountInDebt":"212245.681558204","TotalExposure":"220386.892612411","NumberOfMonth":"42","PaymentDate":"2018-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.0073285"},{"Period":"15","PrincipalPayment":"6584.49531333094","InterestPayment":"1571.83845619967","CashFlow":"8156.33376953061","OutstandingExposure":"326304.436524266","CapitalAmountInDebt":"205661.186244873","TotalExposure":"213817.520014403","NumberOfMonth":"45","PaymentDate":"2018-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00740575"},{"Period":"16","PrincipalPayment":"6632.23370845791","InterestPayment":"1538.96265667038","CashFlow":"8171.1963651283","OutstandingExposure":"327843.399180937","CapitalAmountInDebt":"199028.952536415","TotalExposure":"207200.148901543","NumberOfMonth":"48","PaymentDate":"2018-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.007483"},{"Period":"17","PrincipalPayment":"6681.08947867149","InterestPayment":"1504.70863841343","CashFlow":"8185.79811708492","OutstandingExposure":"329348.10781935","CapitalAmountInDebt":"192347.863057743","TotalExposure":"200533.661174828","NumberOfMonth":"51","PaymentDate":"2019-03-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00756025"},{"Period":"18","PrincipalPayment":"6731.08150642587","InterestPayment":"1469.05680410351","CashFlow":"8200.13831052939","OutstandingExposure":"330817.164623454","CapitalAmountInDebt":"185616.781551317","TotalExposure":"193816.919861847","NumberOfMonth":"54","PaymentDate":"2019-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.0076375"},{"Period":"19","PrincipalPayment":"6782.22917439702","InterestPayment":"1431.98706547303","CashFlow":"8214.21623987005","OutstandingExposure":"332249.151688927","CapitalAmountInDebt":"178834.55237692","TotalExposure":"187048.768616791","NumberOfMonth":"57","PaymentDate":"2019-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00771475"},{"Period":"20","PrincipalPayment":"178834.55237692","InterestPayment":"1393.47883212097","CashFlow":"180228.031209041","OutstandingExposure":"333642.630521048","CapitalAmountInDebt":"0","TotalExposure":"180228.031209041","NumberOfMonth":"60","PaymentDate":"2019-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00779200000000002"}]}
In this example the last principal payment amount has been set to zero and a first payment date of 15-June-2015 has been added.
SELECT *
FROM wct.ConstantCashFlowFR( 300000, --@OutstandingAmount
0, --@LastPrinPayAmount
3, --@PaymentFrequency
'2019-12-15', --@MaturityDate
'2014-12-15', --@ReferenceDate
NULL, --@PrevPayDate
NULL, --@StartDate
'2015-06-15', --@FirstPayDate
NULL,
--@GracePeriodStartDate
NULL, --@GracePeriodEndDate,
'SELECT * FROM #fwdrates' --@FutureRates
);
This produces the following result.
{"columns":[{"field":"Period","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PrincipalPayment","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"InterestPayment","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CashFlow","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"OutstandingExposure","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CapitalAmountInDebt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"TotalExposure","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"NumberOfMonth","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PaymentDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"GraceInterest","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"InterestRate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Period":"0","PrincipalPayment":"0","InterestPayment":"0","CashFlow":"0","OutstandingExposure":"300000","CapitalAmountInDebt":"300000","TotalExposure":"300000","NumberOfMonth":"0","PaymentDate":"2014-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0"},{"Period":"1","PrincipalPayment":"14898.8252638372","InterestPayment":"1921.125","CashFlow":"18753.377668056","OutstandingExposure":"301921.125","CapitalAmountInDebt":"285101.174736163","TotalExposure":"301921.125","NumberOfMonth":"6","PaymentDate":"2015-06-30 00:00:00.000","GraceInterest":"1933.42740421877","InterestRate":"0.0128485080140626"},{"Period":"2","PrincipalPayment":"14984.2877673823","InterestPayment":"1847.66943817139","CashFlow":"16831.9572055537","OutstandingExposure":"303768.794438171","CapitalAmountInDebt":"270116.88696878","TotalExposure":"286948.844174334","NumberOfMonth":"9","PaymentDate":"2015-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00648075"},{"Period":"3","PrincipalPayment":"15071.9902623003","InterestPayment":"1771.35901551952","CashFlow":"16843.3492778198","OutstandingExposure":"305540.153453691","CapitalAmountInDebt":"255044.89670648","TotalExposure":"271888.2459843","NumberOfMonth":"12","PaymentDate":"2015-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00655775"},{"Period":"4","PrincipalPayment":"15161.9653517639","InterestPayment":"1692.15912842332","CashFlow":"16854.1244801873","OutstandingExposure":"307232.312582114","CapitalAmountInDebt":"239882.931354716","TotalExposure":"256737.055834903","NumberOfMonth":"15","PaymentDate":"2016-03-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00663475"},{"Period":"5","PrincipalPayment":"15254.2465642278","InterestPayment":"1610.03426452002","CashFlow":"16864.2808287478","OutstandingExposure":"308842.346846634","CapitalAmountInDebt":"224628.684790488","TotalExposure":"241492.965619236","NumberOfMonth":"18","PaymentDate":"2016-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00671175"},{"Period":"6","PrincipalPayment":"15348.8683726337","InterestPayment":"1524.94798387143","CashFlow":"16873.8163565052","OutstandingExposure":"310367.294830506","CapitalAmountInDebt":"209279.816417855","TotalExposure":"226153.63277436","NumberOfMonth":"21","PaymentDate":"2016-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00678875"},{"Period":"7","PrincipalPayment":"15445.8662141846","InterestPayment":"1436.86289957089","CashFlow":"16882.7291137555","OutstandingExposure":"311804.157730077","CapitalAmountInDebt":"193833.95020367","TotalExposure":"210716.679317426","NumberOfMonth":"24","PaymentDate":"2016-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00686575"},{"Period":"8","PrincipalPayment":"15545.2765107161","InterestPayment":"1345.74065777653","CashFlow":"16891.0171684927","OutstandingExposure":"313149.898387853","CapitalAmountInDebt":"178288.673692954","TotalExposure":"195179.690861447","NumberOfMonth":"27","PaymentDate":"2017-03-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00694275"},{"Period":"9","PrincipalPayment":"15647.136689669","InterestPayment":"1251.54191715611","CashFlow":"16898.6786068251","OutstandingExposure":"314401.440305009","CapitalAmountInDebt":"162641.537003285","TotalExposure":"179540.21561011","NumberOfMonth":"30","PaymentDate":"2017-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00701975"},{"Period":"10","PrincipalPayment":"15751.4673820392","InterestPayment":"1154.26698811231","CashFlow":"16905.7343701515","OutstandingExposure":"315555.707293122","CapitalAmountInDebt":"146890.069621246","TotalExposure":"163795.803991397","NumberOfMonth":"33","PaymentDate":"2017-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.007097"},{"Period":"11","PrincipalPayment":"15858.3475541119","InterestPayment":"1053.78935946282","CashFlow":"16912.1369135747","OutstandingExposure":"316609.496652584","CapitalAmountInDebt":"131031.722067134","TotalExposure":"147943.858980709","NumberOfMonth":"36","PaymentDate":"2017-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.007174"},{"Period":"12","PrincipalPayment":"15967.7821735487","InterestPayment":"950.143774639304","CashFlow":"16917.925948188","OutstandingExposure":"317559.640427224","CapitalAmountInDebt":"115063.939893585","TotalExposure":"131981.865841773","NumberOfMonth":"39","PaymentDate":"2018-03-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00725125"},{"Period":"13","PrincipalPayment":"16079.832761197","InterestPayment":"843.246083510139","CashFlow":"16923.0788447071","OutstandingExposure":"318402.886510734","CapitalAmountInDebt":"98984.1071323882","TotalExposure":"115907.185977095","NumberOfMonth":"42","PaymentDate":"2018-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.0073285"},{"Period":"14","PrincipalPayment":"16194.5422284594","InterestPayment":"733.051551395684","CashFlow":"16927.5937798551","OutstandingExposure":"319135.938062129","CapitalAmountInDebt":"82789.5649039288","TotalExposure":"99717.1586837839","NumberOfMonth":"45","PaymentDate":"2018-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00740575"},{"Period":"15","PrincipalPayment":"16311.9546373098","InterestPayment":"619.514314176099","CashFlow":"16931.4689514859","OutstandingExposure":"319755.452376306","CapitalAmountInDebt":"66477.610266619","TotalExposure":"83409.0792181049","NumberOfMonth":"48","PaymentDate":"2018-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.007483"},{"Period":"16","PrincipalPayment":"16432.1152261139","InterestPayment":"502.587353018206","CashFlow":"16934.7025791321","OutstandingExposure":"320258.039729324","CapitalAmountInDebt":"50045.4950405051","TotalExposure":"66980.1976196372","NumberOfMonth":"51","PaymentDate":"2019-03-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00756025"},{"Period":"17","PrincipalPayment":"16555.070436199","InterestPayment":"382.222468371858","CashFlow":"16937.2929045708","OutstandingExposure":"320640.262197696","CapitalAmountInDebt":"33490.4246043062","TotalExposure":"50427.717508877","NumberOfMonth":"54","PaymentDate":"2019-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.0076375"},{"Period":"18","PrincipalPayment":"16680.8679391858","InterestPayment":"258.370253216071","CashFlow":"16939.2381924019","OutstandingExposure":"320898.632450912","CapitalAmountInDebt":"16809.5566651203","TotalExposure":"33748.7948575222","NumberOfMonth":"57","PaymentDate":"2019-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00771475"},{"Period":"19","PrincipalPayment":"16809.5566651203","InterestPayment":"130.980065534618","CashFlow":"16940.536730655","OutstandingExposure":"321029.612516446","CapitalAmountInDebt":"0","TotalExposure":"16940.536730655","NumberOfMonth":"60","PaymentDate":"2019-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00779200000000002"}]}
In this example there are no payments in 2018.
SELECT *
FROM wct.ConstantCashFlowFR( 300000, --@OutstandingAmount
0, --@LastPrinPayAmount
3, --@PaymentFrequency
'2019-12-15', --@MaturityDate
'2014-12-15', --@ReferenceDate
NULL, --@PrevPayDate
NULL, --@StartDate
'2015-06-15', --@FirstPayDate
'2018-01-01',
--@GracePeriodStartDate
'2019-01-01', --@GracePeriodEndDate,
'SELECT * FROM #fwdrates' --@FutureRates
);
This produces the following results.
{"columns":[{"field":"Period","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PrincipalPayment","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"InterestPayment","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CashFlow","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"OutstandingExposure","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CapitalAmountInDebt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"TotalExposure","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"NumberOfMonth","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PaymentDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"GraceInterest","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"InterestRate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Period":"0","PrincipalPayment":"0","InterestPayment":"0","CashFlow":"0","OutstandingExposure":"300000","CapitalAmountInDebt":"300000","TotalExposure":"300000","NumberOfMonth":"0","PaymentDate":"2014-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0"},{"Period":"1","PrincipalPayment":"17865.7568513617","InterestPayment":"1921.125","CashFlow":"21720.3092555805","OutstandingExposure":"301921.125","CapitalAmountInDebt":"282134.243148638","TotalExposure":"301921.125","NumberOfMonth":"6","PaymentDate":"2015-06-30 00:00:00.000","GraceInterest":"1933.42740421877","InterestRate":"0.0128485080140626"},{"Period":"2","PrincipalPayment":"17970.3732159486","InterestPayment":"1828.44149628554","CashFlow":"19798.8147122342","OutstandingExposure":"303749.566496286","CapitalAmountInDebt":"264163.86993269","TotalExposure":"283962.684644924","NumberOfMonth":"9","PaymentDate":"2015-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00648075"},{"Period":"3","PrincipalPayment":"18077.6971068503","InterestPayment":"1732.3206180511","CashFlow":"19810.0177249014","OutstandingExposure":"305481.887114337","CapitalAmountInDebt":"246086.172825839","TotalExposure":"265896.190550741","NumberOfMonth":"12","PaymentDate":"2015-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00655775"},{"Period":"4","PrincipalPayment":"18187.7684205277","InterestPayment":"1632.72023515624","CashFlow":"19820.4886556839","OutstandingExposure":"307114.607349493","CapitalAmountInDebt":"227898.404405312","TotalExposure":"247718.893060996","NumberOfMonth":"15","PaymentDate":"2016-03-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00663475"},{"Period":"5","PrincipalPayment":"18300.6281762129","InterestPayment":"1529.59711576735","CashFlow":"19830.2252919802","OutstandingExposure":"308644.20446526","CapitalAmountInDebt":"209597.776229099","TotalExposure":"229428.001521079","NumberOfMonth":"18","PaymentDate":"2016-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00671175"},{"Period":"6","PrincipalPayment":"18416.3185393689","InterestPayment":"1422.90690337529","CashFlow":"19839.2254427442","OutstandingExposure":"310067.111368636","CapitalAmountInDebt":"191181.45768973","TotalExposure":"211020.683132474","NumberOfMonth":"21","PaymentDate":"2016-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00678875"},{"Period":"7","PrincipalPayment":"18534.8828458376","InterestPayment":"1312.60409313326","CashFlow":"19847.4869389709","OutstandingExposure":"311379.715461769","CapitalAmountInDebt":"172646.574843892","TotalExposure":"192494.061782863","NumberOfMonth":"24","PaymentDate":"2016-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00686575"},{"Period":"8","PrincipalPayment":"18656.3656267154","InterestPayment":"1198.64200749743","CashFlow":"19855.0076342128","OutstandingExposure":"312578.357469266","CapitalAmountInDebt":"153990.209217177","TotalExposure":"173845.21685139","NumberOfMonth":"27","PaymentDate":"2017-03-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00694275"},{"Period":"9","PrincipalPayment":"18780.812633958","InterestPayment":"1080.97277115228","CashFlow":"19861.7854051103","OutstandingExposure":"313659.330240418","CapitalAmountInDebt":"135209.396583219","TotalExposure":"155071.181988329","NumberOfMonth":"30","PaymentDate":"2017-06-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00701975"},{"Period":"10","PrincipalPayment":"18908.2566527075","InterestPayment":"959.581087551104","CashFlow":"19867.8377402586","OutstandingExposure":"314618.91132797","CapitalAmountInDebt":"116301.139930511","TotalExposure":"136168.97767077","NumberOfMonth":"33","PaymentDate":"2017-09-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.007097"},{"Period":"11","PrincipalPayment":"19038.7790124555","InterestPayment":"834.344377861489","CashFlow":"19873.1233903169","OutstandingExposure":"315453.255705831","CapitalAmountInDebt":"97262.3609180559","TotalExposure":"117135.484308373","NumberOfMonth":"36","PaymentDate":"2017-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.007174"},{"Period":"12","PrincipalPayment":"19162.5299477012","InterestPayment":"730.318752543452","CashFlow":"22367.0815049798","OutstandingExposure":"316183.574458375","CapitalAmountInDebt":"78099.8309703547","TotalExposure":"97992.6796705994","NumberOfMonth":"49","PaymentDate":"2019-01-31 00:00:00.000","GraceInterest":"2474.23280473521","InterestRate":"0.0329474991870546"},{"Period":"13","PrincipalPayment":"19304.1824383589","InterestPayment":"592.465317741111","CashFlow":"19896.6477561","OutstandingExposure":"316776.039776116","CapitalAmountInDebt":"58795.6485319958","TotalExposure":"78692.2962880959","NumberOfMonth":"52","PaymentDate":"2019-04-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.007586"},{"Period":"14","PrincipalPayment":"19449.1252821227","InterestPayment":"450.565753612817","CashFlow":"19899.6910357355","OutstandingExposure":"317226.605529728","CapitalAmountInDebt":"39346.5232498731","TotalExposure":"59246.2142856086","NumberOfMonth":"55","PaymentDate":"2019-07-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00766325"},{"Period":"15","PrincipalPayment":"19597.4147305756","InterestPayment":"304.561763215643","CashFlow":"19901.9764937913","OutstandingExposure":"317531.167292944","CapitalAmountInDebt":"19749.1085192975","TotalExposure":"39651.0850130887","NumberOfMonth":"58","PaymentDate":"2019-10-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.0077405"},{"Period":"16","PrincipalPayment":"19749.1085192975","InterestPayment":"102.457264766701","CashFlow":"19851.5657840642","OutstandingExposure":"317633.624557711","CapitalAmountInDebt":"0","TotalExposure":"19851.5657840642","NumberOfMonth":"60","PaymentDate":"2019-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00518794378321363"}]}
In this example the first payment date is set to NULL and the previous payment date is set to '2014-11-15' so that the first payment date will be calculated using the previous payment date.
SELECT *
FROM wct.ConstantCashFlowFR( 300000, --@OutstandingAmount
0, --@LastPrinPayAmount
3, --@PaymentFrequency
'2019-12-15', --@MaturityDate
'2014-12-15', --@ReferenceDate
'2014-11-15', --@PrevPayDate
NULL, --@StartDate
NULL, --@FirstPayDate
'2018-01-01',
--@GracePeriodStartDate
'2019-01-01', --@GracePeriodEndDate,
'SELECT * FROM #fwdrates' --@FutureRates
);
This produces the following result.
{"columns":[{"field":"Period","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PrincipalPayment","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"InterestPayment","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CashFlow","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"OutstandingExposure","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CapitalAmountInDebt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"TotalExposure","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"NumberOfMonth","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PaymentDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"GraceInterest","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"InterestRate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Period":"0","PrincipalPayment":"0","InterestPayment":"0","CashFlow":"0","OutstandingExposure":"300000","CapitalAmountInDebt":"300000","TotalExposure":"300000","NumberOfMonth":"0","PaymentDate":"2014-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0"},{"Period":"1","PrincipalPayment":"16774.231184574","InterestPayment":"1890.375","CashFlow":"18664.606184574","OutstandingExposure":"301890.375","CapitalAmountInDebt":"283225.768815426","TotalExposure":"301890.375","NumberOfMonth":"2","PaymentDate":"2015-02-28 00:00:00.000","GraceInterest":"0","InterestRate":"0.00630125"},{"Period":"2","PrincipalPayment":"16870.072519696","InterestPayment":"1806.48475994699","CashFlow":"18676.557279643","OutstandingExposure":"303696.859759947","CapitalAmountInDebt":"266355.69629573","TotalExposure":"285032.253575373","NumberOfMonth":"5","PaymentDate":"2015-05-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00637825"},{"Period":"3","PrincipalPayment":"16968.4589012205","InterestPayment":"1719.32601958894","CashFlow":"18687.7849208094","OutstandingExposure":"305416.185779536","CapitalAmountInDebt":"249387.237394509","TotalExposure":"268075.022315319","NumberOfMonth":"8","PaymentDate":"2015-08-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.006455"},{"Period":"4","PrincipalPayment":"17069.3626958998","InterestPayment":"1628.99743466094","CashFlow":"18698.3601305607","OutstandingExposure":"307045.183214197","CapitalAmountInDebt":"232317.87469861","TotalExposure":"251016.23482917","NumberOfMonth":"11","PaymentDate":"2015-11-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.006532"},{"Period":"5","PrincipalPayment":"17172.8554066894","InterestPayment":"1535.38883388311","CashFlow":"18708.2442405725","OutstandingExposure":"308580.57204808","CapitalAmountInDebt":"215145.01929192","TotalExposure":"233853.263532493","NumberOfMonth":"14","PaymentDate":"2016-02-29 00:00:00.000","GraceInterest":"0","InterestRate":"0.006609"},{"Period":"6","PrincipalPayment":"17278.9755692746","InterestPayment":"1438.45959898578","CashFlow":"18717.4351682604","OutstandingExposure":"310019.031647066","CapitalAmountInDebt":"197866.043722646","TotalExposure":"216583.478890906","NumberOfMonth":"17","PaymentDate":"2016-05-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.006686"},{"Period":"7","PrincipalPayment":"17387.7627977139","InterestPayment":"1338.16805369625","CashFlow":"18725.9308514101","OutstandingExposure":"311357.199700762","CapitalAmountInDebt":"180478.280924932","TotalExposure":"199204.211776342","NumberOfMonth":"20","PaymentDate":"2016-08-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.006763"},{"Period":"8","PrincipalPayment":"17499.2578071183","InterestPayment":"1234.47144152653","CashFlow":"18733.7292486449","OutstandingExposure":"312591.671142288","CapitalAmountInDebt":"162979.023117813","TotalExposure":"181712.752366458","NumberOfMonth":"23","PaymentDate":"2016-11-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00684"},{"Period":"9","PrincipalPayment":"17613.5024369949","InterestPayment":"1127.32590290592","CashFlow":"18740.8283399008","OutstandingExposure":"313718.997045194","CapitalAmountInDebt":"145365.520680819","TotalExposure":"164106.349020719","NumberOfMonth":"26","PaymentDate":"2017-02-28 00:00:00.000","GraceInterest":"0","InterestRate":"0.006917"},{"Period":"10","PrincipalPayment":"17730.5241077685","InterestPayment":"1016.72279302181","CashFlow":"18747.2469007903","OutstandingExposure":"314735.719838216","CapitalAmountInDebt":"127634.99657305","TotalExposure":"146382.24347384","NumberOfMonth":"29","PaymentDate":"2017-05-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00699425"},{"Period":"11","PrincipalPayment":"17850.4024416384","InterestPayment":"902.53896951718","CashFlow":"18752.9414111556","OutstandingExposure":"315638.258807733","CapitalAmountInDebt":"109784.594131412","TotalExposure":"128537.535542567","NumberOfMonth":"32","PaymentDate":"2017-08-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00707125"},{"Period":"12","PrincipalPayment":"17973.1517169457","InterestPayment":"784.795171148396","CashFlow":"18757.9468880941","OutstandingExposure":"316423.053978882","CapitalAmountInDebt":"91811.442414466","TotalExposure":"110569.38930256","NumberOfMonth":"35","PaymentDate":"2017-11-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.0071485"},{"Period":"13","PrincipalPayment":"18088.5956109075","InterestPayment":"689.389168229621","CashFlow":"21350.3289956606","OutstandingExposure":"317112.443147111","CapitalAmountInDebt":"73722.8468035585","TotalExposure":"92500.8315826956","NumberOfMonth":"49","PaymentDate":"2019-01-31 00:00:00.000","GraceInterest":"2572.3442165235","InterestRate":"0.0355264365636325"},{"Period":"14","PrincipalPayment":"18222.3094069344","InterestPayment":"559.261515851795","CashFlow":"18781.5709227862","OutstandingExposure":"317671.704662963","CapitalAmountInDebt":"55500.5373966241","TotalExposure":"74282.1083194103","NumberOfMonth":"52","PaymentDate":"2019-04-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.007586"},{"Period":"15","PrincipalPayment":"18359.1291533193","InterestPayment":"425.31449320468","CashFlow":"18784.443646524","OutstandingExposure":"318097.019156168","CapitalAmountInDebt":"37141.4082433048","TotalExposure":"55925.8518898288","NumberOfMonth":"55","PaymentDate":"2019-07-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00766325"},{"Period":"16","PrincipalPayment":"18499.1079491124","InterestPayment":"287.493070507301","CashFlow":"18786.6010196197","OutstandingExposure":"318384.512226675","CapitalAmountInDebt":"18642.3002941924","TotalExposure":"37428.9013138121","NumberOfMonth":"58","PaymentDate":"2019-10-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.0077405"},{"Period":"17","PrincipalPayment":"18642.3002941924","InterestPayment":"96.715205916057","CashFlow":"18739.0155001084","OutstandingExposure":"318481.227432591","CapitalAmountInDebt":"0","TotalExposure":"18739.0155001084","NumberOfMonth":"60","PaymentDate":"2019-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00518794378321363"}]}
In this example the grace period and the previous payment date are eliminated and a start date has been added.
SELECT *
FROM wct.ConstantCashFlowFR( 300000, --@OutstandingAmount
0, --@LastPrinPayAmount
3, --@PaymentFrequency
'2019-12-15', --@MaturityDate
'2014-12-15', --@ReferenceDate
'2014-11-15', --@PrevPayDate
NULL, --@StartDate
'2014-10-15', --@FirstPayDate
NULL,
--@GracePeriodStartDate
NULL, --@GracePeriodEndDate,
'SELECT * FROM #fwdrates' --@FutureRates
);
This produces the following result.
{"columns":[{"field":"Period","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PrincipalPayment","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"InterestPayment","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CashFlow","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"OutstandingExposure","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"CapitalAmountInDebt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"TotalExposure","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"NumberOfMonth","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PaymentDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"GraceInterest","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"InterestRate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Period":"0","PrincipalPayment":"0","InterestPayment":"0","CashFlow":"0","OutstandingExposure":"300000","CapitalAmountInDebt":"300000","TotalExposure":"300000","NumberOfMonth":"0","PaymentDate":"2014-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0"},{"Period":"1","PrincipalPayment":"13406.2626876987","InterestPayment":"1890.375","CashFlow":"15296.6376876987","OutstandingExposure":"301890.375","CapitalAmountInDebt":"286593.737312301","TotalExposure":"301890.375","NumberOfMonth":"2","PaymentDate":"2015-02-28 00:00:00.000","GraceInterest":"0","InterestRate":"0.00630125"},{"Period":"2","PrincipalPayment":"13480.7194683809","InterestPayment":"1827.96650501219","CashFlow":"15308.6859733931","OutstandingExposure":"303718.341505012","CapitalAmountInDebt":"273113.01784392","TotalExposure":"288421.703817314","NumberOfMonth":"5","PaymentDate":"2015-05-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00637825"},{"Period":"3","PrincipalPayment":"13557.1962726885","InterestPayment":"1762.94453018251","CashFlow":"15320.1408028711","OutstandingExposure":"305481.286035195","CapitalAmountInDebt":"259555.821571232","TotalExposure":"274875.962374103","NumberOfMonth":"8","PaymentDate":"2015-08-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.006455"},{"Period":"4","PrincipalPayment":"13635.6563137531","InterestPayment":"1695.41862650329","CashFlow":"15331.0749402563","OutstandingExposure":"307176.704661698","CapitalAmountInDebt":"245920.165257479","TotalExposure":"261251.240197735","NumberOfMonth":"11","PaymentDate":"2015-11-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.006532"},{"Period":"5","PrincipalPayment":"13716.1628633371","InterestPayment":"1625.28637218668","CashFlow":"15341.4492355238","OutstandingExposure":"308801.991033885","CapitalAmountInDebt":"232204.002394142","TotalExposure":"247545.451629666","NumberOfMonth":"14","PaymentDate":"2016-02-29 00:00:00.000","GraceInterest":"0","InterestRate":"0.006609"},{"Period":"6","PrincipalPayment":"13798.7458972625","InterestPayment":"1552.51596000723","CashFlow":"15351.2618572698","OutstandingExposure":"310354.506993892","CapitalAmountInDebt":"218405.256496879","TotalExposure":"233756.518354149","NumberOfMonth":"17","PaymentDate":"2016-05-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.006686"},{"Period":"7","PrincipalPayment":"13883.4362394045","InterestPayment":"1477.07474968839","CashFlow":"15360.5109890929","OutstandingExposure":"311831.58174358","CapitalAmountInDebt":"204521.820257475","TotalExposure":"219882.331246568","NumberOfMonth":"20","PaymentDate":"2016-08-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.006763"},{"Period":"8","PrincipalPayment":"13970.2655793751","InterestPayment":"1398.92925056113","CashFlow":"15369.1948299363","OutstandingExposure":"313230.510994141","CapitalAmountInDebt":"190551.5546781","TotalExposure":"205920.749508036","NumberOfMonth":"23","PaymentDate":"2016-11-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00684"},{"Period":"9","PrincipalPayment":"14059.266490729","InterestPayment":"1318.04510370841","CashFlow":"15377.3115944374","OutstandingExposure":"314548.55609785","CapitalAmountInDebt":"176492.288187371","TotalExposure":"191869.599781808","NumberOfMonth":"26","PaymentDate":"2017-02-28 00:00:00.000","GraceInterest":"0","InterestRate":"0.006917"},{"Period":"10","PrincipalPayment":"14150.452836224","InterestPayment":"1234.43118665452","CashFlow":"15384.8840228785","OutstandingExposure":"315782.987284504","CapitalAmountInDebt":"162341.835351147","TotalExposure":"177726.719374025","NumberOfMonth":"29","PaymentDate":"2017-05-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00699425"},{"Period":"11","PrincipalPayment":"14243.9016464935","InterestPayment":"1147.9597032268","CashFlow":"15391.8613497203","OutstandingExposure":"316930.946987731","CapitalAmountInDebt":"148097.933704653","TotalExposure":"163489.795054373","NumberOfMonth":"32","PaymentDate":"2017-08-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00707125"},{"Period":"12","PrincipalPayment":"14339.6090604978","InterestPayment":"1058.67807908771","CashFlow":"15398.2871395855","OutstandingExposure":"317989.625066819","CapitalAmountInDebt":"133758.324644155","TotalExposure":"149156.611783741","NumberOfMonth":"35","PaymentDate":"2017-11-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.0071485"},{"Period":"13","PrincipalPayment":"14437.6473079351","InterestPayment":"966.470774716344","CashFlow":"15404.1180826515","OutstandingExposure":"318956.095841535","CapitalAmountInDebt":"119320.67733622","TotalExposure":"134724.795418872","NumberOfMonth":"38","PaymentDate":"2018-02-28 00:00:00.000","GraceInterest":"0","InterestRate":"0.0072255"},{"Period":"14","PrincipalPayment":"14538.0212582812","InterestPayment":"871.369076417082","CashFlow":"15409.3903346983","OutstandingExposure":"319827.464917952","CapitalAmountInDebt":"104782.656077939","TotalExposure":"120192.046412637","NumberOfMonth":"41","PaymentDate":"2018-05-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00730275"},{"Period":"15","PrincipalPayment":"14640.7872748072","InterestPayment":"773.29600185519","CashFlow":"15414.0832766624","OutstandingExposure":"320600.760919807","CapitalAmountInDebt":"90141.8688031317","TotalExposure":"105555.952079794","NumberOfMonth":"44","PaymentDate":"2018-08-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00738"},{"Period":"16","PrincipalPayment":"14745.9847868253","InterestPayment":"672.210451132154","CashFlow":"15418.1952379574","OutstandingExposure":"321272.971370939","CapitalAmountInDebt":"75395.8840163065","TotalExposure":"90814.0792542639","NumberOfMonth":"47","PaymentDate":"2018-11-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00745725"},{"Period":"17","PrincipalPayment":"14853.6542790982","InterestPayment":"568.070288120861","CashFlow":"15421.7245672191","OutstandingExposure":"321841.04165906","CapitalAmountInDebt":"60542.2297372082","TotalExposure":"75963.9543044273","NumberOfMonth":"50","PaymentDate":"2019-02-28 00:00:00.000","GraceInterest":"0","InterestRate":"0.0075345"},{"Period":"18","PrincipalPayment":"14963.8373156011","InterestPayment":"460.832317202195","CashFlow":"15424.6696328033","OutstandingExposure":"322301.873976263","CapitalAmountInDebt":"45578.3924216071","TotalExposure":"61003.0620544104","NumberOfMonth":"53","PaymentDate":"2019-05-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00761175"},{"Period":"19","PrincipalPayment":"15076.5765639763","InterestPayment":"350.452259329737","CashFlow":"15427.0288233061","OutstandingExposure":"322652.326235592","CapitalAmountInDebt":"30501.8158576308","TotalExposure":"45928.8446809369","NumberOfMonth":"56","PaymentDate":"2019-08-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.007689"},{"Period":"20","PrincipalPayment":"15191.9158206942","InterestPayment":"236.884727404325","CashFlow":"15428.8005480985","OutstandingExposure":"322889.210962997","CapitalAmountInDebt":"15309.9000369366","TotalExposure":"30738.7005850351","NumberOfMonth":"59","PaymentDate":"2019-11-30 00:00:00.000","GraceInterest":"0","InterestRate":"0.00776625"},{"Period":"21","PrincipalPayment":"15309.9000369366","InterestPayment":"39.6620757502427","CashFlow":"15349.5621126869","OutstandingExposure":"322928.873038747","CapitalAmountInDebt":"0","TotalExposure":"15349.5621126869","NumberOfMonth":"60","PaymentDate":"2019-12-31 00:00:00.000","GraceInterest":"0","InterestRate":"0.00259061624534151"}]}
See Also
BALLOON - Schedule with periodic interest payments and principal repaid at maturity
BULLET - Schedule with single interest and principal payment at maturity
CONSTANTCASHFLOW - Schedule with equal periodic cash flows
CONSTANTPAYMENTAMOUNT - Schedule with no maturity with fixed periodic payment amount
CONSTANTPRINCIPALAMOUNT - Schedule with no fixed maturity with a fixed periodic principal payment
CONSTANTPRINCIPALRATE - Schedule with no fixed maturity where a fixed percentage principal payment