SQL Server AMORTSCHED Function

Updated 2023-10-10 14:24:37.143000

Description

Use the table-valued function AMORTSCHED to generate an amortization schedule for a loan. The amortization schedule includes the payment number, the payment date, and the principal amount at the beginning of the period, the interest amount for the period, the principal payment for the period, any deferred interest for the period, and the ending principal amount.

AMORTSCHED supports loans with odd first periods, does US Rule or actuarial interest calculations, and allows you to specify a terminal (or future) value for the loan.

Syntax

SELECT * FROM [westclintech].[wct].[AMORTSCHED] (
  <@PV, float,>
 ,<@LoanDate, datetime,>
 ,<@Rate, float,>
 ,<@FirstPayDate, datetime,>
 ,<@NumPmts, int,>
 ,<@Pmtpyr, int,>
 ,<@DaysInYr, int,>
 ,<@FV, float,>
 ,<@IntRule, nvarchar(4000),>)

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.

@Rate

the annual interest rate. @Rate 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.

Return Type

table

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, 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, 365, 365360 or 365365.

@PV must be greater than zero.

Examples

Generate the amortization schedule 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 *
FROM wct.AMORTSCHED(   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
                       NULL,         --Days in year (defaults to 360)
                       NULL,         --FV (defaults to 0)
                       NULL          --IntRule (defaults to 'A' meaning actuarial)
                   );

This produces the following results.

This is an example of a loan that negatively amortizes principal using the actuarial rule.

SELECT *
FROM wct.AMORTSCHED(   50000,        --Loan Amount
                       '11/01/2010', --Loan Start Date
                       .12,          --Annual Interest Rate
                       '04/15/2011', --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)
                   );

This produces the following result.

The same loan, except that the US Rule is applied, so there is no negative amortization.

SELECT *
FROM wct.AMORTSCHED(   50000,        --Loan Amount
                       '11/01/2010', --Loan Start Date
                       .12,          --Annual Interest Rate
                       '04/15/2011', --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)
                       'U'           --IntRule (defaults to 'A' meaning actuarial)
                   );

This produces the following result.

In this example, we have a loan with weekly payments, but we want to summarize the payment information into by month and year.

SELECT MONTH(date_pmt) as [MONTH],
       YEAR(date_pmt) as [YEAR],
       ROUND(SUM(amt_pmt), 2) as [Monthly Payments],
       ROUND(SUM(amt_int_pay), 2) as [Monthly Interest],
       ROUND(SUM(amt_prin_pay), 2) as [Monthly Principal]
FROM wct.AMORTSCHED(   50000,        --Loan Amount
                       '10/28/2010', --Loan Start Date
                       .12,          --Annual Interest Rate
                       '11/05/2010', --First Payment Date
                       156,          --Number of payments
                       52,           --Number of payments per year
                       NULL,         --Days in year (defaults to 360)
                       NULL,         --FV (defaults to 0)
                       NULL          --IntRule (defaults to 'A' meaning actuarial)
                   )
GROUP BY MONTH(date_pmt),
         YEAR(date_pmt)
ORDER BY 2,
         1;

This produces the following result.

This is an example of a loan with an odd first period with the same payment for every period where the interest is calculated using the actual number of days in the period divided by 360.

SELECT *
FROM wct.AMORTSCHED(   50000,        --Loan Amount
                       '11/01/2010', --Loan Start Date
                       .12,          --Annual Interest Rate
                       '04/15/2011', --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)
                       'A'           --IntRule (defaults to 'A' meaning actuarial)
                   );

This produces the following result.