Updated: 31 October 2010

Use R78IPMT to calculate the interest payment for a specified payment for a loan or lease using the Rule of 78.

SELECT [westclintech].[wct].[R78IPMT] (

<@PV, float,>

,<@LoanDate, datetime,>

,<@Rate, float,>

,<@FirstPayDate, datetime,>

,<@NumPmts, int,>

,<@Pmtpyr, int,>

,<@Per, int,>

,<@DaysInYr, int,>

,<@FV, float,>

,<@IntRule, nvarchar(4000),>)

the principal amount of the loan or lease. *@PV* is an expression of type **float** or of a type that can be implicitly converted to **float**.

the date that the loan starts accruing interest. @*LoanStartDate *is an expression of type **datetime** or of a type that can be implicitly converted to **datetime**.

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

the date that the first payment is due. @*FirstPayDate *is an expression of type **datetime** or of a type that can be implicitly converted to **datetime**.

the total number of payments to be recorded over the life of the loan. *@NumPmts* is an expression of type **int** or of a type that can be implicitly converted to **int**.

the number of loan payments made in a year. *@NumPmts* is an expression of type **int** or of a type that can be implicitly converted to **int**.

the period number for which you want the payment information. *@NumPmts* is an expression of type **int** or of a type that can be implicitly converted to **int**.

the denominator number of days to be used in the calculation of the interest amount in the odd first period. *@DaysInYr* is an expression of type **int** or of a type that can be implicitly converted to **int**.

the future value at the end of the loan. *@FV* is an expression of type **float** or of a type that can be implicitly converted to **float**.

Identifies the loan as conforming to the US Rule (“U”) or the actuarial rule (“A”) regarding the compounding of interest in the odd first period.

float

· If *@DaysInYr* is NULL, then *@DaysInYr* = 360

· If *@FV* is NULL, then *@FV* = 0

· If *@IntRule* is NULL, then *@IntRule* = “A”

· *@FirstPayDate* must be greater than *@LoanDate*

· *@Pmtpyr* must be 1, 2, 3, 4, 6, 12, 13, 24, 26, 52, or 365

· *@NumPmts* must be greater than 1

· *@Rate* must be greater than zero

· *@DaysInYr* must be 360, 364, or 365

· *@PV* must be greater than zero

· *@Per* must be between 1 and *@NumPmts*

Calculate the first interest payment for a 50,000, 5-year loan starting on 1 November with payments due on the first of every month. The rate on the loan is 6 per cent.

SELECT wct.R78IPMT(

50000 --Loan Amount

,'11/01/2010' --Loan Start Date

,.06 --Annual Interest Rate

,'12/01/2010' --First Payment Date

,60 --Number of payments (5*12)

,12 --Number of payments per year

,1 --Period Number

,NULL --Days in year (defaults to 360)

,NULL --FV (defaults to 0)

,NULL --IntRule (Defaults to 'A' for acturial)

) as R78IPMT

This produces the following result.

R78IPMT

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

262.242773386387

(1 row(s) affected)

Calculate the last interest payment for a 50,000, 5-year loan starting on 1 November with payments due on the first of every month. The rate on the loan is 6 per cent.

SELECT wct.R78IPMT(

50000 --Loan Amount

,'11/01/2010' --Loan Start Date

,.06 --Annual Interest Rate

,'12/01/2010' --First Payment Date

,60 --Number of payments (5*12)

,12 --Number of payments per year

,60 --Period Number

,NULL --Days in year (defaults to 360)

,NULL --FV (defaults to 0)

,NULL --IntRule (Defaults to 'A' for acturial)

) as R78IPMT

This produces the following result.

R78IPMT

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

4.37071288977312

(1 row(s) affected)

Calculate the first interest payment for a 150,000 4-year loan at 12 percent interest using the US Rule made on 13 October with no payments due until 4 January and with payments due every 4 weeks after that.

SELECT wct.R78IPMT(

150000 --Loan Amount

,'10/13/2010' --Loan Start Date

,.12 --Annual Interest Rate

,'01/04/2011' --First Payment Date

,52 --Number of payments (4*13)

,13 --Number of payments per year

,1 --Period Number

,365 --Days in year

,0 --FV

,'U' --IntRule

) as R78IPMT

This produces the following result.

R78IPMT

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

1620.15597452123

(1 row(s) affected)

Let’s look at the same interest payment, but this time we will specify the actuarial method as the interest rule.

SELECT wct.R78IPMT(

150000 --Loan Amount

,'10/13/2010' --Loan Start Date

,.12 --Annual Interest Rate

,'01/04/2011' --First Payment Date

,52 --Number of payments (4*13)

,13 --Number of payments per year

,1 --Period Number

,365 --Days in year

,0 --FV

,'A' --IntRule

) as R78IPMT

This produces the following result.

R78IPMT

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

1620.32143663345

(1 row(s) affected)

The difference between the interest payment amount and the payment amount under the actuarial method is added to the principal, resulting a negative principal payment in the first period, increasing the outstanding principal balance.

If you don’t know the period number that you want, you can use the NPNO (Next Payment Number) or PPNO (Previous Payment Number) functions to calculate the period number.

In this example, we calculate the current period interest for a 50,000 5-year loan on 10/31/2010, which originated on 9/15/2008 at a rate of 5.5% with monthy payments commencing on 10/15/2008.

SELECT wct.R78IPMT(

50000 --Loan Amount

,'09/15/2008' --Loan Start Date

,.055 --Annual Interest Rate

,'10/15/2008' --First Payment Date

,60 --Number of payments (5*12)

,12 --Number of payments per year

,wct.NPNO(

'10/31/2010' --Settlement Date

,'10/15/2008' --First Payment Date

,12 --Payments per Yer

,60 --Number of Payments

) --Period Number

,365 --Days in year

,0 --FV

,'A' --IntRule

) as R78IPMT

This produces the following result.

R78IPMT

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

139.684168326293

(1 row(s) affected)