# SQL Server number of periods in a growing annuity

NPERGA

Updated: 07 February 2011

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

 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
Â·         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.
NPER
----------------------
40

(1 row(s) affected)

In the following statement we can see how long it takes to reach various savings threshholds.
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.
FV                   NPER
----------- ----------------------
62500                     12
125000                     17
250000                     24
500000                     32
1000000                     40
2000000                     50

(6 row(s) affected)

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.
FV        NPER                 FVGA N               FVGA N-1
----------- ----------- ---------------------- ----------------------
62500          12       70742.4681010136       61684.9077828534
125000          17       128910.824736141       115342.345690343
250000          24       260187.435696821       236850.350918433
500000          32       525600.096451581       483214.786307652
1000000          40         1002583.156041       926859.117432558
2000000          50       2146258.99019759        1992229.6213551

(6 row(s) affected)