Logo

SQL Server ODDFPPMT Function

Updated 2023-10-06 21:44:34.687000

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.

Syntax

SELECT [wct].[ODDFPPMT](
  <@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.

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.

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.

PerPPMT
1245.886079534899
2295.032176599241
3296.50733748223
4297.98987416964
5299.479823540489
6300.977222658195
7302.482108771483
8303.994519315351
9305.51449191191
10307.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.

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.

PerPPMT
1711600.61036484675
1721608.61341667088
1731616.65648375429
1741624.73976617306
1751632.86346500397
1761641.02778232892
1771649.2329212406
1781657.4790858468
1791665.766481276
18051674.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.

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.

PerPPMT
1-4.90130154744293
232.7464319382852
332.9034353790867
433.0611915761092
533.2197041384625
633.3789766925474
733.5390128821728
833.6998163685912
933.8613908306361
1034.0237399647522

See Also

CUMODDFIPMT - Cumulative interest on the periodic annuity payments between a start period and an end period

CUMODDFIPMT - Cumulative interest on the periodic annuity payments between a start period and an end period

CUMODDFPPMT - Cumulative principal on the periodic annuity payments between a start period and an end period

CUMODDFPPMT - Cumulative principal on the periodic annuity payments between a start period and an end period

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