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)