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/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"}]}
See Also
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