RATE

Updated 2023-10-06 23:21:42.150000

Syntax

SELECT [westclintech].[wct].[RATE] (
  <@Nper, float,>
 ,<@Pmt, float,>
 ,<@PV, float,>
 ,<@FV, float,>
 ,<@Pay_type, int,>)

Description

Use the scalar function RATE to calculate the interest rate of an annuity.

Arguments

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

{"columns":[{"field":"Set @Pay_type equal to","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell","width":157},{"field":"If payments are due","width":192}],"rows":[{"Set @Pay_type equal to":"0","If payments are due":"At the end of a period"},{"Set @Pay_type equal to":"1","If payments are due":"At the beginning of a period"}]}

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

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

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

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

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 semi-annual payment by 2.

Examples

SELECT wct.RATE(20 * 12, 500, -59777.1458511878, 0, 0, NULL) * 12;

Here is the result set.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"0.0800000000000008"}]}