SQL Server PMTSCHED Function

Updated 2023-10-06 23:12:52.507000

Description

Use the table-valued function PMTSCHED to generate an amortization schedule for a loan with no odd periods. The amortization schedule includes the payment number, the principal amount at the beginning of the period, the interest amount for the period, the principal payment for the period, and the ending principal amount.

Syntax

SELECT * FROM [westclintech].[wct].[PMTSCHED] (
  <@PV, float,>
 ,<@pmt, float,>
 ,<@NumPmts, int,>
 ,<@FV, float,>
 ,<@Pay_type, 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.

@pmt

The periodic loan payment. @pmt is an expression of type float or of a type that can be implicitly converted to float.

@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.

@Pay_type

Identifies whether payments are made at the beginning of the period (1) or at the end of the period (0).

Return Type

table

Remarks

@PV, @pmt, and @FV (when it is not zero) should have the same sign.

The rate value is equal to wct.RATE(@Numpmts,-@pmt,@PV,-@FV,@Pay_type,NULL).

Examples

Generate an amortization schedule for loan with a principal amount of 100,000 with 12 payments of 8606.64.

SELECT *
FROM wct.PMTSCHED(   100000,  --pv
                     8606.64, --pmt
                     12,      --numpmts
                     0,       --FV
                     0        --pay_type
                 );

This produces the following result.

Generate an amortization schedule for loan with a principal amount of 32,000 with 60 payments of 600.95.

SELECT *
FROM wct.PMTSCHED(   32000,  --pv
                     600.95, --pmt
                     60,     --numpmts
                     0,      --FV
                     0       --pay_type
                 );

This produces the following result.

Generate an amortization schedule for 250,000 loan with 50,000 balloon payment with 36 payments of 6,533.74 using a CROSS APPLY.

SELECT k.*
FROM
(
    SELECT 250000 as pv,
           6533.74 as pmt,
           36 as numpmts,
           50000 as FV,
           0 as pay_type
) n
    CROSS APPLY wct.PMTSCHED(n.pv, n.pmt, n.numpmts, n.fv, n.pay_type) k;

This produces the following result.

Generate an amortization schedule for a 10,000 loan with 12 monthly payments of 869.95, made at the beginning of the period using CROSS APPLY.

SELECT k.*
FROM
(
    SELECT 10000 as pv,
           869.95 as pmt,
           12 as numpmts,
           0 as FV,
           1 as pay_type
) n
    CROSS APPLY wct.PMTSCHED(n.pv, n.pmt, n.numpmts, n.fv, n.pay_type) k;

This produces the following result.

    num_pmt          amt_prin_init            amt_int_pay           amt_prin_pay           amt_prin_end
----------- ---------------------- ---------------------- ---------------------- ----------------------
          1                  10000       72.2834188150475       797.666581184953       9202.33341881505
          2       9202.33341881505       65.9682214653596        803.98177853464       8398.35164028041
          3       8398.35164028041       59.6030261359176       810.346973864082       7588.00466641632
          4       7588.00466641632       53.1874369882901        816.76256301171       6771.24210340461
          5       6771.24210340461       46.7210550501161       823.228944949884       5948.01315845473
          6       5948.01315845473       40.2034781903678       829.746521809632        5118.2666366451
          7        5118.2666366451       33.6343010942358       836.315698905764       4281.95093773933
          8       4281.95093773933       27.0131152381475       842.936884761853       3439.01405297748
          9       3439.01405297748       20.3395088640143       849.610491135986        2589.4035618415
         10        2589.4035618415        13.613066954043       856.336933045957       1733.06662879554
         11       1733.06662879554       6.83337120446595       863.116628795534       869.950000000005
         12       869.950000000005  -4.77484718430787E-12       869.950000000005                      0