Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server annuity amotrization schedule


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)



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service