NPERGA

Updated 2023-10-06 21:38:17.427000

Syntax

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

Description

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

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 initial annuity payment. @Pmt 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.

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

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

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.

{"columns":[{"field":"NPER","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"NPER":"40"}]}

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.

{"columns":[{"field":"FV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"NPER","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"FV":"62500","NPER":"12"},{"FV":"125000","NPER":"17"},{"FV":"250000","NPER":"24"},{"FV":"500000","NPER":"32"},{"FV":"1000000","NPER":"40"},{"FV":"2000000","NPER":"50"}]}

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.

{"columns":[{"field":"FV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"NPER","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"FVGA N","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"FVGA N-1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"FV":"62500","NPER":"12","FVGA N":"70742.4681010136","FVGA N-1":"61684.9077828534"},{"FV":"125000","NPER":"17","FVGA N":"128910.824736141","FVGA N-1":"115342.345690343"},{"FV":"250000","NPER":"24","FVGA N":"260187.435696821","FVGA N-1":"236850.350918433"},{"FV":"500000","NPER":"32","FVGA N":"525600.096451581","FVGA N-1":"483214.786307652"},{"FV":"1000000","NPER":"40","FVGA N":"1002583.156041","FVGA N-1":"926859.117432558"},{"FV":"2000000","NPER":"50","FVGA N":"2146258.99019759","FVGA N-1":"1992229.6213551"}]}