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

  • March 1, 2015 at 10:16 am

    #1780966

    Greg Snidow (2/27/2015)

    ScottPletcher (2/26/2015)

    Change to money rather than more decimal places. I believe the law now restricts to 4 decimal places when calculating most interest accumulation.

    Scott, I tried money, float, decimal and real in various combinations to no avail. Actually the real datatype got me closest 0 at the last payment, and it was still off by $1.09. I think it all goes back to a comment Jeff made earlier, and that is that bankers use software that accounts for slight differences in the end.

    Ok, so I have done a bunch of research and testing and have some interesting info to share.

    First, I found 28 online amortization calculators. There might be more but there is only so much time in a day (and even less-so with 3 young kids ;-)).

    Now, I have taken a closer look at the "quirky update" method of doing this that is posted on Page 1 of this topic and there are a few notes to share (and if it is not clear, this is purely constructive and not meant in any snide or negative manner).

    • The datatypes used need to be DECIMAL. Using MONEY for the currency fields and parameters is perfect as that is mostly a DECIMAL(19, 4) with slightly less capacity. I am not sure if 4 decimal places is enough for the InterestRate so MONEY and SMALLMONEY might not be appropriate; I use DECIMAL(8, 5).
    • FLOAT and REAL datatypes are too imprecise as they allow for very minute amounts to be added to the value and that is outside of your control. But the fact that you got better results using REAL actually indicates a problem with your formula and not with the datatype.
    • No rounding is being done at all. This is not how any actual system would work. Banks are not going to deduct fractional amounts, or split Principal and Interest on a half-penny. Rounding needs to be done, and it should be "Banker's Rounding", or in .NET terms, using MidpointRounding.ToEven in Math.Round, which is also the default. An accurate, SQLCLR-based function to do this will be available in the next release of SQL#[/url].
    • Interest is not being calculated on a per-month basis: it is being calculated as an aggregate:

      LOAN_INTEREST = (dbo.fnPaymentCalc(...)*loan_periods) - loan_amtThis is not how it should be calculated, and is similar to how all of those online calculators got it wrong. Interest is calculated each month and rounding is applied. A penny up in one month, a penny down in another, etc., but not always evenly applied. Also, the Principal is not simply divided by the number of payments. The main factor is the Interest and then deduct the Principal from that each month. The final month will often have a small amount of Principal left over that needs to be added to the final payment. And this is not just a penny or two. Go to that dinkytown.net link and use the following values:

      Principal = 53707

      Interest = 15.497

      Years = 13

      Then click "View Report" and go to the bottom. The final payment is $2.00 more than the initially calculated amount. (I would normally also recommend trying the Math_CompoundAmortizationSchedule TVF in SQL#, but the current version--3.3--has some minor issues that I have now corrected and will appear in the next release).

    • Related to the prior point, when testing and verifying, you need to check month to month as there can be those rounding issues. But you need to add all rows up to get both Total Paid and Total Interest and see if they match.
    • Related to a prior point: the final payment is 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).

    This might all be fixable in the current quirky update method with some changes, but I don't have time to try that myself.

    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