ODDFPPMT
Updated 2023-10-06 21:44:34.687000
Syntax
SELECT [wct].[ODDFPPMT](
<@Rate, float,>
,<@Per, int,>
,<@Nper, int,>
,<@PV, float,>
,<@FV, float,>
,<@FirstPeriod, float,>);
Description
Use the scalar function ODDFPPMT to calculate the principal portion of a periodic payment for an annuity where the first period is either longer or shorter than the other periods. The principal portion of the payment is the payment amount minus the periodic interest amount.
Arguments
@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.
@Rate
the periodic interest rate. @Rate 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.
@Nper
the number of annuity payments. @Nper is an expression of type int or of a type that can be implicitly converted to int.
@Per
the period of interest. @Per 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.
Return Type
float
Remarks
If @Rate <= -1 then NULL is returned.
If @Nper < 1 then NULL is returned.
If @FirstPeriod <= 0 then NULL is returned.
If @Per < 1 Then NULL is returned.
If @Per > @Nper Then NULL is returned.
If @Nper is NULL then @Nper = 0.
If @Rate is NULL then @Rate = 0.
If @PV is NULL then @PV = 0.
If @FV is NULL then @FV = 0.
If @Per is NULL then @Per = 0.
If @FirstPeriod is NULL then @FirstPeriod = 1.
When @Per = @Nper then the result includes the @FV.
ODDFPPMT 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 ODDFPPMT result will have the opposite sign of @PV.
Examples
Calculate the principal portion for the first period of 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 wct.ODDFPPMT( .005, --@Rate
1, --@Per
36, --@Nper
-11500, --@PV
0, --@FV
1 + 5 / 6e+00 --@FirstPeriod
) as PPMT;
This produces the following result.
{"columns":[{"field":"PPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"PPMT":"245.886079534899"}]}
Using the same basic information in this SQL, we calculate the periodic principal payment for each of the first 10 payments.
SELECT x.Per,
wct.ODDFPPMT( .005, --@Rate
x.Per, --@Per
36, --@Nper
-11500, --@PV
0, --@FV
1 + 5 / 6e+00 --@FirstPeriod
) as PPMT
FROM
(
VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10)
) x (Per);
This produces the following result.
{"columns":[{"field":"Per","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Per":"1","PPMT":"245.886079534899"},{"Per":"2","PPMT":"295.032176599241"},{"Per":"3","PPMT":"296.50733748223"},{"Per":"4","PPMT":"297.98987416964"},{"Per":"5","PPMT":"299.479823540489"},{"Per":"6","PPMT":"300.977222658195"},{"Per":"7","PPMT":"302.482108771483"},{"Per":"8","PPMT":"303.994519315351"},{"Per":"9","PPMT":"305.51449191191"},{"Per":"10","PPMT":"307.042064371475"}]}
Calculate the principal portion of the first periodic payment 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.
SELECT wct.ODDFPPMT( .005, --@Rate
1, --@Per
180, --@Nper
-250000, --@PV
50000, --@FV
0.5 --@FirstPeriod
) as PPMT;
This produces the following result.
{"columns":[{"field":"PPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"PPMT":"1307.46579025083"}]}
Using the same basic information in this SQL, we calculate the periodic principal amount for each of the last 10 payments.
SELECT x.Per,
wct.ODDFPPMT( .005, --@Rate
x.Per, --@Per
180, --@Nper
-250000, --@PV
50000, --@FV
0.5 --@FirstPeriod
) as PPMT
FROM
(
VALUES
(171),
(172),
(173),
(174),
(175),
(176),
(177),
(178),
(179),
(180)
) x (Per);
This produces the following result.
{"columns":[{"field":"Per","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Per":"171","PPMT":"1600.61036484675"},{"Per":"172","PPMT":"1608.61341667088"},{"Per":"173","PPMT":"1616.65648375429"},{"Per":"174","PPMT":"1624.73976617306"},{"Per":"175","PPMT":"1632.86346500397"},{"Per":"176","PPMT":"1641.02778232892"},{"Per":"177","PPMT":"1649.2329212406"},{"Per":"178","PPMT":"1657.4790858468"},{"Per":"179","PPMT":"1665.766481276"},{"Per":"180","PPMT":"51674.0953136824"}]}
Calculate the principal portion of the first 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.
SELECT wct.ODDFPPMT( .25 * 7 / 365e+00, --@Rate
1, --@Per
156, --@Nper
-11000, --@PV
3500, --@FV
DATEDIFF(d, '2014-11-13', '2014-11-25') / 7e+00
--@FirstPeriod
) as PPMT;
This produces the following result.
{"columns":[{"field":"PPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"PPMT":"-4.90130154744293"}]}
Using the same basic information in this SQL, we calculate the periodic principal amount for each of the first 10 payments.
SELECT x.Per,
wct.ODDFPPMT( .25 * 7 / 365e+00, --@Rate
x.Per, --@Per
156, --@Nper
-11000, --@PV
3500, --@FV
DATEDIFF(d, '2014-11-13', '2014-11-25') / 7e+00
--@FirstPeriod
) as PPMT
FROM
(
VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10)
) x (Per);
This produces the following result.
{"columns":[{"field":"Per","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"PPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Per":"1","PPMT":"-4.90130154744293"},{"Per":"2","PPMT":"32.7464319382852"},{"Per":"3","PPMT":"32.9034353790867"},{"Per":"4","PPMT":"33.0611915761092"},{"Per":"5","PPMT":"33.2197041384625"},{"Per":"6","PPMT":"33.3789766925474"},{"Per":"7","PPMT":"33.5390128821728"},{"Per":"8","PPMT":"33.6998163685912"},{"Per":"9","PPMT":"33.8613908306361"},{"Per":"10","PPMT":"34.0237399647522"}]}
See Also
ODDFPMT - Periodic payment for an annuity with an odd first period
ODDFPMT - Periodic payment for an annuity with an odd first period
ODDFPMTSCHED - Amortization schedule for an annuity with odd first period
ODDFPMTSCHED - Amortization schedule for an annuity with odd first period
ODDFIPMT - Interest portion of a periodic payment for an annuity with an odd first period
ODDFIPMT - Interest portion of a periodic payment for an annuity with an odd first period
ODDFPV - Present Value of an annuity with an odd first period
ODDFPV - Present Value of an annuity with an odd first period
PPMT - principal portion of an annuity
PPMT - principal portion of an annuity
LPMT - periodic payment of an annuity with an odd first period
LPMT - periodic payment of an annuity with an odd first period