SQL Server LPMTSCHED Function

Updated 2023-10-11 12:06:48.083000

Description

Use the table-valued function LPMTSCHED to generate a loan amortization schedule, given the period payment amount, the principal amount (or present value amount), and the balloon payment (or future value amount). 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.

LPMTSCHED 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. LPMTSCHED also lets you calculate the amortization schedule using a different term for the amortization and the maturity, with the final payment amount adjusted for the outstanding principal balance.

LPMTSCHED lets you enter the periodic payment, the interest rate, or both. If the rate is not entered, the rate is calculated from the periodic payment. If the periodic payment is not entered, the periodic payment is calculated from the rate. If both the periodic payment and the rate are entered, then the schedule then the interest portion of each period is calculated using the rate, and the principal payment portion is calculated using periodic payment minus that interest payment amount. This may result in an odd final payment or even in a reduction in the number of payments for the loan.

Syntax

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

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 is an expression of type float or of a type that can be implicitly converted to float.

@Rate

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

@Decimals

the number of decimal places to round the calculated amounts. @Decimals is an expression of type int or of a type that can be implicitly converted to int.

@LastPmtNum

the last payment number if the life of the loan is shorter than the amortization period. @LastPmtNum is an expression of type int or of a type that can be implicitly converted to int.

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.

If @Decimals is NULL, then @Decimals = 2.

If @LastPmtNum is NULL, the @LastPmtNum = @NumPmts.

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 periodic payment is 966.64.

SELECT *
FROM wct.LPMTSCHED(   50000,        --Present Value
                      '11/01/2010', --Loan Date
                      966.64,       --Pmt
                      NULL,         --Rate
                      '12/01/2010', --First Payment Date
                      60,           --Number of Payments
                      12,           --Payments per Year
                      NULL,         --Days In Year
                      0,            --Future Value
                      NULL,         --Intrule
                      2,            --Decimals
                      NULL          --Last Payment Number
                  );

This produces the following result.

Generate the amortization schedule for a 50,000, 5-year loan starting on 1 November with payments of 1,161.41 due on the fifteenth of every month starting 15-Apr-2011.

SELECT *
FROM wct.LPMTSCHED(   50000,        --Present Value
                      '11/01/2010', --Loan Date
                      1161.41,      --Pmt
                      NULL,         --Rate
                      '04/15/2011', --First Payment Date
                      60,           --Number of Payments
                      12,           --Payments per Year
                      NULL,         --Days In Year
                      0,            --Future Value
                      NULL,         --Intrule
                      2,            --Decimals
                      NULL          --Last Payment Number
                  );

This produces the following result.

In this example, the US Rule is applied, so there is no negative amortization. The monthly payment is 1160.82, and all other parameters are from the previous example.

SELECT *
FROM wct.LPMTSCHED(   50000,        --Present Value
                      '11/01/2010', --Loan Date
                      1160.82,      --Pmt
                      NULL,         --Rate
                      '04/15/2011', --First Payment Date
                      60,           --Number of Payments
                      12,           --Payments per Year
                      NULL,         --Days In Year
                      0,            --Future Value
                      'U',          --Intrule
                      2,            --Decimals
                      NULL          --Last Payment Number
                  );

This produces the following result.

In this example, we have a 7,125,000 loan that pays monthly and is being amortized over 25 years, but the loan is paid off in full at the end of 10 years. The monthly payment is 41,554.10

SELECT *
FROM wct.LPMTSCHED(   7125000,      --Present Value
                      '11/18/2008', --Loan Date
                      41554.10,     --Pmt
                      NULL,         --Rate
                      '12/1/2008',  --First Payment Date
                      300,          --Number of Payments
                      12,           --Payments per Year
                      NULL,         --Days In Year
                      0,            --Future Value
                      NULL,         --Intrule
                      2,            --Decimals
                      120           --Last Payment Number
                  );

This produces the following result.

If we wanted to produce a summary by year of the previous example, we could enter:

SELECT YEAR(date_pmt) as [YEAR],
       ROUND(SUM(amt_pmt), 2) as [Yearly Payments],
       ROUND(SUM(amt_int_pay), 2) as [Yearly Interest],
       ROUND(SUM(amt_prin_pay), 2) as [Yearly Principal]
FROM wct.LPMTSCHED(   7125000,      --Present Value
                      '11/18/2008', --Loan Date
                      41554.10,     --Pmt
                      NULL,         --Rate
                      '12/1/2008',  --First Payment Date
                      300,          --Number of Payments
                      12,           --Payments per Year
                      NULL,         --Days In Year
                      0,            --Future Value
                      NULL,         --Intrule
                      2,            --Decimals
                      120           --Last Payment Number
                  )
GROUP BY YEAR(date_pmt)
ORDER BY 1;

This produces the following result.

In this example, we will generate a schedule using an annual rate.

SELECT *
FROM wct.LPMTSCHED(   100000,       --Present Value
                      '2012-05-02', --Loan Date
                      NULL,         --Pmt
                      .18,          --Rate
                      '2012-06-01', --First Payment Date
                      12,           --Number of Payments
                      12,           --Payments per Year
                      NULL,         --Days In Year
                      0,            --Future Value
                      NULL,         --Intrule
                      2,            --Decimals
                      NULL          --Last Payment Number
                  );

This produces the following result.

Now, let's take the same loan, and see what happens if we keep the interest rate the same and round the payment up to 9200.

SELECT *
FROM wct.LPMTSCHED(   100000,       --Present Value
                      '2012-05-02', --Loan Date
                      9200,         --Pmt
                      .18,          --Rate
                      '2012-06-01', --First Payment Date
                      12,           --Number of Payments
                      12,           --Payments per Year
                      NULL,         --Days In Year
                      0,            --Future Value
                      NULL,         --Intrule
                      2,            --Decimals
                      NULL          --Last Payment Number
                  );

This produces the following result.

And here's what happens with the same loan, if we keep the payment at 9200 and make the rate NULL, so that the function will calculate the rate based on the input.

SELECT *
FROM wct.LPMTSCHED(   100000,       --Present Value
                      '2012-05-02', --Loan Date
                      9200,         --Pmt
                      NULL,         --Rate
                      '2012-06-01', --First Payment Date
                      12,           --Number of Payments
                      12,           --Payments per Year
                      NULL,         --Days In Year
                      0,            --Future Value
                      NULL,         --Intrule
                      2,            --Decimals
                      NULL          --Last Payment Number
                  );

This produces the following result.

In this example we generate the payment schedule for a 5-year, 75,000 loan which commences on Sep 15th, having a first payment date of Jan 1st and using the 365/360 interest basis.

SELECT *
FROM wct.LPMTSCHED(   75000,        --Present Value
                      '09/15/2022', --Loan Date
                      NULL,         --Pmt
                      .18,          --Rate
                      '01/01/2023', --First Payment Date
                      60,           --Number of Payments
                      12,           --Payments per Year
                      365360,       --Days In Year
                      0,            --Future Value
                      NULL,         --Intrule
                      2,            --Decimals
                      NULL          --Last Payment Number
                  );

This produces the following result.