NPER

Updated 2023-10-06 21:37:37.973000

Syntax

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

Description

Use the scalar function NPER to calculate the number of periods for an investment.

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

is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. @Pmt is an expression of type float or of a type that can be implicitly converted to float.

@Rate

the interest rate per period. @Rate 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.

@PV

the present value of the future payments . @PV is an expression of type float or of a type that can be implicitly converted to float.

Return Type

float

Remarks

NPER performs the following calculation:

NPER = Log((-@Fv * (@Rate / k) + @Pmt) / (@Pmt + (@Rate / k) * @Pv)) / Log(1 + @Rate)If @Pay_type = 0 Then k = 1 Else k = 1 + @Rate

It is important to be consistent with the units for @Rate and @Nper. For example if payments are to be paid monthly, then @Rate should be the monthly rate, which can be specified as the annual rate divided by 12. If payments are made quarterly, divide the annual rate by 4. If payments are made semi-annually, divide the annual rate by 2.

Funds that are paid should be represented with negative numbers. Funds that are received should be represented as positive numbers.

Examples

SELECT wct.NPER(.06 / 12, 2636.11261237265, -400000, 100000, 1);

Here is the result set.

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