Updated: 16 November 2014

Use the scalar function CUMODDFPPMT to calculate the cumulative principal on the periodic payments for an annuity where the first period is either longer or shorter than the other periods.

SELECT [wct].[CUMODDFPPMT](

<@Rate, float,>

,<@Nper, int,>

,<@PV, float,>

,<@FV, float,>

,<@StartPeriod, int,>

,<@EndPeriod, int,>

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

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

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

· The principal payment amount for the final period includes the *@FV* amount.

· CUMODDFPPMT 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 CUMODDFPPMT result will have the opposite sign of *@PV*.

Calculate the principal 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/6^{th} longer than the other periods

SELECT

wct.CUMODDFPPMT(

.005 --@Rate

,36 --@Nper

,-11500 --@PV

,0 --@FV

,1 --@StartPeriod

,1 --@EndPeriod

,1+5/6e+00 --@FirstPeriod

) as [Cumulative Principal]

This produces the following result.

Cumulative Principal

----------------------

245.673168518522

Using the same basic information in this SQL, we calculate the cumulative principal for the first 10 payments.

SELECT

wct.CUMODDFPPMT(

.005 --@Rate

,36 --@Nper

,-11500 --@PV

,0 --@FV

,1 --@StartPeriod

,10 --@EndPeriod

,1+5/6e+00 --@FirstPeriod

) as [Cumulative Principal]

This produces the following result.

Cumulative Principal

----------------------

2954.74403794116

Calculate the principal 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.CUMODDFPPMT(

.005 --@Rate

,180 --@Nper

,-250000 --@PV

,50000 --@FV

,1 --@StartPeriod

,1 --@EndPeriod

,0.5 --@FirstPeriod

) as [Cumulative Principal]

This produces the following result.

Cumulative Principal

----------------------

1308.23854972428

Using the same basic information in this SQL, we calculate the cumulative principal for the last 10 payments.

SELECT

wct.CUMODDFPPMT(

.005 --@Rate

,180 --@Nper

,-250000 --@PV

,50000 --@FV

,171 --@StartPeriod

,180 --@EndPeriod

,0.5 --@FirstPeriod

) as [Cumulative Principal]

This produces the following result.

Cumulative Principal

----------------------

66371.0214100313

Calculate the principal 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.CUMODDFPPMT(

.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 Principal]

This produces the following result.

Cumulative Principal

----------------------

-5.0546399988707

Using the same basic information in this SQL, we calculate the cumulative principal 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 Principal]

This produces the following result.

Cumulative Principal

----------------------

295.385198165395

· CUMLPPMT - Cumulative principal payments for a lease where interest is calculated using the US rule