Mortgage amortization table
-
*** EDIT 2015-03-04: please be advised the code I posted originally below was in need of some improvement, and Solomon Rutzky has so graciously examined, tested, improved and posted a complete working version of my original code posted directly below. If you are looking for an amortization schedule using TSQL, then this post will help you, but please use Solomon's updated version on page 4 of this post HERE. You should also still read the entire thread to understand why the changes were needed. Thank you so much Solomon.
End of edit - Greg
Greetings folks. A friend of mine recently told me he loved SAS because it had an amortization function that could ouput an amortization table for a loan. I suggested SQL Server could do the same thing, and my suggestion was met with some skepticism. So, I thought it good excercise to try, mainly to keep my skills up, but also to help my argument. I believe I have achived success, but before I report back to my friend, I would like some guru's to take a look, and maybe point out any short comings of my code, or any areas possibly succeptible to errors. I am simply creating a 'LOANS' table, with basic header type info about the loan, and a 'LOAN_CUSTOMERS' table, with name only. The customer table is not needed for the procedure, but I included it since we would probably like to see it in some reports. Please note, any table named 'LOANS' or 'LOAN_CUSTOMERS' will be dropped, so change the table names if you need to. So here goes.
First create the LOANS and LOAN_CUSTOMERS tables...
--==== Create and populate a customers table
IF OBJECT_ID('LOAN_CUSTOMERS','u') IS NOT NULL
DROP TABLE LOAN_CUSTOMERS
GO
CREATE TABLE LOAN_CUSTOMERS
(
CUST_ID INT IDENTITY(1,1),
NM_FIRST VARCHAR(20),
NM_LAST VARCHAR(30),
DT_CUST SMALLDATETIME DEFAULT GETDATE()
)
GO
INSERT INTO LOAN_CUSTOMERS (nm_first,nm_last)
SELECT 'John','Doe' UNION ALL
SELECT 'Jane','Buck'
GO
--==== Create and populate a loans table
IF OBJECT_ID('LOANS','u') IS NOT NULL
DROP TABLE LOANS
GO
CREATE TABLE LOANS
(
LOAN_ID INT IDENTITY(1,1),
CUST_ID INT NOT NULL,
LOAN_START_DT SMALLDATETIME NOT NULL,
LOAN_FIRST_PMT_DT SMALLDATETIME NOT NULL,
LOAN_PERIODS INT NOT NULL,
LOAN_RATE FLOAT NOT NULL,
LOAN_PER_ANUM INT NOT NULL,
LOAN_AMT FLOAT NOT NULL,
LOAN_PMT FLOAT NULL,
LOAN_INTEREST FLOAT
)
GO
INSERT INTO LOANS (cust_id,loan_start_dt,loan_first_pmt_dt,loan_periods,loan_rate,loan_per_anum,loan_amt)
SELECT 1,GETDATE(),DATEADD(m,DATEDIFF(m,0,GETDATE())+2,0),360,.05,12,200000 UNION ALL
SELECT 2,GETDATE(),DATEADD(m,DATEDIFF(m,0,GETDATE())+2,0),180,.0375,24,200000
GO
Now, create a function to calculate the payment, which will be used to update the LOANS table with the payment and total interest of the loan.
--==== Check for existence of, and then create function dbo.fnPaymentCalc
IF OBJECT_ID('fnPaymentCalc','fn') IS NOT NULL
DROP FUNCTION fnPaymentCalc
GO
CREATE FUNCTION [dbo].[fnPaymentCalc]
(
@loan_amt FLOAT,
@periods INT,
@per_anum INT,
@rate FLOAT
)
RETURNS FLOAT
AS
BEGIN
DECLARE @payment FLOAT
DECLARE @calc_rate FLOAT
SELECT @calc_rate = @rate/@per_anum
SELECT @payment = @loan_amt*((@calc_rate*POWER((1+@calc_rate),@periods))/(POWER(1+@calc_rate,@periods)-1))
RETURN @payment
END
GO
Now update LOANS, using the function...
UPDATE LOANS
SET LOAN_PMT = dbo.fnPaymentCalc(loan_amt,loan_periods,loan_per_anum,loan_rate),
LOAN_INTEREST = (dbo.fnPaymentCalc(loan_amt,loan_periods,loan_per_anum,loan_rate)*loan_periods) - loan_amt
Now for the procedure. I am using the quirky update method. Admitedly, I still do not fully conceive how it works, but have used it enough, and tested against Excel, that I trust it's use.
--==== First check for and drop the procedure if it exists
IF OBJECT_ID('spGetAmort','p') IS NOT NULL
DROP PROCEDURE spGetAmort
GO
--==== Start of procedure
CREATE PROCEDURE spGetAmort (@loan_id INT)
AS
--==== Declare the local variables.
DECLARE @loan_amt FLOAT
DECLARE @payment FLOAT
DECLARE @Balance FLOAT
DECLARE @cur_interest FLOAT
DECLARE @cur_principle FLOAT
DECLARE @cum_interest FLOAT
DECLARE @cum_principle FLOAT
DECLARE @periods INT
DECLARE @per_anum INT
DECLARE @rate FLOAT
DECLARE @calc_rate FLOAT
--==== Create a temp table for the output of the procedure.
IF OBJECT_ID('TempDB..#amort','u') IS NOT NULL
DROP TABLE #amort
CREATE TABLE #amort
(
LOAN_ID INT NOT NULL,
PERIOD INT NOT NULL,
PMT_DT SMALLDATETIME,
BALANCE FLOAT NULL,
PAYMENT FLOAT NULL,
CUR_INTEREST FLOAT NULL,
CUM_INTEREST FLOAT NULL,
CUR_PRINCIPLE FLOAT NULL,
CUM_PRINCIPLE FLOAT NULL
)
--==== Put an index on the table to ensure the updates occur in the
-- proper order.
CREATE CLUSTERED INDEX IX_#amort_period
ON #amort(period)
--==== Set the local variables from the loans table.
SELECT @loan_amt = (SELECT loan_amt FROM loans
WHERE loan_id = @loan_id)
SELECT @periods = (SELECT loan_periods FROM loans
WHERE loan_id = @loan_id)
SELECT @per_anum = (SELECT loan_per_anum FROM loans
WHERE loan_id = @loan_id)
SELECT @rate = (SELECT loan_rate FROM loans
WHERE loan_id = @loan_id)
SELECT @calc_rate = @rate/@per_anum
--==== To set @payment, we are assuming the payment has already been
-- set in table LOANS using the above function. If it has not been
-- set, we can set it manually here by swapping with the commented
-- out method below.
SELECT @payment = (SELECT loan_pmt FROM loans
WHERE LOAN_ID = @loan_id)
--SELECT @payment = @loan_amt*((@calc_rate*POWER((1+@calc_rate),@periods))/(POWER(1+@calc_rate,@periods)-1))
--==== First populate a header row for the output. This will be period 0,
-- and will not show any activity
INSERT INTO #amort
SELECT @loan_id,0,NULL,@loan_amt,0,0,0,0,0
--==== Now populate a row for each period. The values will be updated later. A loop
-- is used here for those folks who do not have a tally table.
DECLARE @i INT
SET @i = 1
WHILE @i <= @periods
BEGIN
INSERT INTO #amort
SELECT
LOAN_ID = @loan_id,
PERIOD = @i,
PMT_DT = DATEADD(m,@i-1,loan_first_pmt_dt),
BALANCE = 0,
PAYMENT = @payment,
CUR_INTEREST = 0,
CUM_INTEREST = 0,
CUR_PRINCIPLE = 0,
CUM_PRINCIPLE = 0
FROM loans
WHERE loan_id = @loan_id
SELECT @i = @i + 1
END
--==== Now update the values in the table.
UPDATE a
SET @cur_interest = cur_interest = CASE WHEN period > 0 THEN @Balance * @calc_rate ELSE 0 END,
@cur_principle = cur_principle = CASE WHEN period > 0 THEN payment - @cur_interest ELSE 0 END,
@Balance = balance = CASE WHEN period > 0 THEN @Balance - @cur_principle ELSE balance END,
@cum_interest = cum_interest = CASE WHEN period > 0 THEN @cum_interest + @cur_interest ELSE cum_interest END,
@cum_principle = cum_principle = CASE WHEN period > 0 THEN @cum_principle + @cur_principle ELSE cum_principle END
FROM #amort a
SELECT * FROM #amort
OPTION (MAXDOP 1)
GO
Now, test it...
EXEC spGetAmort 1
Again, this is purely academic in nature, so please let me know of any issues.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less. -
There're a few optimizations I might make for performance but it looks real good, Greg.
If you notice, the final entry didn't really get as close to zero as bankers and little ol' ladies with umbrellas would like. 😛 Change all instances of FLOAT to a DECIMAL datatype with at least 15 decimal places like any good business calculator would have in it behind the scenes. My recommendation would be DECIMAL(28,18).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you so much Jeff. I was trying to figure out that last line. However, I found that when copied and pasted into Excel, then formatted as money, the balance showed 0. I know that is cheating though, so I will try the decimal datatype as you suggested, and let you know how it turns out.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less. -
Well, now I think the bankers and old ladies feathers will really be ruffled. I tried DECIMAL(28,18) and now I'm left with a positive balance of 35.991877150516022430. I guess its me against the procedure, but it may require opening some of the old business school books to really understand what is happening with the formula.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less. -
I would think CLR would be a great fit for doing an amortization calculation and/or schedule.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Thanks Kevin. Although I see lots of discussions about CLR's, I have absolutely no idea what that is.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less. -
Common Language Runtime. It is a way to do .NET flavor development from within the database engine. Very good for certain operations such as string manipulation, complex mathematics, parallelism operations, etc. Beware that you need to do things right or you can get suboptimal perf - just like TSQL. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Greg Snidow (7/6/2011)
Well, now I think the bankers and old ladies feathers will really be ruffled. I tried DECIMAL(28,18) and now I'm left with a positive balance of 35.991877150516022430. I guess its me against the procedure, but it may require opening some of the old business school books to really understand what is happening with the formula.There may be a bit more to do. I'll have to take a deeper look later on tonight.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Greg,
Did you change ALL the variables to DECIMAL??? Even the "period" related INTs?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (7/8/2011)
Greg,Did you change ALL the variables to DECIMAL??? Even the "period" related INTs?
I thought I did Jeff, but I will have to double check again. Your asking me makes me think you must know I missed something. It did seem a bit odd to go from essentially 0 to positive 35. I should have some time this weekend to look further into, so I'll let you know what happens. Thank you.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less. -
I've found the problem. Precision is lost during the multiplication. SQL Server multiplies differently than Excel does. Excel can handle up to 30 decimal places. When you multiply two 17 place decimals in SQL Server, you end up missing 5 significant digits to the right of the decimal... enough to cause the errors we're seeing due to compounding. Both FLOAT and 30 place decimals in SQL Server still come up with different values than Excel... and I believe that Excel is correct.
I'll play with this more tomorrow night. I'll probably submit this as a CONNECT item, as well.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you for putting my mind at ease Jeff. I have been trying everything under the sun, and I have been unable to come closer than the final balance of -1.67119651450776E-09 given by FLOAT. I was looking through my personal mortgage records to see if there was any clause about the final payment, but I could not find anything. I was almost about to put another case statement for the final balance row to just set it equal to 0 if it was less than a penny. Anyhow, I very much appreciate your good time you have spent considering my issue. Thanks again.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less. -
I brought it down to 1.20 E-31 by using 1073.64324602427796965698515822583333 for the loan_pmt (don't ask where I got that number from 🙂 ).
I think the major issue is the lost precision when using POWER inside [dbo].[fnPaymentCalc].
No matter what data type you use outside the POWER function, it'll always convert it to FLOAT with all the issues involved.
It might help to calculate the value of loan_pmt outside of SQL Server (e.g. by using a CLR).
IIRC the precision issue with POWER has already been discussed and MS did not really decide to resolve it...
Edit: the Windows calculator returned 1073.6432460242779696569851582251 leading to 6.64E-26
-
I think rearraging the calculation in dbo.fnPaymentCalc improves things a bit
Change
SELECT @payment = @loan_amt*((@calc_rate*POWER((1+@calc_rate),@periods))/(POWER(1+@calc_rate,@periods)-1))
to
SELECT @payment = @loan_amt*@calc_rate + @loan_amt*@calc_rate/(POWER((1+@calc_rate),@periods)-1)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hello Greg!
I love you your logic regarding the Mortgage Amortization table. I was wondering if there was a way to tweak the logic to allow for multiple loans? I'm relatively new to SQL world and understand the logic somewhat. I tried doing a union all with two separate loans, but I still only get the first one. Does it have to do with the Loan_ID Identity (1,1)?
Any suggestions you can provided will be greatly appreciate! Thanks!
Viewing 15 posts - 1 through 15 (of 97 total)
You must be logged in to reply to this topic. Login to reply