PMTSCHED
Updated: 31 October 2010
Use 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.
Syntax
SELECT * FROM [westclintech].[wct].[PMTSCHED] (
<@PV, float,>
,<@pmt, float,>
,<@NumPmts, int,>
,<@FV, float,>
,<@Pay_type, float,>)
Arguments
@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.
@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.
@Pay_type
Identifies whether payments are made at the beginning of the period (1) or at the end of the period (0).
Return Types
RETURNS TABLE (
[num_pmt] [int] NULL,
[amt_prin_init] [float] NULL,
[amt_int_pay] [float] NULL,
[amt_prin_pay] [float] NULL,
[amt_prin_end] [float] NULL
)
|
Column
|
Column Description
|
|
num_pmt
|
The payment number.
|
|
amt_prin_init
|
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.
|
|
amt_int_pay
|
The interest payment amount for the period. The interest payment amount is equal to amt_prin_int – amt_prin_end - @pmt
|
|
amt_prin_pay
|
The principal payment amount for the period. The principal payment amount is equal to amt_prin_init – amt_prin_end.
|
|
amt_prin_end
|
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.
num_pmt amt_prin_init amt_int_pay amt_prin_pay amt_prin_end
----------- ---------------------- ---------------------- ---------------------- ----------------------
1 100000 499.994613980976 8106.64538601902 91893.354613981
2 91893.354613981 459.461823674021 8147.17817632598 83746.176437655
3 83746.176437655 418.726371601093 8187.91362839891 75558.2628092561
4 75558.2628092561 377.787244461579 8228.85275553842 67329.4100537177
5 67329.4100537177 336.643423891961 8269.99657610804 59059.4134776096
6 59059.4134776096 295.29388643532 8311.34611356468 50748.067364045
7 50748.067364045 253.737603518282 8352.90239648172 42395.1649675632
8 42395.1649675632 211.973541424944 8394.66645857506 34000.4985089882
9 34000.4985089882 170.000661270562 8436.63933872944 25563.8591702587
10 25563.8591702587 127.817918976172 8478.82208102383 17085.0370892349
11 17085.0370892349 85.4242652420508 8521.21573475795 8563.82135447697
12 8563.82135447697 42.8186455230316 8563.82135447697 0
(12 row(s) affected)
Generate an amortization schedule for loan with a principal amount of 32,000 with 60 payments of 600.95.
SELECT *
FROM wct.PMTSCHED(
100000 --pv
,8606.64 --pmt
,12 --numpmts
,0 --FV
,0 --pay_type
)
This produces the following result.
num_pmt amt_prin_init amt_int_pay amt_prin_pay amt_prin_end
----------- ---------------------- ---------------------- ---------------------- ----------------------
1 32000 127.996817638387 472.953182361613 31527.0468176384
2 31527.0468176384 126.105051955211 474.844948044789 31052.2018695936
3 31052.2018695936 124.205719385619 476.744280614381 30575.4575889792
4 30575.4575889792 122.298789674637 478.651210325363 30096.8063786539
5 30096.8063786539 120.384232434433 480.565767565568 29616.2406110883
6 29616.2406110883 118.462017155692 482.487982844308 29133.752628244
7 29133.752628244 116.532113206983 484.417886793017 28649.334741451
8 28649.334741451 114.594489834405 486.355510165595 28162.9792312854
9 28162.9792312854 112.649116161035 488.300883838965 27674.6783474464
10 27674.6783474464 110.695961186432 490.254038813568 27184.4243086328
11 27184.4243086328 108.734993786205 492.215006213795 26692.209302419
12 26692.209302419 106.766182711336 494.183817288664 26198.0254851304
13 26198.0254851304 104.789496587985 496.160503412015 25701.8649817184
14 25701.8649817184 102.804903916727 498.145096083274 25203.7198856351
15 25203.7198856351 100.812373072116 500.137626927884 24703.5822587072
16 24703.5822587072 98.8118723023392 502.138127697661 24201.4441310095
17 24201.4441310095 96.8033697283856 504.146630271614 23697.2975007379
18 23697.2975007379 94.7868333438994 506.163166656101 23191.1343340818
19 23191.1343340818 92.7622310143954 508.187768985605 22682.9465650962
20 22682.9465650962 90.7295304769389 510.220469523061 22172.7260955732
21 22172.7260955732 88.6886993394467 512.261300660553 21660.4647949126
22 21660.4647949126 86.639705080411 514.310294919589 21146.154499993
23 21146.154499993 84.5825150480698 516.36748495193 20629.7870150411
24 20629.7870150411 82.5170964602178 518.432903539782 20111.3541115013
25 20111.3541115013 80.4434164033912 520.506583596609 19590.8475279047
26 19590.8475279047 78.3614418325772 522.588558167423 19068.2589697373
27 19068.2589697373 76.2711395705003 524.6788604295 18543.5801093078
28 18543.5801093078 74.1724763072737 526.777523692726 18016.802585615
29 18016.802585615 72.0654185997148 528.884581400285 17487.9180042148
30 17487.9180042148 69.9499328708218 531.000067129178 16956.9179370856
31 16956.9179370856 67.8259854094533 533.124014590547 16423.793922495
32 16423.793922495 65.6935423694551 535.256457630545 15888.5374648645
33 15888.5374648645 63.5525697693113 537.397430230689 15351.1400346338
34 15351.1400346338 61.4030334917363 539.546966508264 14811.5930681255
35 14811.5930681255 59.2448992828024 541.705100717198 14269.8879674083
36 14269.8879674083 57.0781327516481 543.871867248352 13726.01610016
37 13726.01610016 54.9026993698674 546.047300630133 13179.9687995299
38 13179.9687995299 52.7185644708982 548.231435529102 12631.7373640008
39 12631.7373640008 50.5256932495279 550.424306750472 12081.3130572503
40 12081.3130572503 48.3240507613693 552.625949238631 11528.6871080117
41 11528.6871080117 46.1136019221915 554.836398077809 10973.8507099338
42 10973.8507099338 43.8943115074828 557.055688492517 10416.7950214413
43 10416.7950214413 41.6661441518402 559.28385584816 9857.51116559317
44 9857.51116559317 39.4290643483284 561.520935651672 9295.9902299415
45 9295.9902299415 37.1830364481314 563.766963551869 8732.22326638963
46 8732.22326638963 34.9280246596493 566.021975340351 8166.20129104928
47 8166.20129104928 32.6639930482663 568.286006951734 7597.91528409754
48 7597.91528409754 30.3909055356228 570.559094464377 7027.35618963317
49 7027.35618963317 28.1087258989749 572.841274101025 6454.51491553214
50 6454.51491553214 25.817417770729 575.132582229271 5879.38233330287
51 5879.38233330287 23.5169446378598 577.43305536214 5301.94927794073
52 5301.94927794073 21.2072698412114 579.742730158789 4722.20654778194
53 4722.20654778194 18.8883565750905 582.06164342491 4140.14490435703
54 4140.14490435703 16.5601678864798 584.38983211352 3555.75507224351
55 3555.75507224351 14.2226666746894 586.727333325311 2969.0277389182
56 2969.0277389182 11.8758156905126 589.074184309487 2379.95355460871
57 2379.95355460871 9.51957753578904 591.430422464211 1788.5231321445
58 1788.5231321445 7.15391466273582 593.796085337264 1194.72704680724
59 1194.72704680724 4.77878937348146 596.171210626519 598.55583618072
60 598.55583618072 2.39416381928027 598.55583618072 0
(60 row(s) affected)
Generate an amortization schedule for 250,000 loan with 50,000 ballon 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.
num_pmt amt_prin_init amt_int_pay amt_prin_pay amt_prin_end
----------- ---------------------- ---------------------- ---------------------- ----------------------
1 250000 1562.49433274673 4971.24566725327 245028.754332747
2 245028.754332747 1531.4241600216 5002.3158399784 240026.438492768
3 240026.438492768 1500.15979941935 5033.58020058065 234992.858292188
4 234992.858292188 1468.70003727184 5065.03996272816 229927.81832946
5 229927.81832946 1437.04365232425 5096.69634767575 224831.121981784
6 224831.121981784 1405.18941568829 5128.55058431171 219702.571397472
7 219702.571397472 1373.13609079564 5160.60390920436 214541.967488268
8 214541.967488268 1340.88243334869 5192.85756665131 209349.109921616
9 209349.109921616 1308.42719127402 5225.31280872598 204123.79711289
10 204123.79711289 1275.76910467214 5257.97089532786 198865.826217563
11 198865.826217563 1242.9069057695 5290.8330942305 193574.993123332
12 193574.993123332 1209.83931886825 5323.90068113175 188251.0924422
13 188251.0924422 1176.5650602989 5357.1749397011 182893.917502499
14 182893.917502499 1143.08283836733 5390.65716163267 177503.260340867
15 177503.260340867 1109.39135330812 5424.34864669188 172078.911694175
16 172078.911694175 1075.48929723085 5458.25070276915 166620.660991405
17 166620.660991405 1041.37535407186 5492.36464592814 161128.296345477
18 161128.296345477 1007.04819954118 5526.69180045882 155601.604545019
19 155601.604545019 972.506501072794 5561.23349892721 150040.371046091
20 150040.371046091 937.748917772095 5595.99108222791 144444.379963863
21 144444.379963863 902.774100363729 5630.96589963627 138813.414064227
22 138813.414064227 867.580691139689 5666.15930886031 133147.254755367
23 133147.254755367 832.167323905285 5701.57267609471 127445.682079272
24 127445.682079272 796.532623928631 5737.20737607137 121708.474703201
25 121708.474703201 760.675207884991 5773.06479211501 115935.409911086
26 115935.409911086 724.593683803929 5809.14631619607 110126.26359489
27 110126.26359489 688.286651015407 5845.45334898459 104280.810245905
28 104280.810245905 651.752700094725 5881.98729990528 98398.8229459998
29 98398.8229459998 614.990412808964 5918.74958719104 92480.0733588088
30 92480.0733588088 577.99836206146 5955.74163793854 86524.3317208702
31 86524.3317208702 540.775111834993 5992.96488816501 80531.3668327052
32 80531.3668327052 503.319217138467 6030.42078286153 74500.9460498437
33 74500.9460498437 465.629223949401 6068.1107760506 68432.8352737931
34 68432.8352737931 427.703669156888 6106.03633084311 62326.79894295
35 62326.79894295 389.541080506993 6144.19891949301 56182.600023457
36 56182.600023457 351.139976543034 6182.60002345697 50000
(36 row(s) affected)
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 -2.00770955416374E-10 869.950000000201 9130.0499999998
2 9130.0499999998 72.2834188150621 797.666581184938 8332.38341881486
3 8332.38341881486 65.9682214653906 803.981778534609 7528.40164028025
4 7528.40164028025 59.603026135944 810.346973864056 6718.0546664162
5 6718.0546664162 53.1874369883037 816.762563011696 5901.2921034045
6 5901.2921034045 46.7210550501361 823.228944949864 5078.06315845464
7 5078.06315845464 40.2034781903524 829.746521809648 4248.31663664499
8 4248.31663664499 33.634301094283 836.315698905717 3412.00093773927
9 3412.00093773927 27.0131152381393 842.936884761861 2569.06405297741
10 2569.06405297741 20.3395088640711 849.610491135929 1719.45356184148
11 1719.45356184148 13.6130669540319 856.336933045968 863.116628795513
12 863.116628795513 6.83337120448709 863.116628795513 0
(12 row(s) affected)