Writing your own functions using XLeratorDB
Jan
11
Written by:
Charles Flock
1/11/2009 4:39 PM
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;
go
CREATE FUNCTION AMORT
( @PV as Float,
@RATE as Float,
@NUMYEARS as Float,
@FV as Float,
@PAYTYPE as Float
)
RETURNS TABLE
AS
RETURN
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
go
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
,SUM([PRINCIPAL PAYMENT AMOUNT]) as Principal
,SUM([INTEREST PAYMENT AMOUNT]) as Interest
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.