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