ODDFIPMT
Updated 2024-02-26 16:41:53.657000
Syntax
SELECT [wct].[ODDFIPMT](
<@Rate, float,>
,<@Per, int,>
,<@Nper, int,>
,<@PV, float,>
,<@FV, float,>
,<@FirstPeriod, float,>)
Description
Use the scalar function ODDFIPMT to calculate the interest portion of a periodic payment for an annuity where the first period is either longer or shorter than the other periods. The interest for the first period is calculated as:
ODDFIPMT = -@PV * ((1 + @Rate)@FirstPeriod-1)
Else
ODDFIMPT = -@PV@Per-1 * @Rate
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.
ODDFIPMT 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 ODDFPMT result will have the opposite sign of @PV.
Examples
Calculate the interest 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/6 th longer than the other periods.
SELECT wct.ODDFIPMT( .005, --@Rate
1, --@Per
36, --@Nper
-11500, --@PV
0, --@FV
1 + 5 / 6e+00 --@FirstPeriod
) as IPMT;
This produces the following result.
{"columns":[{"field":"IPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"IPMT":"105.416666666667"}]}
Using the same basic information in this SQL, we calculate the periodic interest for each of the first 10 payments.
SELECT x.Per,
wct.ODDFIPMT( .005, --@Rate
x.Per, --@Per
36, --@Nper
-11500, --@PV
0, --@FV
1 + 5 / 6e+00 --@FirstPeriod
) as IPMT
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":"IPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Per":"1","IPMT":"105.416666666667"},{"Per":"2","IPMT":"56.2705696023255"},{"Per":"3","IPMT":"54.7954087193293"},{"Per":"4","IPMT":"53.3128720319182"},{"Per":"5","IPMT":"51.82292266107"},{"Per":"6","IPMT":"50.3255235433675"},{"Per":"7","IPMT":"48.8206374300765"},{"Per":"8","IPMT":"47.3082268862191"},{"Per":"9","IPMT":"45.7882542896424"},{"Per":"10","IPMT":"44.2606818300828"}]}
Calculate the interest 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.ODDFIPMT( .005, --@Rate
1, --@Per
180, --@Nper
-250000, --@PV
50000, --@FV
0.5 --@FirstPeriod
) as IPMT;
This produces the following result.
{"columns":[{"field":"IPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"IPMT":"625"}]}
Using the same basic information in this SQL, we calculate the periodic interest for each of the last 10 payments.
SELECT x.Per,
wct.ODDFIPMT( .005, --@Rate
x.Per, --@Per
180, --@Nper
-250000, --@PV
50000, --@FV
0.5 --@FirstPeriod
) as IPMT
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":"IPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Per":"171","IPMT":"331.855425404118"},{"Per":"172","IPMT":"323.852373579885"},{"Per":"173","IPMT":"315.80930649653"},{"Per":"174","IPMT":"307.726024077759"},{"Per":"175","IPMT":"299.602325246894"},{"Per":"176","IPMT":"291.438007921874"},{"Per":"177","IPMT":"283.232869010229"},{"Per":"178","IPMT":"274.986704404026"},{"Per":"179","IPMT":"266.699308974792"},{"Per":"180","IPMT":"258.370476568412"}]}
Calculate the interest 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.ODDFIPMT( .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 IPMT;
This produces the following result.
{"columns":[{"field":"IPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"IPMT":"90.4109589041096"}]}
Using the same basic information in this SQL, we calculate the periodic interest for each of the first 10 payments.
SELECT x.Per,
wct.ODDFIPMT( .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 IPMT
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":"IPMT","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Per":"1","IPMT":"90.4109589041096"},{"Per":"2","IPMT":"52.7632254183782"},{"Per":"3","IPMT":"52.6062219775782"},{"Per":"4","IPMT":"52.4484657805551"},{"Per":"5","IPMT":"52.2899532182039"},{"Per":"6","IPMT":"52.1306806641154"},{"Per":"7","IPMT":"51.9706444744936"},{"Per":"8","IPMT":"51.8098409880722"},{"Per":"9","IPMT":"51.648266526031"},{"Per":"10","IPMT":"51.4859173919116"}]}
See Also
IPMT - Calculate the interest portion of a periodic payment
LIPMT - Calculate the interest payment on a loan 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
ODDFPPMT - Principal portion of a periodic payment for an annuity with an odd first period
ODDFPV - Present Value of an annuity with an odd first period