ODDFSCHED
Updated 2023-10-06 22:31:05.990000
Syntax
SELECT * FROM [westclintech].[wct].[ODDFSCHED](
<@Rate, float,>
,<@Nper, int,>
,<@PV, float,>
,<@FV, float,>
,<@FirstPeriod, float,>
,<@IntRule, nvarchar(4000),>)
Description
Use the table-valued function ODDFSCHED to generate an annuity-like payment schedule where the first period is a different length of the time than all subsequent periods and those subsequent periods are assumed to be of equal length. Principal amortization, however, assumes all periods (including the first one) are of equal length. This results in a schedule where the cash flow for the first period is different than the cash flow for all subsequent periods.
To generate a payment schedule where the first period is of a different length but the payments are the same for all periods, use the ODDFPMTSCHED function.
Arguments
@FirstPeriod
the length of the first period expressed in periods. @FirstPeriod is an expression of type float or of a type that can be implicitly converted to float.
@Rate
the periodic interest rate. @Rate is of type float or of a type that can be implicitly converted to float.
@IntRule
use 'U' to calculate the first period interest using the US rule and 'A' to calculate first period interest using the Actuarial rule.
@FV
the future value; the ending balance in the amortization schedule. @FV is an expression of type float or of a type that can be implicitly converted to float.
@Nper
the number of periods (repayments). @Nper is of type int or of a type that can be implicitly converted to int.
@PV
the present value or principal amount. @PV is of type float or of a type that can be implicitly converted to float.
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": "d105593b-b8ea-4312-b83f-3192412b4885", "colName": "num_pmt", "colDatatype": "int", "colDesc": "The number of the payment from 0 to @Nper."}, {"id": "24eecb69-c9a6-41c1-9510-5992eaa68ffe", "colName": "amt_prin_init", "colDatatype": "float", "colDesc": "the amt_prin_end from the previous row."}, {"id": "09101f23-c622-4415-bc27-cd3df30dde4a", "colName": "amt_pmt", "colDatatype": "float", "colDesc": "amt_prin_pay + amt_int_pay."}, {"id": "9c89d134-3337-41a0-9990-72dca4f6c0b9", "colName": "amt_int_pay", "colDatatype": "float", "colDesc": "When num_pmt > 1 then PMT(@Rate,@Nper,@PV,@FV) - amt_prin_pay. When num_pmt = 1, if @IntRule = 'U' then amt_prin_init * @Rate * @FirstPeriod else amt_prin_init * (POWER(1+@Rate, @FirstPeriod) \u2013 1)."}, {"id": "63cf4cc1-ec0b-44fb-a784-2f30bf5888a5", "colName": "amt_prin_pay", "colDatatype": "float", "colDesc": "amt_prin_init \u2013 amt_prin_end."}, {"id": "303d264c-c489-4aa6-9ead-997f296241d2", "colName": "amt_prin_end", "colDatatype": "float", "colDesc": "\t PV(@Rate,@Nper - num_pmt,PMT(@Rate,@Nper,@PV,@FV),@FV)"}]}
Remarks
@Rate must be greater than -1.
@Nper must be greater than zero.
@FirstPeriod must be greater than zero.
@IntRule must be either 'U' or 'A'.
Examples
Example #1
A loan for 11,500 to be amortized over 36 periods at a periodic interest rate of 0.5%. The first period is half as long as a regular period. Interest is accrued using the US method.
SELECT *
FROM wct.ODDFSCHED( 0.005, --@Rate
36, --@Nper
-11500, --@PV
0, --@FV
0.5, --@FirstPeriod
'U' --@IntRule
);
This produces the following result.
{"columns":[{"field":"num_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_init","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_int_pay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_pay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_end","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"num_pmt":"0","amt_prin_init":"0","amt_pmt":"0","amt_int_pay":"0","amt_prin_pay":"0","amt_prin_end":"11500"},{"num_pmt":"1","amt_prin_init":"11500","amt_pmt":"321.10228069288","amt_int_pay":"28.75","amt_prin_pay":"292.35228069288","amt_prin_end":"11207.6477193071"},{"num_pmt":"2","amt_prin_init":"11207.6477193071","amt_pmt":"349.852280692891","amt_int_pay":"56.038238596539","amt_prin_pay":"293.814042096352","amt_prin_end":"10913.8336772108"},{"num_pmt":"3","amt_prin_init":"10913.8336772108","amt_pmt":"349.852280692891","amt_int_pay":"54.5691683860609","amt_prin_pay":"295.28311230683","amt_prin_end":"10618.5505649039"},{"num_pmt":"4","amt_prin_init":"10618.5505649039","amt_pmt":"349.852280692891","amt_int_pay":"53.0927528245288","amt_prin_pay":"296.759527868362","amt_prin_end":"10321.7910370356"},{"num_pmt":"5","amt_prin_init":"10321.7910370356","amt_pmt":"349.852280692891","amt_int_pay":"51.6089551851859","amt_prin_pay":"298.243325507705","amt_prin_end":"10023.5477115279"},{"num_pmt":"6","amt_prin_init":"10023.5477115279","amt_pmt":"349.852280692891","amt_int_pay":"50.1177385576452","amt_prin_pay":"299.734542135246","amt_prin_end":"9723.81316939263"},{"num_pmt":"7","amt_prin_init":"9723.81316939263","amt_pmt":"349.852280692891","amt_int_pay":"48.6190658469699","amt_prin_pay":"301.233214845921","amt_prin_end":"9422.57995454671"},{"num_pmt":"8","amt_prin_init":"9422.57995454671","amt_pmt":"349.852280692891","amt_int_pay":"47.1128997727323","amt_prin_pay":"302.739380920159","amt_prin_end":"9119.84057362655"},{"num_pmt":"9","amt_prin_init":"9119.84057362655","amt_pmt":"349.852280692891","amt_int_pay":"45.5992028681471","amt_prin_pay":"304.253077824744","amt_prin_end":"8815.5874958018"},{"num_pmt":"10","amt_prin_init":"8815.5874958018","amt_pmt":"349.852280692891","amt_int_pay":"44.0779374790185","amt_prin_pay":"305.774343213872","amt_prin_end":"8509.81315258793"},{"num_pmt":"11","amt_prin_init":"8509.81315258793","amt_pmt":"349.852280692891","amt_int_pay":"42.5490657629366","amt_prin_pay":"307.303214929954","amt_prin_end":"8202.50993765798"},{"num_pmt":"12","amt_prin_init":"8202.50993765798","amt_pmt":"349.852280692891","amt_int_pay":"41.0125496883006","amt_prin_pay":"308.83973100459","amt_prin_end":"7893.67020665339"},{"num_pmt":"13","amt_prin_init":"7893.67020665339","amt_pmt":"349.852280692891","amt_int_pay":"39.4683510332738","amt_prin_pay":"310.383929659617","amt_prin_end":"7583.28627699377"},{"num_pmt":"14","amt_prin_init":"7583.28627699377","amt_pmt":"349.852280692891","amt_int_pay":"37.916431384971","amt_prin_pay":"311.93584930792","amt_prin_end":"7271.35042768585"},{"num_pmt":"15","amt_prin_init":"7271.35042768585","amt_pmt":"349.852280692891","amt_int_pay":"36.3567521384397","amt_prin_pay":"313.495528554451","amt_prin_end":"6957.8548991314"},{"num_pmt":"16","amt_prin_init":"6957.8548991314","amt_pmt":"349.852280692891","amt_int_pay":"34.7892744956602","amt_prin_pay":"315.063006197231","amt_prin_end":"6642.79189293417"},{"num_pmt":"17","amt_prin_init":"6642.79189293417","amt_pmt":"349.852280692891","amt_int_pay":"33.2139594646782","amt_prin_pay":"316.638321228213","amt_prin_end":"6326.15357170596"},{"num_pmt":"18","amt_prin_init":"6326.15357170596","amt_pmt":"349.852280692891","amt_int_pay":"31.6307678585358","amt_prin_pay":"318.221512834355","amt_prin_end":"6007.9320588716"},{"num_pmt":"19","amt_prin_init":"6007.9320588716","amt_pmt":"349.852280692891","amt_int_pay":"30.0396602943669","amt_prin_pay":"319.812620398524","amt_prin_end":"5688.11943847308"},{"num_pmt":"20","amt_prin_init":"5688.11943847308","amt_pmt":"349.852280692891","amt_int_pay":"28.4405971923802","amt_prin_pay":"321.411683500511","amt_prin_end":"5366.70775497257"},{"num_pmt":"21","amt_prin_init":"5366.70775497257","amt_pmt":"349.852280692891","amt_int_pay":"26.8335387748697","amt_prin_pay":"323.018741918021","amt_prin_end":"5043.68901305454"},{"num_pmt":"22","amt_prin_init":"5043.68901305454","amt_pmt":"349.852280692891","amt_int_pay":"25.2184450652744","amt_prin_pay":"324.633835627616","amt_prin_end":"4719.05517742693"},{"num_pmt":"23","amt_prin_init":"4719.05517742693","amt_pmt":"349.852280692891","amt_int_pay":"23.5952758871476","amt_prin_pay":"326.257004805743","amt_prin_end":"4392.79817262118"},{"num_pmt":"24","amt_prin_init":"4392.79817262118","amt_pmt":"349.852280692891","amt_int_pay":"21.9639908631106","amt_prin_pay":"327.88828982978","amt_prin_end":"4064.9098827914"},{"num_pmt":"25","amt_prin_init":"4064.9098827914","amt_pmt":"349.852280692891","amt_int_pay":"20.3245494139668","amt_prin_pay":"329.527731278924","amt_prin_end":"3735.38215151248"},{"num_pmt":"26","amt_prin_init":"3735.38215151248","amt_pmt":"349.852280692891","amt_int_pay":"18.6769107575678","amt_prin_pay":"331.175369935323","amt_prin_end":"3404.20678157716"},{"num_pmt":"27","amt_prin_init":"3404.20678157716","amt_pmt":"349.852280692891","amt_int_pay":"17.0210339078835","amt_prin_pay":"332.831246785007","amt_prin_end":"3071.37553479215"},{"num_pmt":"28","amt_prin_init":"3071.37553479215","amt_pmt":"349.852280692891","amt_int_pay":"15.3568776739784","amt_prin_pay":"334.495403018912","amt_prin_end":"2736.88013177324"},{"num_pmt":"29","amt_prin_init":"2736.88013177324","amt_pmt":"349.852280692891","amt_int_pay":"13.6844006588721","amt_prin_pay":"336.167880034019","amt_prin_end":"2400.71225173922"},{"num_pmt":"30","amt_prin_init":"2400.71225173922","amt_pmt":"349.852280692891","amt_int_pay":"12.0035612587039","amt_prin_pay":"337.848719434187","amt_prin_end":"2062.86353230503"},{"num_pmt":"31","amt_prin_init":"2062.86353230503","amt_pmt":"349.852280692891","amt_int_pay":"10.3143176615214","amt_prin_pay":"339.537963031369","amt_prin_end":"1723.32556927366"},{"num_pmt":"32","amt_prin_init":"1723.32556927366","amt_pmt":"349.852280692891","amt_int_pay":"8.61662784638128","amt_prin_pay":"341.23565284651","amt_prin_end":"1382.08991642715"},{"num_pmt":"33","amt_prin_init":"1382.08991642715","amt_pmt":"349.852280692891","amt_int_pay":"6.91044958213996","amt_prin_pay":"342.941831110751","amt_prin_end":"1039.1480853164"},{"num_pmt":"34","amt_prin_init":"1039.1480853164","amt_pmt":"349.852280692891","amt_int_pay":"5.19574042658843","amt_prin_pay":"344.656540266302","amt_prin_end":"694.4915450501"},{"num_pmt":"35","amt_prin_init":"694.4915450501","amt_pmt":"349.852280692891","amt_int_pay":"3.4724577252619","amt_prin_pay":"346.379822967629","amt_prin_end":"348.111722082471"},{"num_pmt":"36","amt_prin_init":"348.111722082471","amt_pmt":"349.852280692891","amt_int_pay":"1.74055861041978","amt_prin_pay":"348.111722082471","amt_prin_end":"0"}]}
Example #2
A loan for 36000 to be amortized over 60 period at a periodic interest rate of 1.5%. The first period is 2/3 the length of a regular period and interest is accrued using the actuarial method.
SELECT *
FROM wct.ODDFSCHED( 0.015, --@Rate
60, --@Nper
-36000, --@PV
0, --@FV
0.5, --@FirstPeriod
'A' --@IntRule
);
This produces the following result.
{"columns":[{"field":"num_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_init","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_int_pay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_pay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_end","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"num_pmt":"0","amt_prin_init":"0","amt_pmt":"0","amt_int_pay":"0","amt_prin_pay":"0","amt_prin_end":"36000"},{"num_pmt":"1","amt_prin_init":"36000","amt_pmt":"643.158410673721","amt_int_pay":"268.995023297793","amt_prin_pay":"374.163387375927","amt_prin_end":"35625.8366126241"},{"num_pmt":"2","amt_prin_init":"35625.8366126241","amt_pmt":"914.163387375931","amt_int_pay":"534.387549189359","amt_prin_pay":"379.775838186571","amt_prin_end":"35246.0607744375"},{"num_pmt":"3","amt_prin_init":"35246.0607744375","amt_pmt":"914.163387375931","amt_int_pay":"528.690911616573","amt_prin_pay":"385.472475759358","amt_prin_end":"34860.5882986781"},{"num_pmt":"4","amt_prin_init":"34860.5882986781","amt_pmt":"914.163387375931","amt_int_pay":"522.908824480171","amt_prin_pay":"391.25456289576","amt_prin_end":"34469.3337357824"},{"num_pmt":"5","amt_prin_init":"34469.3337357824","amt_pmt":"914.163387375931","amt_int_pay":"517.040006036735","amt_prin_pay":"397.123381339195","amt_prin_end":"34072.2103544432"},{"num_pmt":"6","amt_prin_init":"34072.2103544432","amt_pmt":"914.163387375931","amt_int_pay":"511.083155316646","amt_prin_pay":"403.080232059285","amt_prin_end":"33669.1301223839"},{"num_pmt":"7","amt_prin_init":"33669.1301223839","amt_pmt":"914.163387375931","amt_int_pay":"505.036951835758","amt_prin_pay":"409.126435540173","amt_prin_end":"33260.0036868437"},{"num_pmt":"8","amt_prin_init":"33260.0036868437","amt_pmt":"914.163387375931","amt_int_pay":"498.90005530266","amt_prin_pay":"415.263332073271","amt_prin_end":"32844.7403547705"},{"num_pmt":"9","amt_prin_init":"32844.7403547705","amt_pmt":"914.163387375931","amt_int_pay":"492.671105321566","amt_prin_pay":"421.492282054365","amt_prin_end":"32423.2480727161"},{"num_pmt":"10","amt_prin_init":"32423.2480727161","amt_pmt":"914.163387375931","amt_int_pay":"486.348721090751","amt_prin_pay":"427.81466628518","amt_prin_end":"31995.4334064309"},{"num_pmt":"11","amt_prin_init":"31995.4334064309","amt_pmt":"914.163387375931","amt_int_pay":"479.93150109646","amt_prin_pay":"434.231886279471","amt_prin_end":"31561.2015201514"},{"num_pmt":"12","amt_prin_init":"31561.2015201514","amt_pmt":"914.163387375931","amt_int_pay":"473.418022802274","amt_prin_pay":"440.745364573657","amt_prin_end":"31120.4561555778"},{"num_pmt":"13","amt_prin_init":"31120.4561555778","amt_pmt":"914.163387375931","amt_int_pay":"466.806842333666","amt_prin_pay":"447.356545042265","amt_prin_end":"30673.0996105355"},{"num_pmt":"14","amt_prin_init":"30673.0996105355","amt_pmt":"914.163387375931","amt_int_pay":"460.096494158042","amt_prin_pay":"454.066893217889","amt_prin_end":"30219.0327173176"},{"num_pmt":"15","amt_prin_init":"30219.0327173176","amt_pmt":"914.163387375931","amt_int_pay":"453.285490759761","amt_prin_pay":"460.87789661617","amt_prin_end":"29758.1548207015"},{"num_pmt":"16","amt_prin_init":"29758.1548207015","amt_pmt":"914.163387375931","amt_int_pay":"446.37232231053","amt_prin_pay":"467.791065065401","amt_prin_end":"29290.3637556361"},{"num_pmt":"17","amt_prin_init":"29290.3637556361","amt_pmt":"914.163387375931","amt_int_pay":"439.355456334542","amt_prin_pay":"474.807931041389","amt_prin_end":"28815.5558245947"},{"num_pmt":"18","amt_prin_init":"28815.5558245947","amt_pmt":"914.163387375931","amt_int_pay":"432.233337368925","amt_prin_pay":"481.930050007006","amt_prin_end":"28333.6257745877"},{"num_pmt":"19","amt_prin_init":"28333.6257745877","amt_pmt":"914.163387375931","amt_int_pay":"425.004386618817","amt_prin_pay":"489.159000757114","amt_prin_end":"27844.4667738306"},{"num_pmt":"20","amt_prin_init":"27844.4667738306","amt_pmt":"914.163387375931","amt_int_pay":"417.667001607464","amt_prin_pay":"496.496385768467","amt_prin_end":"27347.9703880621"},{"num_pmt":"21","amt_prin_init":"27347.9703880621","amt_pmt":"914.163387375931","amt_int_pay":"410.219555820931","amt_prin_pay":"503.943831555","amt_prin_end":"26844.0265565071"},{"num_pmt":"22","amt_prin_init":"26844.0265565071","amt_pmt":"914.163387375931","amt_int_pay":"402.660398347616","amt_prin_pay":"511.502989028315","amt_prin_end":"26332.5235674788"},{"num_pmt":"23","amt_prin_init":"26332.5235674788","amt_pmt":"914.163387375931","amt_int_pay":"394.987853512179","amt_prin_pay":"519.175533863752","amt_prin_end":"25813.348033615"},{"num_pmt":"24","amt_prin_init":"25813.348033615","amt_pmt":"914.163387375931","amt_int_pay":"387.20022050423","amt_prin_pay":"526.963166871701","amt_prin_end":"25286.3848667433"},{"num_pmt":"25","amt_prin_init":"25286.3848667433","amt_pmt":"914.163387375931","amt_int_pay":"379.295773001151","amt_prin_pay":"534.86761437478","amt_prin_end":"24751.5172523685"},{"num_pmt":"26","amt_prin_init":"24751.5172523685","amt_pmt":"914.163387375931","amt_int_pay":"371.272758785533","amt_prin_pay":"542.890628590398","amt_prin_end":"24208.6266237781"},{"num_pmt":"27","amt_prin_init":"24208.6266237781","amt_pmt":"914.163387375931","amt_int_pay":"363.129399356683","amt_prin_pay":"551.033988019248","amt_prin_end":"23657.5926357589"},{"num_pmt":"28","amt_prin_init":"23657.5926357589","amt_pmt":"914.163387375931","amt_int_pay":"354.863889536383","amt_prin_pay":"559.299497839547","amt_prin_end":"23098.2931379193"},{"num_pmt":"29","amt_prin_init":"23098.2931379193","amt_pmt":"914.163387375931","amt_int_pay":"346.474397068788","amt_prin_pay":"567.688990307142","amt_prin_end":"22530.6041476122"},{"num_pmt":"30","amt_prin_init":"22530.6041476122","amt_pmt":"914.163387375931","amt_int_pay":"337.959062214187","amt_prin_pay":"576.204325161743","amt_prin_end":"21954.3998224505"},{"num_pmt":"31","amt_prin_init":"21954.3998224505","amt_pmt":"914.163387375931","amt_int_pay":"329.315997336762","amt_prin_pay":"584.847390039169","amt_prin_end":"21369.5524324113"},{"num_pmt":"32","amt_prin_init":"21369.5524324113","amt_pmt":"914.163387375931","amt_int_pay":"320.543286486172","amt_prin_pay":"593.620100889759","amt_prin_end":"20775.9323315215"},{"num_pmt":"33","amt_prin_init":"20775.9323315215","amt_pmt":"914.163387375931","amt_int_pay":"311.638984972829","amt_prin_pay":"602.524402403102","amt_prin_end":"20173.4079291184"},{"num_pmt":"34","amt_prin_init":"20173.4079291184","amt_pmt":"914.163387375931","amt_int_pay":"302.601118936782","amt_prin_pay":"611.562268439149","amt_prin_end":"19561.8456606793"},{"num_pmt":"35","amt_prin_init":"19561.8456606793","amt_pmt":"914.163387375931","amt_int_pay":"293.427684910191","amt_prin_pay":"620.735702465739","amt_prin_end":"18941.1099582135"},{"num_pmt":"36","amt_prin_init":"18941.1099582135","amt_pmt":"914.163387375931","amt_int_pay":"284.116649373209","amt_prin_pay":"630.046738002722","amt_prin_end":"18311.0632202108"},{"num_pmt":"37","amt_prin_init":"18311.0632202108","amt_pmt":"914.163387375931","amt_int_pay":"274.665948303165","amt_prin_pay":"639.497439072766","amt_prin_end":"17671.5657811381"},{"num_pmt":"38","amt_prin_init":"17671.5657811381","amt_pmt":"914.163387375931","amt_int_pay":"265.073486717076","amt_prin_pay":"649.089900658855","amt_prin_end":"17022.4758804792"},{"num_pmt":"39","amt_prin_init":"17022.4758804792","amt_pmt":"914.163387375931","amt_int_pay":"255.337138207198","amt_prin_pay":"658.826249168733","amt_prin_end":"16363.6496313105"},{"num_pmt":"40","amt_prin_init":"16363.6496313105","amt_pmt":"914.163387375931","amt_int_pay":"245.45474446966","amt_prin_pay":"668.708642906271","amt_prin_end":"15694.9409884042"},{"num_pmt":"41","amt_prin_init":"15694.9409884042","amt_pmt":"914.163387375931","amt_int_pay":"235.424114826062","amt_prin_pay":"678.739272549868","amt_prin_end":"15016.2017158543"},{"num_pmt":"42","amt_prin_init":"15016.2017158543","amt_pmt":"914.163387375931","amt_int_pay":"225.243025737822","amt_prin_pay":"688.920361638109","amt_prin_end":"14327.2813542162"},{"num_pmt":"43","amt_prin_init":"14327.2813542162","amt_pmt":"914.163387375931","amt_int_pay":"214.909220313249","amt_prin_pay":"699.254167062682","amt_prin_end":"13628.0271871535"},{"num_pmt":"44","amt_prin_init":"13628.0271871535","amt_pmt":"914.163387375931","amt_int_pay":"204.420407807309","amt_prin_pay":"709.742979568622","amt_prin_end":"12918.2842075849"},{"num_pmt":"45","amt_prin_init":"12918.2842075849","amt_pmt":"914.163387375931","amt_int_pay":"193.77426311378","amt_prin_pay":"720.38912426215","amt_prin_end":"12197.8950833228"},{"num_pmt":"46","amt_prin_init":"12197.8950833228","amt_pmt":"914.163387375931","amt_int_pay":"182.968426249839","amt_prin_pay":"731.194961126092","amt_prin_end":"11466.7001221967"},{"num_pmt":"47","amt_prin_init":"11466.7001221967","amt_pmt":"914.163387375931","amt_int_pay":"172.000501832963","amt_prin_pay":"742.162885542968","amt_prin_end":"10724.5372366537"},{"num_pmt":"48","amt_prin_init":"10724.5372366537","amt_pmt":"914.163387375931","amt_int_pay":"160.868058549809","amt_prin_pay":"753.295328826121","amt_prin_end":"9971.24190782758"},{"num_pmt":"49","amt_prin_init":"9971.24190782758","amt_pmt":"914.163387375931","amt_int_pay":"149.568628617416","amt_prin_pay":"764.594758758514","amt_prin_end":"9206.64714906907"},{"num_pmt":"50","amt_prin_init":"9206.64714906907","amt_pmt":"914.163387375931","amt_int_pay":"138.099707236042","amt_prin_pay":"776.063680139889","amt_prin_end":"8430.58346892918"},{"num_pmt":"51","amt_prin_init":"8430.58346892918","amt_pmt":"914.163387375931","amt_int_pay":"126.458752033943","amt_prin_pay":"787.704635341987","amt_prin_end":"7642.87883358719"},{"num_pmt":"52","amt_prin_init":"7642.87883358719","amt_pmt":"914.163387375931","amt_int_pay":"114.643182503816","amt_prin_pay":"799.520204872115","amt_prin_end":"6843.35862871508"},{"num_pmt":"53","amt_prin_init":"6843.35862871508","amt_pmt":"914.163387375931","amt_int_pay":"102.65037943073","amt_prin_pay":"811.513007945201","amt_prin_end":"6031.84562076988"},{"num_pmt":"54","amt_prin_init":"6031.84562076988","amt_pmt":"914.163387375931","amt_int_pay":"90.4776843115466","amt_prin_pay":"823.685703064384","amt_prin_end":"5208.15991770549"},{"num_pmt":"55","amt_prin_init":"5208.15991770549","amt_pmt":"914.163387375931","amt_int_pay":"78.1223987655936","amt_prin_pay":"836.040988610337","amt_prin_end":"4372.11892909516"},{"num_pmt":"56","amt_prin_init":"4372.11892909516","amt_pmt":"914.163387375931","amt_int_pay":"65.5817839364341","amt_prin_pay":"848.581603439497","amt_prin_end":"3523.53732565566"},{"num_pmt":"57","amt_prin_init":"3523.53732565566","amt_pmt":"914.163387375931","amt_int_pay":"52.8530598848316","amt_prin_pay":"861.310327491099","amt_prin_end":"2662.22699816456"},{"num_pmt":"58","amt_prin_init":"2662.22699816456","amt_pmt":"914.163387375931","amt_int_pay":"39.9334049724752","amt_prin_pay":"874.229982403456","amt_prin_end":"1787.9970157611"},{"num_pmt":"59","amt_prin_init":"1787.9970157611","amt_pmt":"914.163387375931","amt_int_pay":"26.8199552364267","amt_prin_pay":"887.343432139504","amt_prin_end":"900.653583621601"},{"num_pmt":"60","amt_prin_init":"900.653583621601","amt_pmt":"914.163387375931","amt_int_pay":"13.5098037543298","amt_prin_pay":"900.653583621601","amt_prin_end":"0"}]}
Example #3
In this example the principal amount is 25,000, the first period is 1.5 times the length of a regular period, the periodic rate is .75% and there will be a 5,000 balance at the end of the amortization. Interest is calculated using the US rule.
SELECT *
FROM wct.ODDFSCHED( 0.0075, --@Rate
60, --@Nper
-25000, --@PV
5000, --@FV
1.5, --@FirstPeriod
'U' --@IntRule
);
This produces the following result.
{"columns":[{"field":"num_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_init","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_pmt","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_int_pay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_pay","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"amt_prin_end","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"num_pmt":"0","amt_prin_init":"0","amt_pmt":"0","amt_int_pay":"0","amt_prin_pay":"0","amt_prin_end":"25000"},{"num_pmt":"1","amt_prin_init":"25000","amt_pmt":"546.417104527081","amt_int_pay":"281.25","amt_prin_pay":"265.167104527081","amt_prin_end":"24734.8328954729"},{"num_pmt":"2","amt_prin_init":"24734.8328954729","amt_pmt":"452.667104527077","amt_int_pay":"185.51124671605","amt_prin_pay":"267.155857811027","amt_prin_end":"24467.6770376619"},{"num_pmt":"3","amt_prin_init":"24467.6770376619","amt_pmt":"452.667104527077","amt_int_pay":"183.507577782457","amt_prin_pay":"269.159526744621","amt_prin_end":"24198.5175109173"},{"num_pmt":"4","amt_prin_init":"24198.5175109173","amt_pmt":"452.667104527077","amt_int_pay":"181.488881331875","amt_prin_pay":"271.178223195202","amt_prin_end":"23927.3392877221"},{"num_pmt":"5","amt_prin_init":"23927.3392877221","amt_pmt":"452.667104527077","amt_int_pay":"179.455044657916","amt_prin_pay":"273.212059869162","amt_prin_end":"23654.1272278529"},{"num_pmt":"6","amt_prin_init":"23654.1272278529","amt_pmt":"452.667104527077","amt_int_pay":"177.405954208894","amt_prin_pay":"275.261150318183","amt_prin_end":"23378.8660775347"},{"num_pmt":"7","amt_prin_init":"23378.8660775347","amt_pmt":"452.667104527077","amt_int_pay":"175.341495581509","amt_prin_pay":"277.325608945568","amt_prin_end":"23101.5404685892"},{"num_pmt":"8","amt_prin_init":"23101.5404685892","amt_pmt":"452.667104527077","amt_int_pay":"173.261553514414","amt_prin_pay":"279.405551012664","amt_prin_end":"22822.1349175765"},{"num_pmt":"9","amt_prin_init":"22822.1349175765","amt_pmt":"452.667104527077","amt_int_pay":"171.166011881825","amt_prin_pay":"281.501092645252","amt_prin_end":"22540.6338249312"},{"num_pmt":"10","amt_prin_init":"22540.6338249312","amt_pmt":"452.667104527077","amt_int_pay":"169.054753686978","amt_prin_pay":"283.6123508401","amt_prin_end":"22257.0214740911"},{"num_pmt":"11","amt_prin_init":"22257.0214740911","amt_pmt":"452.667104527077","amt_int_pay":"166.927661055681","amt_prin_pay":"285.739443471397","amt_prin_end":"21971.2820306197"},{"num_pmt":"12","amt_prin_init":"21971.2820306197","amt_pmt":"452.667104527077","amt_int_pay":"164.784615229651","amt_prin_pay":"287.882489297426","amt_prin_end":"21683.3995413223"},{"num_pmt":"13","amt_prin_init":"21683.3995413223","amt_pmt":"452.667104527077","amt_int_pay":"162.625496559911","amt_prin_pay":"290.041607967167","amt_prin_end":"21393.3579333552"},{"num_pmt":"14","amt_prin_init":"21393.3579333552","amt_pmt":"452.667104527077","amt_int_pay":"160.450184500165","amt_prin_pay":"292.216920026913","amt_prin_end":"21101.1410133282"},{"num_pmt":"15","amt_prin_init":"21101.1410133282","amt_pmt":"452.667104527077","amt_int_pay":"158.258557599962","amt_prin_pay":"294.408546927116","amt_prin_end":"20806.7324664011"},{"num_pmt":"16","amt_prin_init":"20806.7324664011","amt_pmt":"452.667104527077","amt_int_pay":"156.050493498001","amt_prin_pay":"296.616611029076","amt_prin_end":"20510.115855372"},{"num_pmt":"17","amt_prin_init":"20510.115855372","amt_pmt":"452.667104527077","amt_int_pay":"153.82586891529","amt_prin_pay":"298.841235611788","amt_prin_end":"20211.2746197603"},{"num_pmt":"18","amt_prin_init":"20211.2746197603","amt_pmt":"452.667104527077","amt_int_pay":"151.584559648199","amt_prin_pay":"301.082544878878","amt_prin_end":"19910.1920748814"},{"num_pmt":"19","amt_prin_init":"19910.1920748814","amt_pmt":"452.667104527077","amt_int_pay":"149.326440561613","amt_prin_pay":"303.340663965464","amt_prin_end":"19606.8514109159"},{"num_pmt":"20","amt_prin_init":"19606.8514109159","amt_pmt":"452.667104527077","amt_int_pay":"147.051385581864","amt_prin_pay":"305.615718945213","amt_prin_end":"19301.2356919707"},{"num_pmt":"21","amt_prin_init":"19301.2356919707","amt_pmt":"452.667104527077","amt_int_pay":"144.759267689773","amt_prin_pay":"307.907836837305","amt_prin_end":"18993.3278551334"},{"num_pmt":"22","amt_prin_init":"18993.3278551334","amt_pmt":"452.667104527077","amt_int_pay":"142.449958913497","amt_prin_pay":"310.21714561358","amt_prin_end":"18683.1107095198"},{"num_pmt":"23","amt_prin_init":"18683.1107095198","amt_pmt":"452.667104527077","amt_int_pay":"140.123330321399","amt_prin_pay":"312.543774205678","amt_prin_end":"18370.5669353141"},{"num_pmt":"24","amt_prin_init":"18370.5669353141","amt_pmt":"452.667104527077","amt_int_pay":"137.779252014854","amt_prin_pay":"314.887852512224","amt_prin_end":"18055.6790828019"},{"num_pmt":"25","amt_prin_init":"18055.6790828019","amt_pmt":"452.667104527077","amt_int_pay":"135.417593121008","amt_prin_pay":"317.249511406069","amt_prin_end":"17738.4295713958"},{"num_pmt":"26","amt_prin_init":"17738.4295713958","amt_pmt":"452.667104527077","amt_int_pay":"133.038221785467","amt_prin_pay":"319.62888274161","amt_prin_end":"17418.8006886542"},{"num_pmt":"27","amt_prin_init":"17418.8006886542","amt_pmt":"452.667104527077","amt_int_pay":"130.641005164903","amt_prin_pay":"322.026099362174","amt_prin_end":"17096.7745892921"},{"num_pmt":"28","amt_prin_init":"17096.7745892921","amt_pmt":"452.667104527077","amt_int_pay":"128.225809419687","amt_prin_pay":"324.44129510739","amt_prin_end":"16772.3332941847"},{"num_pmt":"29","amt_prin_init":"16772.3332941847","amt_pmt":"452.667104527077","amt_int_pay":"125.792499706385","amt_prin_pay":"326.874604820692","amt_prin_end":"16445.458689364"},{"num_pmt":"30","amt_prin_init":"16445.458689364","amt_pmt":"452.667104527077","amt_int_pay":"123.340940170226","amt_prin_pay":"329.326164356851","amt_prin_end":"16116.1325250071"},{"num_pmt":"31","amt_prin_init":"16116.1325250071","amt_pmt":"452.667104527077","amt_int_pay":"120.870993937555","amt_prin_pay":"331.796110589523","amt_prin_end":"15784.3364144176"},{"num_pmt":"32","amt_prin_init":"15784.3364144176","amt_pmt":"452.667104527077","amt_int_pay":"118.382523108121","amt_prin_pay":"334.284581418957","amt_prin_end":"15450.0518329986"},{"num_pmt":"33","amt_prin_init":"15450.0518329986","amt_pmt":"452.667104527077","amt_int_pay":"115.875388747488","amt_prin_pay":"336.791715779589","amt_prin_end":"15113.2601172191"},{"num_pmt":"34","amt_prin_init":"15113.2601172191","amt_pmt":"452.667104527077","amt_int_pay":"113.349450879141","amt_prin_pay":"339.317653647937","amt_prin_end":"14773.9424635711"},{"num_pmt":"35","amt_prin_init":"14773.9424635711","amt_pmt":"452.667104527077","amt_int_pay":"110.804568476786","amt_prin_pay":"341.862536050292","amt_prin_end":"14432.0799275208"},{"num_pmt":"36","amt_prin_init":"14432.0799275208","amt_pmt":"452.667104527077","amt_int_pay":"108.240599456399","amt_prin_pay":"344.426505070678","amt_prin_end":"14087.6534224502"},{"num_pmt":"37","amt_prin_init":"14087.6534224502","amt_pmt":"452.667104527077","amt_int_pay":"105.657400668377","amt_prin_pay":"347.009703858701","amt_prin_end":"13740.6437185915"},{"num_pmt":"38","amt_prin_init":"13740.6437185915","amt_pmt":"452.667104527077","amt_int_pay":"103.054827889427","amt_prin_pay":"349.61227663765","amt_prin_end":"13391.0314419538"},{"num_pmt":"39","amt_prin_init":"13391.0314419538","amt_pmt":"452.667104527077","amt_int_pay":"100.43273581466","amt_prin_pay":"352.234368712418","amt_prin_end":"13038.7970732414"},{"num_pmt":"40","amt_prin_init":"13038.7970732414","amt_pmt":"452.667104527077","amt_int_pay":"97.7909780493007","amt_prin_pay":"354.876126477777","amt_prin_end":"12683.9209467636"},{"num_pmt":"41","amt_prin_init":"12683.9209467636","amt_pmt":"452.667104527077","amt_int_pay":"95.1294071007266","amt_prin_pay":"357.537697426351","amt_prin_end":"12326.3832493373"},{"num_pmt":"42","amt_prin_init":"12326.3832493373","amt_pmt":"452.667104527077","amt_int_pay":"92.4478743700235","amt_prin_pay":"360.219230157054","amt_prin_end":"11966.1640191802"},{"num_pmt":"43","amt_prin_init":"11966.1640191802","amt_pmt":"452.667104527077","amt_int_pay":"89.7462301438515","amt_prin_pay":"362.920874383226","amt_prin_end":"11603.243144797"},{"num_pmt":"44","amt_prin_init":"11603.243144797","amt_pmt":"452.667104527077","amt_int_pay":"87.0243235859722","amt_prin_pay":"365.642780941105","amt_prin_end":"11237.6003638559"},{"num_pmt":"45","amt_prin_init":"11237.6003638559","amt_pmt":"452.667104527077","amt_int_pay":"84.282002728916","amt_prin_pay":"368.385101798161","amt_prin_end":"10869.2152620577"},{"num_pmt":"46","amt_prin_init":"10869.2152620577","amt_pmt":"452.667104527077","amt_int_pay":"81.5191144654324","amt_prin_pay":"371.147990061645","amt_prin_end":"10498.0672719961"},{"num_pmt":"47","amt_prin_init":"10498.0672719961","amt_pmt":"452.667104527077","amt_int_pay":"78.7355045399651","amt_prin_pay":"373.931599987112","amt_prin_end":"10124.135672009"},{"num_pmt":"48","amt_prin_init":"10124.135672009","amt_pmt":"452.667104527077","amt_int_pay":"75.9310175400639","amt_prin_pay":"376.736086987014","amt_prin_end":"9747.39958502194"},{"num_pmt":"49","amt_prin_init":"9747.39958502194","amt_pmt":"452.667104527077","amt_int_pay":"73.1054968876558","amt_prin_pay":"379.561607639422","amt_prin_end":"9367.83797738252"},{"num_pmt":"50","amt_prin_init":"9367.83797738252","amt_pmt":"452.667104527077","amt_int_pay":"70.2587848303735","amt_prin_pay":"382.408319696704","amt_prin_end":"8985.42965768581"},{"num_pmt":"51","amt_prin_init":"8985.42965768581","amt_pmt":"452.667104527077","amt_int_pay":"67.3907224326355","amt_prin_pay":"385.276382094442","amt_prin_end":"8600.15327559137"},{"num_pmt":"52","amt_prin_init":"8600.15327559137","amt_pmt":"452.667104527077","amt_int_pay":"64.5011495669348","amt_prin_pay":"388.165954960143","amt_prin_end":"8211.98732063123"},{"num_pmt":"53","amt_prin_init":"8211.98732063123","amt_pmt":"452.667104527077","amt_int_pay":"61.5899049047354","amt_prin_pay":"391.077199622342","amt_prin_end":"7820.91012100889"},{"num_pmt":"54","amt_prin_init":"7820.91012100889","amt_pmt":"452.667104527077","amt_int_pay":"58.656825907559","amt_prin_pay":"394.010278619518","amt_prin_end":"7426.89984238937"},{"num_pmt":"55","amt_prin_init":"7426.89984238937","amt_pmt":"452.667104527077","amt_int_pay":"55.7017488179187","amt_prin_pay":"396.965355709159","amt_prin_end":"7029.93448668021"},{"num_pmt":"56","amt_prin_init":"7029.93448668021","amt_pmt":"452.667104527077","amt_int_pay":"52.7245086500913","amt_prin_pay":"399.942595876986","amt_prin_end":"6629.99189080322"},{"num_pmt":"57","amt_prin_init":"6629.99189080322","amt_pmt":"452.667104527077","amt_int_pay":"49.7249391810316","amt_prin_pay":"402.942165346046","amt_prin_end":"6227.04972545718"},{"num_pmt":"58","amt_prin_init":"6227.04972545718","amt_pmt":"452.667104527077","amt_int_pay":"46.7028729409255","amt_prin_pay":"405.964231586152","amt_prin_end":"5821.08549387102"},{"num_pmt":"59","amt_prin_init":"5821.08549387102","amt_pmt":"452.667104527077","amt_int_pay":"43.6581412040238","amt_prin_pay":"409.008963323054","amt_prin_end":"5412.07653054797"},{"num_pmt":"60","amt_prin_init":"5412.07653054797","amt_pmt":"452.667104527077","amt_int_pay":"40.5905739791066","amt_prin_pay":"412.076530547971","amt_prin_end":"5000"}]}
See Also
ODDFPMTSCHED - Amortization schedule for an annuity with odd first period
PMT - Calculate the periodic payment for an annuity
PMTSCHED - Calculate an amortization schedule for a loan with no odd periods.