ODDFRATE

Updated 2023-10-06 22:29:28.310000

Syntax

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

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.

Arguments

@Pmt

the periodic annuity payment. @Pmt 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.

@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.

@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.

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

{"columns":[{"field":"Rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Rate":"0.00500110451315692"}]}

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.

{"columns":[{"field":"Rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Rate":"0.00499996750975355"}]}

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.

{"columns":[{"field":"Rate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Rate":"0.25000215699995"}]}

ODDFRATE

Syntax

Description

Arguments

@Pmt

@FirstPeriod

@FV

@Nper

@PV

Return Type

Remarks

Examples

See Also