Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server amortization schedule odd-first period annuities


ODDFPMTSCHED

Updated: 16 November 2014


Use the table-valued function ODDFPMTSCHED to generate an amortization schedule for an annuity where the first period is either longer or shorter than all the other periods.
Syntax
SELECT * FROM [wct].[ODDFPMTSCHED](
  <@Rate, float,>
 ,<@Nper, int,>
 ,<@PV, float,>
 ,<@FV, float,>
 ,<@FirstPeriod, float,>)
Arguments
@Rate
the periodic interest rate. @Rate is an expression of type float or of a type that can be implicitly converted to float.
@Nper
the number of annuity payments. @Nper is an expression of type int or of a type that can be implicitly converted to int.
@PV
the present value of the annuity. @PV is an expression of type float or of a type that can be implicitly converted to float.
@FV
the future value as at the end of the annuity. @FV is an expression of type float or of a type that can be implicitly converted to float.
@FirstPeriod
the length of the first period. @FirstPeriod is an expression of type float or of a type that can be implicitly converted to float.
Return Type
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
Description
num_pmt
Payment number
amt_prin_init
Initial principal amount; amt_prin_end from the previous row
amt_int_pay
Interest portion of the periodic payment
amt_print_pay
Principal portion of the periodic payment
amt_prin_end
Ending principal amount; amt_prin_initamt_prin_pay

Remarks
·         If @Rate <= -1 then no rows are returned.
·         If @Nper < 1 then no rows are returned.
·         If @FirstPeriod <= 0 then no rows are returned.
·         If @Nper is NULL then @Nper = 1.
·         If @Rate is NULL then @Rate = 0.
·         If @PV is NULL then @PV = 0.
·         If @FV is NULL then @FV = 0.
·         If @FirstPeriod is NULL then @FirstPeriod = 1.
·         amt_prin_pay for the final period includes @FV so that the amt_prin_end for the final period is zero.
·         ODDFPMTSCHED uses the same conventions for the sign of the inputs and the results as Excel and Google spreadsheets; generally @PV and @FV should have opposite signs and the periodic interest and principal payments will have the opposite sign of @PV.
Examples
Generate a payment schedule for an annuity assuming a periodic rate of 0.5%, with 36 periodic payments. The price of the annuity is 11,500 and there is no cash value at the end of the annuity. The first period is 1 and 5/6th longer than the other periods.
SELECT
   *
FROM
   wct.ODDFPMTSCHED(
        .005         --@Rate
       ,36           --@Nper
       ,-11500       --@PV
       ,0            --@FV
       ,1+5/6e+00    --@FirstPeriod
   )
This produces the following result.
num_pmt     amt_prin_init          amt_int_pay            amt_prin_pay           amt_prin_end
----------- ---------------------- ---------------------- ---------------------- ----------------------
0           0                      0                      0                      11500
1           11500                  105.63622380599        245.673168518522       11254.3268314815
2           11254.3268314815       56.2716341574104       295.037758167102       10959.2890733144
3           10959.2890733144       54.7964453665771       296.512946957935       10662.7761263564
4           10662.7761263564       53.3138806317928       297.995511692719       10364.7806146637
5           10364.7806146637       51.8239030733256       299.485489251187       10065.2951254125
6           10065.2951254125       50.326475627069        300.982916697443       9764.31220871509
7           9764.31220871509       48.8215610435835       302.487831280929       9461.82437743416
8           9461.82437743416       47.3091218871685       304.000270437344       9157.82410699682
9           9157.82410699682       45.7891205349987       305.520271789514       8852.30383520731
10          8852.30383520731       44.2615191760468       307.047873148465       8545.25596205884
11          8545.25596205884       42.7262798102892       308.583112514223       8236.67284954462
12          8236.67284954462       41.1833642477349       310.126028076777       7926.54682146784
13          7926.54682146784       39.6327341073473       311.676658217165       7614.87016325067
14          7614.87016325067       38.0743508162549       313.235041508257       7301.63512174242
15          7301.63512174242       36.5081756087228       314.801216715789       6986.83390502663
16          6986.83390502663       34.9341695251368       316.375222799375       6670.45868222725
17          6670.45868222725       33.3522934111423       317.95709891337        6352.50158331388
18          6352.50158331388       31.7625079165754       319.546884407937       6032.95469890595
19          6032.95469890595       30.164773494539        321.144618829973       5711.81008007597
20          5711.81008007597       28.5590504003948       322.750341924117       5389.05973815185
21          5389.05973815185       26.9452986907662       324.364093633746       5064.69564451811
22          5064.69564451811       25.3234782225926       325.98591410192        4738.70973041619
23          4738.70973041619       23.6935486520932       327.615843672419       4411.09388674377
24          4411.09388674377       22.0554694337237       329.253922890789       4081.83996385298
25          4081.83996385298       20.4091998192752       330.900192505237       3750.93977134774
26          3750.93977134774       18.7546988567436       332.554693467769       3418.38507787998
27          3418.38507787998       17.0919253893979       334.217466935114       3084.16761094486
28          3084.16761094486       15.4208380547419       335.88855426977        2748.27905667509
29          2748.27905667509       13.7413952833816       337.567997041131       2410.71105963396
30          2410.71105963396       12.0535552981777       339.255837026335       2071.45522260763
31          2071.45522260763       10.3572761130343       340.952116211478       1730.50310639615
32          1730.50310639615       8.6525155319938        342.656876792518       1387.84622960363
33          1387.84622960363       6.93923114802226       344.37016117649        1043.47606842714
34          1043.47606842714       5.21738034214229       346.09201198237        697.384056444769
35          697.384056444769       3.48692028223519       347.822472042277       349.561584402492
36          349.561584402492       1.74780792201994       349.561584402492       0


In this example we generate a payment schedule for an annuity assuming a periodic rate of 0.5%, with 180 periodic payments. The price of the annuity is 250,000 and there is a 50,000 cash value at the end of the annuity. The first period is one-half as long as the other periods. We will return the last 24 rows.
SELECT
   *
FROM (
   SELECT TOP 24
       *
   FROM
       wct.ODDFPMTSCHED(
           .005     --@Rate
          ,180      --@Nper
          ,-250000 --@PV
          ,50000    --@FV
          ,0.5      --@FirstPeriod
       )
   ORDER BY
       num_pmt DESC
   )n
ORDER BY
   1 ASC
This produces the following result.
num_pmt     amt_prin_init          amt_int_pay            amt_prin_pay           amt_prin_end
----------- ---------------------- ---------------------- ---------------------- ----------------------
157         87961.1029083781       439.805514541946       1492.65373222511       86468.449176153
158         86468.449176153        432.342245880758       1500.1170008863        84968.3321752667
159         84968.3321752667       424.841660876391       1507.61758589066       83460.714589376
160         83460.714589376        417.303572946894       1515.15567382016       81945.5589155558
161         81945.5589155558       409.727794577815       1522.73145218924       80422.8274633666
162         80422.8274633666       402.114137316861       1530.34510945019       78892.4823539164
163         78892.4823539164       394.462411769618       1537.99683499744       77354.485518919
164         77354.485518919        386.772427594664       1545.68681917239       75808.7986997466
165         75808.7986997466       379.043993498763       1553.41525326829       74255.3834464783
166         74255.3834464783       371.276917232415       1561.18232953464       72694.2011169436
167         72694.2011169436       363.471005584765       1568.98824118229       71125.2128757614
168         71125.2128757614       355.626064378837       1576.83318238822       69548.3796933731
169         69548.3796933731       347.741898466917       1584.71734830014       67963.662345073
170         67963.662345073        339.81831172539        1592.64093504167       66371.0214100313
171         66371.0214100313       331.855107050153       1600.6041397169        64770.4172703144
172         64770.4172703144       323.85208635165        1608.60716041541       63161.810109899
173         63161.810109899        315.809050549524       1616.65019621753       61545.1599136815
174         61545.1599136815       307.725799568445       1624.73344719861       59920.4264664829
175         59920.4264664829       299.602132332398       1632.85711443466       58287.5693520482
176         58287.5693520482       291.437846760298       1641.02140000676       56646.5479520415
177         56646.5479520415       283.232739760231       1649.22650700682       54997.3214450346
178         54997.3214450346       274.986607225201       1657.47263954185       53339.8488054928
179         53339.8488054928       266.699244027524       1665.76000273953       51674.0888027533
180         51674.0888027533       258.370444013799       51674.0888027533       0
 


In this example we c
alculate the weekly payment for an automobile lease with a term of 3 years and an annual interest rate of 25%. The amount to be financed is 11,000 and the residual value at the end of the lease is 3,500. The first payment is due 2014-11-25 and we will return the first 52 payments.
SELECT TOP 53
   *
FROM
   wct.ODDFPMTSCHED(
        .25 * 7/365e+00     --@Rate
       ,156                 --@Nper
       ,-11000              --@PV
       ,3500                --@FV
       ,DATEDIFF(d,'2014-11-13','2014-11-25')/7e+00    --@FirstPeriod
   )
ORDER BY
   num_pmt   ASC
This produces the following result.
num_pmt     amt_prin_init          amt_int_pay            amt_prin_pay           amt_prin_end
----------- ---------------------- ---------------------- ---------------------- ----------------------
0           0                      0                      0                      11000
1           11000                  90.5657016076069       -5.0546399988707       11005.0546399989
2           11005.0546399989       52.7639606027363       32.7471010059999       10972.3075389929
3           10972.3075389929       52.6069539540761       32.90410765466         10939.4034313382
4           10939.4034313382       52.4491945338135       33.0618670749227       10906.3415642633
5           10906.3415642633       52.2906787327677       33.2203828759684       10873.1211813873
6           10873.1211813873       52.1314029244616       33.3796586842745       10839.741522703
7           10839.741522703        51.9713634650155       33.5396981437207       10806.2018245593
8           10806.2018245593       51.8105566930929       33.7005049156433       10772.5013196437
9           10772.5013196437       51.6489789297969       33.8620826789393       10738.6392369647
10          10738.6392369647       51.486626478599        34.0244351301371       10704.6148018346
11          10704.6148018346       51.3234956252357       34.1875659835005       10670.4272358511
12          10670.4272358511       51.1595826376409       34.3514789710953       10636.07575688
13          10636.07575688         50.9948837658643       34.5161778428719       10601.5595790371
14          10601.5595790371       50.8293952419584       34.6816663667778       10566.8779126704
15          10566.8779126704       50.6631132799278       34.8479483288083       10532.0299643416
16          10532.0299643416       50.4960340756091       35.0150275331271       10497.0149368084
17          10497.0149368084       50.3281538066179       35.1829078021183       10461.8320290063
18          10461.8320290063       50.1594686322217       35.3515929765144       10426.4804360298
19          10426.4804360298       49.9899746932927       35.5210869154434       10390.9593491143
20          10390.9593491143       49.8196681121947       35.6913934965414       10355.2679556178
21          10355.2679556178       49.6485449926852       35.8625166160509       10319.4054390018
22          10319.4054390018       49.4766014198734       36.0344601888628       10283.3709788129
23          10283.3709788129       49.3038334600654       36.2072281486708       10247.1637506642
24          10247.1637506642       49.1302371607174       36.3808244480188       10210.7829262162
25          10210.7829262162       48.9558085503516       36.5552530583846       10174.2276731578
26          10174.2276731578       48.7805436384272       36.7305179703089       10137.4971551875
27          10137.4971551875       48.6044384152822       36.9066231934539       10100.5905319941
28          10100.5905319941       48.4274888520279       37.0835727567082       10063.5069592373
29          10063.5069592373       48.2496909004525       37.2613707082837       10026.2455885291
30          10026.2455885291       48.0710404929499       37.4400211157863       9988.80556741328
31          9988.80556741328       47.8915335423911       37.6195280663451       9951.18603934693
32          9951.18603934693       47.7111659420764       37.7998956666597       9913.38614368027
33          9913.38614368027       47.5299335655903       37.9811280431459       9875.40501563713
34          9875.40501563713       47.3478322667518       38.1632293419843       9837.24178629514
35          9837.24178629514       47.1648578794987       38.3462037292375       9798.89558256591
36          9798.89558256591       46.9810062177833       38.5300553909528       9760.36552717495
37          9760.36552717495       46.7962730754983       38.7147885332379       9721.65073864171
38          9721.65073864171       46.6106542263636       38.9004073823726       9682.75033125934
39          9682.75033125934       46.4241454238466       39.0869161848896       9643.66341507445
40          9643.66341507445       46.2367424010402       39.274319207696        9604.38909586676
41          9604.38909586676       46.0484408705937       39.4626207381425       9564.92647512861
42          9564.92647512861       45.8592365245925       39.6518250841436       9525.27465004447
43          9525.27465004447       45.6691250344605       39.8419365742757       9485.43271347019
44          9485.43271347019       45.4781020508829       40.0329595578532       9445.39975391234
45          9445.39975391234       45.2861632036905       40.2248984050457       9405.1748555073
46          9405.1748555073        45.0933041017464       40.4177575069898       9364.75709800031
47          9364.75709800031       44.8995203328808       40.6115412758554       9324.14555672445
48          9324.14555672445       44.7048074637472       40.806254144989        9283.33930257946
49          9283.33930257946       44.5091610397643       41.0019005689719       9242.33740201049
50          9242.33740201049       44.312576584983        41.1984850237532       9201.13891698674
51          9201.13891698674       44.1150496019903       41.3960120067459       9159.74290497999
52          9159.74290497999       43.9165755718235       41.5944860369127      9118.14841894308

 

 

See Also

 



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service