SQL Server ODDFRATE Function

Updated 2023-10-06 22:29:28.310000

Description

Use the scalar function ODDFRATE to calculate the periodic interest rate for an annuity where the first period is either longer or shorter than the other periods.

Syntax

SELECT [westclintech].[wct].[ODDFRATE](
  <@Nper, int,>
 ,<@pmt, float,>
 ,<@PV, float,>
 ,<@FV, float,>
 ,<@FirstPeriod, float,>)

Arguments

@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 (current) 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 @Nper < 1 then NULL is returned.

If @FirstPeriod <= 0 then NULL is returned.

If the signs of @FV, @PV, and @PV are the same then NULL is returned.

If @Pmt = 0 and @FV = 0 then -1 is returned.

If @Nper is NULL then @Nper = 0.

If @Pmt is NULL then @Pmt = 0.

If @PV is NULL then @PV = 0.

If @FV is NULL then @FV = 0.

If @FirstPeriod is NULL then @FirstPeriod = 1.

ODDFRATE uses the same conventions for the sign of the inputs and the results as Excel and Google spreadsheets; generally @Pmt and @FV should have then same sign and @PV will have the opposite sign.

Examples

Calculate the rate for an annuity with 36 periodic payments of 351.31 and a current value of 11,500. 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.ODDFRATE(   36,           --@Nper
                       351.31,       --@Pmt
                       -11500,       --@PV           
                       0,            --@FV
                       1 + 5 / 6e+00 --@FirstPeriod
                   ) as Rate;

This produces the following result.

Calculate the rate for an annuity with a current value of 250,000 and 180 periodic payments of 1932.46. 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.ODDFRATE(   180,     --@Nper
                       1932.46, --@Pmt
                       -250000, --@PV           
                       50000,   --@FV
                       0.5      --@FirstPeriod
                   ) as Rate;

This produces the following result.

Calculate the annual rate for an automobile lease having a current value of 11000 with a term of 3 years and weekly payments of 85.51. The residual value at the end of the lease is 3,500. The first payment is due 2014-11-25.

SELECT wct.ODDFRATE(   156,                                            --@Nper
                       85.51,                                          --@pmt
                       -11000,                                         --@PV
                       3500,                                           --@FV
                       DATEDIFF(d, '2014-11-13', '2014-11-25') / 7e+00 
                                 --@FirstPeriod
                   ) * 365 / 7 as Rate;

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

LRATE - annual interest rate for an annuity with an odd first period

ODDFIPMT - Interest portion of a 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

RATE - Rate of an annuity given number of periods, periodic payment, present value, and future value