SQL Server ODDFPMTSCHED Function

Updated 2023-10-06 21:42:47.807000

Description

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

table

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/6^th 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.

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.

In this example we c alculate 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.

See Also

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

LPMTSCHED - Amortization schedule for a loan with constant periodic payemnts and an odd first period where interest is accrued using the US rule

ODDFIPMT - Interest portion of a periodic payment for an annuity with an 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

ODDFRATE - Rate of an annuity with an odd first period given number of periods, periodic payment, present value, and future value.

PMTSCHED - Calculate an amortization schedule for a loan with no odd periods.