Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server RATE function for odd-first period loans


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



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service