 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

Support  Copyright 2008-2019 Westclintech LLC         Privacy Policy        Terms of Service