LRATE
Updated 2023-10-11 12:58:11.953000
Syntax
SELECT [westclintech].[wct].[LRATE] (
<@PV, float,>
,<@LoanDate, datetime,>
,<@Pmt, float,>
,<@FirstPayDate, datetime,>
,<@NumPmts, int,>
,<@Pmtpyr, int,>
,<@DaysInYr, float,>
,<@FV, float,>
,<@IntRule, nvarchar(4000),>
,<@Guess, float,>)
Description
Use the scalar-valued function LRATE to calculate the annual interest rate for an annuity with an odd first period.
Arguments
@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.
@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.
@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.
@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.
@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.
@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.
@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.
@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.
@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.
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, 365, 365360 or 365365.
@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.
{"columns":[{"field":"RATE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"RATE":"0.0599999673476083"}]}
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.
{"columns":[{"field":"RATE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"RATE":"0.0601320514137377"}]}
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.
{"columns":[{"field":"RATE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"RATE":"0.0600022174117869"}]}
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.
{"columns":[{"field":"RATE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"RATE":"0.120421963481495"}]}
Calculate the rate for a 75,000 5-year loan made on Sep15th with no payments due until Jan 1st and with a payment of 1987.77 due monthly thereafter.
SELECT wct.LRATE( 75000, --PV
'09/15/2022', --Loan Date
1987.77, --PMT
'01/01/2023', --First Payment Date
60, --Number of payments (5*12)
12, --Number of payments per year
365360, --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.
{"columns":[{"field":"RATE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"RATE":"0.179999855108578"}]}