CUMODDFIPMT
Updated: 16 November 2014
Use the scalar function CUMODDFIPMT to calculate the cumulative interest on the periodic payments for an annuity where the first period is either longer or shorter than the other periods.
Syntax
SELECT [wct].[CUMODDFIPMT](
<@Rate, float,>
,<@Nper, int,>
,<@PV, float,>
,<@FV, float,>
,<@StartPeriod, int,>
,<@EndPeriod, int,>
,<@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.
@StartPeriod
the first period to be included in the accumulation. @StartPeriod is an expression of type int or of a type that can be implicitly converted to int.
@EndPeriod
the last period to be included in the accumulation. @EndPeriod is an expression of type int or of a type that can be implicitly converted to int.
@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 @StartPeriod < 1 then NULL is returned.
· If @EndPeriod > @Nper then NULL is returned.
· If @EndPeriod < @StartPeriod then NULL is returned.
· If @FirstPeriod <= 0 then NULL is returned.
· If @Rate is NULL then @Rate = 0.
· If @Nper is NULL then @Nper = 1.
· If @PV is NULL then @PV = 0.
· If @FV is NULL then @FV = 0.
· If @StartPeriod is NULL then @StartPeriod = 1.
· If @EndPeriod is NULL then @EndPeriod = @StartPeriod.
· If @FirstPeriod is NULL then @FirstPeriod = 1.
· CUMODDFIPMT 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 CUMODDFIPMT result will have the opposite sign of @PV.
Examples
Calculate the interest portion for the first period of 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.CUMODDFIPMT(
.005 --@Rate
,36 --@Nper
,-11500 --@PV
,0 --@FV
,1 --@StartPeriod
,1 --@EndPeriod
,1+5/6e+00 --@FirstPeriod
) as [Cumulative Interest]
This produces the following result.
Cumulative Interest
----------------------
105.63622380599
Using the same basic information in this SQL, we calculate the cumulative interest for the first 10 payments.
SELECT
wct.CUMODDFIPMT(
.005 --@Rate
,36 --@Nper
,-11500 --@PV
,0 --@FV
,1 --@StartPeriod
,10 --@EndPeriod
,1+5/6e+00 --@FirstPeriod
) as [Cumulative Interest]
This produces the following result.
Cumulative Interest
----------------------
558.349885303963
Calculate the interest portion of the first 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.CUMODDFIPMT(
.005 --@Rate
,180 --@Nper
,-250000 --@PV
,50000 --@FV
,1 --@StartPeriod
,1 --@EndPeriod
,0.5 --@FirstPeriod
) as [Cumulative Interest]
This produces the following result.
Cumulative Interest
----------------------
624.220697042765
Using the same basic information in this SQL, we calculate the cumulative interest for the last 10 payments.
SELECT
wct.CUMODDFIPMT(
.005 --@Rate
,180 --@Nper
,-250000 --@PV
,50000 --@FV
,171 --@StartPeriod
,180 --@EndPeriod
,0.5 --@FirstPeriod
) as [Cumulative Interest]
This produces the following result.
Cumulative Interest
----------------------
2953.57105763922
Calculate the interest portion of the first 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.CUMODDFIPMT(
.25 * 7/365e+00 --@Rate
,156 --@Nper
,-11000 --@PV
,3500 --@FV
,1 --@StartPeriod
,1 --@EndPeriod
,DATEDIFF(d,'2014-11-13','2014-11-25')/7e+00 --@FirstPeriod
) as [Cumulative Interest]
This produces the following result.
Cumulative Interest
----------------------
90.5657016076075
Using the same basic information in this SQL, we calculate the cumulative interest for the first 10 payments.
SELECT
wct.CUMODDFIPMT(
.25 * 7/365e+00 --@Rate
,156 --@Nper
,-11000 --@PV
,3500 --@FV
,1 --@StartPeriod
,10 --@EndPeriod
,DATEDIFF(d,'2014-11-13','2014-11-25')/7e+00 --@FirstPeriod
) as [Cumulative Interest]
This produces the following result.
Cumulative Interest
----------------------
559.725417921967
See Also