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_init – amt_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 calculate 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