Westclintech

Because there are never enough ways to generate amortization schedules. A look into the XLeratorDB PMTSCHED function, some time-value of money math, nested common table expressions (CTE), windowing functions and a CROSS APPLY.

We get a lot of traffic on the web site for ‘loan amortization’ and, as a result, we have a lot of functions to perform the most common calculations. There are always new twists and nuances which aren’t fully captured in these functions, but since they run using SQL there is always a way to incorporate these twists and nuances and achieve the desired result. In this article I will show you how to take the XLeratorDB PMTSCHED function, which is designed for fixed-rate loans, and get it to calculate the amortization for variable rate loans. For purposes of this article, the term loan and annuity are used interchangeably.

Basic fixed rate annuity math

This is generally how things are taught in school in terms of generating a loan amortization schedule. First, calculate the periodic payment using the PMT function.

DECLARE @bal as float = 100000 ;
DECLARE @rate as float = 0.05 ;
DECLARE @nper as int = 360 ;
DECLARE @pmtperyr as float = 12 ;
DECLARE @pmt as float = wct . PMT ( @rate / @pmtperyr , @nper , - @bal , 0 , 0 );

 
 
SELECT @pmt as pmt ;

This produces the following result.

Then we do some very simple arithmetic to calculate the first line of the amortization schedule.

SELECT
   @bal as [Beginning],
   @pmt as [Payment],
   @bal * @rate / @pmtperyr as [Interest],
   @pmt - @bal * @rate / @pmtperyr as [Principal],
   @bal - (@pmt - @bal * @rate / @pmtperyr) as [Ending];

Which produces:

The ending balance then becomes the beginning balance for the next line in the schedule.

Introducing the XLeratorDB PMTSCHED function

We created the PMTSCHED table-valued function in XLeratorDB to simplify this process. Using this function, we can just enter the appropriate parameters and the schedule is automatically generated.

SELECT TOP 5
   num_pmt as [Num],
   amt_prin_init as [Beginning],
   amt_int_pay as [Interest],
   amt_prin_pay as [Principal],
   amt_prin_end as [Ending]
FROM
   wct.PMTSCHED(@bal,@pmt,@nper,0,0)
ORDER BY
   [Num];

This produces the following result.

Using the PV function to generate an amortization schedule

To generate the appropriate amortization schedule, it’s helpful to understand the relationship between the PMT and PV functions. At the beginning of this article, the first thing that we did was calculate the periodic payment for the given input parameters. Without getting too deeply into the math, the PMT value can then be substituted into the PV function and will return the beginning balance.

SELECT
       wct.PV(@rate / @pmtperyr,@nper,-@pmt,0,0) as [Beginning]

This produces the following result.

Thus, it is possible to generate the schedule simply by using the PV function and a numbers or tally table. In this example we produce the same results as those returned in PMTSCHED.

SELECT
   Num,
   Beginning,
   @pmt - (Beginning - ending) as Interest,
   Beginning - Ending as Principal,
   Ending
FROM (
   SELECT
       Num,
       wct.PV(@rate / @pmtperyr,@nper - num + 1,-@pmt,0,0) as [Beginning],
       wct.PV(@rate / @pmtperyr,@nper - num,-@pmt,0,0) as [Ending]
   FROM (VALUES
       (1),(2),(3),(4),(5)
       )n(Num)
   )nn

This produces the following result.

Variable Rates

But what happens when the interest rate is not a fixed rate? Let’s look at an example where the rates on a 30-year monthly annuity (or loan) look like this:

We can take advantage of PV math to calculate the principal balance at the point in time when the rate changes and calculate the new periodic payment based on that new principal amount.

There are many ways to do this but for the purposes of this article I am going to use a table variable and then populate it with the rates and the months in which those rates become effective.

DECLARE @sch TABLE (
   mthStart int NOT NULL,
   rate float NOT NULL,
   PRIMARY KEY (mthStart)
   );
 
INSERT INTO
   @sch
SELECT
   *
  FROM (VALUES
   (1, .05),
   (61, .04),
   (121, .03),
   (241, .02),
   (301, .04)
   )n(mthStart, rate);

Using a common table expression (CTE) to calculate the future values

Having done that, we can use a nested CTE (common table expression) to calculate the payments and principal balance over the life of the loan.

--CTE
with tbl
--First part: assign row numbers to @sch
  as (SELECT ROW_NUMBER() OVER (ORDER BY mthStart) as rn,
             mthstart,
             rate
        FROM @sch s),
--Nested (recursive) CTE
     ann
  as (SELECT rn,          
           --row number
             mthstart,           
           --starting month
             rate,               
           --rate  
             @bal as pv,  
           --starting balance    
             wct.PMT(rate / @pmtperyr, @nper, -@bal, 0, 0) as pmt   
           --periodic payment
        FROM tbl          
       WHERE rn = 1              
      --from the first row in tbl becomes anchor
   --recursive part
    UNION ALL
    SELECT rn,
           mthstart,
           rate,
           PV,
           wct.PMT(n.rate / @pmtperyr, @nper - n.mthstart + 1, -PV, 0, 0) as pmt 
          --calcuate the new periodic payment
    FROM
    (
        SELECT ann.rn + 1 as rn, 
             --add 1 to previous row number
               tbl.mthstart,            
             --getting the starting month from the tbl CTE
               tbl.rate,                
             --get the rate for the starting month from the tbl CTE
               wct.PV(ann.rate / @pmtperyr, @nper - tbl.mthstart + 1, -ann.pmt, 0, 0) as pv    
             --calculate the balance of the loan as at the commencement of the starting month
        FROM ann
            INNER JOIN tbl
                ON ann.rn + 1 = tbl.rn
    ) n
   )
SELECT cast(rn as int) as rn,
       mthstart,
       rate,
       pv,
       pmt
FROM ann;