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.

SELECT [wct].[ODDFPMT](

<@Rate, float,>

,<@Nper, int,>

,<@PV, float,>

,<@FV, float,>

,<@FirstPeriod, float,>)

the periodic interest rate. *@Rate* is an expression of type **float** or of a type that can be implicitly converted to **float**.

the number of annuity payments. @*Nper *is an expression of type **int** or of a type that can be implicitly converted to **int**.

the present value of the annuity. *@PV* is an expression of type **float** or of a type that can be implicitly converted to **float**.

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**.

the length of the first period. *@FirstPeriod* is an expression of type **float** or of a type that can be implicitly converted to **float**.

float

Â· 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*.

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/6^{th} 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