Login     Register

        Contact Us     Search

Writing your own functions using XLeratorDB

Jan 11

Written by: Charles Flock
1/11/2009 4:39 PM  RssIcon

Build your own mortgage calculator using XLeratorDB/financial.

One of the exciting things about XLeratorDB is that you can use these functions to create your own user-defined functions in SQL Server. This permits you to put an awful lot of business intelligence on the database server in a very short period of time.
In this article I will show you how to create mortgage amortization schedule using a simple table valued function (TVF) and the PMT, PPMT, and IPMT functions in XLeratorDB. This is something I have done hundreds of time in EXCEL, but I think that you will find, as I did, that SQL Server is actually a better tool for this.
A quick review. The PMT function calculates the periodic payment function for an annuity (for more information go to the PMT documentation). The PPMT function calculates the principal payment for an investment for a given period (for more information go to the PPMT documentation). The IPMT function calculates the interest payment for and investment for a given period (for more information go to the IPMT documentation). The amortization schedule that we are going to create will show the period, the payment amount, the principal payment amount, and the interest payment amount. We will use CTE to generate the periods.
We will create the variables @PV, @RATE, @NUMYEARS, @FV, @PAYTYPE to store the variables used in this function. The @PV is the amount of the mortgage. We will assume that the payments are monthly. The @FV is the residual value. The @PAYTYPE identifies whether the payment is made at the beginning of the period (1) or at the end of the period (0).
if (wct.FunctionExists('dbo','AMORT') <>0) drop function dbo.AMORT;
( @PV as Float,
 @RATE as Float,
 @NUMYEARS as Float,
 @FV as Float,
 @PAYTYPE as Float
with mc as
    select cast(1 as float) as [Period]
    ,cast(wct.PMT(@RATE/12,12*@NUMYEARS,-@PV,@FV,@Paytype) as float) as [Payment Amount]
    ,cast(wct.PPMT(@RATE/12,1,12*@NUMYEARS,-@PV,@FV,@Paytype) as float) as [Principal Payment Amount]
    ,cast(wct.IPMT(@RATE/12,1,12*@NUMYEARS,-@PV,@FV,@Paytype) as float) as [Interest Payment Amount]
    union all
    select Period + cast(1 as float)
    ,cast(wct.PMT(@RATE/12,12*@NUMYEARS,-@PV,@FV,@Paytype) as float)
    ,cast(wct.PPMT(@RATE/12,Period + cast(1 as float),12*@NUMYEARS,-@PV,@FV,@Paytype) as float)
    ,cast(wct.IPMT(@RATE/12,Period + cast(1 as float),12*@NUMYEARS,-@PV,@FV,@Paytype) as float)
    from mc
    where Period < 12*@NUMYEARS   
)     select *
    From mc
wct.FunctionExists is part of XLeratorDB/util and is available for free.
Let’s say we wanted to generate a schedule for a $400,000, 30-year fixed rate mortgage at 5.0%. We would simple enter:
select *
from AMORT(400000,0.05,30,0,0)
order by Period
option (maxrecursion 720)

Which would return (I have not included all 360 rows):
Period                 Payment Amount         Principal Payment Amount Interest Payment Amount
---------------------- ---------------------- ------------------------ -----------------------
1                      2147.28649204856       480.619825381892         1666.66666666667
2                      2147.28649204856       482.62240798765          1664.66408406091
3                      2147.28649204856       484.633334687599         1662.65315736096
4                      2147.28649204856       486.652640248797         1660.63385179976
5                      2147.28649204856       488.680359583167         1658.60613246539
6                      2147.28649204856       490.716527748097         1656.56996430046
7                      2147.28649204856       492.761179947048         1654.52531210151
8                      2147.28649204856       494.81435153016          1652.4721405184
9                      2147.28649204856       496.87607799487          1650.41041405369
10                     2147.28649204856       498.946394986515         1648.34009706204
11                     2147.28649204856       501.025338298959         1646.2611537496
12                     2147.28649204856       503.112943875204         1644.17354817335
13                     2147.28649204856       505.209247808018         1642.07724424054
14                     2147.28649204856       507.314286340551         1639.97220570801
15                     2147.28649204856       509.42809586697          1637.85839618159
16                     2147.28649204856       511.550712933082         1635.73577911548
17                     2147.28649204856       513.68217423697          1633.60431781159
It is interesting to note that the maxrecursion option needs to be included in the SELECT statement and thus exists outside of the TVF, which was surprising to us. So, it’s important to note that if the mortgage is going to be for more than 60 years, the maxrecursion option needs to be increased to the number of years multiplied by 12.
Why do I say that this is easier than EXCEL? If I had to do this in EXCEL, I would generally have entered my constants in a few cells in the upper left of my worksheet and then copied formulas into 1080 cells and used the Data Fill Series option to create the periods. None of that is too hard. But what if I want a 40 year mortgage? Or a 15-year mortgage? What if I want to capture information about certain periods? Let’s say I wanted to know what the payments would be (in total) for periods 13 through 24. In my opinion, it gets very hard to do this kind of thing in EXCEL. Whereas in SQL Server we would simply enter:
select sum([PAYMENT AMOUNT]) as Payments
from AMORT(400000,0.05,30,0,0)
where period between 13 and 24
option (maxrecursion 720)

which would return:
Payments               Principal              Interest
---------------------- ---------------------- ----------------------
25767.4379045827       6203.39134686265       19564.0465577201
(1 row(s) affected)

If you decided to add dates to the TVF, it would then be possible to see what the interest payments would be for each calendar-year or each 12-month period, simply by using the SUM function and group by the year in the date.
There really is no limit to what you can do when you have the equivalent of the EXCEL functions available to you in SQL Server.


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service