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.

SELECT [wct].[ODDFRATE](

<@Nper, int,>

,<@pmt, float,>

,<@PV, float,>

,<@FV, float,>

,<@FirstPeriod, float,>)

the number of annuity payments. @*Nper *is an expression of type **int** or of a type that can be implicitly converted to **int**.

the periodic annuity payment. *@Pmt* is an expression of type **float** or of a type that can be implicitly converted to **float**.

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

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

the length of the first period. *@FirstPeriod* is an expression of type **float** or of a type that can be implicitly converted to **float**.

float

Â· 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.

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.

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