SQL Server ODDFIPMT Function
Updated 2024-02-26 16:41:53.657000
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
Syntax
SELECT [wct].[ODDFIPMT](
<@Rate, float,>
,<@Per, int,>
,<@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.
@Per
the period of interest. @Per is an expression of type int or of a type that can be implicitly converted to int.
@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
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.
| 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.
| Per | IPMT |
|---|---|
| 1 | 105.416666666667 |
| 2 | 56.2705696023255 |
| 3 | 54.7954087193293 |
| 4 | 53.3128720319182 |
| 5 | 51.82292266107 |
| 6 | 50.3255235433675 |
| 7 | 48.8206374300765 |
| 8 | 47.3082268862191 |
| 9 | 45.7882542896424 |
| 10 | 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.
| 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.
| Per | IPMT |
|---|---|
| 171 | 331.855425404118 |
| 172 | 323.852373579885 |
| 173 | 315.80930649653 |
| 174 | 307.726024077759 |
| 175 | 299.602325246894 |
| 176 | 291.438007921874 |
| 177 | 283.232869010229 |
| 178 | 274.986704404026 |
| 179 | 266.699308974792 |
| 180 | 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.
| 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.
| Per | IPMT |
|---|---|
| 1 | 90.4109589041096 |
| 2 | 52.7632254183782 |
| 3 | 52.6062219775782 |
| 4 | 52.4484657805551 |
| 5 | 52.2899532182039 |
| 6 | 52.1306806641154 |
| 7 | 51.9706444744936 |
| 8 | 51.8098409880722 |
| 9 | 51.648266526031 |
| 10 | 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