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