Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server present value odd-first period annuities


ODDFPV

Updated: 16 November 2014


Use the scalar function ODDFPV to calculate the present value of an annuity where the first period is either longer or shorter than the other periods.
Syntax
SELECT [wct].[ODDFPV](
  <@Rate, float,>
 ,<@Nper, int,>
 ,<@Pmt, float,>
 ,<@FV, float,>
 ,<@FirstPeriod, float,>)
Arguments
@Rate
the periodic interest rate. @Rate is an expression of type float or of a type that can be implicitly converted to float.
@Nper
the number of annuity payments. @Nper is an expression of type int or of a type that can be implicitly converted to int.
@Pmt
the periodic annuity payment. @Pmt is an expression of type float or of a type that can be implicitly converted to float.
@FV
the future value as at the end of the annuity. @FV is an expression of type float or of a type that can be implicitly converted to float.
@FirstPeriod
the length of the first period. @FirstPeriod is an expression of type float or of a type that can be implicitly converted to float.
Return Type
float
Remarks
·         If @Rate <= -1 then NULL is returned.
·         If @Nper < 1 then NULL is returned.
·         If @FirstPeriod <= 0 then NULL is returned.
·         If @Nper is NULL then @Nper = 0
·         If @Pmt is NULL then @Pmt = 0
·         If @Rate is NULL then @Rate = 0
·         If @FV is NULL then @FV = 0
·         If @FirstPeriod is NULL then @FirstPeriod = 1
·         ODDFPV uses the same conventions for the sign of the inputs and the results as Excel and Google spreadsheets; generally @Pmt and @FV should have then same sign and the ODDFPV result will have the opposite sign.
Examples
Calculate the present value of an annuity assuming a periodic rate of 0.5%, with 36 periodic payments. The periodic payment is 351.31 and there is no cash value at the end of the annuity. The first period is 1 and 5/6th longer than the other periods.
SELECT
   wct.ODDFPV(
        .005      --@Rate
       ,36        --@Nper
       ,351.31    --@Pmt
       ,0         --@FV
       ,1+5/6e+00 --@FirstPeriod
   ) as PV
This produces the following result.
PV
----------------------
-11500.0198920617


Calculate the present value of an annuity assuming a periodic rate of 0.5%, with 180 periodic payments of 1932.46. There is a 50,000 cash value at the end of the annuity. The first period is one-half as long as the other periods.
SELECT
   wct.ODDFPV(
        .005     --@Rate
       ,180      --@Nper
       ,1932.46 --@Pmt
       ,50000    --@FV
       ,0.5      --@FirstPeriod
   ) as PV
This produces the following result.
PV
----------------------
-250000.089483625
 


Calculate the present value of an automobile lease with a term of 3 years and an annual interest rate of 25%. Then weekly payment is 85.51 and the residual value at the end of the lease is 3,500. The first payment is due 2014-11-25.
SELECT
   wct.ODDFPV(
        .25 * 7/365e+00     --@Rate
       ,156                 --@Nper
       ,85.51               --@PV
       ,3500                --@FV
       ,DATEDIFF(d,'2014-11-13','2014-11-25')/7e+00    --@FirstPeriod
   ) as PV
This produces the following result.
PV
----------------------
-10999.8839704582

 

 

See Also

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service