Login     Register

        Contact Us     Search

Creating amortization schedules for variable rate loans in SQL Server

Jun 4

Written by: Charles Flock
6/4/2021 9:26 AM  RssIcon

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;

This produces the following result.

Let’s take a closer look at what’s going on in the next CTE. In the tbl CTE, we simply put the @sch table in mthstart order, assigning a ROW_NUMBER() value to each ordered row.

SELECT ROW_NUMBER() OVER (ORDER BY mthStart) as rn,

           mthstart,

           rate

FROM @sch s

It produces a resultant table that looks like this.

<

The nested CTE, ann, is a recursive CTE. We need a recursive CTE because each row subsequent to the anchor row requires calculated values from the preceding row. The anchor row gets things started.

SELECT rn,

       mthstart,

       rate,

       @bal as pv,

       wct.PMT(rate / @pmtperyr, @nper, -@bal, 0, 0) as pmt

FROM tbl

WHERE rn = 1

The anchor row gets the row number (rn), starting month (mthstart) and annual interest rate from the tbl CTE where rn = 1. It gets the starting balance for the loan from the @bal variable. It calls the XLeratorDB PMT function and calculates the periodic payments associated with the start of the loan. Note that -@bal is passed into PMT function.

We use this SQL in the recursive part of the CTE.

UNION ALL

SELECT rn,

       mthstart,

       rate,

       PV,

       wct.PMT(n.rate / @pmtperyr, @nper - n.mthstart + 1, -PV, 0, 0) as pmt

FROM

(

    SELECT ann.rn + 1 as rn,

           tbl.mthstart,

           tbl.rate,

           wct.PV(ann.rate / @pmtperyr, @nper - tbl.mthstart + 1, -ann.pmt, 0, 0) as pv

    FROM ann

        INNER JOIN tbl

            ON ann.rn + 1 = tbl.rn

) n

We add 1 to the row number from the previous row. We get the starting month and the interest rate from the tbl CTE where the tbl row number is equal to ann row number + 1. We calculate the present value of the loan as at the beginning of the starting month for the remaining periods using the XLeratorDB PV function. Finally, we calculate the new periodic payment using the rate from the tbl CTE, the remaining payments as the number of periods and the PV of the remaining payments, using the XLeratorDB PMT function.

Adding the PMTSCHED function to the mix

The next step is to call the XLeratorDB PMTSCHED function. Since PMTSCHED is a table-valued function we can use a CROSS APPLY to generated the full schedule. The way CROSS APPLY works is that the table-value function is applied to every row in the resultant table from our CTE. Since PMTSCHED returns multiple rows, we need to define that start and end rows for the function. We can do this easily using standard SQL.

We will replace the current SQL at the end of the nested CTE with following SQL.

SELECT k.num_pmt + (n.mthStart - 1) as num_pmt,

       k.amt_prin_init as [Beginning],

       n.pmt as [Payment],

       k.amt_int_pay as [Interest],

       k.amt_prin_pay as [Principal],

       amt_prin_end as [Ending]

  FROM (   SELECT mthstart,

                  pv,

                  pmt,

                  LEAD(pv, 1, 0) OVER (ORDER BY RN) as FV,   

                 --get the PV from the next row asn pass to PMRSCHD as FV

                  LEAD(mthstart, 1, @nper + 1) OVER (ORDER BY rn) - mthstart as numpmts

                 --calculate the remaining number of payments as at the next row

             FROM ann) n

 CROSS APPLY wct.PMTSCHED(pv, pmt, numpmts, FV, 0) k;

Let’s look at what’s happening in the SQL. We are going to return 5 rows from our CTE:

For each row we want to obtain mthstart, pv and pmt for the current row. We also want to return the pv for the next row and pass it into the PMTSCHED function as FV parameter. When it’s the final row, we pass 0 to the FV parameter. We also want to return the mthstart from the next row. When it’s the final row, we will return the @nper value plus one. We will then subtract the mthstart from the current row and pass that value to numpmts parameter in the PMTSCHED function. Here’s summary of what’s in the results table and gets passed to PMTSCHED.

Put the whole thing together

Now, when we put all the SQL together, we get the full amortization table for the life of the loan.

Try it out for yourself

You should download the 15-day trial of XLeratorDB today and try this out for yourself. XLeratorDB can be installed on SQL Server 2012, 2014, 2016, 2017 and 2019. The trial is a fully functioning version of XLeratorDB including over 900 functions specifically written for SQL Server. You can find documentation for all these functions with examples that can be copied into SSMS on the documentation section of Westclintech.com. Have questions? Send us an email at support@westclintech.com.

Copyright ©2021

Tags:
Categories:

Search Blogs

Copyright 2008-2022 Westclintech LLC         Privacy Policy        Terms of Service