ODDFRATE
Updated: 16 November 2014
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 [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.
@Pmt
the periodic annuity payment. @Pmt is an expression of type float or of a type that can be implicitly converted to float.
@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.
Rate
----------------------
0.00500009249040195
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.
Rate
----------------------
0.00500000422661209
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.
Rate
----------------------
0.249993317585699
See Also