PMTSCHED
Updated 2023-10-06 23:12:52.507000
Syntax
SELECT * FROM [westclintech].[wct].[PMTSCHED] (
<@PV, float,>
,<@pmt, float,>
,<@NumPmts, int,>
,<@FV, float,>
,<@Pay_type, float,>)
Description
Use the table-valued function PMTSCHED to generate an amortization schedule for a loan with no odd periods. The amortization schedule includes the payment number, the principal amount at the beginning of the period, the interest amount for the period, the principal payment for the period, and the ending principal amount.
Arguments
@Pay_type
Identifies whether payments are made at the beginning of the period (1) or at the end of the period (0).
@pmt
The periodic loan payment. @pmt is an expression of type float or of a type that can be implicitly converted to float.
@NumPmts
the total number of payments to be recorded over the life of the loan. @NumPmts is an expression of type int or of a type that can be implicitly converted to int.
@FV
the future value at the end of the loan. @FV is an expression of type float or of a type that can be implicitly converted to float.
@PV
the principal amount of the loan or lease. @PV is an expression 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": "ee930c50-97c7-4c7d-aac9-55a484ccd50c", "colName": "num_pmt", "colDatatype": "int", "colDesc": "The payment number."}, {"id": "2df11545-2603-41ff-8b7e-0ba0f6832914", "colName": "amt_prin_init", "colDatatype": "float", "colDesc": "The principal amount at the beginning of the period. For the first period, the principal amount is the amount of the loan, otherwise the principal amount is the ending principal amount from the prior period."}, {"id": "368de213-2aac-4f9c-9db8-ad5516a33aa2", "colName": "amt_int_pay", "colDatatype": "float", "colDesc": "The interest payment amount for the period. The interest payment amount is equal to amt_prin_int \u2013 amt_prin_end - @pmt"}, {"id": "1ce70aa2-ec0b-42a9-9b61-95c07c11f45d", "colName": "amt_prin_pay", "colDatatype": "float", "colDesc": "The principal payment amount for the period. The principal payment amount is equal to amt_prin_init \u2013 amt_prin_end."}, {"id": "47aa34a5-bd95-4f0f-839d-4f000bffebb6", "colName": "amt_prin_end", "colDatatype": "float", "colDesc": "The ending principal amount. The ending principal amount is the present value of the remaining payments discounted using the implied rate from @pmt."}]}
Remarks
@PV, @pmt, and @FV (when it is not zero) should have the same sign.
The rate value is equal to wct.RATE(@Numpmts,-@pmt,@PV,-@FV,@Pay_type,NULL).
Examples
Generate an amortization schedule for loan with a principal amount of 100,000 with 12 payments of 8606.64.
SELECT *
FROM wct.PMTSCHED( 100000, --pv
8606.64, --pmt
12, --numpmts
0, --FV
0 --pay_type
);
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_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":"1","amt_prin_init":"100000","amt_int_pay":"499.994613980976","amt_prin_pay":"8106.64538601902","amt_prin_end":"91893.354613981"},{"num_pmt":"2","amt_prin_init":"91893.354613981","amt_int_pay":"459.461823674021","amt_prin_pay":"8147.17817632598","amt_prin_end":"83746.176437655"},{"num_pmt":"3","amt_prin_init":"83746.176437655","amt_int_pay":"418.726371601093","amt_prin_pay":"8187.91362839891","amt_prin_end":"75558.2628092561"},{"num_pmt":"4","amt_prin_init":"75558.2628092561","amt_int_pay":"377.787244461579","amt_prin_pay":"8228.85275553842","amt_prin_end":"67329.4100537177"},{"num_pmt":"5","amt_prin_init":"67329.4100537177","amt_int_pay":"336.643423891961","amt_prin_pay":"8269.99657610804","amt_prin_end":"59059.4134776096"},{"num_pmt":"6","amt_prin_init":"59059.4134776096","amt_int_pay":"295.29388643532","amt_prin_pay":"8311.34611356468","amt_prin_end":"50748.067364045"},{"num_pmt":"7","amt_prin_init":"50748.067364045","amt_int_pay":"253.737603518282","amt_prin_pay":"8352.90239648172","amt_prin_end":"42395.1649675632"},{"num_pmt":"8","amt_prin_init":"42395.1649675632","amt_int_pay":"211.973541424944","amt_prin_pay":"8394.66645857506","amt_prin_end":"34000.4985089882"},{"num_pmt":"9","amt_prin_init":"34000.4985089882","amt_int_pay":"170.000661270562","amt_prin_pay":"8436.63933872944","amt_prin_end":"25563.8591702587"},{"num_pmt":"10","amt_prin_init":"25563.8591702587","amt_int_pay":"127.817918976172","amt_prin_pay":"8478.82208102383","amt_prin_end":"17085.0370892349"},{"num_pmt":"11","amt_prin_init":"17085.0370892349","amt_int_pay":"85.4242652420508","amt_prin_pay":"8521.21573475795","amt_prin_end":"8563.82135447697"},{"num_pmt":"12","amt_prin_init":"8563.82135447697","amt_int_pay":"42.8186455230316","amt_prin_pay":"8563.82135447697","amt_prin_end":"0"}]}
Generate an amortization schedule for loan with a principal amount of 32,000 with 60 payments of 600.95.
SELECT *
FROM wct.PMTSCHED( 32000, --pv
600.95, --pmt
60, --numpmts
0, --FV
0 --pay_type
);
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_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":"1","amt_prin_init":"32000","amt_int_pay":"127.996817638387","amt_prin_pay":"472.953182361613","amt_prin_end":"31527.0468176384"},{"num_pmt":"2","amt_prin_init":"31527.0468176384","amt_int_pay":"126.105051955211","amt_prin_pay":"474.844948044789","amt_prin_end":"31052.2018695936"},{"num_pmt":"3","amt_prin_init":"31052.2018695936","amt_int_pay":"124.205719385619","amt_prin_pay":"476.744280614381","amt_prin_end":"30575.4575889792"},{"num_pmt":"4","amt_prin_init":"30575.4575889792","amt_int_pay":"122.298789674637","amt_prin_pay":"478.651210325363","amt_prin_end":"30096.8063786539"},{"num_pmt":"5","amt_prin_init":"30096.8063786539","amt_int_pay":"120.384232434433","amt_prin_pay":"480.565767565568","amt_prin_end":"29616.2406110883"},{"num_pmt":"6","amt_prin_init":"29616.2406110883","amt_int_pay":"118.462017155692","amt_prin_pay":"482.487982844308","amt_prin_end":"29133.752628244"},{"num_pmt":"7","amt_prin_init":"29133.752628244","amt_int_pay":"116.532113206983","amt_prin_pay":"484.417886793017","amt_prin_end":"28649.334741451"},{"num_pmt":"8","amt_prin_init":"28649.334741451","amt_int_pay":"114.594489834405","amt_prin_pay":"486.355510165595","amt_prin_end":"28162.9792312854"},{"num_pmt":"9","amt_prin_init":"28162.9792312854","amt_int_pay":"112.649116161035","amt_prin_pay":"488.300883838965","amt_prin_end":"27674.6783474464"},{"num_pmt":"10","amt_prin_init":"27674.6783474464","amt_int_pay":"110.695961186432","amt_prin_pay":"490.254038813568","amt_prin_end":"27184.4243086328"},{"num_pmt":"11","amt_prin_init":"27184.4243086328","amt_int_pay":"108.734993786205","amt_prin_pay":"492.215006213795","amt_prin_end":"26692.209302419"},{"num_pmt":"12","amt_prin_init":"26692.209302419","amt_int_pay":"106.766182711336","amt_prin_pay":"494.183817288664","amt_prin_end":"26198.0254851304"},{"num_pmt":"13","amt_prin_init":"26198.0254851304","amt_int_pay":"104.789496587985","amt_prin_pay":"496.160503412015","amt_prin_end":"25701.8649817184"},{"num_pmt":"14","amt_prin_init":"25701.8649817184","amt_int_pay":"102.804903916727","amt_prin_pay":"498.145096083274","amt_prin_end":"25203.7198856351"},{"num_pmt":"15","amt_prin_init":"25203.7198856351","amt_int_pay":"100.812373072116","amt_prin_pay":"500.137626927884","amt_prin_end":"24703.5822587072"},{"num_pmt":"16","amt_prin_init":"24703.5822587072","amt_int_pay":"98.8118723023392","amt_prin_pay":"502.138127697661","amt_prin_end":"24201.4441310095"},{"num_pmt":"17","amt_prin_init":"24201.4441310095","amt_int_pay":"96.8033697283856","amt_prin_pay":"504.146630271614","amt_prin_end":"23697.2975007379"},{"num_pmt":"18","amt_prin_init":"23697.2975007379","amt_int_pay":"94.7868333438994","amt_prin_pay":"506.163166656101","amt_prin_end":"23191.1343340818"},{"num_pmt":"19","amt_prin_init":"23191.1343340818","amt_int_pay":"92.7622310143954","amt_prin_pay":"508.187768985605","amt_prin_end":"22682.9465650962"},{"num_pmt":"20","amt_prin_init":"22682.9465650962","amt_int_pay":"90.7295304769389","amt_prin_pay":"510.220469523061","amt_prin_end":"22172.7260955732"},{"num_pmt":"21","amt_prin_init":"22172.7260955732","amt_int_pay":"88.6886993394467","amt_prin_pay":"512.261300660553","amt_prin_end":"21660.4647949126"},{"num_pmt":"22","amt_prin_init":"21660.4647949126","amt_int_pay":"86.639705080411","amt_prin_pay":"514.310294919589","amt_prin_end":"21146.154499993"},{"num_pmt":"23","amt_prin_init":"21146.154499993","amt_int_pay":"84.5825150480698","amt_prin_pay":"516.36748495193","amt_prin_end":"20629.7870150411"},{"num_pmt":"24","amt_prin_init":"20629.7870150411","amt_int_pay":"82.5170964602178","amt_prin_pay":"518.432903539782","amt_prin_end":"20111.3541115013"},{"num_pmt":"25","amt_prin_init":"20111.3541115013","amt_int_pay":"80.4434164033912","amt_prin_pay":"520.506583596609","amt_prin_end":"19590.8475279047"},{"num_pmt":"26","amt_prin_init":"19590.8475279047","amt_int_pay":"78.3614418325772","amt_prin_pay":"522.588558167423","amt_prin_end":"19068.2589697373"},{"num_pmt":"27","amt_prin_init":"19068.2589697373","amt_int_pay":"76.2711395705003","amt_prin_pay":"524.6788604295","amt_prin_end":"18543.5801093078"},{"num_pmt":"28","amt_prin_init":"18543.5801093078","amt_int_pay":"74.1724763072737","amt_prin_pay":"526.777523692726","amt_prin_end":"18016.802585615"},{"num_pmt":"29","amt_prin_init":"18016.802585615","amt_int_pay":"72.0654185997148","amt_prin_pay":"528.884581400285","amt_prin_end":"17487.9180042148"},{"num_pmt":"30","amt_prin_init":"17487.9180042148","amt_int_pay":"69.9499328708218","amt_prin_pay":"531.000067129178","amt_prin_end":"16956.9179370856"},{"num_pmt":"31","amt_prin_init":"16956.9179370856","amt_int_pay":"67.8259854094533","amt_prin_pay":"533.124014590547","amt_prin_end":"16423.793922495"},{"num_pmt":"32","amt_prin_init":"16423.793922495","amt_int_pay":"65.6935423694551","amt_prin_pay":"535.256457630545","amt_prin_end":"15888.5374648645"},{"num_pmt":"33","amt_prin_init":"15888.5374648645","amt_int_pay":"63.5525697693113","amt_prin_pay":"537.397430230689","amt_prin_end":"15351.1400346338"},{"num_pmt":"34","amt_prin_init":"15351.1400346338","amt_int_pay":"61.4030334917363","amt_prin_pay":"539.546966508264","amt_prin_end":"14811.5930681255"},{"num_pmt":"35","amt_prin_init":"14811.5930681255","amt_int_pay":"59.2448992828024","amt_prin_pay":"541.705100717198","amt_prin_end":"14269.8879674083"},{"num_pmt":"36","amt_prin_init":"14269.8879674083","amt_int_pay":"57.0781327516481","amt_prin_pay":"543.871867248352","amt_prin_end":"13726.01610016"},{"num_pmt":"37","amt_prin_init":"13726.01610016","amt_int_pay":"54.9026993698674","amt_prin_pay":"546.047300630133","amt_prin_end":"13179.9687995299"},{"num_pmt":"38","amt_prin_init":"13179.9687995299","amt_int_pay":"52.7185644708982","amt_prin_pay":"548.231435529102","amt_prin_end":"12631.7373640008"},{"num_pmt":"39","amt_prin_init":"12631.7373640008","amt_int_pay":"50.5256932495279","amt_prin_pay":"550.424306750472","amt_prin_end":"12081.3130572503"},{"num_pmt":"40","amt_prin_init":"12081.3130572503","amt_int_pay":"48.3240507613693","amt_prin_pay":"552.625949238631","amt_prin_end":"11528.6871080117"},{"num_pmt":"41","amt_prin_init":"11528.6871080117","amt_int_pay":"46.1136019221915","amt_prin_pay":"554.836398077809","amt_prin_end":"10973.8507099338"},{"num_pmt":"42","amt_prin_init":"10973.8507099338","amt_int_pay":"43.8943115074828","amt_prin_pay":"557.055688492517","amt_prin_end":"10416.7950214413"},{"num_pmt":"43","amt_prin_init":"10416.7950214413","amt_int_pay":"41.6661441518402","amt_prin_pay":"559.28385584816","amt_prin_end":"9857.51116559317"},{"num_pmt":"44","amt_prin_init":"9857.51116559317","amt_int_pay":"39.4290643483284","amt_prin_pay":"561.520935651672","amt_prin_end":"9295.9902299415"},{"num_pmt":"45","amt_prin_init":"9295.9902299415","amt_int_pay":"37.1830364481314","amt_prin_pay":"563.766963551869","amt_prin_end":"8732.22326638963"},{"num_pmt":"46","amt_prin_init":"8732.22326638963","amt_int_pay":"34.9280246596493","amt_prin_pay":"566.021975340351","amt_prin_end":"8166.20129104928"},{"num_pmt":"47","amt_prin_init":"8166.20129104928","amt_int_pay":"32.6639930482663","amt_prin_pay":"568.286006951734","amt_prin_end":"7597.91528409754"},{"num_pmt":"48","amt_prin_init":"7597.91528409754","amt_int_pay":"30.3909055356228","amt_prin_pay":"570.559094464377","amt_prin_end":"7027.35618963317"},{"num_pmt":"49","amt_prin_init":"7027.35618963317","amt_int_pay":"28.1087258989749","amt_prin_pay":"572.841274101025","amt_prin_end":"6454.51491553214"},{"num_pmt":"50","amt_prin_init":"6454.51491553214","amt_int_pay":"25.817417770729","amt_prin_pay":"575.132582229271","amt_prin_end":"5879.38233330287"},{"num_pmt":"51","amt_prin_init":"5879.38233330287","amt_int_pay":"23.5169446378598","amt_prin_pay":"577.43305536214","amt_prin_end":"5301.94927794073"},{"num_pmt":"52","amt_prin_init":"5301.94927794073","amt_int_pay":"21.2072698412114","amt_prin_pay":"579.742730158789","amt_prin_end":"4722.20654778194"},{"num_pmt":"53","amt_prin_init":"4722.20654778194","amt_int_pay":"18.8883565750905","amt_prin_pay":"582.06164342491","amt_prin_end":"4140.14490435703"},{"num_pmt":"54","amt_prin_init":"4140.14490435703","amt_int_pay":"16.5601678864798","amt_prin_pay":"584.38983211352","amt_prin_end":"3555.75507224351"},{"num_pmt":"55","amt_prin_init":"3555.75507224351","amt_int_pay":"14.2226666746894","amt_prin_pay":"586.727333325311","amt_prin_end":"2969.0277389182"},{"num_pmt":"56","amt_prin_init":"2969.0277389182","amt_int_pay":"11.8758156905126","amt_prin_pay":"589.074184309487","amt_prin_end":"2379.95355460871"},{"num_pmt":"57","amt_prin_init":"2379.95355460871","amt_int_pay":"9.51957753578904","amt_prin_pay":"591.430422464211","amt_prin_end":"1788.5231321445"},{"num_pmt":"58","amt_prin_init":"1788.5231321445","amt_int_pay":"7.15391466273582","amt_prin_pay":"593.796085337264","amt_prin_end":"1194.72704680724"},{"num_pmt":"59","amt_prin_init":"1194.72704680724","amt_int_pay":"4.77878937348146","amt_prin_pay":"596.171210626519","amt_prin_end":"598.55583618072"},{"num_pmt":"60","amt_prin_init":"598.55583618072","amt_int_pay":"2.39416381928027","amt_prin_pay":"598.55583618072","amt_prin_end":"0"}]}
Generate an amortization schedule for 250,000 loan with 50,000 balloon payment with 36 payments of 6,533.74 using a CROSS APPLY.
SELECT k.*
FROM
(
SELECT 250000 as pv,
6533.74 as pmt,
36 as numpmts,
50000 as FV,
0 as pay_type
) n
CROSS APPLY wct.PMTSCHED(n.pv, n.pmt, n.numpmts, n.fv, n.pay_type) k;
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_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":"1","amt_prin_init":"250000","amt_int_pay":"1562.49433274673","amt_prin_pay":"4971.24566725327","amt_prin_end":"245028.754332747"},{"num_pmt":"2","amt_prin_init":"245028.754332747","amt_int_pay":"1531.4241600216","amt_prin_pay":"5002.3158399784","amt_prin_end":"240026.438492768"},{"num_pmt":"3","amt_prin_init":"240026.438492768","amt_int_pay":"1500.15979941935","amt_prin_pay":"5033.58020058065","amt_prin_end":"234992.858292188"},{"num_pmt":"4","amt_prin_init":"234992.858292188","amt_int_pay":"1468.70003727184","amt_prin_pay":"5065.03996272816","amt_prin_end":"229927.81832946"},{"num_pmt":"5","amt_prin_init":"229927.81832946","amt_int_pay":"1437.04365232425","amt_prin_pay":"5096.69634767575","amt_prin_end":"224831.121981784"},{"num_pmt":"6","amt_prin_init":"224831.121981784","amt_int_pay":"1405.18941568829","amt_prin_pay":"5128.55058431171","amt_prin_end":"219702.571397472"},{"num_pmt":"7","amt_prin_init":"219702.571397472","amt_int_pay":"1373.13609079564","amt_prin_pay":"5160.60390920436","amt_prin_end":"214541.967488268"},{"num_pmt":"8","amt_prin_init":"214541.967488268","amt_int_pay":"1340.88243334869","amt_prin_pay":"5192.85756665131","amt_prin_end":"209349.109921616"},{"num_pmt":"9","amt_prin_init":"209349.109921616","amt_int_pay":"1308.42719127402","amt_prin_pay":"5225.31280872598","amt_prin_end":"204123.79711289"},{"num_pmt":"10","amt_prin_init":"204123.79711289","amt_int_pay":"1275.76910467214","amt_prin_pay":"5257.97089532786","amt_prin_end":"198865.826217563"},{"num_pmt":"11","amt_prin_init":"198865.826217563","amt_int_pay":"1242.9069057695","amt_prin_pay":"5290.8330942305","amt_prin_end":"193574.993123332"},{"num_pmt":"12","amt_prin_init":"193574.993123332","amt_int_pay":"1209.83931886825","amt_prin_pay":"5323.90068113175","amt_prin_end":"188251.0924422"},{"num_pmt":"13","amt_prin_init":"188251.0924422","amt_int_pay":"1176.5650602989","amt_prin_pay":"5357.1749397011","amt_prin_end":"182893.917502499"},{"num_pmt":"14","amt_prin_init":"182893.917502499","amt_int_pay":"1143.08283836733","amt_prin_pay":"5390.65716163267","amt_prin_end":"177503.260340867"},{"num_pmt":"15","amt_prin_init":"177503.260340867","amt_int_pay":"1109.39135330812","amt_prin_pay":"5424.34864669188","amt_prin_end":"172078.911694175"},{"num_pmt":"16","amt_prin_init":"172078.911694175","amt_int_pay":"1075.48929723085","amt_prin_pay":"5458.25070276915","amt_prin_end":"166620.660991405"},{"num_pmt":"17","amt_prin_init":"166620.660991405","amt_int_pay":"1041.37535407186","amt_prin_pay":"5492.36464592814","amt_prin_end":"161128.296345477"},{"num_pmt":"18","amt_prin_init":"161128.296345477","amt_int_pay":"1007.04819954118","amt_prin_pay":"5526.69180045882","amt_prin_end":"155601.604545019"},{"num_pmt":"19","amt_prin_init":"155601.604545019","amt_int_pay":"972.506501072794","amt_prin_pay":"5561.23349892721","amt_prin_end":"150040.371046091"},{"num_pmt":"20","amt_prin_init":"150040.371046091","amt_int_pay":"937.748917772095","amt_prin_pay":"5595.99108222791","amt_prin_end":"144444.379963863"},{"num_pmt":"21","amt_prin_init":"144444.379963863","amt_int_pay":"902.774100363729","amt_prin_pay":"5630.96589963627","amt_prin_end":"138813.414064227"},{"num_pmt":"22","amt_prin_init":"138813.414064227","amt_int_pay":"867.580691139689","amt_prin_pay":"5666.15930886031","amt_prin_end":"133147.254755367"},{"num_pmt":"23","amt_prin_init":"133147.254755367","amt_int_pay":"832.167323905285","amt_prin_pay":"5701.57267609471","amt_prin_end":"127445.682079272"},{"num_pmt":"24","amt_prin_init":"127445.682079272","amt_int_pay":"796.532623928631","amt_prin_pay":"5737.20737607137","amt_prin_end":"121708.474703201"},{"num_pmt":"25","amt_prin_init":"121708.474703201","amt_int_pay":"760.675207884991","amt_prin_pay":"5773.06479211501","amt_prin_end":"115935.409911086"},{"num_pmt":"26","amt_prin_init":"115935.409911086","amt_int_pay":"724.593683803929","amt_prin_pay":"5809.14631619607","amt_prin_end":"110126.26359489"},{"num_pmt":"27","amt_prin_init":"110126.26359489","amt_int_pay":"688.286651015407","amt_prin_pay":"5845.45334898459","amt_prin_end":"104280.810245905"},{"num_pmt":"28","amt_prin_init":"104280.810245905","amt_int_pay":"651.752700094725","amt_prin_pay":"5881.98729990528","amt_prin_end":"98398.8229459998"},{"num_pmt":"29","amt_prin_init":"98398.8229459998","amt_int_pay":"614.990412808964","amt_prin_pay":"5918.74958719104","amt_prin_end":"92480.0733588088"},{"num_pmt":"30","amt_prin_init":"92480.0733588088","amt_int_pay":"577.99836206146","amt_prin_pay":"5955.74163793854","amt_prin_end":"86524.3317208702"},{"num_pmt":"31","amt_prin_init":"86524.3317208702","amt_int_pay":"540.775111834993","amt_prin_pay":"5992.96488816501","amt_prin_end":"80531.3668327052"},{"num_pmt":"32","amt_prin_init":"80531.3668327052","amt_int_pay":"503.319217138467","amt_prin_pay":"6030.42078286153","amt_prin_end":"74500.9460498437"},{"num_pmt":"33","amt_prin_init":"74500.9460498437","amt_int_pay":"465.629223949401","amt_prin_pay":"6068.1107760506","amt_prin_end":"68432.8352737931"},{"num_pmt":"34","amt_prin_init":"68432.8352737931","amt_int_pay":"427.703669156888","amt_prin_pay":"6106.03633084311","amt_prin_end":"62326.79894295"},{"num_pmt":"35","amt_prin_init":"62326.79894295","amt_int_pay":"389.541080506993","amt_prin_pay":"6144.19891949301","amt_prin_end":"56182.600023457"},{"num_pmt":"36","amt_prin_init":"56182.600023457","amt_int_pay":"351.139976543034","amt_prin_pay":"6182.60002345697","amt_prin_end":"50000"}]}
Generate an amortization schedule for a 10,000 loan with 12 monthly payments of 869.95, made at the beginning of the period using CROSS APPLY.
SELECT k.*
FROM
(
SELECT 10000 as pv,
869.95 as pmt,
12 as numpmts,
0 as FV,
1 as pay_type
) n
CROSS APPLY wct.PMTSCHED(n.pv, n.pmt, n.numpmts, n.fv, n.pay_type) k;
This produces the following result.
num_pmt amt_prin_init amt_int_pay amt_prin_pay amt_prin_end
----------- ---------------------- ---------------------- ---------------------- ----------------------
1 10000 72.2834188150475 797.666581184953 9202.33341881505
2 9202.33341881505 65.9682214653596 803.98177853464 8398.35164028041
3 8398.35164028041 59.6030261359176 810.346973864082 7588.00466641632
4 7588.00466641632 53.1874369882901 816.76256301171 6771.24210340461
5 6771.24210340461 46.7210550501161 823.228944949884 5948.01315845473
6 5948.01315845473 40.2034781903678 829.746521809632 5118.2666366451
7 5118.2666366451 33.6343010942358 836.315698905764 4281.95093773933
8 4281.95093773933 27.0131152381475 842.936884761853 3439.01405297748
9 3439.01405297748 20.3395088640143 849.610491135986 2589.4035618415
10 2589.4035618415 13.613066954043 856.336933045957 1733.06662879554
11 1733.06662879554 6.83337120446595 863.116628795534 869.950000000005
12 869.950000000005 -4.77484718430787E-12 869.950000000005 0