Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server periodic payment odd-first period annuities


ODDFPMT

Updated: 16 November 2014


Use the scalar function ODDFPMT to calculate the periodic payment for an annuity where the first period is either longer or shorter than the other periods.
Syntax
SELECT [wct].[ODDFPMT](
  <@Rate, float,>
 ,<@Nper, int,>
 ,<@PV, 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.
@PV
the present value of the annuity. @PV 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 @Rate is NULL then @Rate = 0.
·         If @PV is NULL then @PV = 0.
·         If @FV is NULL then @FV = 0.
·         If @FirstPeriod is NULL then @FirstPeriod = 1.
·         ODDFPMT uses the same conventions for the sign of the inputs and the results as Excel and Google spreadsheets; generally @PV and @FV should have opposite signs and the ODDFPMT result will have the opposite sign of @PV.
Examples
Calculate the periodic payment for an annuity assuming a periodic rate of 0.5%, with 36 periodic payments. The price of the annuity is 11,500 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.ODDFPMT(
        .005      --@Rate
       ,36        --@Nper
       ,-11500    --@PV
       ,0         --@FV
       ,1+5/6e+00 --@FirstPeriod
   ) as PMT

This produces the following result.
PMT
----------------------
351.309392324512


Calculate the periodic payment for an annuity assuming a periodic rate of 0.5%, with 180 periodic payments. The price of the annuity is 250,000 and 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.ODDFPMT(
        .005     --@Rate
       ,180      --@Nper
       ,-250000  --@PV
       ,50000    --@FV
       ,0.5      --@FirstPeriod
   ) as PMT
This produces the following result.
PMT
----------------------
1932.45924676706
 


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

 

 

See Also

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service