RATE
Updated: 5 August 2010
Use RATE to calculate the interest rate per period of an annuity.
Syntax
SELECT [westclintech].[wct].[RATE] (
<@Nper, float,>
,<@Pmt, float,>
,<@PV, float,>
,<@FV, float,>
,<@Pay_type, int,>)
Arguments
@Nper
the total number of periods in the annuity to be calculated. @Nper is an expression of type float or of a type that can be implicitly converted to float.
@Pmt
the payment made each period. @Pmt cannot change over the life of the annuity. @Pmt is an expression of type float or of a type that can be implicitly converted to float.
@PV
the present value at the end 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 at the end of the annuity. @FV is an expression of type float or of a type that can be implicitly converted to float.
@Pay_type
the number 0 or 1 and indicates when payments are due. @Pay_type is an expression of type int or of a type that can be implicitly converted to int. If @Pay_type is NULL it is assumed to be 0.
Set @Pay_type equal to

If payments are due

0

At the end of a period

1

At the beginning of a period

Return Type
float
Remarks
· Rate finds the value that satisfies the following condition:
PMT(x, @Nper, @PV, @FV, @Pay_type)  @Pmt = 0
and it does this through iteration. If the solution does not resolve within 100 iterations, RATE will return an error
· The value returned by RATE is a function of @Nper. This means that if you entered monthly payment and the number of periods as the number of months, then the value for RATE is the monthly rate and needs to be multiplied by 12 to give the annual rate. Similarly, quarterly payments need to be adjusted by multiplying RATE by 4 and semiannual payment by 2.
Examples
SELECT wct.RATE(20*12
,500
,59777.1458511878
,0
,0
,NULL)*12
Here is the result set

0.0800000000000008
See Also