LRATE
Updated: 11 May 2012
Use the scalar-valued function LRATE to calculate the annual interest rate for an annuity with an odd first period.
Syntax
SELECT [wctFinancial].[wct].[LRATE] (
<@PV, float,>
,<@LoanDate, datetime,>
,<@Pmt, float,>
,<@FirstPayDate, datetime,>
,<@NumPmts, int,>
,<@Pmtpyr, int,>
,<@DaysInYr, float,>
,<@FV, float,>
,<@IntRule, nvarchar(4000),>
,<@Guess, float,>)
Arguments
@PV
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.
@LoanDate
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.
@Pmt
the payment made each period. @Pmt cannot change over the life of the annuity. @Pmt is an expression of type float or of a type that can be implicitly converted to float.
@FirstPayDate
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.
@NumPmts
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.
@Pmtpyr
the number of loan payments made in a year. @Pmtpyr is an expression of type int or of a type that can be implicitly converted to int.
@DaysInYr
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.
@FV
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.
@IntRule
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.
@Guess
the user-supplied initial guess used in the first iteration of the rate calculation. @Guess is an expression of type float or of a type that can be implicitly converted to float or is NULL.
Return Type
float
Remarks
· 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, 5, 6, 12, 13, 24, 26, 52, or 365
· @NumPmts must be greater than 1
· @DaysInYr must be 360, 364, or 365
· @PV must be greater than zero
Examples
Calculate the rate for a 50,000, 5-year loan starting on 1 November with payments of 966.64 due on the first of every month.
SELECT wct.LRATE(
50000 --PV
,'11/01/2010' --Loan Date
,966.64 --PMT
,'12/01/2010' --First Payment Date
,60 --Number of payments (5*12)
,12 --Number of payments per year
,NULL --Days in year (defaults to 360)
,NULL --FV (defaults to 0)
,NULL --IntRule (defaults to 'A' meaning actuarial)
,NULL --Guess
) as RATE
This produces the following result.
RATE
----------------------
0.06
Calculate the rate for a 50,000, 5-year loan starting on 1 November with payments of 892.42 due every 4 weeks, starting on 1 December.
SELECT wct.LRATE(
50000 --PV
,'11/01/2010' --Loan Date
,892.42 --PMT
,'12/01/2010' --First Payment Date
,65 --Number of payments (5*12)
,13 --Number of payments per year
,NULL --Days in year (defaults to 360)
,NULL --FV (defaults to 0)
,NULL --IntRule (defaults to 'A' meaning actuarial)
,NULL --Guess
) as RATE
This produces the following result.
RATE
----------------------
0.0600012653476037
Calculate the rate for a 50,000, 5-year loan starting on 1 November with payments of 412.45, due semi-monthly, starting on 1 December. The loan has a balloon payment of 10,000 at the end.
SELECT wct.LRATE(
50000 --PV
,'11/01/2010' --Loan Date
,412.45 --PMT
,'12/01/2010' --First Payment Date
,120 --Number of payments (5*12)
,24 --Number of payments per year
,NULL --Days in year (defaults to 360)
,10000 --FV (defaults to 0)
,NULL --IntRule (defaults to 'A' meaning actuarial)
,NULL --Guess
) as RATE
This produces the following result.
RATE
----------------------
0.0600022174769416
Calculate the rate for a 150,000 4-year loan made on 13 October with no payments due until 4 January and with a payment of 3710.27 due every 4 weeks after that.
SELECT wct.LRATE(
150000 --PV
,'10/13/2010' --Loan Date
,3710.27 --PMT
,'01/04/2011' --First Payment Date
,52 --Number of payments (5*12)
,13 --Number of payments per year
,NULL --Days in year (defaults to 360)
,NULL --FV (defaults to 0)
,NULL --IntRule (defaults to 'A' meaning actuarial)
,NULL --Guess
) as RATE
This produces the following result.
RATE
----------------------
0.119988440098166