SQL Server NPERGA Function

Updated 2023-10-06 21:38:17.427000

Description

Use the scalar function NPERGA to calculate the number of whole periods for a growing annuity to reach a future value.

Syntax

SELECT [wctFinancial].[wct].[NPERGA](
  <@FV, float,>
 ,<@Pgr, float,>
 ,<@Pmt, float,>
 ,<@Rate, float,>
 ,<@Pay_type, int,>)

Arguments

@FV

the future value of the annuity. @FV is an expression of type float or of a type that can be implicitly converted to float.

@Pgr

the periodic growth rate of the annuity. This is the percentage amount, expressed as a decimal, by which the annuity will increase in each period. @Pgr is an expression of type float or of a type that can be implicitly converted to float.

@Pmt

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

@Rate

the percentage rate of return, expressed as a decimal, that you expect the annuity to earn over the number of periods. The annuity payments are compounded using this value. @Rate 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.

Return Type

float

Remarks

If the @Pay_type is not equal to zero, it is assumed to be 1.

To calculate the Future value of a growing annuity, use the FVGA function.

Examples

Let's say that you are contributing $3200 per year into a tax-deferred account which you expect to earn 7% per year and you expect your contribution to increase 3% per year. How many years (periods) until the tax-deferred account contains $1 million?

SELECT wct.NPERGA(   1000000, --@FV
                     .03,     --@Pgr
                     3200,    --@Pmt
                     .07,     --@Rate
                     1        --@Pay_type
                 ) as NPER;

This produces the following result.

In the following statement we can see how long it takes to reach various savings thresholds .

SELECT FV,
       wct.NPERGA(fv, .03, 3200, .07, 1) as NPER
FROM
(
    VALUES
        (62500),
        (125000),
        (250000),
        (500000),
        (1000000),
        (2000000)
) n (fv);

This produces the following result.

We can double check that calculation with following SQL.

SELECT *,
       wct.FVGA(3200, .03, NPER, .07, 1) as [FVGA N],
       wct.FVGA(3200, .03, NPER - 1, .07, 1) as [FVGA N-1]
FROM
(
    SELECT FV,
           wct.NPERGA(fv, .03, 3200, .07, 1) as NPER
    FROM
    (
        VALUES
            (62500),
            (125000),
            (250000),
            (500000),
            (1000000),
            (2000000)
    ) n (fv)
) m;

This produces the following result.