Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

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)



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service