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

    @jeff:

    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



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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