Home Forums SQL Server 2008 T-SQL (SS2K8) Mortgage amortization table RE: Mortgage amortization table

  • March 1, 2015 at 10:45 pm

    #1781023

    dwain.c (2/24/2015)

    There's a loan amortization example here: Exploring Recursive CTEs by Example[/url]

    It won't be as fast as a QU approach, but the example does handle multiple loans and if I recall correctly it also handles the final penny rounding.

    So I finally had a chance to test your rCTE method and had a few notes. Again, just like with the items I mentioned related to Greg's method, I am not being negative and this doesn't take anything away from what is clearly an excellent article.

  • The final payment should calculated, not assumed to be the original payment calculation. It needs to be (Interest applied to beginning balance of the final month) + (beginning balance of the final month). I believe this is fixable in your current model since you seem to be handling final payment details via

    CASE PaymentNo + 1 WHEN Period THEN

  • The monthly payment amount and monthly interest amounts are not being calculated correctly due to not enough decimal places for R. In the CROSS APPLY, R is being CAST to MONEY which only allows for 4 decimal places. It should have at least 8 decimal places. I would recommend using DECIMAL(20, 18). tried changing the datatype to be that but then started getting the following error:

    Types don't match between the anchor and the recursive part in column "Balance" of recursive query "Payments".

  • For the InterestAPR field I would suggest using DECIMAL(8, 5) instead of FLOAT, though this might be a non-issue if you can get R to be CAST into a DECIMAL(20, 18). But still.

    • I hope this helps. Take care,

    Solomon...

    SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR