Mortgaging the future
Note:
Over
here I lamented about the removal of Date and Time and mentioned that had
been working on a post extolling their virtues. I'm going to salvage what I can
of that (and my self-respect for having been caught off guard by this) with the
following. This concept will hopefully appear again soon without Date data type
feature as an article on SQLJunkies.
The other day I asked cyberspace what interesting yet practical mathematical
problems they had written programs for. The answers varied widely, but one that
reminded me of my own experiences was generate an amortization schedule. You
would use such a schedule to show the progression of some significant loan where
periodic interest is charged and payments are made. A common use of an
amortization schedule is a mortgage -- a loan commonly associate with the
purchase of real estate like your house. The math for such a schedule
itself is fairly easy and the programming involved is not overly complex.
Unless you decide you want to write it in T-SQL, that is.
Why? As a somewhat declarative set-based language, T-SQL is good at
assembling data into results expressed as a matrix. It is not as rich as we
might like, however, for doing procedural computations. Also, many SQL dialects
make working with dates seem awkward to programmers better versed in the .NET
family of languages. So it seemed like an interesting challenge to write a T-SQL
query that would do some procedural computation. It also afforded me the
opportunity to learn more about Yukon's new features.
So here's what I set out to do. I wanted to write a Stored Procedure that
took the following as parameters:
- The principal loan amount
- The annual interest rate on the loan
- The duration of the loan (in years)
- The number of payments per year.
My procedure would compute the periodic payment for the loan and generate the
amortization schedule as an XML document fragment. The stored procedure would
also show payment due dates. Turns out that this last part was the most
difficult thing to with T-SQL, but it was made much easier (for me at least)
thanks to T-SQL's Date data type.
Let's take a look at the Query. Due to system limitations, I can't offer the
whole script as one file, but if you'd like it, please contact me through my
Weblog at
http://www.sqljunkies.com/weblog/ktegels/
The procedure starts simply enough by define the parameters we expect to
receive.
ALTER procedure dbo.procGetAmmoritzationScheduleAsXML
(
@bal numeric(13,2) = 100000.0
, @irate numeric(13,2) = 0.05
, @dur int = 30
, @freq int = 12
)
as
We also need to declare a few local variables before using them.
declare @n int -- toal # of payments
declare @index int -- loop controller
declare @pmt numeric(13,2) -- periodic repayment amount
declare @prate float(53) -- periodic interest rate
declare @pratep1 float(53) -- prate plus 1.
declare @icharge numeric(13,2) -- amount of interest charged
declare @curr_bal numeric(13,2) -- balance after each repayment
declare @ibal numeric(13,2) -- intermediate balance
declare @error int -- last error #
declare @month_span int -- number of months between payments
declare @day_span int -- number of days between payments
Next, I want to validate that the values of the parameters themselves
actually make sense for my procedure to use.
-- Validate that we got good parameters
-- Loan durations must be between 1 and 30 years
if (@dur < 1) or (@dur > 30) or (@dur is null)
begin
raiserror('Invalid Duration',18,1)
set @error = @@error
goto ERROR
end
-- loan amounts must be between one-thousand and
-- ten-million currency units
if (@bal < 1000.0) or (@bal > 10000000.0) or (@bal is null)
begin
raiserror('Invalid Loan Amount',18,2)
set @error = @@error
goto ERROR
end
-- must have 1,2,4,12,26 or 52 payment per year
-- repayments per year.
if not (@freq in (1,2,4,12,26,52))
begin
raiserror('Invalid Payment Frequency',18,4)
set @error = @@error
goto ERROR
end
With the preliminaries out of the way, I can start to do some interesting
work. the first thing I want to do is figure out when payments are doing to be
due. That fairly straight forward: if the first payment is due just once a year,
the first payment is due on year from the loan date. If the the loan is to be
repaid more frequently, say, monthly, then the first payment is due one month.
In my solution, the number of months between payments is stored in @mspan and
the number of days between payments is stored in @dspan. There is no need to
compute both: Monthly payment intervals only make sense if the there are 12 or
few payments per year. Since the number of days in a month varies, it doesn't
make sense to me to try to computer the number of days before between such
payments. When the number of days between payments is fixed, then it makes sense
to just use the number of days instead.
Note that I assume the loan starts on the current system day. You could, of
course, define that as a parameter if you like. It made this assumption show I
could show you how to convert a T-SQL DateTime, which is what GetDate() in Yukon
returns, to a Yukon Date data type instance. Nothing magic, but it demonstrates
that you will to do a little bit of work to cast date types around.
declare @dspan int
declare @mspan int
declare @due_date date
if @freq < 26
set @mspan = 12/@freq
else
set @dspan = (case @freq when 26 then 14 when 52 then 7 end)
-- Convert the system date to a CLR-style date
set @due_date = convert(date,convert(nvarchar(64),GetDate()))
The next step is to convert the annual interest rate into the interest rate
that would be charged each period.
-- how many total payments?
set @n = @dur*@freq
-- this computes the interest rate charged
-- each load period
if @irate > 1.0
set @prate = (@irate/100.0)/@freq
else
set @prate = @irate/@freq
set @pratep1 = 1.0+@prate
Computing the actual periodic payment is also fairly straight
forward, assuming you know the right formula. if you don't know the formula, or understand that one, you can
learn more about it
here.
-- Compute the amount of each repayment
set @pmt= ((@bal*power(1.0+@prate,(@dur*@freq)))*@prate)
/(power(1.0+@prate,@dur*@freq)-1)
A little bit of problem we're going to run into is where to save the result
of each interest and payment calculation. I took the easy way out and created a
temporary table to save those results to.
-- Create a scratch table to hold our working
-- results. Each repayment cycle generates a new row in
-- this table.
create table #amortsched
(
payment_num int
, due_date date
, icharge numeric(14,2)
, balance numeric(14,2)
)
Now we can simulate the behavior of the loan easy. This is the one part of
this script, though, I that I don't like. See, I'd rather have a more formal
construct for controlling the execution of a loop. When we're simulating the
loan, we want to repeat the simulation for each transaction. But T-SQL, being a
set-oriented language doesn't have such a construct. We have to make due with
the potential spaghetti-code of gotos instead.
set @index = 1
set @curr_bal = @bal
LOOPTOP:
set @icharge = @curr_bal * @prate
set @ibal = (@curr_bal + @icharge) - @pmt
The only unusual part of the T-SQL code for Yukon is how I compute the
current due date. Ah, Yukon makes this so easy: All I have to do is call the
AddDays or AddMonths method of the current date and assign the result back the
current date. The syntax for calling these methods is to prefix them with the
"::" accessor symbols.
if @mspan is null
set @due_date = @due_date::AddDays(@dspan)
else
set @due_date = @due_date::AddMonths(@mspan)
All that's left to do is record the transaction results to the temporary
table and progress to the next payment cycle if appropriate.
insert into #amortsched(payment_num,icharge,balance,due_date)
values (@index,@icharge,@ibal,@due_date
set @curr_bal = @ibal
set @index = @index +1
if @index > @n goto LOOPEXIT
goto LOOPTOP
LOOPEXIT:
I had previously stated that I wanted my results in an XML document fragment.
i might want that for any number of reasons, but specifically, I wanted to just
use these results along with a simple transform in an ASP..NET XML control.
Generating the XML is easy, too.
select 1 as tag
, null as parent
, @pmt as [schedule!1!payment]
, @curr_bal as [schedule!1!lastPayment]
, null as [payment!2!dueDate]
, null as [payment!2!interestCharged]
, null as [payment!2!balance]
union
select 2 as tag
, 1 as parent
, null
, null
, due_date::ToString()
, icharge
, balance
from #amortsched
order by [payment!2!dueDate]
for xml explicit
All that's left to do is drop the temporary table since we are now done with
it.
drop table #amortsched
ERROR:
return @error
So, does it work? Of course! Just create a database to house the procedure,
then execute it like this:
-- $250,000 year loan at 5% for 15 years with monthly payments
exec dbo.procGetAmmoritzationScheduleAsXML 250000,.05,15,12
The resulting XML looks just like this.
<schedule payment="1976.98" lastPayment="1.18">
<payment dueDate="2004-06-20" interestCharged="1041.67"
balance="249064.69" />
<payment dueDate="2004-07-20" interestCharged="1037.77"
balance="248125.48" />
<payment dueDate="2004-08-20" interestCharged="1033.86"
balance="247182.36" />
...
<payment dueDate="2019-03-20" interestCharged="24.51"
balance="3930.55" />
<payment dueDate="2019-04-20" interestCharged="16.38"
balance="1969.95" />
<payment dueDate="2019-05-20" interestCharged="8.21"
balance="1.18" />
</schedule>
So as you can see, while T-SQL may not be the most elegant tool for this type
of task, it can certainly get the job done. Could I have done a more complex
example? Sure! But this one covers my key points:
- T-SQL can do handle procedure calculations, albeit in a less-than-modern
way
- Start with SQL Server 2000, generate XML document fragments with T-SQL
became much easier.
- By using Yukon's build in Date function, its feels a lot cleaner to
develop date computation routines.
Would I choose to do this kind of calculation with a production SQL Server
and application? Depends on many factors, but at least know I know I've got the
option of doing so without killing the machine. That's a feeling I can live with
-- I haven't managed to mortgage my future to any application, business-tier or
middle-tier component code.