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